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.
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. :)
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. :)
No comments:
Post a Comment