Forum Discussion
Super UserDirectLake error – 4 GB string store limit (need help reshaping data)
Hi jaryszek ,
Thank you for the update. Please try below two options.
1. Keep the big string in Dim_EA_AmortizedCosts_Resources_Changed and add a surrogate key.
It is simple and you only modify the existing dimension table. And No extra join complexity. The dimension table still holds the large text column, which contributes to the string store size though less critical than fact table. If the dimension table is also very large and queried often, performance might effect.
2. Create a new lookup table with only ResourceKey + original string.It Keeps the main dimension lean, only attributes needed for analysis. The big string is isolated in a separate table, used only for drill-through or detailed views. It reduces memory pressure on visuals that don’t need the full path. It adds one more relationship like Fact --> Dim --> Lookup. Slightly more complex model.
Please follow below approach for DirectLake.1. Use only numeric measures + surrogate keys in Fact table.
2. Use only attributes needed for slicing/filtering in Main dimension.
3. Use only for long text fields used rarely like drill-through in Separate detail table.
Note: Please try option 2, The full path is rarely used in visuals. If you want to minimize memory and improve performance.
Please refer below updated model.FactUsage --> Dim_EA_AmortizedCosts_Resources_Changed --> ResourcePathLookup
Note: FactUsage stores ResourceKey. Dim_EA_AmortizedCosts_Resources_Changed stores attributes like SubscriptionID, ResourceGroup, Provider, StorageAccount. ResourcePathLookup stores ResourceKey + full original path.I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
Super UserThank you very much,
one question:
"1. If the column is a resource path, break it into components like subscription ID, resource group, provider and account name. Then create a lookup table for these components and reference it via a surrogate key."
"and keep the original in a separate table for drill-through."
I have already a lookup table, my dimension table, which you see on my screen: Dim_EA_AmortizedCosts_Resources_Changed.
Would you advise to keep this big string in this table and create just surrogate INT key to connect with fact table
OR
Would you suggest adding the entirely new lookup table connected to Dim_EA_AmortizedCosts_Resources_Changed with only INT Surrogate key and String ?
And why?
Best,
Jacek
Community SupportHi jaryszek ,
Thank you for the update. Please try below two options.
1. Keep the big string in Dim_EA_AmortizedCosts_Resources_Changed and add a surrogate key.
It is simple and you only modify the existing dimension table. And No extra join complexity. The dimension table still holds the large text column, which contributes to the string store size though less critical than fact table. If the dimension table is also very large and queried often, performance might effect.
2. Create a new lookup table with only ResourceKey + original string.
It Keeps the main dimension lean, only attributes needed for analysis. The big string is isolated in a separate table, used only for drill-through or detailed views. It reduces memory pressure on visuals that don’t need the full path. It adds one more relationship like Fact --> Dim --> Lookup. Slightly more complex model.
Please follow below approach for DirectLake.
1. Use only numeric measures + surrogate keys in Fact table.
2. Use only attributes needed for slicing/filtering in Main dimension.
3. Use only for long text fields used rarely like drill-through in Separate detail table.
Note: Please try option 2, The full path is rarely used in visuals. If you want to minimize memory and improve performance.
Please refer below updated model.
FactUsage --> Dim_EA_AmortizedCosts_Resources_Changed --> ResourcePathLookup
Note: FactUsage stores ResourceKey. Dim_EA_AmortizedCosts_Resources_Changed stores attributes like SubscriptionID, ResourceGroup, Provider, StorageAccount. ResourcePathLookup stores ResourceKey + full original path.
I hope this information helps. Please do let us know if you have any further queries.
Regards,
Dinesh
- v-dineshya
Hi jaryszek ,
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.
Regards,
Dinesh