Chapter 5 : SQL Server Analysis Layer
Overview
Prepared for the student community
Exercise:
Create a new Analysis Services Multidimensional and Data Mining project. Save as SSAS_Exercise1 in your folder. In this project, implement the following requirements:
- Create a data source view named DSV_ssas_ex1 that includes the FactResellerSales, DimPromotion, DimEmployee, DimProduct and DimDate tables of the AdventureWorksDW database.
- Create a dimension that has Date as the main table with English Month Name (as Month) and Calendar Year (as Year) as dimension attributes.
- Deploy the project to the Analysis Server. Get a screenshot of your output for this task and save as Dep_SSAS_Firstname_ID in your folder.
- Create a Key Performance Indicator (KPI) to visualize the company’s performance in terms of Order Quantity. A value must be generated based on the total Order Quantity. Refer to the following table. Use the Reversed Gauge to visualize the output. Get a screenshot of your KPI code and KPI output. Save these screenshots as KPICode and KPIOutput respectively in your folder.
Order Quantity | Value Generated |
More than 100000 | 1 |
Equal to 100000 | 0 |
Otherwise | -1 |
- Use the cube to generate an output for each of the following business questions:
- For English Month Name May and Order Date Calendar Year 2012 and 2014, how much Sales Amount and Extended Amount had been generated in May 2012,2014? Get a screenshot of your answer and save as Firstname_ID_actQ1
- For English Promotion Name Road-650 Overstock and Order Date Calendar Year 2012 to 2014), how much Total product Cost had been generated by each employee (Firstname and Lastname)? Get a screenshot of your answer and save as Firstname_ID_actQ2.
Eercise Solution:
Answer or Solution :
Step1:
Make ensure that to create the new analysis services Multidimensional and Data mining project, we have to use the software in the Sql server 2012[ sql server management studio and Sql server Data tools].
Step2: Open the Sql Server Data tools->select ->Business Intelligence, then it will show in the SCREEN1.
Screen 1
Step3: Go to the File Menu->Choose the New Menu Item-> Choose the Project as shown in the SCREEN2.
Screen 2
Step4: After choosen the new project ,it will show the below screen->Choose the Analysis Services->Select Analysis Services Multidimensional and Data mining->In the Template it will ask you the name of the project and Location- to keep the project to be stored.
Screen 3
Step5: For Example-> AS PER THE EXERCISE-> I have named the project as SSAS-Exercise1 and place in my PC->DESKTOP-> in the created folder->sql and it is saved.
SCREEN4
Step6: After created the Project->It will shows you the below screen->in that you can notice Solution explorer Window and Designer Page ->Refer the Screen5.
Screen 5
- In the Solution Explorer Window –It will shows your Created Project[SSAS-Exercise1] and its object.In that Select the Data Sources item->Right click->It will show the New Datasource-> in the popup menu->Open it.
- It will open the Data source wizard->Click the NEXT button.
Screen 6
- Once click the Next Button –It will lead you to the window as below.
Screen 7
D) In that-By default it will show already created connection [which I have highlighted in the Left hand side->Click the DELETE button -> to remove the connection. Click NEW button -> make the new Connection.
E) It will show the Connection manager Window-> Copy and paste your Server Name from the Sql server management Studio. Then Select the DATABASE AS AdventureWorksDW [IN THE SCENARIO IT IS MENTIONED TO USE THE DATABASE].Then you may CLICK THE Test Connection-To make ensure the connection confirmation.
Screen 8
F) After press the OK button –It will open impersonation information window. In that page –Choose the User service account as shown in the below screen-refer Screen 10.[Note make ensure that all the related services are in the running status-Refer-screen 9],then click the NEXT button.
Screen 9
Screen10
G) In the completing the wizard window-> give the name of the Data source as per your exercise. Then click the button Finish.
Screen 11
H) Finally Data Source will be created and it will show in the solution explorer as follows:
Screen 12
Step 7: To create the Data Source View:
- In the solution explorer window-> choose the Data Source View->Right Click-> Popup menu will appear->CLICK->New DATA SOURCE VIEW.
- It will open the Data Source View Wizard window->Click NEXT-button.
Screen 13
- It will shows you the below window ->To select the Data Source-Already created->then click NEXT button.
Screen 14
- It will lead you to Select Tables AND views-window-As per the exercise-> Choose the FACTRESELLERSALES table move to included objects part-> as shown in the below screen.
Screen15
- It will show as per the below screen-> screen 16-> click the add related tables-> shows all the related tables for the FACT table. You can selected the needed tables for the exercises, others you can unselect it.
Screen 16
- As per the exercise, you need the tables are DIMEMPLOYEE, DIMPROMOTION , DIMDATE,DIMPRODUCT with FACTRESELLERSALES. Then click NEXT button.
- It will open the Completing View Wizard window-> Give the name for the Data source view as per the exercise[ DSV_SSAS_EX1]
Screen 17
- Click finish button .It will shows you as below screen18
Screen 18
Step7: Create Dimension
- Select the Dimension in the Solution Explorer->Right click->choose the New dimension
Screen 19
- Then it will lead to Dimension Wizard->Click Next button.
Screen 20
- It will open the below window:
Screen 21
- Choose the choice as USE AN EXISTING TABLE in the selection creation method->then click the Next Button.
- It open the Specify source Information window as below: Choose the main table as DimDate,then Click NEXT button.
Screen 22
- It will open the select Dimension Attributes window:
- You can choose the calendar year-> attribute type-> regular-> in that DATE-> in side Calendar-> Year.As well for the month also[English month-> attribute type ->regular-> DATE->Caledar->month.
Screen 23
Screen 24
Then press the OK button.
vi.It will show the window –completing the wizard-as below: Then click FINISH button.
Screen 25
Then it will appear as below:
Screen 26
Step8: Cube creation
- Select the Cube in the solution explorer window->right click->choose the New Cube. Then it will appear as below.Click the NEXT button
Screen 27
- It will open the Cube wizard-as below. Choose the choice as Use existing tables,then click the NEXT button.
Screen 28
- It will open the select Measure Group Tables window as follows-> choose the FACTRESLLERSALES Table, or click the Suggest button
Screen 29
iv. Then click the NEXT button-It will shows the select Measures window.[unselect the keys]
Screen 30
Click the NEXT button.
In the next window [select existing Dimensions]it shows for the select existing Dimensions,then click NEXT button.
Screen 31
In the next window[Select New Dimensions]-unselect the fact reseller sales,then click NEXT button.
Screen 32
It will open the Completing the wizard window.
Screen 33
Give the name of the Cube and click FINISH button. Cube is created as below
Screen 34
Make ensure that in the Dimension and it tables -> your required attributes ,are selected for your business queries.
For example in the dimPromotion[ attributes are: Englissh promotion Name IS SELECTED] AS SHOWN BELOW.
Similarly –you have to select the needed attributes for the given business queries in the respective dimensions
Screen 35
Then save the project.
Select the project in the Solution explorer window->right click ->choose the properties->Deployment->Server-> enter the server name
Screen 36
Again-> in the solution explorer-> in your project->< right click-> choose the Deploy.
Screen 37
If it is successfully completed all the steps-> below screen will appear .[deployed successfully]
Screen 38
In the Cube-> Select BROWSE-> IT WILL APPEAR AS BELOW.
Screen 39
- FOR THE 5.1 business query->ANSWER AS FOLLOWS:
[For English Month Name May and Order Date Calendar Year 2012 and 2014, how much Sales Amount and Extended Amount had been generated in May 2012,2014? Get a screenshot of your answer and save as Firstname_ID_actQ1]
Screen 40
Next -> drag and drop sales amount, extended amount from the Measures to the designer as follows. It will show the result for the business queries.
Screen 41
Similarly-you can do for the other business queries as below.
For English Promotion Name Road-650 Overstock and Order Date Calendar Year 2012 to 2014), how much Total product Cost had been generated by each employee (Firstname and Lastname)? Get a screenshot of your answer and save as Firstname_ID_actQ2.
Screen 42
KPI Question:
- Create a Key Performance Indicator (KPI) to visualize the company’s performance in terms of Order Quantity. A value must be generated based on the total Order Quantity. Refer to the following table. Use the Reversed Gauge to visualize the output. Get a screenshot of your KPI code and KPI output. Save these screenshots as KPICode and KPIOutput respectively in your folder.
Order Quantity | Value Generated |
More than 100000 | 1 |
Equal to 100000 | 0 |
Otherwise | -1 |
KPI code:
Screen 43
KPI Output:
Screen 44
********************end**************************