Aug 04 2010
Too Many Objects? Too Many Rows? Try Prompting For Level of Detail
A while back I was on a project where the users wanted to set up reports that initially displayed about six different dimension objects and a bunch of measures. They also wanted to have the flexibility of dragging a different set of dimension objects on the report and either adding to or replacing an existing dimension. The idea was good. The amount of data brought back was a problem. I was able to fix that with some interesting prompt objects in the universe.
The Problem Definition
For the example I will present in this post I will once again use my version of Island Resorts Marketing universe which I have converted to Oracle. I will create a report that initially shows the Resort and (for simplicity) a single measure (Revenue). The report will be designed to let the user drag on additional details like Service Line and Service. But I will design my objects in such a way that if the user doesn’t want to see the information at that level of detail they don’t incur the overhead (row count) simply because the object is present in the query. In order to accomplish this, I will prompt the user with a list that includes the tokens ‘Resort’, ‘Service Line’, and ‘Service.’ The user will select the lowest level of detail they expect to use on the report. In this particular example the selections are hierarchical, meaning that selecting ‘Service Line’ implies that the Resort data will also be present. There is another option ‘None’ that can be selected if they want to deactivate the entire list.
Note that XI 3.1 offers a new feature called Query Stripping (in service pack 3) that works for BW and other OLAP queries and does this process automatically. It is not (yet) available for relational databases. Continue reading “Too Many Objects? Too Many Rows? Try Prompting For Level of Detail”
