SQL Server Analysis Services
The structure shown in the previous diagram helps you to find the junction of the three dimensions in every granular level of the defined attributes. For example, the total sales amount of a Product in August 2012 in a New Zealand store is a point of junction of three axes of each value represented together in the cube shown in the previous diagram.In real-world requirements, you might need many more than three dimensions for some reports and analytics.
We are going to use "AdventureWorksDW2012" Database
You can download AdventureWorksDW2012 Db from this link https://msftdbprodsamples.codeplex.com/downloads/get/165405 or just search for adventureworksdw2012 database download on GOOGLE.
Developing your first cube
- Open SQL Server Data Tools by clicking on Start, then navigate to your Microsoft SQL Server 2012 folder and under that, choose SQL Server Data Tools.
- Go to the File menu and under New, select Project
- In the New Project window, under templates in the left-hand side pane, click on Analysis Services and from the list of templates in the main pane, choose Analysis Services Multidimensional and Data Mining Project. Name the project "MultidimensionalProject_01".
Creating the cube using Cube Wizard
- The first step is to create a data source connection. To create a data source connection, right-click on a data sources folder.
- Open the SSAS project and select New Data Source. Create a connection to the AdventureWorksDW2012 database.
- In Impersonation Information choose Inherit.
- There is one more step before creating the cube, and that step is the creation of the Data Source View.
- Right-click on the Data Source Views folder and choose New Data Source View. Choose the data source from step 1. In the Select Tables and View step, choose the tables as shown in the next screenshot and name the data source view AdventureWorksDW2012:
- You can also use the Add Related Tables button, as shown in the previous screenshot, to include all objects related to the selected object.
- After creating DSV, you will see a database style diagram of tables with their relationship in the design area.
- Now, it is time to create the cube. Right-click on Cubes and select New Cube.Follow the steps in the wizard, and in the Select Creation Method step, choose Use Existing Tables.
- In the Select Measure Group Tables step, select the checkbox of the FactInternetSales table.
- In the Select Measures step, just leave all the measures as checked.
- In the Select New Dimensions step, leave all the tables as checked except for Fact Internet Sales.
- Save the cube as Internet Sales.
Viewing the cube
- In Solution Explorer, right-click on the project "MultidimensionalProject_01" and choose Process.
- You will see a dialog box stating The Server contents appears to be out of date. Would you like to build and deploy the project first?. Answer it by clicking on Yes.
- In the Process window, leave all the configurations as the default and click on Run.
- After completing the Process Progress step, close the Process window.
- Go to the Browser tab, which is the last tab in the cube designer.
- If you've got a message saying that the database does not exist or the cube is not up to date, try to reconnect the browser. You can click on the Reconnect button,which is the third icon from the top left-hand side of the Browser tab.
- In the Browser tab, from the Metadata pane, under the Internet Sales measures, drag-and-drop Sales Amount into the main empty pane in the middle. Do the same for the Order Quantity measure.
- You will see a grand total of Sales Amount and Order Quantity calculated from the cube.
- From the Metadata pane, under the Sales Territory dimension, drag-and-drop Sales Territory Key into the main pane as shown in the following screenshot: