HOW TO BUILD FRAMEWORK MODEL FLAWELESSLY

Hi Guys,

This is useful for those who have some idea how to create a model and are aware of all the terminologies used in cognos framework model.I have build 4 professional Framework models till now,However i always find it a challange when it comes to data correctness of the model.Below are the points that i have writen for my guidance going forward while building FM.

1) Import all the tables that you are going to use to build your model first.FM is the best place to analyse relationship.Keep option to auto detect relationship ON.

2) Whenever a key from one table is not used as foreign key in another table .Cognos treats them as Fact and dimension and creates a 1 to n relationship.

3) Understand what your project is all about first.Get a person from business to give you overall idea of business.Do some research on wikipedia to find how things work.Terminologies used.I believe its a waste to start building model without you having some proper idea of business. Once you have idea you know what you are trying to report.Going forward report development will be piece of cake for you and your team.

4) Understand the granularity by using FM diagrams .Make good use of hide dependencies option.

5) Get some key data that you are trying to output in a excel first.This will gurantee you that the hierarchies  that you are building are giving proper data.This is very important as in End mostly data issue comes. Check your data thoroughly for each dimension and measure you build.I know it will take some tim.But the rewards are huge.

6) Now Most important ans – Why is full outer join required when you are joining 2 facts.

the relationship between a fact and dimension is always 1 to n .This is not the reason why we have a full outer join.Remember full outer join usually has a confirmed dimension into picture that is there are 2 facts and one dimension .The relation between each fact and dimension is 1 to n .

Suppose you have sales and inventory stock and product and time (Which gives what stocks are there in store).There can be situation where a new product has come, so entry is there in product table and inventory level table and not a single has been sold so no entry in sales table.So without full outer join we cannot display product,inventory,sales that has all the product in store present in report. Now the question is in that case how can we say that the relationship is 1 to n we have to say its 0 to n(Absolutely correct). In our model(Go sales) we do not have thi
s condition.When we have 0 to n then its not a fact and dimension

However if you bring a time dimension into picture.Each inventory item is there in fact may be for this year or past 5 years.So the relationship 1 to n holds true.Remember that the product is the confirmed dimension over here and it has inventory and sales.However if you try to
show data of all the product that had inventory then with right outer ( sales-product <-> inventory-product).You will have all the product for the year specified that had inventory.If you do a left outer you will have all the products that had sales. If you want to show list of all the product,inventory,sales then you need to go for full outer join.

Conside the case of product, Inventory level and order details. Here product is the confirmed dimension and Inventory level and order details are facts. Now

Below is the actual query that is generated.Its result of a Minimized sql option setup.

select (coalesce(“D2″.”PRODUCT_NUMBER”, “D3″.”PRODUCT_NUMBER”)) “PRODUCT_NUMBER”,
“D3″.”ORDER_NUMBER” “ORDER_NUMBER”,
“D2″.”OPENING_INVENTORY” “OPENING_INVENTORY”
from (
select distinct “ORDER_DETAILS”.”PRODUCT_NUMBER” “PRODUCT_NUMBER”,
“ORDER_DETAILS”.”ORDER_NUMBER” “ORDER_NUMBER”
from “GOSALES”.”ORDER_DETAILS” “ORDER_DETAILS”) “D3″
FULL OUTER JOIN (
select “INVENTORY_LEVELS”.”PRODUCT_NUMBER” “PRODUCT_NUMBER”,
sum(“INVENTORY_LEVELS”.”OPENING_INVENTORY”) “OPENING_INVENTORY”
from “GOSALES”.”INVENTORY_LEVELS” “INVENTORY_LEVELS”
group by “INVENTORY_LEVELS”.”PRODUCT_NUMBER”) “D2″
on “D3″.”PRODUCT_NUMBER”=”D2″.”PRODUCT_NUMBER”

Lets look why a full outer join is required in the above case.

Here every product has order and every product is there in the inventory

7)If something is only joining on RSUM its a issue.RSUM is just sequence of numbers.when cognos cannot find any valid key to join on then it will use this.

8) Reading Cardinality- -

———————————————————————————————–
Sales staff(1:1) joined to order(0:n)

While reading always read opposite. each order has only one and only one sales staff
Each sales staff has zero or n orders

