Dave's Adventures in Business Intelligence

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.

My steps are:

• Create a derived table to provide values for my prompt
• Create a custom LOV query to display the prompt values in the desired order
• Create a prompt object that allows a user to select the desired level of detail
• Create custom Level of Detail (LOD) versions of the impacted dimension objects
• Build my report

Creating the Level of Detail Prompt

First I have to create some data in my universe to feed my list of values (LOV) query. I have detailed this technique before. It’s quite simple to use a derived table and select against the DUAL table (in the case of Oracle) or equivalent and build any sort of list. Something like this will give me a list of values for the Resort level of detail (LOD) prompt.

select 1 as LOD_Order, 'Resort'  as LOD_Resort from dual
union
select 2, 'Service Line' from dual
union
select 3, 'Service' from dual
union
select 4, 'None' from dual

I have defined two columns for my derived table. The first is called LOD_Order and it will be used to order the prompt items in the way I expect to see them. The second column is the value that will populate my LOV. After creating this derived table I checked to see what the values were, and they came up as expected.

Creating the Custom LOV Query

The next step is to build the objects I need to create the list of values (LOV) that will appear in my prompt definition. I will create a class called “LOV” that includes objects for both the LOD_ORDER and the LOD_RESORT columns. Eventually these objects will be hidden, but I will need them to be visible in order to create my custom LOV query. The LOV will be built on the Resort LOD object, and initially it won’t look too special. It includes only the selected object. The trick is that I want to see the values in a specific order; that’s why I added the “order” column to my derived table. In order to create a sort on this object, I need to click on the Manage Sorts button shown here.

That button is only active if my database supports an order by clause that includes objects that do not appear in the select clause. If that button does not appear and I know that technique will work, I can update my designer parameter file as detailed in an earlier post titled, “SORT_BY_NO=NO? Very Confusing…” which shows how to accomplish that. Clicking that button allows me to define a custom sort based on the LOD Order column.

When I run the query I see the rows in the order I expect.

Defining the Prompt

I am going to reuse the prompt logic in multiple objects (Resort, Service Line and Service) so I am going to define it once and reference it using the @Select() function. Here’s the prompt:

@Prompt('Please select desired Level of Detail','A','LOV\Resort LOD',mono,constrained)

This is fairly standard syntax. The prompt text is defined, and the type of response is ‘A’ for character data. The LOV definition points to the custom query I created in the last step. I only want to allow a user to select a single value, and because the data only works if it comes in as I expect I am constraining the user. They have to pick from my list in order to proceed.

Building the Level of Detail Objects

I will be able to save some effort by reusing the prompt definition using the @Select() function. Here’s what my new Resort object looks like:

case @Select(Level of Detail\LOD Prompt)
when 'None' then 'Resort N/A' 
else RESORT.resort 
end

Resort is the top of the list. That means if I pick Resort, Service Line, or Service, I want to see the resort values populated. This means I can take a shortcut. If the user selects ‘None’ then I won’t show the resort values. If they pick anything else, I will.

The rest of my objects look very similar.

Service Line

case @Select(Level of Detail\LOD Prompt)
when 'None' then 'Service Line N/A' 
when 'Resort' then 'Service Line N/A'
else SERVICE_LINE.service_line
end

Service

case @Select(Level of Detail\LOD Prompt)
when 'None' then 'Service N/A' 
when 'Resort' then 'Service N/A'
when 'Service Line' then 'Service N/A'
else SERVICE.service
end

What I did was add a line of code to each of my objects, since each was at a lower level of detail. Note that I could have taken the opposite approach and defined my Service object like this:

case @Select(Level of Detail\LOD Prompt)
when 'Service' then SERVICE.service
else 'Service N/A'
end

Building the Report

I can now create a very simple report that includes all three of my new LOD objects and the Revenue. When I run the report at the Service level of detail it will show all of the detailed data, just as it would have before I created these custom objects. The results from Island Resorts end up being 27 rows of data.

If I rerun the exact same report at the Resort level of detail I get three rows.

The objects that I dropped from my level of detail are showing “N/A” values in the block. The prompt shows my four choices in the desired order because of the customizations I did for the LOV earlier.

What is the true impact here? What have I really accomplished?

Impact Analysis

Sample databases like Island Resorts are great for fooling around because they’re small. They’re not so great for demonstrating techniques like this because the true impact is hard to determine. I went from 27 rows down to 3, not really a big deal, right? On a larger database with more combinations of dimension values, the impact could be far more significant. I might drop from 50,000 rows down to several hundred. And the beauty of it is that if the user doesn’t need the level of detail, they don’t pay the penalty of downloading 50,000 rows only to roll it up (via projection) on the report. If they later drag on one of the suppressed objects and see the “N/A” result, all the user has to do is refresh the report and select the new level of detail to get more rows.

My sample report screen shots included all of the objects at the same time in order to show what was really happening. When I used this technique for a real report, I only included objects that I wanted to see from the beginning. The other LOD objects were listed in the available objects.

Alternate Solution

The solution I outlined here works as desired if there is a rigorous hierarchy and the user is just selecting the lowest level of detail desired on the report. What I actually had to implement was a bit more complex and I will provide a sketch of the solution here. The actual requirement was based on different combinations of dimension objects. A matrix of choices might look like this:

Resort (by itself)
Service Line (also by itself)
Service
Resort + Service Line
Resort + Service
Service Line + Service
Resort + Service Line + Service
None

Instead of the simple case statement objects I showed earlier, I had to use a pattern matching function and look for the string “Resort” anywhere in the prompt result. If the user picked anything that included the Resort as an option, then the Resort object would return a value. Ultimately I created prompt “bit” objects in order to simplify the logic.

Conclusion

I thought this was an interesting technique to share at this point, what with the recent release of SP3 with the Query Stripping feature for cube data. It worked well for the project. In some cases this strategy allowed us to reduce the number of rows by an order of magnitude (10,000s of rows down to 1,000s or even hundreds) while retaining a large number of objects in the report. The biggest drawback of this solution is that my SQL code will still hit the requested tables, even if I am not really requesting any data. Going back to my earlier example, a true query stripping process would eliminate the resort, service line, and service tables if I selected ‘None’ from the prompt. In my “level of detail” solution all of those tables are still present and therefore have an impact on the query performance.

The issue I was trying to solve in this case was related to the number of rows downloaded to the report. The queries ran fast enough that the query performance was not as big of a concern. It was my goal to provide a way to reduce the row counts without losing the flexibility to add more dimension objects to my report on the fly. This technique accomplished that.