Designing a new factTable

  • 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

  • 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

  • Hi Koen

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

    Mike

  • 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

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

  • Thanks for your input.

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

    /Michael

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

  • 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)

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

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