Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
I was just wonderring what is best practice for fact table dimension keys?
Is it best practice to hide these?
I'm concerned that users will use the IDs in the fact tables rather than the actual dimension tables.
Any advice would be great.
Thanks
Mattie
In my experience, there are two types of dimension keys.
1. System-generated keys are integers that typically have no intrinsic value. It's best to hide these in fact and dimension tables. These are assigned by source systems or an ETL process.
2. Dimension codes are used when system-generated keys aren't available. For example, if a Finance team prepares a budget, team members won't know the system-generated keys for dimensions such as Account and Department. Instead, they will assign budget amounts to dimension codes. In the model, the Budget table will be joined to the dimension tables based on codes rather than keys. In effect, the codes become keys. You can hide these codes in fact tables, but they should be visible in dimension tables.
If possible, hide the entire fact table. This will force users to use dimension tables and measures in their visuals. In some cases, fact tables contain attributes like Voucher Text that don't have a corresponding dimension table. Fields such as this may prevent hiding the fact table.
Further discussion on the topic can be found in the book The Data Warehouse Toolkit by Ralph Kimball.
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |