Data Model: Using Reference tables to merge in Categorizations for Dimension usage

[Anonymous]

I have a Fact table that needs data quality improvement.

I have 7 Taxonomies that I need to merge or relate to a categorizations heiarchy for a fact table.

Data model:
-1 Fact table with 2 million rows

-1 Reference table with 7 Taxonomy keys that can be merged into fact table and expand the categorizations -OR-
-7 Reference tables with 1 Taxonomy each that can be merged into the fact table and expand the categorizations.
- 1 Dimentions table for Categorizations filtering (across other related fact tables)


Am I making this harder than it is?  Is there guide for Multi-Fact tables with refernece tables and dimentions table filters?  Or is this just a big bucks consulting task?

I do not own the database, so updating the view is not possible.

[VasTg]

@Anonymous 

It is little bit hard to follow in text. Do you mind share few mockup examples for each of these tables?

Quote by J_West:

  " Or is this just a big bucks consulting task?"

Lol... Happy to assist.. 🙂

[Anonymous]

Here was my other idea, Model B to create a nest model of reference, but for some reason when I add the other 5 nest ref tables the realtionships are not active (I know because of the loops are not allowed in joins).

However if something like this was allowed so I can filter the fact tables by the comep,lex nest reference tables that would be hidden, with the other reference tables that are not enabled, this would eliminate the processing of the merge.

However no one has ever said this was a legal model or even if there is an alternative.

[Anonymous]

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?

[VasTg]

@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

[Anonymous]

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

[VasTg · Accepted solution]

@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 think I have ended up at the conclusion to just merge in the tax to the fact table at the ProdSeg level and use normal diminetions tables.  It is impossible to process in Power BI, but I have a Excel Cached model that I may take to a db at somepoint.  In Excel i will do these merges where it is more managable.

[VasTg]

@Anonymous 

Before you start to do it in Excel, when you merged in Power BI, did you uncheck the "Enable Load" for all the 7 taxonamies tables? Otherwise, it would have loaded the table twice and caused it to run longer.

Just a thought.

Connect on Linkedin 

[Anonymous]

yes. I disable many tables in processing.

[VasTg]

@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

[Anonymous]

The cross over duplication is a result of my anonomizing the data manually.  Not a issue in the actual data sets.  Or at least it is my intent to correct these if they exist to have a smooth rollup.

[Anonymous]

I have uploaded the basic model ideas to a OneDrive folder.

https://1drv.ms/u/s!AkqpactFHcpZmztMfsfS1SFxMh9K?e=MfqiyY

[Anonymous]

Took a bit to anonomize the model data.  Not perfect but should get the point.

I want to be able to convert\connect\etc a Identifier in the table to another category, then use that category to join a dimentions table.  Today I merge the identifier field, Product Name, and expand the ProdSeg category into that table.  I also do this for others like Item ID, Core Model& Core Type, and Product ID.  With each expanded field, I concantant if not null into one ProdSeg field and remove the reference columns.

This is merging and joining is pretty standard in moderation I am lead to believe, but on my scale with 2milion rows and 7 different merges, this becomes a process refresh load nightmare of hours or days.

Is there a way to create keys and not do a merge into the fact table?  The limitation appears to be joins into the mutlti-fact snowflake mess I have on my hands.  I also have 2 or 3 more fact tables I would like to add to the model by link of the dimentions tables, which should be fine as long as they do not require merges  too.  I know too much info.

Thanks for any advice you might have on how to eliminatte the processings.