9) Determinants 

determinants come into picture if we have multiple facts at different granularity .. like one at month level and one at day level .Determinent identify set of database columns that uniquely identify set of database data … if the time has 2 joins one at month level with one fact and one at day level with another fact it comes into play .

Cognos get confused about the level of data .. that is whether month is the lowest level or day is the lowest level so to clear this we need to define determinants

In case of all our dimensional models we have defined time as year level month level and day level this makes sure that cognos know what is what level so we never need
determinents for time ..

Suppose we are joining 2 facts with one confirmed dimension in that case the data is grouped at lowest common level notice common so if one is at day and one is at month
the lowest common level should be month and not day … so when by mistake cognos takes day as lowest common level because it does not know the relation between day and month
we have a double counting

10)Different cases that i encountered.

1) If you pick product name from product name lookup , opening inventory from inventory level.You see that the total inventory repeats for each and every product name.If you pick product number from product table and opening inventory from inventory level.You see that opening inventory splits according to product number.What is the reason.

Probable– It treats product name lookup and inventory level as two separates facts and does not try to join them .In this case since its picking data from 2 facts without any dimensional info reuired in the report.It wont bring any dimension into the query ,It can directly show the data as 2 measures that are not related in any way.

11) First thing to consider whenever you look at any table is what is the primary key.That tells you what other tables are required to properly check data.Best place to look for primary keys is the determinant section of query item.

Thing to consider.

We are using a OLTP system only cognos sees it as a Fact and dimension .The Main idea is to protect dimensional information. We have to consider that all the products are present in product table .Take example of inventory level ,product and sales target and time .Entries for product are kept in inventory table only when the product is available. Similarly sales target is not defined for all the products for the year.

Now if when we have join between inventory product and then a join with sales target it has to be a full outer join.If we want all the products to be shown.This will show only those product that have sales target or inventory level or both.But not those products that are not in sales target and in inventory level.Like a new product that got just added. (Keep that in mind)
Now if you want to show how many orders were there for a country.We have country,branch,order header,order detail.You will observe that the relationships are all 1 to n in sequence.That is
country to branch t to n , branch to order header 1 to n and order header to order detail 1 to n.

When you pick data from country and branch with inner join all the data will come.as their relationship is 1 to n.That is a county is present for each and every branch. If the relationship was 0 to N. then outer join would have been used to get all the countries

How cognos decides which side is 1:1 and which side is 1:N

When product is joined to inventory level on product number.Product number is key for product table and is unique in it so its 1:1 whereas in inventory level.Product level repeats so its 1 to n.
What happens if we have a 1 to N relationship and we change it directly to 1 to 1.Will the data be affected and if yes why??

The data will come correct.The only thing that will be affected is aggregation.Cognos will not be able to aggregate properly.That is if you say product line has

Curious case of Unit cost and product name

This looks preety simple but takes long time to understand. Every product has unit cost . Unit cost looks like a measure , however it cannot be summed up across the product hierarchy like product line >product type > product . as it makes no sense .Similarly if you have time dimension and your product unit cost changes many times in a month base d on order , what do you do.One way is to make product unit cost as a attribute and put it in a dimension.Now this will create lot of problems as the product unit cost is not fixed set of values and so could not be treated as a dimension.

So either you will show all the product values,Now remember every product might have 2 values in a month so your data is repeating so you need to set the regular aggregate to average.The unit cost will only make perfect sense when all the dimension related to it are present .Like order , product and time. Otherwise it should be set to average

Understanding Scope in Cognos Framework

Scope does not govern where clause or how the query is formed.Very important its a common misconception .However if you have dimension and levels in your model.Scope does affect the drill up and drill down capabilities, if you change the scope of product hierarchy and inventory and set the scope of inventory for product line only instead of full hierarchy you will see that the
values will change drastically.This proves that scope affects the values while doing drill up and drill down

Star schema grouping

They are differnet from creating a shortcut.Its same as creating shortcuts and movign them to a new namespace

Where to create relationships .Business layer or Database layer.

If you create relationshp or determinants on model query subjects they wont go for minimized sql they will work as a view. It also effects how other joint to query item are treated– Very important Page 329