Managing history in power play cubes

[Devmanning]

Friends,
Welcome any suggestion that would solve my issue.i am using Cognos 10.2 and have a sales cube built.My issue is that I have a product table where a column refers whether the product is ours or competition,In January 2015 i changed one of the products to competetion as they bought the rights for the product to distribute it.now in my cube 2014 sales for the product falls under competetion.How can I show the sales for 2014 under my company and 2015 onwards under competition.its a bit urgent so will appreciate some quick replies.thanks in advance

[bdbits]

Think of a cube as a star schema (even if the underlying data is not). You have a fact table, call it sales, which has links to all the dimensions including product info. So your product info dimension needs two members - one for the product info in 2014, and one for the product info in 2015. The sales fact rows need to link to the product info member appropriate to the time in which the fact occurred. How to best do this can depend greatly greatly on your data architecture, for example whether you are hitting a source system directly or you have a data warehouse, and are you using an FM package as your data source or something else. If accessing the source system directly, the first issue is whether the source system has anything captured which shows the change. If not, you may still be able to build your queries such that you can present the appropriate product info member to Transformer for a given fact. If you are using a data warehouse, then there is probably an ETL process that populates it and could be used to set the appropriate keys to link a given sales fact to the proper product info member.

I hope this helps you see what needs to be done. A bit more information on your setup might help formulate more detailed answers.

[Devmanning]

Thanks a ton bdbits,your explanation is awesome, I have got the logic.We use FM and then publish cube packages.10.2 is new to me coming from 7 environment.I am going to investigate further based on your recommendations.Will reach out if I have further questions thank you.
Dev