Forum Discussion
How to create hierarchy not in fct table or based on integer key columns?
Hello,
I have fct_table with integers keys to dim tables where my fields from hierarchy exists.
How to make hierarchies from there?
Currently I have string fields in fct_table and can create hierarchies but this is not efficient so i should switch to star schema best schema practices and make them as integer keys to dim tables.
But how to make them working ?
Create additional bridge table and put all strings there for hierachies? or what?
Best,
Jacek
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.
11 Replies
Hello,
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,
JacekHi 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.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
So this is what i found as advice:
You can bring the related fields into the table with the lowest level of granularity, which can be done on this table by adding calculated columns as demonstrated by the following example, then you are able to create hierarchy. ColumnName=RELATED(DimProductSubcategory[EnglishProductSubcategoryName]) For more details, please check the following blog. https://intelligentsql.wordpress.com/2013/05/08/tabular-hierarchies-across-multiple-tables/ Thanks, Lydia Zhang
How this will have effect on performance? It is adding additional DAX column...
Best,
Jacekjaryszek ,
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.
Hi jaryszek ,
Thanks for reaching out to the Microsoft fabric community forum.
Could you please provide sample data (Fact & Dim )that fully represents the issue or question you're referring to? Kindly ensure the data is in a usable format ( PBIX, Excel or CSV) rather than a screenshot, and does not contain any sensitive or unrelated information.
Looking forward to your response.
We appreciate your engagement and thank you for being an active part of the community.
Best regards,
LakshmiAnybody can advise?
Hi jaryszek ,
If you want to model a hierarchy across Dim_Subscriptions and Dim_ResourceGroups while following star schema best practices, you have three solid options. First, you can flatten the hierarchy into a single dimension table combine both into something like Dim_SubscriptionResourceGroup, which makes reporting and hierarchy navigation much easier. Second, if you need to keep them separate for reuse or normalization, you can introduce a bridge table that links subscriptions to resource groups, though this leans toward a snowflake schema and adds complexity. Third, you can keep both dimensions separate and simulate the hierarchy using calculated columns or DAX in your reporting layer or even use a composite key in the fact table to represent the relationship this is more flexible but less elegant. Each approach has trade-offs, so the best choice depends on your reporting needs and how tightly coupled those dimensions are.
Best Regards,
Lakshmi.
yeeah but the case it that i want to exlude very long string to be in the same tables because of performance issues.
Bridge table and common dimension table will consist of multiple gathered strings, yes?
So it means that performance can be very bad.
Am I right?
Best,
Jacek
