How to create a cube design from facts and dimensions.

Problem: How to create a cube design from facts and dimensions.

Solution:

To create or access a cube in SQL Server Analysis Services you first need to develop the cube, there are few pre-requisites to design.

1.       BIDS or SSDT( Designer)
2.       SQL Server Analysis Services
3.       Datamart or Data ware house to access the facts and dimensions
4.       Load OLTP tables into facts and dimensions using SSIS or ETL.
5.       Administrator permissions on the server to deploy cubes.

Example:

In this example, I would like to show we can create a cube from adventure works data ware house tables.

Step 1: launch the SQL Server Data Tools and create a new project for Analysis Services Multi-dimensional project.

Step 2: Open the solution to expand all the folders and right click on the data source to specify the connection string from where the data should be accessed.

Step 3: Create a data source view using the existing data source created in step 2 by right clicking on the data source view to create new.

Step 4: from the wizard you must select a table you wish create a data cube and you can add related tables to the fact by selecting add related tables on the below.

Step 4: on the next step, create a new cube by right clicking on the cubes folder

Step 5: after the data source view, has been created we can create a cube on top of the existing data source view by right clicking on the project to create new cube and a wizard will be prompted.

Step 6: Now we can select measure groups, attributes and dimensions members etc. from the wizard depending on the user’s requirement we need to expose the data elements.

Results:

Now if you open the cube file from the project it looks like below.