Nov 10 2008

Designer XI 3 New Feature: Database Delegated Measures

Categories: 2008 GBN - Dallas, Universe Design Dave Rathbun @ 7:34 am

A Brief Overview of Measure Objects

A universe has three basic types of result objects: dimensions, details, and measures. Measure objects are analytical values like dollars or quantities. Measure objects have two extra settings that dimensions and details do not have: a SQL aggregate function and a report Projection function. The SQL aggregate function is not enforced by the application, but it should always be present. (That’s a subject for another blog post. Or two.)

The SQL aggregate function is performed by the database server, and the projection function is used by the report engine. This is what makes measures “roll up” when you slice and dice or drill on a report. The problem is, certain calculations cannot be done by the database because there is no valid projection function. Designer 3.x gives me a way to address that by introducing a new projection function setting of Database delegated. This post will explain why that’s important and how it works.

How Projection Works

The projection function is designed to complement the SQL aggregation operation. For example the Revenue object from Island Resorts has the following SQL formula:

sum(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)

The object also has a Sum projection function. If I run a query that returns the Resort and the Year and the Revenue the output looks like this:

Raw Data

Using a simple drag-and-drop technique I can remove the Year from the output block and the projection function causes the data to roll up to this result:

Projected Data

This is all controlled via the projection function which is a part of the object properties screen as shown here:

Projection Function

When Projection Fails

This all works very well until a special function like Average is considered. Averages cannot easily be projected because the source data could be very skewed. A sum operation can be applied recursively. What this means is that 1 + 5 generates the same result as 1 + (2 + 3). An average is not recursive. An average of 1 and 2.5 is not the same as an average of 1, 2, and 3. For the record, and average of 1 and 2.5 is 1.75, and an average of 1, 2, and 3 is 2. Even with a very small set of data the results of an average projection can be very wrong.

The basic problem here is that averages have to work with the source data. I cannot apply an average to an average and expect to get the correct result. It is for this reason that report developers have had to create average and percentage calculations on their reports rather than reusing an object from a universe. In order to deliver the correct result I have to work with the source data.

Averages Do Not Average Well

As I said in the prior paragraph, the only way to generate the correct result for an average is to recalculate it from the source data. In order to demonstrate this I have created an Average Revenue object in my universe. For this screen shot I have used the average object in two different queries. The first shows Average Revenue by Resort and Year, and the second shows Average Revenue by Resort only.

Average Data

The object is created with the following SQL:

avg(INVOICE_LINE.DAYS * INVOICE_LINE.NB_GUESTS * SERVICE.PRICE)

… and the projection function is set to Average. As I did before I can apply a simple drag-and-drop operation to remove the Year object from the first block, allowing the report engine to project the Average Revenue using the selected projection function of Average. Are the results correct?

Projected Average Data

The results are wrong because the projection in the first block is taking three years of data, summing them up, and then dividing the total by three to get the new average value. The true result based on a database calculation is shown in the right block. The second block was not affected by the Year values since the query did not include that object in the result set.

Delegated Measures

This is where the delegation process comes in. As a universe designer I can now create an object that will project correctly (yay) at the expense of having to run a database query (boo). Instead of projecting my average calculation using the Average function, I will use the Database Delegated option instead. Here’s how that looks:

Delegated Projection

When I run the same query with my new measure definition here is what the initial output looks like:

Average Data

The difference becomes apparent when I drag-and-drop the Year object away from my block:

Average Data

The note #TOREFRESH is telling me that before I can see the numbers for that column I have to refresh my document. I think that it’s nice that it doesn’t refresh right away, as it gives me the opportunity to make more adjustments. Perhaps on a more complex report I want to remove (or add) more than one object from the block. In any case, when I click the refresh button the results are displayed.

Average Data

Note that the two blocks are 100% the same now. The option to delegate the average calculation to the database has given me the power to create an entirely new type of object that I could not have done before.

Conclusion

This is another nice new feature for Designer. It will provide me with better control over how my measure objects are handled. In this case, the solution is not without a cost… I may have to refresh the report in order to force the data to be updated. If the query takes a long time to run, there is a cost involved. It may ultimately still be easier to do this type of calculation on the report.

Here is another challenge that is not solved by this technique: I can’t do calculations that cross contexts in the universe. Suppose that I have one context for current year measures, and a second context for prior year measures. If I want to compare current to prior year values, that has to be done on the report. Delegating that calculation to the database is not possible because the values come from two different contexts.

