BOE Report Scheduler Asking for Context
Many SAP Business Objects 4 users have report scheduling complaints which are based on a universe designed in Information Design Tool and have a design that allows context selection. Issue is that when report is scheduled that can take two different paths, it ask for the context but available list of context is not displayed, resulting in the in-ability to schedule the report. Up-till SP4 (service pack 4) SAP has not released a fix for this and until that time, following are the do's and don't's for scheduling a report containing context choices:
- Create a new report and in the query panel set option to save the context choice
- Save the report
- Open the report and refresh the report using refresh icon on the top
- Select the context of choice
- Once report is refreshed, save it again
- Do not change the selection of the query objects in the query panel. Once you change objects and save it, you will not be able to schedule this report again. The only work around would be to re-create the report from scratch by following through step 1 to step 5
Good luck!
SAP Business Objects: Refreshing Structure in Information Design Tool
If Data Foundation Layer has already been developed or is being developed, then refreshing structure to reflect latest DB changes have to be done in following sequence:
- Take a backup of your project before proceeding with this process
- Open Data Foundation Layer and select "Refresh Structure"
- Utility will gather information and will categorize changes into Missing Tables, Missing Columns, Added Columns, Modified Column. On each screen it will give you option to choose which changes to reflect
- Un-check all Missing Tables and Missing Columns. Take note of missing tables and missing columns. Check all Added Columns and Modified Columns
- Complete the process. This will update changed columns and add new columns
- Manually remove missing tables from your Data Foundation and also any relevant joins
- For missing columns, make sure they are not used in any joins. If so, you need to update joins and remove/change any columns which are removed from database
- Save your Data Foundation and back it up again to protect your progress
- Once you are sure that you have updated all the joins that contained missing columns, run Refresh Structure again. This time, it should not show you any Missing Table (since you removed them manually)
- Check all missing columns and proceed. It will remove all obsolete columns from your Data Foundation
- Now try to save your work. If Information Design Tool gives you a strange error/exception, that means you have not updated all the joins and there are still some joins containing missing columns.
- Restore your Data Foundation from latest backup and go through your joins again and make sure all missing columns have been removed from joins. You have to iterate this process until you are able to save successfully after removing missing columns by Refreshing Structure
- Once Data Foundation is updated and saved, update object definitions in Business Layer
This information is for SAP Business Objects 4 SP2. This approach is useful if you want to retain your aliases, contexts and joins while refreshing structure.
Teradata Query Banding In SAP Business Objects Information Design Tool
Usually customers require tracking and auditing of users and queries they are running on DWH/Teradata. There is no one way of tracking queries run by BI application users. An application user is not necessarily a Teradata user, therefore, query band is a useful feature for tracking logons and queries generated from BO and BO users. Queries are logged in DBQL along with necessary information. For instance:
- Application (Business Objects) username
- Universe name
- Document name
- etc (further details can be found in SAP BO documentation)
This is achieved using query banding feature provided in Teradata and is exploited in SAP Business Objects using 'BEGIN_SQL' parameter for SQL Generation. An example of BEIGN_SQL string is as follows:
SET QUERY_BAND='USER='@Variable('BOUSER');Document='@Variable('DPNAME')';' for transaction;
As per SAP documentation, it should work fine and it does work fine if you are using it in Universe Design Tool. But when used in Information Design Tool, it returns an error due to problem with SQL generation. Issue is due to @Variable function which returns a value enclosed in single quotes. For some reason, this is taken care of in Universe Design Tool but since Information Design Tool is new, it causes SQL error on Teradata.
Until SAP development team fixes this issue, a workaround can be used for query banding in Information Design Tool and that is to call a Teradata Stored Procedure in BEGIN_SQL and pass necessary parameters to it as inputs. Query band can then be set within the procedure.
For further details or queries, leave comments and we will reply.
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.