Designing a new factTable

Question

Designing a new factTable

Michael Torsson

SSC Eights!

Points: 881
More actions
January 27, 2014 at 12:22 am

#303709

Hi
I have a factTable that is filling up with appr. 500' rows every day and holds now 100'' rows. There are around 25 dimensions to this facttable. We now need to apply additional transformations on that data for some new reports without changing the existing model. There are a couple of new dimensions that needs to be addeed for the transformations and reports.
The analysis is made with Cubes and the Reports doesn't have to render in 2 seconds :-).
My dilemma is if we should store the new factTable with the complete set of dimensions from the old one + the new dimensions or just with a key from the old one.
The transormations can lead to many new rows.
1. New factTable with 25+5 Dimensions
Or
2. New FactTable with OldFactTableKey + 5 dimensions
The cube can have a view with joined facttables, point for solution 2.
History does not have to be added in the new facttable, point for solution 1
Regards
Mike

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 1

Do these new dimensions change the grain of the fact table?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Michael Torsson SSC Eights! Points: 881 More actions · Answer 2

Hi Koen

With the new dimensions it is possible that the transformations will change the grain.

Mike

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Personally I would go for option 1. I don't really like keys to other fact tables in the design.

And I would add the entire history in the new fact table, if possible. That way your old fact table is just a rollup of the new fact table.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

xsevensinzx One Orange Chip Points: 25560 More actions · Answer 4

I never touch the data source if I don't have too. I would make the new fact with a copy of the existing dimensions plus the addition of the new dimensions. This is what they coin as your data mart (mini-data warehouse) that can be completely conformed/tailored to your new business needs and can also be repopulated in the event of rapture.

Just my 2 cents.

Michael Torsson SSC Eights! Points: 881 More actions · Answer 5

Thanks for your input.

To keep them apart is the best way to go forward in case of reloading failure etc.

/Michael

azhar.iqbal499 SSCrazy Points: 2812 More actions · Answer 6

To implement a new Fact table, You need to maintain loading process twice, I think you should attach new dimensions with existing fact Table if you convince your Lead other wise option 1 in better.

aaron.reese SSChampion Points: 13498 More actions · Answer 7

Fact tables should stand alone. Dimensions can be shared between fact tables.

Create a new fact table with all the information required for the new process. If necessary, seed it by copying the existing fact table history into it.

This defines the difference between your Data Warehouse (where all the data is kept) and the Data-Mart (the data that the specific user requires in the format they require it)