But it’s nice to have options.

This feature was one of several mentioned in my Designer Essentials presentation delivered at the 2008 GBN conference. This particular feature was on page 10.

54 Responses to “Designer XI 3 New Feature: Database Delegated Measures”

  1. Comment by Irfan

    Dave,
    Thanks for sharing, Its a nice one.

    What are the other advantages of Delegated Measures. When should we use it or create one in Universe ? The example of average is one… but I would like to know more…

    Thanks.

  2. Comment by Dave Rathbun

    Prior to this feature if a measure could not be projected with one of the basic functions (sum, count, min, and so on) the only remaining option was None. Generally these included averages, percentages, or potentially any operation that involving division. Any of these could be candidates for database delegated measures.

    I doubt it would ever make sense to delegate a sum operation. :)

  3. Comment by Mark Anscomb

    Hi Dave,

    My juries still out on this one, especially taking contexts into consideration.
    I still think the best place for these calcs are on the report, it could also be quite DB resource intensive, if you use delegated measures on a poor data model.
    However, any improvements are better tha non as far as Designer is concerned, now if only it could also deal with shrunken dimension tables in a sensible fashion!

    All the Best,

    Mark.

  4. Comment by Nick

    It would be interesting though wouldn’t it? To delegate a sum operation just to see what happens…especially if your database isn’t performant and isn’t likely to be…

  5. Comment by Dave Rathbun

    Nick, from one of the stories I was told, very early on when delegated measures were presented they did, in fact, do a sum. Which is rather silly. :lol:

    Mark, if the components of the calculation cross a context then there’s still no trick… the calculation has to be done on the report. But the nice part of this idea is that we finally have a choice about standard things like averages. Before this option there was no choice. I’ve frequently said that the #1 goal for a universe designer is to deliver correct results. A valid #2 goal would be to do that as fast as possible, but not at the expense of correctness. If users get incorrect results, they will come to me. If users complain about slow response time, and we can blame the hardware. :P

  6. Comment by Venkata

    Dave,
    Nice meeting you at the Dallas Conference. Regarding the delgated measures, I haven’t seen 3.0 Product. But interesting to know about the delagated measures.
    1)Is there a way to make the delagated measures refresh automatically. ?
    2) Now after second refresh, Does it have 2 sqls in the document ?

    I appreciate your work and time

  7. Comment by Dave Rathbun

    Hi, Venkata, and thanks for your question. I have not found a way to make the measures refresh automatically. I believe that it’s a nice implementation the way it is, in that the user picks when they get to refresh. For example suppose that a query takes 15 seconds to run and I want to make four different adjustments to my report block. If I have to wait 15 seconds in between each one it might get frustrating.

    I believe there is only one SQL in the document. There seems to be some caching done as well. I removed the Year object and got the #TOREFRESH message, refreshed the data, and then put the Year back onto the block. I did not have to refresh a second time; the original data was still in place.

  8. Comment by George Davies

    Dave,
    So what happens if you want to plot both the average by year and the average by resort
    a/ side by side that is both your original table and the one without the year?
    and b/ in a break level on resort (in the original table?)

  9. Comment by Dave Rathbun

    George, that is an interesting question. I haven’t tried to do anything like that yet. The first guess I woudl make is that you would have to include two blocks, each with their own data provider.

    When I get time to do an experiment, I will post the results.

  10. Comment by Dan Flanery

    I’ve thought for several years that BO should allow you to store formulas at the Universe level as Formula Objects. When you drag one of those Formula Objects into a data provider, BO would silently pull whatever Measures, Details and Dimensions which are components of that formula into the query. That way you could create Objects that would properly calculate things like weighted averages. Seems like a much more elegant solution than these “Database Delegated” measures.

  11. Comment by Dave Rathbun

    Dan, you are not alone in your desire for Universe Variables. :)

  12. Comment by Marek

    Hi,
    I think that not only averages but also measures defined as COUNT (DISTINCT table.column) are good candidates for database delegated measures.

  13. Comment by Bob Gray

    Hi Dave,

    Have you seen a situation where the #TOREFRESH value doesn’t disappear with a refresh? I’ve tried this with an object that uses division SUM(X)/SUM(Y). I also have objects individually for X & Y that include SUM() aggregates and rewrote the same object definition as @Select(class\ObjectX)/@Select(class\ObjectY) and database delegation. #TOREFRESH just won’t go away :-( I was hoping my first exposure to the new functionality would have been more successful.

  14. Comment by Dave Rathbun

    Hi, Bob, nice name. ;) Unfortunately my current client is still on XI R2 so we haven’t been able to test this new feature yet. I experimented with it in order to build the presentation (and this blog post) but haven’t really tried to “stress test” it yet.

  15. Comment by Andrew Radtke

    I have an object in my universe which is a percentage calculation based on other measures. This percentage calculation must be performed at the level at which data is aggregated.

    When I define this numeric object as a measure with “Database Delegated” and run a WEBI report that uses this field I get a #MULTIVALUE error in that field.

    I am using Universe Designer 12.1.0.882 and Web Intelligence Rich Client 12.1.0.882.

  16. Comment by Dave Rathbun

    Hi, Andrew, and welcome. What does the SQL look like for your measure? Does it include aggregate functions?

  17. Comment by bobj

    Hi,

    I tried to do the same thing on Island Universe but when I remove the Year object I don’t get #TOREFRESH message. Am I doing something wrong? Thanks!

  18. Comment by James Halligan

    Hi Dave,

    despite my initial confusion at the #TOREFRESH message, the Database Delegated Measures have provided with a neat solution to a distinct count in SQL Server 2005….very neat

  19. Comment by Josh

    Dave, I think it’s true when we total Bahamas Beach for 4 years and then sum up these by 4 the result would be $4,114.87 (at first block). It’s same as we do it manually with our calculation. If at the second block the Bahamas Beach Average Revenue is different with the first block, there is something wrong with the data source because it has nothing connection with a formula like 1 and 2.5 resulting 1.75 and 1, 2 and 3 resulting 2 except the average result at the first block is different with our manually calculation.
    Please explain this. CMIIW.

  20. Comment by David

    Dave,

    Great post. Thank you.

    After reading your post, I was curious to see how would react the Database delegated function in Polestar, and how Polestar would handle the #TOREFRESH behaviour compared to Web Intelligence. Well, unfortunately, when you set a measure to Database delegated, Polestar ignores that measure. Polestar will allow you to select the measure when configuring the space, but it will not display it in the dropdown list of measures when exploring a space. Hopefully, Business Objects will correct this in future versions.

  21. Comment by Mike P

    Hi,

    I used this siugestion and it worked great until I brought the data into Explorer (Polestar). BTW this option is deifnately needed if you are using Explorer since you don’t have the type of control on calcualtion that you get in Crystal.

    Here’s what happended, maybe some one out there has tried this in Polestar (epxlorer).

    Step 1; Chenged percentage to database defined
    Step 2: Checked on explort with univers
    Step 3: exported (worked fine)
    Step 4: Created new info space in Explorer and broiught in db fields including the percent field
    Step 5: Indexed info space (worked fine)
    Step 6: Launched Explorer, but the percent measure does not show??

    Any ideas?

    Thanks,
    Mike

  22. Comment by Gaurav Saxena

    Hi Dave,

    Thanks for all the info you share on this website.
    Your adventures are really helpful to me.

    Keep exploring.

    Thanks
    Gaurav

  23. Comment by Manoj Bapat

    Hi Dave,

    Regarding cross contexts in the universe, in order to compare current to prior year values coming from two different contexts – if we check the Multiple Paths options under Universe parameters will that help in this case of Delegated Measures ?

    Universe parameters – SQL – Multiple Paths – Check the below options.
    Multiple SQL statements for each context
    Multiple SQL statements for each measure

    Regards,
    Manoj

  24. Comment by Dave Rathbun

    Hi Manoj, and welcome.

    Universe objects (calculations) cannot cross contexts because the results come from two separate SQL passes. The multiple statements for each context is required in order to allow the user to generate the query easily. They can put current year and prior year on the same query panel. But as the measures will come from different contexts there is no way to generate the CY / PY comparison until you get to the report.

  25. Comment by Kathy

    Do you know if you must turn on “query on drill” in the document properties in order for a “smart measure or delegated measure” to work correctly?

  26. Comment by Dave Rathbun

    Kathy, I have not tested for this specifically but I believe that delegated measures should work with that setting on or off. It should not matter. With database delegated it should always query, no matter what that setting is.

    Basically any time a measure changes context it has to refresh. If you see the #TOREFRESH that’s what it’s waiting for. By setting up the “query on drill” it may be more convenient? I don’t know, I need to test this. :)

  27. Comment by Lakshmi

    Hi Dave,

    Can you please explain usage of SMART_AGGREGATE. Where to use this?

  28. Comment by bo_stuffed

    Awesome explanation!!!

  29. Comment by Uinmesh Mathew

    Hi Dave,

    Good to know this. I am a fan of your presentation and knowledge. I have a question though. Do you have any idea what is happening with the query when the Document is refreshed? Is a new query getting posted to the database? I was wondering if the query changes, what will happen to another tab which is using year?

  30. Comment by Dave Rathbun

    I think the point is that a new query is sent to the database. It had not occurred to me to test what happens if there is more than one block (or report) within the document that might be impacted. Seems like I have something to test. :)

  31. Comment by Andy

    Nice post. Thanks a lot.

  32. Comment by sskumar

    hi dave..

    Thanks for the explanation.

    but i have a doubt here.

    avg of 3938.21,4192.06,4228.44,4100.77(fore Bahamas Beach) is exactly 4114.87(present in first block). whats wrong in this.

    I dont understand how it comes correct as 4151.47

    Could you please explain

    Thanks,
    Isskumar

  33. Comment by Dave Rathbun

    You cannot project an average because the source data is not available. Suppose I gave you two monthly numbers for average sales: $100 and $200. The average of those is 150. However, that’s not the correct answer. :) The first month average was for 100,000 orders at $100 each, so the average order is $100. The second month average was one order for $200. Now, what is the average order over two months?

    In order to figure that out, I have to go back to the source data and recalculate.

    If my sample size were the same (I had 100,000 orders in both months) then and only then can I average the displayed data.

  34. Comment by sskumar

    Hi Dave,

    Thank you so much. Now i got to know and practiced with sample data. it was working fine. Thanks again:)

    Thanks.
    IssKumar

  35. Comment by Ken Hartman

    Hi Dave,

    Thanks for the explanation on how Database Delegation works. Just an FYI, and I have not heard anything from SAP on whether they will address this in a later release of Explorer, but if you have a universe which leverages the Database Delegation function, any measures using this will not appear in the information space even though you can select them from the universe object. There is an SAP note on this (Note # 1479478) but it isn’t helpful – only says you cannot use these measures. The only workaround I know of is to duplicate your universe and change the properties from Database Delegated back to Sum – a maintenance nightmare.

    Ken

  36. Comment by sandy

    Hi Dave
    I have a question regarding percentages and averages. We have a view develoepd for all the calculations. when i include these objects in the universe should i just change the projection to database delegated with out having to define the object as
    avg(column_name_from_view) in the object definition?

  37. Comment by Andy J

    It can also be used for sums – I had an example with a master-detail table layout, count distinct on master but if an item was included in query from the detail table then it counted all distinct rows ie at detail level even though the detail item was not included in the report. The query includes all rows as I believe this is standard Webi behaviour. As soon as the measure was database delegated the problem was resolved and the query only counted the distinct rows used in the report

  38. Comment by Cesar Rodriguez

    Hi Dave, I have an object with a division formula and aggregate function sum. With a minimum aggregation level it work, but when replace with other superior level, the aggregate function sum work, as a normal work and the result needs to be a division.

    I proposed to my user, use a database delegation, with all refresh that it need, or use a local variable in each report.

    Do you know if exist a third option to this behavior?

    Best Regards

  39. Comment by RSJ

    Thank You!

  40. Comment by Dave Rathbun

    Database delegated measures are designed for ratio calculations (either current year to prior year, or averages, or percentages, or…). Whether you use database delegated measures or local variables really depends on how quick your reports refresh.

  41. Comment by Prashant

    Hi Dave
    I have a measure object called begining and ending balance which i want to aggregate based on daily,month,quarter, and year. When I want to see monthly data and I remove the date dimension..It rolls up the individual records giving me the wrong result. For Example my daily transaction data is like..
    User Date Year month BegBal EndBal
    I1 11-May-2011 10PM 2011 5 0 100
    I1 11-May-2011 11PM 2011 5 100 200
    I1 11-May-2011 12PM 2011 5 200 250

    This is correct when I see at lowest daily level data…But if I remove the date column..then i want month begining and ending balance which should be 0 and 250 respectively…however the report shows 300 and 550 that is it sums up..

    Please help!!Thanks a lot in advance

  42. Comment by Dave Rathbun

    The challenge with elements like this is that the obvious choice of min() and max() don’t work, because the ending balance could – with the right set of transactions – be less than the starting balance. It’s for this reason that I would suggest calculating these values using a running sum function on the report instead.

  43. Comment by Prashant

    Dave thanks a lot for the reply..You are the best!!

    I am doing the calculation using Running sum and the report is slicing and dicing between month,year and daily perfectly. I calculated the begining balance using previous(ending_bal) function. The ending is calculated using using Running sum. So things went right. thanks again..

    The problem now is that the report is taking too much time to fetch data due to use of report level variables. As the data volume is around 80K to 100K.

    Please suggest me something in this regard. Let me know if I am not clear in explaining my problem.

  44. Comment by Prashant

    Hi Dave

    i am waiting for your reply..

    Thanks!!
    Prsahant

  45. Comment by Dave Rathbun

    Hi, Prashant, your volume question is really beyond the scope of this blog post.

  46. Comment by Jan

    Hi Dave,
    Thank you for explaining this. I have been testing this feature as an alternative for using two separate queries (one with fewer dimensions to use in a ‘totals’ row). It seems to work, however when combined with query drill and drilling, the report still ask for a #torefresh. I had hoped that a query drill would act as a refresh (since it sends the query to the database). Now the option with two separate queries seem better because it does not need a refresh after each drill.
    Is there a way to combine the (query) drill and a refresh?
    btw using XI R3.
    Thanks

  47. Comment by David

    In my opinion the greatest benefit of using this feature is making it explicit to the report consumer that something isn’t quite right due to the report changing.

    I prefer this to having an aggregrated number appear as it gives the impression of correctness when that is not the case.

  48. Comment by Brian W.

    Here’s a “gotchya” with delegated measures: #UNAVAILABLE

    http://scn.sap.com/thread/2102322

  49. Comment by Jill

    I believe Delegated measures can be used for solving issues with distinct counts, however I have been unsuccessful in using this feature with teradata as the database. the error I get is:
    “Database error: [Teradata][ODBC teradata Driver][Teradata Database] Numeric overflow occured during computation.. (IES 10901) (WIS 10901)”. I believe there is are currently a few bugs using this feature with specific databases like teradata. Please confirm if you are aware of any current fixes.

  50. Comment by Sandeep Munagala

    Hi Dave,

    This is really interesting topic. I used Database deligated Projection in universe for Avg and also for STDDEV function. When I use these objects in Webi I am able to get the correct result, But my issue is I have drill filters in the report where users can select values for their analysis. When they perform analysis by selecting values for the filter except STDDEV and AVG values all other values are changing accoring to the selection which is correct. STDDEV and AVG values remain same regardless of the drill filter selection. This is because of the DATABASE DELEGATED projection. Is there any other solution to get the correct value and also capable of using Drill filters to the correct values according to the selection?

    Thanks,
    Sandeep Munagala.

  51. Comment by Dave Rathbun

    A database delegated value is only valid in the context of the query. If you apply filters (and I suspect even drill filters fit this category) then the query really needs to be refreshed in order to get the correct result.

  52. Comment by Dave Rathbun

    I am not aware of any database-specific issues with the delegated feature. The SQL itself does not change when using this feature. What changes would be when the SQL gets executed. With this feature on, each time a user wants to project measures they will have to refresh the data for the report.

  53. Comment by Nash

    Hi Dave,
    I have a ratio calculation (E.g. Share of Market% where numerator is sum of volume sales for a product divided by total sum of volume sales across the entire product category). I have to deliver this as a single measure in the unv, which must support drill down across geography, product & time dimensions.
    Since this is a non-additive fact, do you have any suggestions regarding its implementation in the universe? I tried making this database delegated but it doesn’t seem to add a lot of value since arriving at the denominator itself seems to be a problem as it changes based on the dimensions selected by the user.
    I suggested that it needs to be done on the report, but the users are insistent!

    Thanks,
    Nachiketa

  54. Comment by Dave Rathbun

    Hi, Nachiketa, unfortunately I think you’ll find that you need to be equally insistent. When there is a ratio of any kind, the database delegated option is really the only solution, as we need to ensure that the proper values are used in the calculation. There is some hope in the future that we might get “universe hosted variables” which would be another option, but that doesn’t help you today…

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.