Determinants in Framework Manager

Determinants are an feature of cognos BI suite which provide more control over the granularity of a data item. It is mainly used to identify at what level of granularity the fields should be grouped, and at which level fileds are uniquely identified in a query. It also hellps in avoiding double counting.
Data items within Query1 according to their hierarchy:
  1.  Year
  2. Quarter
  3. Month
  4. Week
  5. Day

If we have an fact known as quantity sold then this fact could be grouped at each level in the above query.
But often while working on project it happens that our fact lies at multiple grains viz revenue is at day  level where as planned revenue is at month level.

Lets us consider the below example
Table 1 RegionDim
Region
Country
State
City
Location
APAC
India
Gujarat
Surat
Adajan
APAC
India
Gujarat
Surat
Parle point
APAC
India
Gujarat
Surat
Citylight
APAC
India
Gujarat
Surat
Pal

Table 2 Target Fact
City
Month
Planned Revenue
Planned Effort
Surat
Jan
75000000 Rs
22 Days
Islamabad
Jan
50000000 Rs
20 Days

Now if we try to create a Query Subject(Q3) with the following data items
  1. State
  2. City
  3. Planned Revenue
  4. Planned Effort

and try to test the output, it will be as follows
State
City
Planned Revenue
Planned Efforts
Gujarat
Surat
75000000 Rs
22 Days
Gujarat
Surat
75000000 Rs
22 Days
Gujarat
Surat
75000000 Rs
22 Days
Gujarat
Surat
75000000 Rs
22 Days

As there are four areas for the same city Surat, it forces model to return four repeating rows. So in order to remove this double counting we need to use a mechanism called as determinants.

Following are the steps to create determinants.
  1.   Right click the query subject RegionDim and select the determinant tab.
  2.  Create a determinant by clicking Add button & rename it.
  3.  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 (Area 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).
Name
Uniquely Identified
Group by
Region

N
Country

N
State

N
City

N
Area
Y
N

Keys
Attribute

Region




Now if we try to test the result of query subject Q3, it will be
State
City
Planned Revenue
Planned Efforts
Gujarat
Surat
75000000 Rs
22 Days

I hope concept of determinants is clear.
Thanks Keep Reading.

No comments: