Granularity is a simple concept describing the level data relates to.
When the model query subject is now run, only one row of data is returned:
Imagine sales areas:
Level1: Country
Level2: Sales Area
Level3: State
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.
An example of this is budgets.
Using our example above, we want to join our sales area table to our budgets table.
Level1: Country
Level2: Sales Area
Level3: State
Level4: City
Level5: Store
Level6: DepartmentLevel7: 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 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
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).
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:
Great explanation with nice examples...Thank you
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteThank you Explained good.
ReplyDeleteGood stuff for congos learners. please share more topics with examples. thanks a lot.
ReplyDeleteThank you for the vivid explanation in a easy way. No complex terms but simply meaningful.
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteGreat explanation! Just one question; IBM says it's a best practice to check only UI OR Group by in the same determinant.. Why do you checked both in Employee? If you remove Group by check, what hepped to you example/data?
ReplyDeletePlease explain why Country is set as 'Key'
ReplyDelete