Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Designing a new factTable Expand / Collapse
Author
Message
Posted Monday, January 27, 2014 12:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:08 AM
Points: 35, Visits: 260
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
Post #1534865
Posted Monday, January 27, 2014 12:28 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
Do these new dimensions change the grain of the fact table?



How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1534869
Posted Monday, January 27, 2014 12:38 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:08 AM
Points: 35, Visits: 260
Hi Koen

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

Mike
Post #1534873
Posted Monday, January 27, 2014 12:47 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 12:52 PM
Points: 13,636, Visits: 11,509
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.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1534875
Posted Monday, January 27, 2014 9:48 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, December 15, 2014 11:29 AM
Points: 92, Visits: 457
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.
Post #1535290
Posted Tuesday, January 28, 2014 1:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:08 AM
Points: 35, Visits: 260
Thanks for your input.
To keep them apart is the best way to go forward in case of reloading failure etc.

/Michael
Post #1535624
Posted Thursday, February 20, 2014 3:21 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 4:07 AM
Points: 140, Visits: 457
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.
Post #1543384
Posted Friday, March 21, 2014 10:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 12:16 AM
Points: 461, Visits: 693
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)

Post #1553592
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse