The extensibility of a dimensional model can be on following possible ways:

• Add to the existing fact tables, in terms of new facts.
• Adding attributes to the dimensional attributes tables.
• Add to the records to the existing schema. For example you may like to add the records related to international markets (over and above the markets within US).
• Adding an entirely new fact table linked to the existing dimensional attribute table.
• Add a now flake table to the existing dimensional attribute table.

In-short, there are various ways to leverage existing dimensional model, instead of creating a new one. Typically if the data-mart/warehouse effort is more localized at department/function level, it is easier to create a totally new dimensional model. However this in long run adds unnecessary load on the data warehouse and data integrity could suffer in longer run.

Managing the changes in the dimensional model

Managing upstream and downstream impacts

A change in the dimensional model will have upstream impact on the ETL routines. For example, let us say that you added customer location as a new attribute in the customer dimension table, you will need to change:

• The extraction routine, which pulls out data from customer table.
• The transformation routine, which will convert the 'customer PIN-Code' to 'customer city'.

Managing the conformed dimensions

While you could be needing the customer age in only one data mart for the sake of analysis, you would like to have it added into the customer dimension table in the standalone data marts. This is to ensure that you are following the principle of foundation dimensions.

Managing the down-stream changes

With change in your dimensional model, you would need to look at your OLAP population routines and also the existing reports and views. Most of the views and reports are not impacted, if you have added an attribute or a fact. However, if you have deleted a dimension, attribute or a fact, you will need to scan existing views and reports which get impacted.

Managing historical population

Extending upon the same example, if you have a customer age added as an additional attribute, you have a choice on if you want to populate the historical records. For example, I can update all previous transaction records with the customer location at that time (if I have this data in the source system).

The impact and activity analysis will be done before you go ahead with making a change in the dimensional model. A smart BI tool will be able to support the impact analysis and significantly reduce the effort estimation.