Nov 10 2008
Designer XI 3 New Feature: Database Delegated Measures
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:
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:
This is all controlled via the projection function which is a part of the object properties screen as shown here:
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.
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?
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:
When I run the same query with my new measure definition here is what the initial output looks like:
The difference becomes apparent when I drag-and-drop the Year object away from my block:
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.
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.
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.
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.
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.
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…
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.
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.
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
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.
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?)
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.
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.
Dan, you are not alone in your desire for Universe Variables.
Hi,
I think that not only averages but also measures defined as COUNT (DISTINCT table.column) are good candidates for database delegated measures.
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.
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.
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.
Hi, Andrew, and welcome. What does the SQL look like for your measure? Does it include aggregate functions?
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!
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
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.
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.
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
Hi Dave,
Thanks for all the info you share on this website.
Your adventures are really helpful to me.
Keep exploring.
Thanks
Gaurav
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
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.
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?
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.