Forum Discussion
Super UserHow to create hierarchy not in fct table or based on integer key columns?
jaryszek ,
Thank you for sharing your blog post.
- The RELATED () function pulls data from a related table into the current one, typically used in calculated columns.
- This operation is evaluated during data refresh, not during report interaction. So, it affects model processing time, not query performance.
We appreciate your engagement and thank you for being an active part of the community.
Best Regards,
Lakshmi.
Super UserHello,
problem is teoretical, not sample needed but I created:
https://drive.google.com/file/d/1s8CSoDy5R9V0jS4rz2XqK20jv6V46xOi/view?usp=drive_link
(why users can not add attachment on this forum btw? This is a huge limitation).
What is the best architecture design to use hierarchy as text but not keep it in fact table?
As you can see I have hierarchy created from fct_table where there are strings.
So accordingly to best design practice i should have for each field seperate dimenstion table, each for Subscriptions, ResourceGroups and ResourceNames...
But how to keep thos hierarchies and use across report if I will divide them into dim tables?
Best,
Jacek
Community SupportHi jaryszek ,
Thanks for sharing the sample data, but somehow I was not able to access it. Here is a step-by-step approach:
To optimize your model, you should move to a star schema by creating dimension tables for your hierarchies and replacing the string fields in the fact table with integer keys. For example, you can create a Geography dimension (dimGeography) with hierarchy levels like GeographyKey, Region, Country, State, and City, and a Product dimension (dimProduct) with ProductKey, Product, and Category. Then, for each row in the fact table, map the string values to the corresponding keys from the dimension tables. The new fact table should only store keys and measures, for instance, SalesKey, Date, GeographyKey, ProductKey, and SalesAmount. In Power BI, define relationships from FactSales[GeographyKey] to dimGeography[GeographyKey] and FactSales[ProductKey] to dimProduct[ProductKey], and build hierarchies directly in the dimension tables, such as Region → Country → State → City in dimGeography and optionally Category → Product in dimProduct. This approach reduces duplication, improves performance, and allows for fully functional hierarchies while following star schema best practices
We appreciate your engagement and thank you for being an active part of the community.
Best Regards,
Lakshmi.
- jaryszek
thank you.
It could work if hierarchies parts would be in the same dimenstion table.
But I have for example 2 dim_tables:1. Dim_Subscriptions
2. Dim_ResourceGroups
And now I want to create hierarchy from them...
How to make this to keep the best design star schema practices like you wrote?
Best,
Jacek