Friday, February 20, 2009

Dissecting Paint Repository

The other day I was trying to re-create the Paint repository shipped by Oracle. I had done a similar process for 'SH' (Sales History) repository and it was pretty simple. But while building 'Paint' repository, I learnt quite a few new things.  I'll go through some of the important things that will help understand how Paint repository works.

XML file as Source Database

The 'Paint' repository has the data shipped in a list of XML files. The XML files can be located in {OracleBI}\server\Sample\paint
  • Market
    This XML(or rather Table) has a column called LevelX which determines whether the other attributes present in the row describe a 'Region','District' or 'Market'. The hierarchy being Region -> District -> Markets where Markets corresponds to the lowest granular level dimension.
  • Period
    This XML also has a column called LevelX which determines whether the row describes the Time dimension 'Week','Month' or 'Year'. The hierarchy being Year -> Month -> Week with Week being the lowest granular level dimension.
  • Product
    This too has a column called 'LEVELX' which determines whether the row describes a Product Type, Brand or UPC. 
  • Fact
    This XML corresponds to the Fact table. Unlike other Fact tables that store measures at the lowest granular level, this table also has pre-aggregated measures stored at 'Region','Brand' and 'Month'.
  • Forecast
    This XML stores the Forcast Facts used for reporting purposes 
Dimensional Tables in Business Model

Paint repository has a total of three dimensional tables in its Business model. They are 'Markets', 'Periods' and 'Products'.  When we carefully examine source for the them, we can see that instead of having one instance of respective source tables, each of them have 3 instances as shown in the picture below. 



Let us dissect Markets dimension. In the Business model, the Markets Logical table(dimension) is made up of three instances of the physical table Market. The difference between each of this instances(Market, District, Region) could be found under 'Column Mapping' and 'Content' section of the Properties tab.

Since 'Market' is the lowest granular level at which facts are stored, all the columns in the Market logical table are mapped to this instance of Market Physical table. To find out when this instance will be invoked by BI server, we need to give a closer look at the Logical Level to which it is mapped. Here it is mapped to 'Market Key'. This means that whenever an user selects the 'Market' column, this partiuclar instance will be invoked.

Also, in 'Content' section we can see the optional where clause restricting to retrieve only rows at 'MARKET' level. The condition added in the where clause is 
Paint.""."".Market.LevelX='MARKET'


                         Fig.1 Market

Since 'District' is the next logical level in hierarchy, we will take a look at the 'District' instance. To identofy a District, it is must for us to know the 'Dsitrict' and the 'Region' it belongs to. All other columns(which are related to Market) are not required. Hence we see that in this instance, 'District'and 'Region' columns are mapped while all the other columns are unmapped. 'District' is the Logical Level to which this instance is mapped. Hence whenever an user selects the 'District' column in the presentation layer, this instance of the Market(physical table) will be invoked. THe following where clause is added in it to retrieve only Districts.

Paint.""."".Market.LevelX = 'DISTRICT'


                         Fig.2 District

Similarly, the Region instance is also mapped. 

In the next blog, I will talk about how Dimension Hierarchy is built for this repository. I will also talk about how to use Calculation wizard to create some calculated columns in Business Model.

4 comments:

  1. hai article is too good ....but culd u pls help me how do i import more than one table .as per ur above comments i have followed the steps to create one more repository using defualt Paint .But while importing i'm able to select only one table rather than 5 (paint repository having around 5 tables).kindly let me know is thr any shortcut keys to select these at a time while importing .

    ReplyDelete
  2. Hai article is too good ....but as per the notes i have tried to create one more repository using defualt paint(server-sample-paint) .But while importing i'm unable to select multiple table(while paint is having 5 tables).can u pls give me the steps to import these 5 tables(XML files) at a time .is ther any shortcut keys ..kindly suggest.

    ReplyDelete
  3. I am really sorry. I have been a lot dormant for a long time. Got into project not involving OBIEE. WIll try to come back to terms and give you an update.

    ReplyDelete
  4. wonderful information, I had come to know about your blog from my friend nandu , hyderabad,i have read atleast 7 posts of yours by now, and let me tell you, your website gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanks a ton once again, Regards, obiee training institutes in hyderebad


    ReplyDelete