In my previous blogs I did focus on how Crystal Reports can leverage data from a SAP BI or from a SAP R/3 system and how you can build a Universe for Web Intelligence on top of SAP BI.
This time I would like to explain some new functionality that was added in our XI Release 3.0 version of Web Intelligence.
First I would like to explain the functionality of "Delegated Search" for the list of values and in the second part I will focus on "database delegated measures".
Delegated Search
Delegated search is a feature where you can configure a list of values (list of members for a variable) to be enabled for a server-side search, enabling the user to leverage the underlying backend system for a faster search of values.
Following the steps described in my previous blog I created a new Universe on top of a SAP BI query where the query also contains a SAP Variable.
In this case the variable is referencing the characteristic Country.
In the universe you will then find two hidden entries for the list of values- one references the key of the characteristic and one references the description.
By double-click on the list of values (select the "Base" list of values) you can view the properties.
By selecting the option "Delegate search" you enable the user to leverage the more powerful backend system to search for specific members, which is especially very helpful in case of very large list of values for variables.
After exporting the changed Universe to the BusinessObjects Enterprise system you can now create a new Web Intelligence report and you will recognize the changes in the prompting UI.
You can recognize that first of all no members of the list of values are being loaded and that the user has to leverage the search to retrieve valid values that can be selected.
In this case I entered the search criteria "A*" and retrieved all countries starting with an "A".
Database delegated measures
Most of the measures in a universe will be additive measures but there is also the need to have measures that are semi-additive.
Two typical examples for a semi-additive measure are an Inventory level of a product or the headcount of employees. An inventory level might be additive along the product dimension or the plant / warehouse dimension but will not be additive along a time dimension.
In a universe, any measure can hold a projection function (Sum, Min, Max,Count, and Avg). The projection function is used to aggregate the measure locally in Web Intelligence when the number of dimensions displayed in a report is smaller than the number of dimensions in the query result set.
Non-additive measures, such as ratio, average, and weight, can only be shown at the same aggregation level as the query result set. Therefore, non-additive measures generally have their projection function set to None in the universe.
The projection function Database delegated allows you to delegate the aggregation of a non-additive measure to the database server. These are called smart measures in Web Intelligence. A smart measure has its projection function set to Database delegated on the properties page of the object properties
Lets use a concrete example. For the year 2006 we have the following inventory numbers per month.
| Year | Month | Stock |
| 2006 | January | 5 |
| February | 4 | |
| March | 3 | |
| April | 6 | |
| May | 7 | |
| June | 8 | |
| July | 2 | |
| August | 3 | |
| September | 1 | |
| October | 5 | |
| November | 6 | |
| December | 3 |
So then summarizing the inventory per quarter and year without considering the semi-additive measure would result in a wrong summary where the total of the year would be too high.
| Year | Quarter | Month | Stock |
| 2006 | 106 | ||
| Q1 | 12 | ||
| January | 5 | ||
| February | 4 | ||
| March | 3 | ||
| Q2 | 21 | ||
| April | 6 | ||
| May | 7 | ||
| June | 8 | ||
| Q3 | 6 | ||
| July | 2 | ||
| August | 3 | ||
| September | 1 | ||
| Q4 | 14 | ||
| October | 5 | ||
| November | 6 | ||
| December | 3 | ||
The correct result would be:
| Year | Quarter | Month | Stock |
| 2006 | 53 | ||
| Q1 | 12 | ||
| January | 5 | ||
| February | 4 | ||
| March | 3 | ||
| Q2 | 21 | ||
| April | 6 | ||
| May | 7 | ||
| June | 8 | ||
| Q3 | 6 | ||
| July | 2 | ||
| August | 3 | ||
| September | 1 | ||
| Q4 | 14 | ||
| October | 5 | ||
| November | 6 | ||
| December | 3 | ||
To configure the property "Database delegated" for a measure in the universe you only need to double-click the measure object and navigate to the tab "Properties".
You can now set the Function used for aggregation to "Database delegated".
When creating a report leveraging such a measure you will identify that the "Summary" function in the Web Intelligence UI is limited to the "Default Aggregation" which in this case results in a database delegated aggregation.
I hope this is giving everyone a good introduction to the database delegated search feature and the database delegated measure feature in Web Intelligence XIr 3.0.
- SAP Managed Tags:
7 Comments
