Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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

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 mil...
Need Help
  • VasTg's avatar
    VasTg
    6 years ago

    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.

     

VasTg's avatar
VasTg
Memorable Member
6 years ago

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's avatar
Anonymous
Not applicable
6 years ago

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.