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.

    
      

Refresh Dashboard Automatically

                Lets now see how to refresh a dashboard page automatically once in every few seconds. This will refresh the entire page ofcourse. If you would want to refresh a part/ section of the page containing a report, then check this link.
                Go the 'Edit Dashboard' and beneath the reports, add a section with a 'Text' dashboard object inserted into it. In the Text, enter the below code as follows.

<META HTTP-EQUIV = "REFRESH" CONTENT = "3">
<META HTTP-EQUIV="CACHE-CONTROL" CONTENT="NO-CACHE" >
     
          This will refresh the page automatically in 3 seconds.

         You can then format the section to remove the borders and to turn off 'collapsible'.

  

         Now, when we save and see the dashboard, it will refresh the page every 3 seconds.

Automatic partial page refresh in OBI EE - Dashboard

      I had a customer query asking if it possible to refresh a portion of the dashboard automatically once in 3 seconds. The answer is pretty much yes using a java script. I shall blog about that now.

    Lets take the sample report below which we would want to refresh every 3 seconds in the dashboard page. The report should refresh for the corresponding year chosen as the prompt.Lets take the following report for example.

        Based on the year selected, the products and their revenue should be seen. The other requirement is that this report should automatically get refreshed once in every 3 seconds. Inorder to achieve this, first enable the 'Refresh' link to this report by going to 'Edit Dashboard'. Go to the report section and choose 'Report Links' as shown below.

Click on OK and save and Run.The Refresh link will now be available beneath the report in the dashboard. Right click the link and 'Copy Shortcut' to get the link and paste it in a notepad as follows:

On pasting in a notepad, the script should look something like this.

Now, use the above script and write the below java script.

<script>
function  partialRefresh(){
HereLink('d:dashboard~p:iid0novn57ohc76k~r:ou0jgrg1b4vgru0m','Refresh'); } setTimeout( "partialRefresh()", 3000  );
</script>

Edit the report and add a static Text view below the report. In the static text view, paste the above code and check the 'Contains HTML Markup' checkbox as below.

When you save it, it is as follows.

Save the report and the steps are done. Thus only a single report can be automatically refreshed every few seconds.