BI Dimensional Modeling - Design Techniques

In this post I will present some basic techniques and rules for successful dimensional modeling. For a basic knowledge on Dimensional Modeling read: BI Dimensional Modeling - Data Marts.

Four-step technique for designing data marts

1. Select the business process to model. The main idea behind the BI concept is combining data from different departments in the organization into a unique standardized warehouse. When you design the data warehouse you need to be aware of this idea and understand that a business processes should not belong to a certain organizational business department. So if you design a data mart for handling the order business process, it will be used in the sales department as well as in the marketing department.
2. Declare the grain of the business process.  

• Answer the question: "How do you describe a single row in the fact table?" Dimensions should not necessarily help define it. It could represent an individual line item on a POS transaction, the sold amount of a product per day, etc.
• Choose the lowest grain even if such a detail level is not necessary right away. A good grain is how resilience to change is obtained in a data warehouse. Choose a higher grain only when you know for sure that you won't ever need a more granular detail level. 

1. Choose the dimensions. 

• Answer the question: "How do business people describe the data that results from the business process?"
• Dimension attributes should be all possible descriptions that take single values in the context of a measurement.
• The grain determines all the primary dimensions. Any other additional dimensions should take only one value for a combination of the primary dimensions.

1. Identify the facts. Now that you've decided which are the grain and the dimensions of the data mart you should identify the measurements that you want to store. 

• Answer the question: "What are we measuring?". 
• It's better to store calculated fields (Ex: store directly the total dollar amount do not expect that the user will compute it by multiplying quantity and unit price). In this way we assure that the value is calculated correct and all the users refer to it consistently. 
• Usually facts should be addable, if not they should be considered candidates for dimension attributes. It does not make sense to add product unit price, so the unit price will be an attribute in the product dimension.

Conformed dimensions technique - Data Bus Matrix