Dimensional modeling is a technique to organize your data in a way that is easily consumable for data analytics visualization tools and other data analytics products, such as PowerBI. Dimensional models are different from operational models in that they are highly de-normalized, whereas operational data tends to be highly normalized for application efficiency and security purposes. We call operational data OLTP sources (Online Transactional Processing) and analytical, or dimensional, data OLAP (Online Analytical Processing.)

These are examples of OLTP tables:

OLAP tables consist of facts and dimensions. A fact is a business measure or metric, and a dimension is a business entity. A fact could contain things like amount of a sale, length of time of a transaction, or a student grade. A dimension would be things like employees, dates, and organizations. Some examples:

In the above diagram, we de-normalize the employee and organization tables by adding the addresses into those tables and adding additional typing. We add in an item dimension to see attributes about items being sold. The date dimension contains numerous attributes about a particular date (day of week, is it a holiday, what fiscal quarter is it in, etc.) This way, we can interrogate the fact by various dimensional attributes. The fact is the lowest level of granularity, in this case an individual sales transaction showing the item count and sale amount. We want the lowest level of granularity in our dimensional models so we can aggregate up as needed. If we do not bring in the lowest level, we cannot dilute the rows back to their source values.

There are several benefits of good dimensional modeling for analytics:

• The data is easily consumed by visualization tools as the foreign keys between the facts and dimensions is well built and thus reduces any manual intervention
• The data models are grouped into smaller subject areas that are focused on a particular part of the business (sales, HR marketing, etc..) so targeted use cases are more easily addressed
• As development continues from an initial subject area to future ones, these is a benefit of a “conformed network architecture” which utilizes, or conforms, the dimensions already created from previous subject areas. So, if you’ve created a marketing SA and then go to create a sales SA, you will already have most, or all, of the needed dimensions (employee, organization, date, etc..) and will only need to create new fact tables for sales metrics.
• Good data modeling is ETL (Extract, Transform and Load) Tool and visualization tool agnostic, you can apply the logical model to any number of physical implementations.
• Dimensional models allow power users to “slice-and-dice” the data in order to make new data discoveries

So, with good dimensional modeling techniques, users can quickly gain analytical insights to their data! Want to gain these insights for your company or discuss this topic further? Contact us today!