Special Dimension Scenarios
1. Special Dimension Scenarios
In this video, we’ll talk about some special dimension scenarios, like the time dimension, and how to implement them in Microsoft Fabric.2. Date dimension
Fact tables store information about business events recorded at a specific point in time, so they almost always have a date field recording when the event occurred. The date dimension is the most commonly used dimension for analysis, supporting filtering and aggregating data by date. For example, a date dimension might support analysis of sales by month.3. Date dimension
The natural key of the date dimension should be of the DATE data type. The surrogate key should be an integer to optimize space. Ideally, it should be a number in the form year month day. This has many advantages: it is space efficient because it's an integer, it naturally sorts, and it is human readable. At a minimum, the date dimension should include attributes for Year, Month and Day. Additionally, it can include other attributes to support analysis by date, for example Quarter, Fiscal Year, IsWeekend and IsHoliday.4. Time dimension
For some areas, data in the fact table might be required to be down to the time-of-day level. In these cases, it is recommended to have a separate time dimension. For example, a time dimension might support analysis of sales by time of day.5. Time dimension
The natural key should use the time data type. The surrogate key could be an integer, and like the date dimension, it could take a human readable form like HoursMinutes or HoursMinutesSeconds. A grain of minutes would take up only 1,440 rows, while a grain of seconds would take up 86,400 rows.6. Conformed dimensions
Conformed dimensions relate to many fact tables. For example, the Date dimension is commonly used by all models, so it makes sense to share the same table. Conformed dimensions help bring consistency across models supporting multiple areas. In this example, the date and the product dimensions are shared by two star schemas: sales and marketing.7. Role-Playing dimensions
When a dimension is referenced multiple times in a fact table, it's known as a role-playing dimension. In the example, the sales fact table has order date, shipping date, and delivery date dimension keys. The date dimension is role-playing because it's related three times to the fact table as if there were separate dimensions for order date, shipping date and delivery date.8. Multivalued dimensions
The relationships between fact table and dimensions are usually one-to-many. For example, each fact table record in this schema is related to one record of each of the dimensions. Additionally, each salesperson covers one Sales Region.9. Multivalued dimensions
Many-to-many relationships might be required to model some scenarios. For example, if a salesperson were to cover multiple sales regions, the previous schema would be insufficient. To handle this scenario, we use a bridge table. Bridge tables join tables by storing pairs of related dimension keys. This scenario would look like this with a bridge table to join the Salesperson and the Sales Region tables.10. Let's practice!
Now, let's do a couple of exercises to practice with some of these dimensions.Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.