Business Intelligence – Oracle

Archive for November 5th, 2007

Oracle BI EE – SAMetaExport – For improving Query Performance – Precursor for Materialized Views

Posted by Venkatakrishnan J on November 5, 2007

I came across another handy utility called as the SAMetaExport. What this utility can do is, it can convert the dimensions(hierarchies) that you define in the Business Model layer of the Administration tool into actual CREATE DIMENSION script for Oracle. As you would know Dimensions in an Oracle database help in determining the Query Rewrites while using Materialized Views. You can get this utility from {OracleBI}/Server/Bin. The syntax for using this tool is as follows

SAMetaExport···-r···”<full path including the Repository Name>”···-u <username> -p <password> -f <full path of the filename containing details about the BM name etc> -t “ORACLE”

As you see above this utility requires another file as its input. This file should include the following details

BUSINESS_MODEL= “<Business Model Name>”
PHYSICAL_DATABASE =”<Physical Database Name>”
RUN_AS_USER = “Administrator”
OUTPUT_FOLDER = “D:\oracle\OracleBI\Oracle Export”

For ex:

RUN_AS_USER = “Administrator”
OUTPUT_FOLDER = “D:\oracle\OracleBI\Oracle Export”



Let us try running this utility and see the output generated. The output would be a sql file which would have the CREATE DIMENSION script.



Posted in All Posts, OBI EE Plus | 1 Comment »

Oracle BI EE – Understanding Todate and AGO – Achieving YTD, QTD and MTD

Posted by Venkatakrishnan J on November 5, 2007

Though i had known about the existence of repository functions Ago and TODate, i had never used them before especially in the context of achieving YTD, QTD and MTD. So, i thought i would give it a try and see how it works and of course find out the possible use cases of using these functions. In this article we would see how to go about using ToDate function. Just remember that we do not have these functions in Answers. To use these one would have to create custom logical columns in the repository. Before we start lets look at the syntax of both of these functions.


      TODATE(<Measure Column>, <Level Column in the Time Dimension>)



      AGO(<Measure Column>, <Level Column in the Time Dimension>, <Shift Required>)


Now lets look at TODATE function in particular. According to the docs TODATE function helps in achieving Year to Date(YTD), Month to Date(MTD) and Quarter to Date(QTD) kind of functionality. So what does YTD, QTD and MTD mean in the context of say Sales(a measure). Lets understand these first.

YTD Sale   –   It means the summation of sales from 1st Jan of the current year to the Current Date. The current Date is kind of a misnomer since its value can vary(can have values like day, month, quarter etc) depending on the granularity. We will understand this while going over these functions in detail.

QTD Sale   –   It means the summation of sales from first of first month of the current quarter of the current year to the Current Date.

MTD Sale   –   It means the summation of sales from first of the current month to the Current Date.

The base schema that i use to demonstrate for this will be the BISE1_TUTORIALWH that gets bundled with bise1. The first step that i did was to create a time Dimension. TODATE and AGO can be used only along with Time Dimensions. Lets create one with the following hierarchy.

      Year   ->   Quarter   ->   Month of Year   -> Dimension Key


Remeber while creating the Dimension mark it as a Time Dimension.


Also, the lowest unique level has to be identified as the chronological key for the time dimension. In our case Dimension Key would be the chronological key.


Now lets create a new Logical column YTD Sales. As you see below, TODATE will take sales as one parameter and a level in the Time Dimension(not the logical table) as another parameter. Similarly, lets create QTD Sale and MTD Sale.



Remember that these custom logical columns are very specific regarding their granularity. Varying the granularity will give wrong results in the answes. Lets understand this by creating a simple report. Our report will have the following columns

   Year   Quarter   Month   Sales   YTD sales


Lets look at the results and try to understand what this function does.


As you see above YTD Sales basically achieves a rolling sum. For example, If you take Year 2003, Quarter 1 and Month 2, the sales is 20,095. But the YTD Sale is a summation of Month 1 + Month 2 = 20095 + 3683 = 23778. We do not have a day level granularity. We only have a month level grain in the time dimension hence, it rolls it up to the month level. Now lets remove the Month of year column and just have Year, Quarter, Sales and YTD Sales.



Oops. What has happened to the YTD? According to the definition of YTD, for Year 2003 and Quarter 1, we must have a summation of sales for all the months in Quarter 1. i.e. 3683 + 20095 + 4157 = 27936. But what we have in the actual result is 55397. How is that possible? Thats one of the reasons why understanding grain in TODATE and AGO is very important. As we removed the month from the report, what YTD has done is it has done a summation of the YTD values of all the months in the previous report instead of the Sales ie. 3683 + 23778 + 27936 = 55397. Same would be the case for MTD and QTD.



Here we have seen only TODATE. Lets looks at AGO in a later article.

Posted in All Posts, OBI EE Plus | 28 Comments »