Adding A New Cube To The Warehouse

This post explains the tasks which were done/followed when adding a new data cube to an already existing data warehouse. This more like a guide line to follow when you are creating a new cube to the system.

To add a new cube to the system table structure should be created. Following steps are taken to add brand new set of tables from the sources to data warehouse

1 Extraction process

• Create relevant tables in extraction

• Create views for all those created tables

• Create the DTS Package to transform data from source to extraction

2 Transformation process

• Create the relevant fact table to store the measures

• Create the needed dimensions to store new attributes

• Create views for newly created tables

• Create SPs to apply set of rules to filter information and create calculations for the fact tables

• Create DTS packages to call the SPs.

All stored procedures and queries can be written using SQL using Microsoft SQL query analyzer.

3 Cube Structure Creations (Loading)

Create cube for the relevant fact table including the needed dimensions using Microsoft SQL business intelligent studio(2005)

After cube structure is created following task should be followed

• Adjust the dimension property values in the cube structure

• Under the calculation tab create an MDX query in order to create the calculation.

• Under newly created dimensions create attribute hierarchy and adjust the properties accordingly.

• Process the cube.

4 Unit Testing

Last but not least Unit testing. Unit testing is a very crucial task in data warehousing. Since we calculate sensitive information. You can do the unit testing process using SQL coding to check the transformation process is properly done

• Source to extraction testing

  • Test all source records are extracted to Ext Database

• Extraction to fact table testing

  • Test fact table calculations are properly calculated.

• Fact tables to cubes

  • Create a pivot to the newly created cube compare data with the fact table, according to the cube calculation.