Saturday 28 April 2012

Framework Issues


In Go sales model order detail and order header are combined to make Sales model query subject .where sales acts as a fact .You will notice that the order header does not have any fact data alone .but when combined with order detail it becomes a fact .

By making it sales fact the model is simplified .The model represents a star schema and is much easier to track. Its difficult in actual scenario to find such a group of tables but worth looking for .

Similary in 1 to n relationship either represent fact or hierarchies are represented.Consider the case of product line , product type ,product they follow 1 to n in a line .

In the screenshot below notice that if you drag item from product brand and product type there are two path for the query this causes ambiguity . So it is solved by removing the join between product brand and sales target .In the other diagram 2 you will notice a new query item products is created which is joined to on product type code to sales target and product brand code to product brand .This new object product is combination of product line ,product type ,product .So actually we are limiting the join of sales target with product only based on product type and the direct join based on product brand does not exists .




The loop is solved in below screenshot.Notice that Products on (right hand corner ) is different from Product at center

Diagram 2




Now notice that the products that is there is a model query subject .so whats the idea behind this??


Above issue is solved below diagram 

Scenario if you drag sales and branch there are two paths that exists that is taken care below


Notice that the sales target that is connected to product type is actually a model query subject that is joined to product type base on product type code.

So its a good place to understand the difference between using model query subjects and creating join to them v/s using a shortcut

As per IBM cognos 10 Help file

1) Shortcut are easy to maintain and are automatically updated if the table structure changes
2) Shortcut are exact replicas and do not allow addition of columns whereas if you create model query subjects you can add or remove columns from query subjects.
3) Creating shortcut does not create a copy of the joins.
4)However if you directly create a copy of query subject it will create a copy of all the joins of that query subject(In our example we have created a new model query subject and not a copy of query subject)
.Its gives just for knowledge purpose.



Point to Ponder -- How will the join form if we take data from sales target and product type .Will the join differ based on among the two which sales target we choose ??

Even in case of shortcut how will the join that is formed decided on?? It depends on where you pick the data from which sales fact


The product forecast and sales also forms a loop we will look below 

As you can see below the retailer type is made by combining retailer and retailer type .Its actually a model query subject,So by creating a join with model query subject we have solved the loop by ensuring that whatever way cognos decides to choose for the loop the joins remain same .Better explained below with products example 

Notice that products is a combination of product type,product line and product .Its between sales target and sales .Now this is a loop as you can also travel from produt type , product and sales.(product line is hidden in diagram dont worry) .So actual any path it takes the join remain the same and so the outputwill be same and hence the loop is solved .

By creating products between sales target and product forecast we have ensured that the joins remain the same for both paths of the loop.

Please refer this diagram for my question 


No comments:

Post a Comment