Various Studies in same Fact table differing by at least on dimension table value

Nov 24, 2009 at 6:22 PM

I have a user that does studies where the dimensions and fact table are set up for the initial study. Subsequent studies may change as little as one value in one of the dimensions from the initial study. The user wants to put the relevant information into the fact table pointing to all the rows for the dimension tables that were the basis for the fact rows of the initial study except for the changed values.

SSIS provided tasks are used to pull data in to the fact table whilst connecting with the dimension tables. The initial study and subsequent studies based on the initial study are identified by a version id. Rows in the dimension table are also identified by a version id. What type of guidelines might there be to the design of the dimension tables to make use of the provided SSIS tasks.

At the moment, it appears that all the rows in the dimension tables need to be duplicated for each subsequent studies. This wastes disk.

The end users of the fact table use excel to draw off scenarios from the fact and dimension tables to create pivot tables. They need to pull of information for the study they are focusing on. Their spreadsheet needs to contain appropriate dimension information that supports the study.

Thanks

Jim Schellenberg