David Taylors' Business Intelligence Blog

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.

Can I Get Row-level Security with WebIntelligence and Still Avoid a Document Refresh?

Sure!

As a follow-up to my post yesterday I wanted to step back and say that normally you do want to handle row-level security from within the semantic layer. The problem is that you can’t get the benefit of the speed of data that comes from historical instances without requiring each user to run the report for themselves.

I did discover that you can provide the same type of security within WebIntelligence. WebIntelligence also has a function which also returns the name of the current user. The function is CurrentUser.

1. The first thing I need to do is build the semantic layer to include my security table.

2. Next, create a new variable which returns a 1 or 0 based on whether or not the user should see the data or not.

=If(CurrentUser()=”Administrator” Or CurrentUser() = [Username];1;0)

3. Create the report and apply the filter to the entire report or to the appropriate data block.

4. Here is what Ron’s sees, when he looks at an historical instance of the WebIntelligence report.

Notice that the Data Summary on the left hand side shows me that there were 71 records but even so, the restricted user ron is only allowed to 3 of the 71.

The beauty of this is that when I look at this data via BusinessObjects LiveOffice, I will see exactly the same restrictions enforced.

REMEMBER: Normally if you need row-level security, then just apply it to the universe. IF, however, you need row-level security on historical instances without requiring them to be refreshed, then use this great trick.