In Analysis services 2008 we can build cubes via three approaches
- top-down
- bottom-up
- empty cube
Traditional way of building cubes is bottom-up from existing relational databases. In the bottom-up approach, you need a data source view from which a cube can be built. Cubes within a project can be built from a single DSV or from multiple DSVs. In the top-down approach, you create the cube and then generate the relational schema based on the cube design. In SSAS 2008 you also have the option to first create an empty cube and then add objects to it. Here I would like to demonstrate top-down approach.
Schema datawarehouse is empty as shown in figure. In this demo we will be designing the tables from SSAS and deploy it into this schema.
Step 1 : Create a new SSAS project and you can see in Fig 2 that datasource, dsv, cube, dimension etc are empty.
Step 2 : right click on the Cube and click on new cube. Choose the option Generate tables in the datasource but do not select the template and leave it as none.
Step 3 : Create all the required measures as shown in Fig 4
ProductKey and CustomerKey are the fields which reference to product and customer dimension tables respectively and so Aggregation should be none.
Step 4 : Create all the dimension as shown in Fig 5
Here we can select the option SCD if the dimension is changing in nature so that SSAS creates extra fields to track.
Step 5: define the dimension usage for the fact created as mentioned in Fig 6
Step 6 : Click finish to complete cube creation. we can generate the schema by selecting Generate schema now option or later by right click on project and choose the option.
Step 7 : Add required fields in all the dimension from attributes window of the dimension layout.
Step 8 : right click on the solution and select option Generate Related schema to create tables. mentioned.
Step 9 : Select the data source from which cube should pick the data in future.
Once the schema successfully generated, you get the window as given below.
Now we can go back to the database datawarehouse which we have created at the begining of the demo and find that these tables are gnerated successfully.
We can use SSIS to pull the records from the source systems and load the records into these tables.
Step 2 : right click on the Cube and click on new cube. Choose the option Generate tables in the datasource but do not select the template and leave it as none.
Step 3 : Create all the required measures as shown in Fig 4
ProductKey and CustomerKey are the fields which reference to product and customer dimension tables respectively and so Aggregation should be none.
Step 4 : Create all the dimension as shown in Fig 5
Here we can select the option SCD if the dimension is changing in nature so that SSAS creates extra fields to track.
Step 5: define the dimension usage for the fact created as mentioned in Fig 6
Step 6 : Click finish to complete cube creation. we can generate the schema by selecting Generate schema now option or later by right click on project and choose the option.
Step 7 : Add required fields in all the dimension from attributes window of the dimension layout.
Step 8 : right click on the solution and select option Generate Related schema to create tables. mentioned.
Step 9 : Select the data source from which cube should pick the data in future.
Once the schema successfully generated, you get the window as given below.
Now we can go back to the database datawarehouse which we have created at the begining of the demo and find that these tables are gnerated successfully.
We can use SSIS to pull the records from the source systems and load the records into these tables.
