Author Topic: Time State Aggregation Rules - IBM Cube Designer 10.2.2  (Read 251 times)

Offline GeethaKL

  • Senior Member
  • ****
  • Join Date: Apr 2015
  • Posts: 62
  • Forum Citizenship: +0/-0
Hi All,
I am working on a simple cube with 2 Dimensions and one Fact table with Quantity.
Calender Date
As it is inventory related, I need to use Aggregation rule Last for measure Quantity.

I though its pretty easy. So built a cube with 2 dimensions and one fact table(Quantity). Checked the implementation
Quantity - Regular aggregate is Sum.
Defined Aggregation rule Last.

Validated the cube .
Issue: the grain of detail of at least one hierarchy under the date dimension does not match the grain of measure dimension in the cube.

How to go about this?
How can I see the Last period  working in Cognos Workspace Advanced, so that if I select Quantity and Year 2017 , I wanted to see whatever I can see in Dec , the same in the summary column?

Please advise
Thanks in advance


Online bus_pass_man

  • Statesman
  • ******
  • Join Date: May 2008
  • Posts: 252
  • Forum Citizenship: +31/-0
Re: Time State Aggregation Rules - IBM Cube Designer 10.2.2
« Reply #1 on: 12 Dec 2017 06:27:56 am »
There's another sentence in that validation message. 

"You must either match the grain of all the hierarchies under the dimension to the measure dimension grain, confirm that the level key is a surrogate key, or remove the aggregation rule."

I guessing you don't really understand the message, which is a pity as way to handle the situation depends on what the situation is.

The validation has observed that the key which you are using in the relationship between your fact table and your dimension isn't being used in any leaf level. It's a warning, not an error.

If you publish the cube as is you could get double counting.

The most common scenario, and the one that I think you're in is that the dimension grain is more detailed than the fact grain.  It is also possible that the key that you are using is a surrogate key.  It is also possible that you have an aggregate rule set when you don't really want it to be set.  I'm fairly certain that this isn't the case for you.

The redbook's 6.5.2 When the fact grain and dimension grain differ has some information on the subject.

The sample model has this scenario in it as an illustration.

Unlike DMR's scope relationship functionality, a dynamic cube doesn't have a way to define the fact grain of a dimension other than the brute force technique of truncating the dimension to the fact grain.
This means that if you have multiple cubes with facts at different levels of granularity to a dimension you need to create multiple instances of a dimension and truncate them to the appropriate level.

I'm assuming you will eventually want to have a model with multiple cubes and re-use as much as possible the work you've done.   You'd need to create a namespace for your dimension and name it something which would make sense (time or something like that.).   You would move the dimension into it.  You would then make another namespace and name it something which indicates the role in which the truncated dimension will play.   You then copy the time dimension into it.  You would then delete the levels which are below the fact grain and then add that dimension into your cube.  Or you could do what is done in the sample model and rename the truncated time dimension something like time to month or whatever and then edit it to remove the levels which are below the fact grain.
It's a bit of a hassle if you have to do further modeling of the dimension later on as you then need to replicate your work in multiple places but such is life.
You would merge the dimensions via virtual cubes.