Forum Discussion
Data Model: Using Reference tables to merge in Categorizations for Dimension usage
Anonymous
I looked at the model and I could recomment couple of approaches.
1. Bring Product segment to facts and join the segment dimension directly to facts intead on top of tax. The tax should join to facts as well. With this tax and segment both filter facts.
But for a single row in fact, if a segment tagged to core is different than a segment tagged to platform, we can't go for this appraoch.
2. Create bridge or linker table between tax and facts. This should have all the combination for segment, tax(core,product, platform etc) and facts. All 7 tax and segment dimension connects to bridge and the bridge connects to facts.
In any case, you can't join the dimension to seperate tax unless you merge them togeather.
Let us know.
I know I could merge and recreate all the measure for 3 of the 4 fact tables nad do pre-processing the merge in a few excel files of local cache then import to Power BI, but this is quite a few steps to chain just a refresh weekly.
I may do this to some dregee to speed incremental refresh up as I only need say, past 3 months data on weekly refresh and maybe a quaterly refersh to ensure older records do not get stale changes.
I am spoiled to think this is possible with Power BI?
Anonymous
Looking at the pictures, I believe you have two options.
1. Merge the reference to fact if doesn't explode the rows.
2. Merge all the reference tables into 1 and define the relationship from Dimension->Reference->facts.
Again, I need to see the data(sample data) and the key columns to suggest anything.
Two million rows is not a big deal even for dimension. So don't worry about the record counts.
Even if you don't have access to create views in database, You could still use the sql to transform the data apart from Power BI transformations
I have uploaded the basic model ideas to a OneDrive folder.
https://1drv.ms/u/s!AkqpactFHcpZmztMfsfS1SFxMh9K?e=MfqiyYI have been doing #1 merge for some time but performance has gotten out of control. I am testing pulling the data to Excel and acache\transform there and it seams to be performing much better with a vlookup.
I have modeled #2 but have granularity issues I have tried to work through with establishing keys and hierarchical structure if not the lowest level on every Taxonomy.
For the SQL, I can create my table selection to merge the fact tables but will still have the Categorization with the 7 meta-Taxonomies.Anonymous
I looked at the model and I could recomment couple of approaches.
1. Bring Product segment to facts and join the segment dimension directly to facts intead on top of tax. The tax should join to facts as well. With this tax and segment both filter facts.
But for a single row in fact, if a segment tagged to core is different than a segment tagged to platform, we can't go for this appraoch.
2. Create bridge or linker table between tax and facts. This should have all the combination for segment, tax(core,product, platform etc) and facts. All 7 tax and segment dimension connects to bridge and the bridge connects to facts.
In any case, you can't join the dimension to seperate tax unless you merge them togeather.
Let us know.
Anonymous
I had a deep look at the data. Given that its a mock up file, i see prodseg is in both CORE and Item.
Verify the fact to have single segment based on all 7 categorization and if it does it better to bring in the segment and join the dimension to fact.
If it helps mark it as a solution
Kudos are nice too