Combine fact at 2 levels - Base and aggregate fact

        Lets now see how to combine data from 2 facts which are at different levels. i.e consider the following dimensions product, time and a fact. The fact has records at day level for each of the product. There is also another aggregated fact at the month level. The reports, when they query for a monthly or a yearly report, it would be beneficial to hit the aggregated fact rather than the day level fact for improved performance.

      To model this, have a time dimension aggregated at the month level. (i.e) for ex -

        Import this table into the physical layer and pull into the BMM layer as below.

     Import the aggregated fact into the physical layer and into the BMM layer as below.

     Go to the content tab of each of the LTS of the both the time dimension and the fact and set the levels to each of them as below.In the time dimension, for the aggregated dimension, set it to the level of Month so that when a report queries at month level or higher, this dimension is used. Similarly for the fact aggregate, set it to Month level for the Time dimension as below. For the base level time dimension and fact, set it to the leaf level (day) in this case.

 For aggregated fact,

 For base time dimension

   For base fact,

 
Now when the reports are generated with the month or a level greater then month included in the report, it would hit the month aggregated and fact aggregated tables. Only when a report uses the date (day level data), the base fact would be used. Thus the performance is improved.

Outer Join in BI EE with 'where' clause

          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.

Set the value to a session variable from the dashboard URL in 11g

        Let me now blog how to set the value to a session variable in 11g from a dashboard URL. This is done in a way different from 10g.

       Create an initialization block to assign a row-wise initialization variable as below. Assign any dummy value to it. This is the value that will be set from the URL.

  Go to the actionschemas.xml file located in 'C:\BIEE11g\Oracle_BI1\bifoundation\web\display' where BIEE11g is my BI home folder. Between the

<AuthenticationSchemaGroup>
<!-- Group for HTTP POST login -->
<AuthenticationSchemaGroup>
tags

add the below line
 <RequestVariable source="url" type="informational" nameInSource="DSN" biVariableName="NQ_SESSION.DSN"/>

        Save and then restart the BI services. Now, we can test this by passing the value of the variable as below.

1) from dashboard URL :

http://vsethura-idc:9704/analytics/saw.dll?Dashboard&DSN=set_from_url

or
2) from GO URL:
http://vsethura-idc:9704/analytics/saw.dll?GO&Path=/shared/Blogs/SessionVariable_GoURL/Sessionvariable&NQUser=weblogic&NQPassword=welcome1&DSN=set_from_url

Login to OBI EE, and create the following report

Add a column with the column formula as below:



and save the report as sessionvariable. When the results are viewed, it is as below:

Thus we can set the non-system session variable's value via URL. However, we cannot set the USER , PASSWORD variable this way.