I recently had a customer who was in the process of migrating from a legacy version of Microstrategy to BusinessObjects. They had been able to migrate over most of their core reports, but there were several reports that required a number of custom groups.
Microstrategy’s approach was to allow the customer to define any number of groups and filters from with the Microstrategy Desktop tool. These global filters and group definitions can then be leveraged by the report editor for building the report. Microstrategy’s SQL generation engine uses multi-pass SQL when creating the reports. Multi-pass SQL means that a single SQL statement cannot deliver back the correct results, so the report data is written into temporary tables. These temporary tables are then combined with additional queries to obtain the final results.
In the case of BusinessObjects, it wasn’t going to be possible to take the same approach. BusinessObjects does not generate multi-pass SQL. Instead, BusinessObjects focuses on creating extremely complex SQL in a single SQL statement. In most cases, this works just as well, but this was an exception. This sales report used a combination of multiple filters at multiple levels in the heirarchy. The Heirarchy was also defined within the custom groupings and not within the database.
The right answer is: Modify the Data Warehouse and ETL processes to make the underlying data better fit your reporting requirements. You see, the problem was that these custom groups should be defined within the data warehouse natively. This would allow for simplified SQL that can be run in a single pass. Translation? Reports the use custom groups will run a heck of a lot faster.
In my situation, the customer wasn’t going to be able to make these changes, but still wanted to migrate the report to BusinessObjects.
The best answer is: Use Sets. Sets gives you the ability to generate those custom grouping within the data warehouse. Unlike Microstrategy which will run the same multi-pass SQL over and over again, sets will allow us to “cache” the previous passes using sets and run single-pass SQL. We can then tie the set tables into the data warehouse and add the custom groupings to our universe.
The advantage of sets over ETL is that it can be much more dynamic. Sets can allow an analyst to create a new set in a matter of minutes and then see a report broken down by the new custom group.
In my case it was just what the doctor ordered. By introducing sets and adding the set tables to the BusinessObjects semantic layer, we now have access to the sets, which in this case, the customer called custom groups. It was perfect.
Now, not only will the customer be able to migrated this complex report from Microstrategy to BusinessObjects, but they will also be able to run the report much faster than ever before because the report will be generated through a single-pass process.
Tomorrow, we’ll take a look at how to set this up using our simplified data.
This has nothing really to do with grouping in web intelligence your fix was to put all the grouping inside the semantic layer which is dependent on a individual to be skilled in universe designer. Most web intelligence users are end business users and don’t want to have to worry about sql when gathering data.
Groups are needed by end users and they can be quite complex depending on an individual, with your solution you would need to create a new “set” every time a new group needs to be created…
Thanks for your feedback. Let me address your response. This solution does NOT require an update to the universe for each new group. Yes, it DOES require someone to create the group using the sets tool, but the data is stored in the sets tables and can be leveraged dynamically by the WebI user via prompts.
You can create simple custom groupings within the formula language using the IF(condition;then-result;else-result). WebIntelligence does not yet have the same custom grouping functionality that exists within Desktop Intelligence, but at the same time, if the group is an important “reusable” group that might exist across multiple reports, you want to make sure the groupings were always done the same way… and the best way to do this is to take the grouping logic OUT of the report and into some type of repository.
Can you clarify what “sets tool” is?
“it DOES require someone to create the group using the sets tool,”
I think this means you have a table that defines a “group” to a unique value (the PK), then you join that to a field in a table in designer that has those values. Then you build a dimension object off that grouping table. I do that currently, but “sets tool” perked my interest as it sounds like there is something that helps do this?
Sure, I’m glad to help. I should probably said “Set Analysis”. If you look back to the post (4a) http://bit.ly/15gBHD you can see that BusinessObjects allows you to create sets visually, etc. This is the “set tool” I am referring to. You can use the Set Analysis application front-end to create, edit and delete the sets. This application writes data into the set tables, set_set and set_set_detail. It’s these tables that you then use from within WebIntelligence to create custom groups.
You could certainly accomplish the same thing by building your own custom grouping table… but if you use set analysis, the application does it for you. I also recommend you check out my latest post about sets being added to the semantic layer directly! http://bit.ly/17P8Ka