Tuesday, 28 October 2008

Granularity & Determinants in Framework Manager

Hi,

following a question from Divya last week, this blog relates to granularity and determinants in Framework Manager.

Granularity is a simple concept describing the level data relates to.
Imagine sales areas:

Level1: Country
Level2: Sales Area
Level3: State
Level4: City
Level5: Store
Level6: Department
Level7: Employee

We can summarise sales volume (quantity) at each of the levels above.
The level at which we report is the grain (or granularity) of the table.

Tables or query subjects in Framework Manager often have multiple levels (grains) which you may have to join to another table with a different level grain.

An example of this is forecasts or budgets.
Using our example above, we want to join our sales area table to our budgets table. Unfortunately the grain of the sales area table is employee but the budgets have been completed at Store level.

In Framework Manager, if we join directly from store in sales areas to store in the budget data the budget data value will be repeated for how many employees are part of that store.












New Query Subject (join on Store with no determinants set):
Sales Area.City
Sales Area.Store
Budget.Month
Budget.Sales_Target

Result:







































As there are four employees at the Bayside store this forces the model query subject to return four rows repeating the sales budget.

To correct this Framework Manager has to be instructed what level of granularity the budget applies to. This is done using determinants.

In our example we need to set the determinants on the Sales Area query subject.
Right click the query subject and select the determinants tab.

One determinant should be created for each level of granularity as shown below.
Create the determinant by clicking Add.
Rename the determinant and then drag the identifying column (Key) into the key window (bottom left). If the level has any attributes that exist at the same level of granularity these should be added to the Attribute window.
If the new determinant you have created is the lowest level grain (Employee in this case) the Uniquely Identified check box should be ticked.
The Group By tickbox should be ticked if the level is ever to be used for aggregating values.

Ensure that once you have created all of your determinants for the query subject that they are in the correct order (use the up and down buttons to sort them correctly).








































After setting the determinants click OK to save the changes.
When the model query subject is now run, only one row of data is returned:





































I hope this helps

Phil

Labels: , ,

24 Comments:

At 10 February 2009 at 18:43 , Blogger Malathi said...

Hi Phil,

I have read your post about the Granularity & Determinants.
You have explained it really in a very good manner. I read about this in the FM user guide before but couldn't understand much but now I got it clearly. Thanks much!

Can you please let me know the process of performance tuning of the reports in Cognos8.

Thanks,
Malathi

 
At 10 February 2009 at 20:30 , Blogger Phil Thompson said...

Hi Malathi, I'm glad the post helped. It's nice to get feedback. I'm afraid I'm not going to be documenting the process of performance tuning the reports in Cognos8 though. This is a good income stream for Digital Viper and we don't want to give all of our secrets away! Needless to say that the majority of tuning can be done at the database and Framework Manager level.

Phil

 
At 6 May 2009 at 11:51 , Blogger Vamshi said...

Hi Phill,

Very nice to meet you.

I need some help and guidence from you.

Please explain me about the Cognos8 Planning..i mean from where to start and how to learn and how to become master on Cognos performence Managment (cpm).


Thanks for your time and waiting for ur valuable reply.

 
At 6 May 2009 at 12:14 , Blogger Phil Thompson said...

Hi Vamshi,

thanks for your enquiry. I have found that the best developers in B.I. and planning are those that have a background in business and then move into IT. Assuming you are trying to learn the software I would suggest that to start off with that your company contacts your local Cognos sales office. Get them to give you an evaluation copy of the software you need. Try installing and configuring on a virtual server and then follow the samples guides. To improve you then need to attend the Cognos courses or get a mentor to guide you.

Good luck!

Phil

 
At 3 June 2009 at 04:56 , Blogger Mr said...

This comment has been removed by the author.

 
At 3 June 2009 at 04:58 , Blogger Mr said...

I read your post and it helps in understanding the determinants as you have explained with a good example.

One concern I had with the post was why have you specified the Employee as both Unique as well as Group by. If it's unique why it needs to grouped by?

Other question I had about determinants is do I always need to specify a level which is unique?

 
At 4 June 2009 at 09:58 , Blogger Black Flag said...

