Skip to Content

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.

To report this post you need to login first.

7 Comments

You must be Logged on to comment or reply to a post.

  1. Sreekanth Maddipatla
    Hello Ingo,

    Thanks a lot for the very informative Blog series.

    Could you please explain how the BEx reporting variables are used in the Crystal reports or in the OLAP Universe? Especially the User exit variables which are calculated based on the user inputs.

    (0) 
  2. Vrushali Akre
    Hi,

    We have created Business Objects OLAP universe (BO XI 3.1) on SAP BI cube (SAP BI 7.0).

    We have done check integrity in OLAP Universe. It is parsing all objects with no error.

    We have developed a Webi report on SAP BI OLAP Universe and trying to create hyperlink report thru’ the hyperlink function available in Webi 3.1 but when we are clicking on hyperlink object we are getting WIS 10901 database error.

    We have tested same hyperlink function in webi 3.1 on efashion universe (Sample non OLAP Universe) and its working fine

    Can you please guide us how to solve this issue?

    Thanks in advance.

    (0) 
    1. Ingo Hilgefort Post author
      hi,

      I would suggest you open an entry in the forums for the Integration Kit and include the details on the hyperlink you trying to create

      Ingo

      (0) 
  3. Davide Cavallari
    Hi Ingo,

    first of all, thank you for your sharing of such useful information!

    I’d like to have some further details about database delegated measures.

    You wrote: “The projection function Database delegated allows you to delegate the aggregation of a non-additive measure to the database server”.

    I was wondering how this actually works, when the back-end is SAP NetWeaver Business Intelligence (SAP NW BI).

    Let’s say I have a semi-additive measure (such as Inventory Level) and want to take advantage of the database delegated option.

    If I understand it correctly, this option works because the corresponding key figure in SAP NW BI is appropriately modelled–i.e. its exeption aggregation is set to “last value”, with “calendar year/month” as the reference characteristic for exception aggregation.

    Could you please tell me if my opinion is correct?

    Thanks & Regards,
      Davide

    (0) 
    1. Ingo Hilgefort Post author
      Hi Davide,

      in case you use the “database delegated” option the aggregation for this keyfigure is not done by Web Intelligence anymore – in fact it is passed down to the underlying source. correct – the aggregation function of the backend is being used.

      ingo

      (0) 
      1. Sarah Kinmond

        Hi Ingo,

        I appreciate this is an old thread but have a quick question (which I think I already know the answer to but just seeking your confirmation).Am I correct in thinking the delegated dbase LOV search supercedes any Maximum List of Values size limit applied to the Information Engine Service on the WPS? The database delegated search is not in any way impacted or limited by this setting?

        Thank you very much for confirming/refuting this.

        Regards,
        Sarah.

        (0) 

Leave a Reply