Creating a Cube and Dimension using SSAS

In my previous article, we took an Overview of SSAS and its various components. In this article, we will create a cube by using Business Intelligence Development Studio (BIDS).

We will continue with the example we discussed with SQL Server Integration Services where we created a database with the help of Import Export wizard

1. In the existing solution named DemoBeginning, add an Analysis Services Project and name it DemoCube.

2. In the Solution Explorer, right click on Data Sources and click on New Data Source. This action launches the Data Source Wizard. Connect to the database created earlier named DemoDB. Select the impersonation information as ‘Use the Service Account’. Keep the default name and click Finish.

3. In the Solution Explorer, right click on Data Source View (DSV) and select New Data Source. Select 3 tables from the Production schema and SalesOrderDetail from Sales schema. We will fetch the last table in a short while. Keep the default name for DSV and click Finish. In this DSV you will see that no tables are related to each other.

Note we did not fetch any keys or relationships from the original database to the newly created database.

4. We will create the logical relationships in the DSV. Right click on each table from the Production schema and make ProductID, ProductSubCategoryID and ProductCategoryID as logical primary keys. To create relationships, drag ProductSubCategoryID from Product table and drop on ProductSubCategory. Verify the relationship is as follows:

Similarly drag ProductCategoryID from ProductSubCategory table and drop it on ProductCategory table. Drag ProductID from SalesOrderDetail and create the relationship by dropping it on ProductID of Product table. We have now created primary keys in dimension tables and subsequent foreign keys in fact tables.

5. Dimensions for cube can be created in 2 ways. One is by right clicking on Dimension in Solution Explorer and the other when we create a cube, the dimensions automatically get created. Right click on the cube in Solution Explorer and start the cube wizard. Select existing tables from the ‘Select Creation Method’ screen. Click Next.

6. Click on the Suggest button from the next screen and you will see that the table named SalesOrderDetails is picked as the table with measures. This suggestion is based on the relationship we created in the previous step. Click Next.

7. Select Order Qty, Unit Price and Line Total as the only measures and click Next

8. Click Next, enter the name DemoCube and click Finish. The result will be as shown below.

You can now see the relationship which we created.

9. Right click on the project and select properties. Click Deployment tab and enter your server name. For default instance you can keep it as localhost; for named instance it will be <machine name>\<instance name>. Select Build, Deploy DemoCube.

10. After successful deployment ,select the Browser tab for cube. If you drag and drop Unit Price, Line Total to the data area and Product category Id to the row area, you will see the result as follows.

This result shows category id which is not very user friendly, it will be better if we see the name instead.

11. Go to solution explorer and double click on Product dimension.

Go to properties for Product Id and change NameColumn property as shown below

Similarly change the NameColumn for Product Subcategory ID and Product Category Id.

Create a hierarchy named Product Category as follows:

We need to process the dimension with the changed properties. Now the previous result is as shown below

12. We can format the Line Total by selecting Cube structure tab, select Line Total, select properties and specify FormatString as currency with the following result:

13. We get a warning for the dimension as “Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies”. To implement this best practice, select each attributes and change its AttributeHierarchyVisible property to False.

14. Let us add one more dimension for Time. Select DSV, right click on the empty area and select Add/Remove tables, add SalesOrderHeader and click. Specify SalesOrderId from SalesOrderDetails as logical primary key and establish relationship to the newly added table.

15. Right click on Dimensions in solution explorer and add a new dimension. Select ‘Generate a Time Table’ on server, and enter details as shown below:

Click Next, Next again and finally Finish. Verify that the wizard automatically creates one hierarchy with year, quarter, month and Data as attributes

16. We need to add this dimension to the cube, so select the cube structure tab, right click on the empty area for dimensions and select add a cube dimension and select Time. Process and deploy the changes.

17. Go to Browser tab, click reconnect and you will see the newly created dimension added.