How to Perform Analysis with SQL Server Analysis Services Project (Step by Step Instruction)

How to Create Cubes, Dimensions and Measures in SQL Server Business Intelligence

What is the use of data if it is not for Analysis. What is the use of RDBMS if it is not providing the Analytics functionality. Here we take a look at the SQL Services Project of SQL Server 2008

There are multiple open source softwares like Pentaho / Jasper etc. that provide the functionality we will see below but nothing comes with the ease like SSAS

To start, open Business Intelligence Studio and create Analysis Services Project

 The wizard will create a project aling with required folders - Dimensions and Cubes the two most important of any Business Intelligence project

Creating Cube is easy in SQL Server. Right click on Cubes and select New Cube - a beautiful Wizard will guide

Select the Generate tables option

The below step helps you in Creating new Dimensions - OLAP is all about measures (Facts and Dimensions)

Facts and Dimensions in SQL Server

Time dimension is created and filled automatically by SQL Server based on settings

The above figure shows the measures and dimensions created by SQL SERVER.

 

 

Now we have the logical data model, we can create the Schema . SQL SERver Schema Creation Wizard helps to  create the required schema

How to import Large files (TXT / CSV) to SQL Server 2008 using SSIS

 SQL Server's Integration Services provides the functionality to laod

This can be done from the SQL Server by selecting the integration services project

The following

How to import CSV / Excel file to SQL Server Table

How to Load Excel File / CSV File to a SQL Server Table / Create SQL Server Table from Excel File

Step 1: Select Import and Export Wizard from Program


Step 2: Select Import and Export Wizard from Program

 Step 3: Select Flat File as Source

Step 4: Select the CSV / Text file

Step 5: Choose the Server

Step 6: Choose Database

Step 7: Specify Schema and Table Names

 Step 8: Run the Pachage

 

Specified column precision 50 is greater than the maximum precision of 38.

What is the Preferred Data Type for Price and Amount Fields

This error occurs when you are trying to define a Decimal field with precision more than 38. If you would require more precission, please use any of the following:

[Sales Price] [Numeric](50,2) NULL,

[Quantity] [int](50) NULL,

[Amount] [Numeric](50,2) NULL,

Money
Float



CREATE TABLE [dbo].[Transact.Details](
[Store ID] [varchar](50) NULL,
[Customer ID] [varchar](50) NULL,
[Transaction ID] [varchar](50) NULL,
[Transaction Date] [varchar](50) NULL,
[Category ID] [varchar](50) NULL,
[Item ID] [varchar](50) NULL,
[Sales Price] [Numeric](38,2) NULL,
[Quantity] [int] NULL,
[Amount] [Numeric](38,2) NULL,
) ON [PRIMARY]


SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

The error occurs when I try to run the BCP utility

SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', see "Surface Area Configuration" in SQL Server Books Online.

How to Export Query as XML - SQL Server 2008

Save Recordset as XML using Select Query in SQL Server 2008

Let us consider the a table (shown below) with couple of columns that needs to be exported as XML

The following query

SELECT TOP 1000 [TrainID] as 'TrainNum'

,[TrainName]

FROM [OnlineTrans].[dbo].[TrainMaster] for XML PATH

Is used for generating a XML 

 On the other hand, If you want to have a meaningfully named element instead of , you need to specify the same in the query. The following query has the row element as well as the root element


SELECT TOP 1000 [TrainID] as 'TrainNum'

,[TrainName]

FROM [OnlineTrans].[dbo].[TrainMaster] for XML path ('TrainInfo'), root ('ParentInfo')

'int' is not a recognized CURSOR option.

This error occurs when a variable name is preceded by # instead of @. # is used to declare temporary table

declare #myi int

Should be replaced by

declare @myi int