I work with a fairly large product database. The business plans to change the product group codes and some names now. We have a lot of history and there will be groups of users who will be interested in using both old and new systems. If I add the new data in the dim table. Below are the table headers: Prodname, old_prod_group, new_prod_group, old_prod_line, new_prod_line,old_prod_nr, new_prod_nr The hierarchy is like this: prod_line->Prod_Group->prod_nr
I would like to setup SSAS in such a way that that old and new prod_nr can roll up to the same prod_group and roll_up to the same prod_line.
There should be a possibility to choose the old names or new names(this I know cna be set in the hierarchies in dimension structure tab) but irrespective of that the fact data should all roll up to the same prod_lines.
so, I think I this comes down to somehow being able to map the old and new prod_grp and telling the cube to consolidate together to show one fact value for a given old/new prod_group pair.
What features or functionalities can help me achive this? Can you please point out to some examples online?
thanks!
Are you tracking historical changes in your dimension already? It sounds like you are asking about how to model a slowly changing dimension- is that what you're after?