How to create hierarchy not in fct table or based on integer key columns?

[jaryszek]

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]

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,
Jacek

[v-lgarikapat · Accepted solution]

@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.

[jaryszek]

Anybody can advise?

[v-lgarikapat]

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.

[jaryszek]

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

[v-lgarikapat]

Hi @jaryszek ,

 Your right to be concerned about performance when dealing with long string fields in shared or bridge tables. If we combine Dim_Subscriptions and Dim_ResourceGroups into a single dimension or use a bridge table, and those tables include verbose string columns like full subscription names or resource group paths, it can definitely impact performance  especially in large models. Long strings increase memory usage, slow down joins and filtering, and reduce compression efficiency in VertiPaq. To mitigate this, we typically rely on surrogate keys for relationships and keep long strings only for display purposes. Another option is to push hierarchy logic to the reporting layer using DAX or calculated columns, which avoids bloating the model. So yes, if not carefully designed, bridge or shared dimension tables with long strings can degrade performance, but with proper modelling techniques, you can still preserve hierarchy logic without sacrificing efficiency.

Best Regards,

Lakshmi.

[jaryszek]

thank you, 

"o mitigate this, we typically rely on surrogate keys for relationships and keep long strings only for display purposes."

ok so if i will put bridge table with integer keys connected to Dim_tables, it should work? 

But How to build hierarchy from integers, not strings?

Best,
Jacek

[jaryszek]

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,
Jacek

[v-lgarikapat]

Hi @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

[v-lgarikapat]

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,
Lakshmi