Good guide here, helped improve my understanding of a process which I already used.. but without complete understanding. Cheers.

 
At 4 June 2009 at 11:16 , Blogger Phil Thompson said...

Hi Black Flag - Thanks for your comments. It's nice to get positive feedback.

Phil

 
At 17 June 2009 at 13:32 , Blogger tashfeld said...

This is the first time I have needed to use determinants seriously within C8. All the examples out there I found were related to time dimensions which did not apply to me. I was able to take your example and apply it to my business case and solve my issue. Thank you so much!

 
At 22 June 2009 at 14:32 , Blogger BI and Cognos Authors said...

Very good post.. Thanks..

 
At 24 August 2009 at 06:15 , Blogger kalyani said...

Hi Phil,

How to increase cube performance.

Thanks
Kalyani

 
At 25 August 2009 at 11:49 , Blogger Simon said...

Good post, thanks!

One thing though.....

I have an organizational Strucutre which varies over time. i.e. in year 0607 the structure will be set, but in 0708 it may vary slightly. It only changes yearly.

I'd like to have a query subject consisting of determinants which will allow for these yearly changes.

I understand I can have multiple hierarchies in a Regular dimension, but can I have the same in a Query Subject?

~thanks
Simon

 
At 25 August 2009 at 12:20 , Blogger Phil Thompson said...

Hi Simon, thanks for your comments. If I understand you correctly, your organisation structure will have more levels in 0708 than in 0607?
If so Why not set your query subject with a number of nominal levels (say 10). You can then set up the grouping and determinants up against these. The contents of the levels can change of course so that in 0607 level8 may be blank but in 0809 it may have an entry. Query subjects frequently have more than one hierarchy within them. It's up to you how you define the use of the data in the regular dimensions which sets out the hierarchy for use by the users. You can always create an 'alias' of the same query subject and create another regular dimension from it..

Phil

 
At 25 August 2009 at 13:42 , Blogger Simon said...

This comment has been removed by the author.

 
At 25 August 2009 at 14:15 , Blogger Simon said...

Got it sorted Phil, issue was with a different Dim. All ok now!

 
At 27 August 2009 at 18:49 , Blogger Narayana said...

Hi,
Its very informative, nice exlanations.
I want to use multi select prompt in Framework manager level.
Is it possible.

Advance thanks.

 
At 28 August 2009 at 11:26 , Blogger Phil Thompson said...

Nrayana,

multi select prompts from FM should be possible. I haven't used them but I'll model it and see how I get on.

Phil

 
At 22 January 2010 at 14:06 , Blogger Bhanu said...

Very relevant and nicely explained piece of information for Cognos guys. Just wanted to keep this info on top in 2010 search too. Thanks Phil.

Bhanu

 
At 15 July 2010 at 15:18 , Blogger Henk said...

Thanks for explaning, I will test this for myself, but does this apply to relational models, dimensional models or both ?

Thanks, Henk

 
At 28 July 2010 at 13:13 , Blogger vidya said...

Hi Phill,

Thanks for giving brief description about Granularity & Determinants.

I need help from u that, how can we provide a security to a report in cognos.
pls let me know about this.
Thanks,
Vidya S

 
At 30 August 2010 at 05:51 , Blogger hai said...

hi phil,

i have been searching for concept of detemintants in F.M through lot of blogs but i haven't got a good idea on it.
you exapalined very well.

Thanks a lot
hareesh

 
At 14 June 2011 at 04:52 , Blogger chiranjeevi said...

excellent
chiru

 
At 3 June 2012 at 08:45 , Blogger Piyush Shukla said...

Hi Phil,
I wanted your guidance in understanding the Attributes specified for a determinant. What is their significance. For example a Day_Key Determinant has Month as an attribute which is a different level I am a little confused why.

Thanks
Piyush

 
At 23 August 2013 at 13:48 , Blogger Sasidhar said...

Hi Phil,

I have read your post about the Granularity and Determinants.
You have explained really good.It is very usefull for FM model development.

Thanks,
Sasidhar

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home