Recently we concluded a very interesting project to migrate Reports from Cognos to Business Objects. We have in scope few Cognos framework models, Cognos cubes and Cognos reports.
The most challenging part was to migrate from the Cognos Cube to relational universes in BusinessObjects. Cognos cubes stores data in itself so it provides a very good report performance plus Cognos cubes contain certain roll ups like last period as time state, last period as average etc, for which there is no direct implementation in BO Universe.
So we end up with not only the respective universes and reports but also certain data marts with aggregated tables in it obviously to take care of performance.
I would like to list below some of the challenges and the solution approach we found out:
- Report Performance: This is always the most important criterion to achieve for any migration from one reporting tool to other. To achieve the same performance as the Cognos cubes we opted for aggregated tables based on different time dimensions. There are two options here for the aggregated tables:
i. Approach One: Aggregated tables as per time dimensions and key values for other dimensions in it.
In Universe Design, these aggregate tables will be joined by the dimension keys with related dimension tables. As there will be joins between multiple aggregated tables to the same dimension tables so it will result in loop. Multiple contexts need to be created to resolve the loops. Aggregate awareness is applied at required measure objects.
ii.Approach Two: Island aggregated tables which contains not only fact data aggregated based on time but also all other dimensions in it. For example, tables for yearly, monthly, weekly with all dimensions data along with fact data in it. It is like self sufficient aggregated tables.
In universe Design, aggregate awareness is required not only for fact measures object but also for dimensions objects. Joins between aggregate table and dimension tables are not required so we don’t need contexts either.
Pros/Cons of the above approaches: In the second approach any changes done in dimension tables will not be visible directly in our aggregate tables as they are not linked to base dimension tables. But the performance is far-far better in second approach as it get rid of all the joins between tables. The aggregate table sizes will be huge in database for second approach.
What to choose now?
If there are no changes happening in dimension tables and performance is a critical factor then go for approach two. But again any changes in dimension tables will result in changes in ETL jobs to build the aggregated tables again.
But if there are regular changes happening in dimensional table and performance is not a critical factor then go for approach one. As the aggregated tables are joined with base dimension tables it will always reflect any dimension changes. But report performance will not be as good as approach two. - Time state rollup as Last period: In Cognos Cube, this property can be set for any measure objects. It means that for a particular time context the last period value will be shown. Example, consider a measure as inventory quantity and that Time state rollup is selected as Last period for this measure object. Now for a weekly report measure object will show last day’s sales value from that week. For a monthly, it will show the sales value from the last week of that month which eventually will be last day’s sales value from the week.
To achieve the same in BusinessObjects, create aggregated tables with last period data pre-calculated in it. In universe design we need to apply aggregate awareness to pick the measures objects from aggregated tables. Else in case we are not using the aggregated tables we have to create a reference table. This reference table contains the logic to check the last period for a certain time context. Then this reference tables is to be joined with the fact tables in the universe. - Time state rollup as Average: Again, this property can be set for any measure objects in Cognos cube. It means that average is calculated of the measure values from all categories in the time period being examined. We can sum measures over all dimensions but take the average over time.
To achieve the same in BusinessObjects, we need to create derived tables in our universes based on combination of time dimensions and other dimensions. For example, consider one dimension as Region which contains Country and State as the hierarchy. Let’s say, another dimension Sales Type. Considering that the time dimensions include month and year aggregation. Below are the derived tables required:
Country, Month
Country, Year
Country, Sales Type, Month
Country, Sales Type, Year
State, Month
State, Year
State, Sales Type, Month
State, Sales Type, Year
Country, State, Month
Country, State, Year
Country, State, Sales Type, Month
Country, State, Sales Type, Year
Sales Type, Month
Sales Type, Year
Then apply aggregate awareness for the measure objects. Phew!!
So more the dimensions more the combinations and more aggregate awareness!!
Well these are few of the challenges for now. More to come on my next blog.
Thanks to my friend and colleague Veeraraghavan Vijayarajan(Raghav) for helping me out with this blog.
