I had a requirement from a customer who wanted to see for a particular year, the sales for all the months for a given product. However, the fact had data only for products that were sold in some months in a year (i.e not all products were sold across all the months in the year). The customer wanted to see a null record for those months, for those products in the report.
To be able to produce a report that will have sales for all months in a year even though sales dint happen for a few months, we can do an outerjoin to get the desired result. But, the moment, we add a filter clause (like product name='DVD Player' or year = 2010), the result will not have rows for all the months. Instead it will only produce results for the months that has data. This is not what we want. Inorder to solve this, we will have to trick OBI EE to build a cartesian product and then to return the data for all months even with the filter.
Lets first consider the below example wherein i have 2 dimensions (product, time) and a fact at day level data.
Inorder to achieve the above report, we introduce another table called Dummy. This table can have only one column with value 1 populated in it as below.
In the physical layer, join the dimensions product and time to this dummy table as follows. Make a complex join between the dummy and the dimensions with the condition as 1=1.
Pull the dummy table to the BMM layer and it would look as follows: (This can be the normal inner join between dummy and the dimensions). However the products and fact would have a outer join. Likewise the time and fact would have an outer join as well.
and likewise between time and fact. (i,e they will have an outer join). Set the aggregation for the dummy column as sum.Add the dummy table to the presentation layer and we can test the results.In all the reports, pull the dummy column and add the filter (dummy is not null) to achieve the cartesian product.
Build a report by pulling in the product, time and sales columns. Add the following filter criteria to it.
when the results are viewed, we get the desired results.
To be able to produce a report that will have sales for all months in a year even though sales dint happen for a few months, we can do an outerjoin to get the desired result. But, the moment, we add a filter clause (like product name='DVD Player' or year = 2010), the result will not have rows for all the months. Instead it will only produce results for the months that has data. This is not what we want. Inorder to solve this, we will have to trick OBI EE to build a cartesian product and then to return the data for all months even with the filter.
Lets first consider the below example wherein i have 2 dimensions (product, time) and a fact at day level data.
Inorder to achieve the above report, we introduce another table called Dummy. This table can have only one column with value 1 populated in it as below.
Pull the dummy table to the BMM layer and it would look as follows: (This can be the normal inner join between dummy and the dimensions). However the products and fact would have a outer join. Likewise the time and fact would have an outer join as well.
Build a report by pulling in the product, time and sales columns. Add the following filter criteria to it.
when the results are viewed, we get the desired results.
No comments:
Post a Comment