emergingbytes.com Technology blog for data warehousing, business intelligence, iPhone Apps Development, Android Apps development and facebook applications

Distinct Measures in Web Intelligence Report

Business intelligence teams across the world have been facing this problem of delivering reports with distinct measures since ages. Many solutions have been proposed and implemented but with increasing hunger of additional dimensions and thirst for quicker results, nothing can fully meet demands of evolving business users. Nevertheless, companies and BI developers are continuously making technological advancements to meet reporting requirements for DISTINCT based analysis. One such effort is done by SAP Business Objects by introducing a feature called "Database Delegate" in their BO XI R3.x version. This post focusing on describing and providing examples of using this feature:

Business Objects Database Delegate:

When designing a universe, many dimensions and measures are created. Measures are the calculated items that are analysed against different combinations of dimensions. When changing the set of dimensions for analysis in report (webi or deski), measure can be projected either using sum, avg, min max  or count. These projections are defined while designing universe under measure properties (by double clicking a measure) and are re-calculated only on report level. Summarization process does not go beyond webi server and all the recalculations take place on webi server or locally if working in offline mode on rich client.

For distinct measures, non of the above mentioned summarization/projection function is appropriate  i.e. sum, count, avg, min and max. By keeping a distinct count in an aggregate table and then counting them or summing them on report level will present incorrect values. For that matter a better approach would be:

• Keep a granular dimension in the aggregate. For example, you want to calculate distinct number of sessions in a day against different combination of dimensions. To achieve this, the aggregate design should hold a set of analytical dimensions, measures and a session key. This session key then can be used to create smart measures (explained later in the post) for calculating distinct measure correctly in webi reports
• While designing universe, use a new projection called Database Delegate, that is available in BO XI R 3.x , on distinct measures. For example, to calculate distinct number of sessions in webi, create a measure called 'Distinct Number of Sessions'.  In the properties of this measure, set the projection function to 'Database Delegate'
• In webi, what will happen is that, when you change combination of  dimensions against which distinct is calculated, '#TOREFRESH' is displayed in Distinct column. This is webi telling report user to hit the refresh button again to recalculate distinct by delegating query to database that will recalculate distinct.
• Once a user has refreshed a report for different combinations of dimensions, user do not need to refresh webi report to analyse any of already refreshed combinations of dimensions  for distinct measure.

Before using this projection function for your production reports, I would recommend to go through BO Designer guide for better understanding of limitations and precautions. Some considerations are as follows:

• Number of report users who will be using reports with 'Database Delegate' projections
• Data Warehouse or data mart processing capacity and average time taken to refresh a webi report containing 'Database Delegate'
• Frequency of webi report usage
• Permission sets assigned to webi report users. (In some cases, business users do not have refresh rights

Example:

Step 1: Create a simple aggregate table:

CREATE TABLE DP_MDB.AGG_DATA_TRAFFIC
(
	  DT DATE
	, SERVICE_TYP VARCHAR(10)
	, PRICE_PLAN VARCHAR(10)
	, SESSION_ID VARCHAR(20)
	, DATA_TRANSFER_BYTES INTEGER
)
PRIMARY INDEX(DT, SERVICE_TYP, PRICE_PLAN, SESSION_ID)
PARTITION BY RANGE_N(DT  BETWEEN DATE '2011-01-01' AND
DATE '2011-12-31' EACH INTERVAL '1' DAY );

Populate this newly created table with sample values:

INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'GPRS', 'Prepaid','session00001',RANDOM(0,5000) );
INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'VAS', 'Prepaid','session00001',RANDOM(0,5000) );
INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'WAP', 'Prepaid','session00001',RANDOM(0,5000) );

INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'MMS', 'Postpaid','session00002',RANDOM(0,5000) );
INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'CONTENT', 'Postpaid','session00002',RANDOM(0,5000) );
INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'SONGS', 'Postpaid','session00002',RANDOM(0,5000) );

INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'GPRS', 'Postpaid','session00003',RANDOM(0,5000) );
INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'CONTENT', 'Prepaid','session00003',RANDOM(0,5000) );
INSERT INTO  DP_MDB.AGG_DATA_TRAFFIC
VALUES (CURRENT_DATE ,'RINGTONE', 'Postpaid','session00004',RANDOM(0,5000) );

Step 2: Create a universe for this aggregate in business objects designer and create following objects:

• Date
• Service Type
• Price Plan
• Distinct Sessions
• Data Transfer Bytes

Go to the properties of 'Distinct Sessions' and select 'Database Delegated'. This option is available under 'Choose how this measure will be projected when aggregated'. For further details, see the below screenshot:

Step 3: Save and export after validating universe.

Step 4: Create a Webi report using the newly created universe using following objects:

• Date
• Service Type
• Price Plan
• Distinct Sessions
• Data Transfer Bytes

and run the report.

Web Intelligence Query

Run Web Intellgence Query

Webi Report Refreshed

Values of all measures are visible including distinct sessions.

Step 5: Try removing dimensions to see how it effects calculation of measures i.e. Distinct Session and Data Transfer Bytes

Removing Service Type dimension results in following:

• Data Transfer Bytes are summed against new combination of dimensions
• Whereas, Distinct Sessions are not summed up or averaged out. Instead, webi is displaying #TOREFRESH message and suggesting report user to refresh the report in order to get recalculated values from database against the new set of dimensions

Webi #TOREFRESH

Conclusion:

This new feature can be very useful if used properly and with right approach. It is a step forward towards solving inconsistent and incorrect calculation of distinct measures by business/end users. It is hoped that more such features will be introduced in coming version and bugs will be removed. Currently there are few bugs in this feature when using with specific databases like Teradata. These bugs will be discussed in future along with possible work arounds until SAP formally releases a fix pack to answer known bugs.