Creating Lookup Tables From Large Fact Tables


#1

If I’m able to pull every piece of data I need from a business objects report I realize I will have a very large and wide file and I should create lookup tables back out of the fact table because the data model will look more correct and the report data size will shrink substantially as I delete columns from the fact table that are now only in lookup table except the one remaining related column in the fact table.

Question, I don’t think I can use Reference to copy the fact table to create my lookup table because when I delete the related columns in the fact table to reduce the model size since they are no longer needed those same columns will also be deleted in the newly referenced lookup table?

Duplicate table and not reference table would be the only option in this scenario, right?

Dave


#2

Don’t delete the columns in your initial source query. Bring in the source query, disabled load and create a reference for each Lookup and Fact table you want to have in the model. Do the data modeling in those new reference queries specific to each need. You will then only be removing the columns POST source data load during a refresh


#3

Yes totally agree Randy, thanks. That’s exactly how to do it. Check out this video in the Advanced Data Transformation and Modeling course Dave. This should explain it.

I call these ‘Staging Queries’ - http://portal.enterprisedna.co/courses/197328/lectures/3694416

That example is slightly different but same technique.


#4

I never even considered referencing the fact table as well but I can see where that will work perfectly.

Thanks Randy and Sam and I will be doing that going forward.

Dave


#5

Hi All

I have changed my method a little due to getting the Firewall error too many times for my liking.

I create a group called Source and setup the connections to the data within those queries. They each have 1 line which is just the Source and disable load.

I then have a staging group with each query referencing the Source Queries, this where I shape the data to a baseline ready to be used in the Data model. These are also disabled.

Lastly I have a Data Model Group which houses the queries that I make the final changes to depending on requirements.

Chris


#6

Yep, nice good solution