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.