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.