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
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.
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