Aug 04 2010

Too Many Objects? Too Many Rows? Try Prompting For Level of Detail

Categories: Prompts, Universe Design Dave Rathbun @ 6:06 pm

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
select 2, 'Service Line' from dual
select 3, 'Service' from dual
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.

level of detail table rows

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.

query panel screen shot

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.

Screen shot of Designer LOV screen

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

Screen shot of Designer LOV results

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

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


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

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'

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.

Web Intelligence report at the lowest level of detail

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

Web Intelligence report at the resort level of detail

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.

Report prompt screen

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)
Resort + Service Line
Resort + Service
Service Line + Service
Resort + Service Line + Service

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.


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.

25 Responses to “Too Many Objects? Too Many Rows? Try Prompting For Level of Detail”

  1. Comment by Sriraman

    Good show dave

  2. Comment by Bill McClernan

    Dave, this is an excellent post. I am presenting at the conference this year on a very similar topic, and was wondering if you would mind if I used some or all of this material (giving you full credit, of course). Please send me an Email.

  3. Comment by Dave Rathbun

    Bill, you are more than welcome to use this concept for your presentation, thanks for asking.

  4. Comment by Bill McClernan

    Thanks Dave, I’ll use it and I hope to see you at the conference!

  5. Comment by Kuldeep


    If we use this approach, I guess drill down will get disabled for resort objects.

  6. Comment by Dave Rathbun

    There is no reason why drilling would not work as long as these special objects were included in a hierarchy.

  7. Comment by Prateek Jain

    Dave..i am trying to implement the above thing in my universe. I got stuck, the Level Of Detail class has an object LOD Prompt. What will be the definition of this object for the above example.

  8. Comment by Dave Rathbun

    Hi, Prateek, the definition of the LOD Prompt is shown in this post under the heading “Defining the prompt.”

  9. Comment by Mathangi K

    Hi Dave, This post would be very useful for me to complete a task at hand. Thanks for that. But I am stuck at defining the prompt. Should this be created as an object? I am confused as to where this @prompt code should go(Select or Where?). Please help.

  10. Comment by Dave Rathbun

    Hi, Mathangi, the prompt is defined as a separate object and then referenced via the @Select() function in order to avoid having to repeat the code multiple times. So the prompt object is built first. Next the level of detail objects are built. Finally, the original @prompt object is hidden from the final view since it is never used by itself.

  11. Comment by Mathangi K

    Hi Dave, Thanks for the quick reply. Pardon me if I was not clear in my earlier email. Whatever you stated in your reply is exactly what I want to do. But when I create a object under a class and then try to define it, where should the @prompt definition go? I did this -
    In the ‘Select’ section of the Object definition, I entered this –
    @Prompt(’Select Product Category or Enter * For All’,'A’,'ProductHierarchy2\ProdCategory’,multi,free) and got a SQL error 42601 when I tried to parse it.

  12. Comment by Dave Rathbun

    The prompt object by itself is designed for re-usability in other objects. It is not expected to parse. But the logic you are showing does not match what is outlined in this post. The parts all have to work together. :) The “All” technique is not applied on this trick, so I’m not sure how you are approaching that bit.

    This is the prompt:

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

    That object will not parse. It will, however, work if I include it in the definition of another object as shown above, like this:

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

  13. Comment by Mathangi K

    Thanks Dave. Now got the right context of what you meant with the prompt object. I’ve got it working now. That was a wrong copy and paste of the prompt code. Sorry about that:-)

  14. Comment by Alex

    Hello Dave,
    Do you know if above sorting technique working for prompts in Crystal Reports on top of universe?
    I did tried several options above but problem is that Crystal doing it’s one sorting ? because you have to provide same values in CR’s Edit parameter dialog that you did provide in Universe in order for them to appear in CR’s “Enter Values” prompts window.
    I think the values are not coming through from Level of Detail Prompt of universe if you will not provide them again in CR.
    But I might be totally off, could you please provided the steps if sorting can be accomplish with CR as well.
    Example of values: 23, 46, 69, 110, 138, 350, 756. In Crystal Prompt they will be sorted: 110, 138, 23, 46, 350, 69, 756.
    Thank you very much,

  15. Comment by Alex

    Any options to do custom sorting in Crystal Reports on top of universe?
    Asked same question before in previous comment but that one might be overlooked:(.
    Thank you,

  16. Comment by Dave Rathbun

    Alex, I did not answer the earlier question because I did not see how it related to this post. When you say that values are being sorted in the order you provide, it is most likely because the value is treated as character instead of numeric. That would explain the results you mentioned in your prior comment, and it has nothing to do with the concept provided in this blog post. If I have misunderstood, please provide more details about your question. Thanks.

  17. Comment by gopi

    Hi Dave,

    I tried implementing ‘ALL’ in Cascading Prompt based on your post, it seems like the concept worked very well, but when it is prompting the user its not displaying the list of values of countries where user needs to enter the country instead but it is showing the region lov’s when once the country name is entered. Could you please suggest me how to overcome this?

  18. Comment by Dave Rathbun

    Hi, your comment does not appear to be related to this post?

  19. Comment by gopi

    Hi Dave,

    Yes, this is not a related post. But, sorry to say that I didnot find right place to post. Could you please consider my post? Thanks in advance.

  20. Comment by Dave Rathbun

    Hi, Gopi, I apologize but I don’t offer personal support via my blog. I will answer questions related to posts in order to provide clarification or fix bugs and so on, but I don’t take off-topic questions here. If you have not done so already, I suggest you post your question on BOB. This is the standard policy for my blog as written up here. Thanks.

  21. Comment by akl007

    Hi Dave,
    This trick doesn’t remove the joins from the query I guess? It just changes the grain of query output (provided we use an aggregated function for the measure). Am I correct?
    On the other hand, Query Stripping will get rid of the joins as well?

  22. Comment by Dave Rathbun

    Yes, this was a far more primitive solution from before Query Stripping was an option. :)

  23. Comment by Sushanta

    Hi Dave,

    Thanks for such a wonderful post. I am facing an issue while using LOV in @prompt() in derived table. Let me try to explain you, what I have done. First I have created one folder named LOV and inside that folder I have created one dimension VisitType. Then I have created one derived table where I am filtering the select statement by VisitType. Please see below the select statement :

    select * from Table1 where vst_type_cd in @Prompt(’Visit Type:’,'A’,'LOV\VisitType ‘,Multi,Free,Persistent,,User:1)

    While I am trying to purse this derived table it is giving ‘Invalid Prompt definition. (WIS 00005)’ error. I am not able to understand why this is giving this error. I have gone through other blogs also and all of them are saying that it will work like this. But in my case it is not working.

    Can you please suggest something ?

    Sushanta Paul

  24. Comment by Dave Rathbun

    What happens if you simplify your prompt syntax to the most basic level?

    select * from Table1 where vst_type_cd in @Prompt('Visit Type:','A','LOV\VisitType',Multi,Free)
  25. Comment by Sushanta

    Thanks Dave for the help. Its working now.

Leave a Reply

If you want to include formulas or code in your comment, please read my Tips for formatting comments first. Tags you can use are listed below.

XHTML: You can use these tags: <a href="" title=""> <acronym title=""> <blockquote cite=""> <code> <em> <strike> <strong> <sup> <sub> <u>

Confirm submission by clicking only the marked checkbox:


Please remember that comments that are not related to this blog post may be ignored or deleted without notice. If you're looking for help on a topic you have already posted on BOB then please do not repost your question here.