Essbase attribute dimension in OBIEE.

World is changing and becoming bigger and faster and so is the data and need to get useful information at seconds notice. Big organizations are demanding quality reports and decision making system which are even faster than Usain Bolt. Relational systems are good for transactional system but when it comes to DSS or OLAP system, multidimensional database, Hadoop are fast replacing relational databases. Essbase is becoming more and more prominent in datawarehousing and business intelligence world. Essbase's multidimensional structure and highly optimized server help in gaining that advantage over relational databases like Oracle and SQL server as far as performance in concerned.

Now with OBIEE also supporting essbase well, most of the business intelligence system are moving towards adopting essbase and OBIEE for datawarehousing and reporting system. As people having knowledge of OBIEE and essbase and its integration would say that it is very easy to expose any essbase cube in OBIEE , however sometimes there are a few things in essbase that gives pain while creating RPD. One of those is attribute dimension. Attribute dimension in an essbase cube is a separate helper dimension with other dimensions. While importing an essbase cube in OBIEE, all the dimensions would be imported using the outline present for an essbase cube. As you would guess, attribute dimension would also get imported as a separate dimension. However as attribute dimensions logically represent attributes of other dimension, it would make sense if the attributes are imported inside the base dimension itself. Consider a product dimension in an essbase cube having data for different products and its sales and cost data stored in the cube. These products has an attribute dimension called packagetype which gives what is the packaging type for a particular product. When using other reporting tools like smartview a person can select attribute value for which data needs to be analysed.



The above figure shows how we can select an attribute dinension value in smartview. However there can be a requirement to shows dimension members and corresponding attributes in a single place. OBIEE being a relational type reporting tool should do this be selecting the product member column exposed in OBIEE and corresponding attribute from the attribute dimension. However on the contrary when you do this you would get a report that would show you something like a cross product of all the members of product and packagetype dimension. This is the default behaviour of essbase as the MDX generated by default has a cross join keyword.

For example suppose the product dimension has 3 members as P1, P2 and P3 available and the attribute dimension has members as TinnedCan and CardBoardBox. Say that product P3 is only packaged as TinnedCan and others are packaged in CardBoardBox. So the logical report should show something like

Product                        PackageType
P1                                CardBoardBox
P2                                CardBoardBox
P3                                TinnedCan

However the result that would be displayed would be


Product                        PackageType
P1                                CardBoardBox
P1                                TinnedCan
P2                                CardBoardBox
P2                                TinnedCan 
P3                                CardBoardBox
P3                                TinnedCan

To resolve this issue we can create cube column in physical layer of RPD and move that column inside the base dimension, here inside product at correct generation level and change the column type to Attribute and in external name provide the external name or the name in essbase cube for that attribute dimension.

Voila!!! This would resolve the issue of cross join and a user can easily generate dimension only reports in OBIEE with attributes as well. 

I guess this is all for today. We would meet and see another topic next time which can help us conquer the bigdata world. Till then Enjoy. :)

Aggregate Navigation in OBIEE

Starting from the last post, where we stopped about aggregation features in OBIEE. Today we will be discussing about aggregate navigation.To reap the benefit of pre aggregated fact tables OBIEE provides a way so that OBIEE automatically decides on itself which table to use for generating a report based on the aggregation level of the query.

To understand the concept of aggregate navigation, let us consider a situation where we have revenue calculated at detail day level (Revenue) and one aggregated at month level(Revenue Agg).  We would follow the below mentioned steps to activate aggregate navigation.

1.       First step is to import both the tables to physical layer of RPD.

2.       Now in the BMM layer, we would create a logical table Revenue.

3.       In the logical table “Revenue”, add a logical table source Revenue. This can be done by clicking on Revenue table in physical layer and dragging and dropping it on the “Revenue” logical table.

4.       On double clicking on LTS Revenue, we will see only Revenue LTS in the Map to these tables area showing that the columns of this logical table maps to only Revenue LTS.  Now drag the physical table Revenue_Agg from physical layer and drop it on logical table Revenue. After this we could see the LTS Revenue as well as Revenue_Agg in the logical table sources for logical table Revenue.

 

5.       Now as these tables in database are at different granular level hence we need to tell OBIEE that these tables are at different levels of granularity. To do this we first click on Revenue LTS and move to Content tab.

 

6.       Now if we do not specify any levels for different dimensions to which this table is related (Location, Product and Time here), then the default level of aggregation would be detail level for each dimensional hierarchy. However to specify different levels for time dimension on which Revenue tables are aggregated we need to specifically specify the level of aggregation in each LTS.

To do this in LTS Revenue content tab, we would specify detail level for time dimension.

Since we are not specifying any level for other dimensions, so the default now would be considered as total level for other dimensions. This is contrasting to when no level is specified for any of the dimensions in which case the default level is detail level.

So to get the correct functionality we would specify detail level for other dimensions too in Revenue LTS.

Now for the other LTS Revenue_Agg, specify period level as time dimension and detail level for other dimensions.

Now when a report is created with column from time dimension as period then Revenue_Agg is queried while if we have column which is lower than period level then Revenue table is queried.