History Cube Design Question

Hi,

In SSAS 2005 - I have History cube with 2 partitions 2005 and 2006 data. Both has different tables in data source view. In cube both partitions are map to same measure group, so there is no different measure groups for 2005 and 2006 just to make it clear.

Time to time I need to add new measures in latest cube (2006 partition) data those measures don't have data for 2005 partition.

Right now when I add new measures in 2006 I have to full process the 2005 and 2006 both partitions. Is there any way or way to design my cube so that when I add new measures to cube I can only full process 2006 partition. If user selects my new measures with 2005 date they can get null/zero that's fine.

Thank you for sharing your ideas - Ashok

This is my third post, previous two different questions no one replied....I start thinking either I ask stupid questions or this site is not good for me. Fingers cross this time...Take it easy....

Are you saying you have dimesion members referenced by the later partition that do not exist for the earlier partition?

When you process the later partition you could uncheck the 'process related objects' option. And you could also process any changed dimensions seperately. I think this should work, but you'd have to try it out for yourself to determine any difficulties.

Try doing this using the impact analysis feature to get a good idea as to what will be affected before actually doing the processing.

|||

Hi Ashok,

What if you create a new measure group which contains only the new measures created for the 2006 partition? To facilitate this, you could add a named query fact table to the DSV, which returns the relevant columns from the 2006 table. Then, you should only need to process this measure group, when you add a new 2006 measure.

|||

Thanks Deepak and Dork,

My Dimension members are not changed for any partitions I am just adding new measures in later partition.

Let me correct one thing first my 2005 and 2006 partitions are based on same database view but I have added filter to partitions by joining with DIM_DT like YR = 2005 and YR = 2006.

Any way, Deepak your solution works fine only thing is now there is new measure group for end users and at one point I have to marge this with rest of the partitions and do full process for 2005 and 2006, may be end of the year will be good time.

Because I also have a cube with different linked cubes (like virtual cube in 2000) I created a new calculated measures in virtual cube for each new measures in new measure group and made it new measures visible false so at least users don't see new measure group they see new calculated measures. I think is looks more complex finally keeping new measure group for current year is good idea.

Let's see how it goes. Thanks again - Ashok

|||

Guys How about if I keep 10 extra user defined fields in fact table and map them when I have new measures and make them visible?

-Ashok

sql