When designing your business intelligence solution your data model should coincide with the way that your business analysts interpret your business structure. Now, the goal here is to hide the complex technicalities. You’ll do this by creating an OLAP model that allows your analysts to create intuitive structured queries. In short, your analysts should be able to query your data warehouse in the same way that they would ask business questions.

Business Intelligence is used to measure aggregations (which are facts) across dimensions. This is the ideal modeling used in the BI environment. The dimensional model is designed using specific business process areas. You may decide to track sales amount as a fact using dimensions such as product or store number or sales date to give meaning to those facts.

Measures or facts are numeric data that corresponds to the questions that your analysts are asking. Facts can point to a record in a dimension table that will give meaning to the data. Common facts include dollar values, sales, and revenue.

Dimensions create the anchor when investigating a fact. An analyst may wish to view sales on a specific date for a specific group of customers. This would require the analyst to create a query that links the Sales fact table with the customer and date dimensions. Without this ability the facts would simply be unstructured facts. The Date and customer dimension allow for filtering, grouping, and labeling to narrow down query results.

This example of a fact table displays how the measures of a customer account are tied to the customer dimension. Each measure will have its own aggregation rule such as sum, average, min, or max. The aggregation rules are defined by the ways the business chooses to compare the values of the measure. In this example I display a monthly average balance attribute. Your business may be interested in a yearly average rather than monthly. In addition, the account rating attribute will be calculated using the pre defined business rules for determining an account rating. It is important that these rules are clearly defined and communicated to your analysts so that the true meaning of the data is understood.

This example of a dimension table displays the details of a customer. It includes attributes that describe business interests about the customer. The customer dimension will tie to multiple fact tables to provide a clear image into the details of customer interactions with the company. For example, the CustomerID shown above clearly links to the FactAccount table shown above.

Dimensional hierarchies define a set of parent-child relationships between attributes within a dimensions. A dimension hierarchy includes levels that can roll up from child to parent. A dimension can have more than one hierarchy. The date dimension example shown can include multiple hierarchies such as “Day > Month > Year”, “Day > Week > Year”, “Day > Month > Quarter > Year”, etc. The specific hierarchies that you will include depend on your internal business needs.

I hope this has been a helpful explanation of dimensional modeling. Have a great day!