Conditional Scheduling in SAP BI Web Intelligence

Introduction

In our sample, we will use the eFashion sample Universe / Database.

The sample business case is:

If Sales Revenue for a given City drops below certain threshold (2 Million USD), I want the board of directors to be informed via email and a personalized report to be sent to this group.
However, if the Sales for a City are more than 2 Million but lower than 3 Million I want the store manager to be informed.

We assume in this sample that you are familiar with both SAP BI Web Intelligence, and with SAP BI Publications.

Let us now see the steps in detail:

1. Create a Webi document that will be used to pre-process the Dynamic Recipients list.

Here you put only the basic stuff that is needed to build the list for the recipients.

In this sample, I entered in the report body the fields City and Sales Revenue.

Then I have created a Variable, type dimension, with the formula:
=If [Sales revenue] <3000000 Then “areamanager@mydomain.com”

ElseIf [Sales revenue] <5000000 Then “boardofdirectors@mydomain.com”

Else “”

Then I added this variable to the table.  This is what I got:

Then I filtered to remove the rows than do not need an alert (Right click, Filter, Add Filter, etc. as per below image)

This is the filtered output.

One last suggested set of steps is to:

– set the page margins to 0  and

– remove the report title

– align the table to the top left immediately under the report header.

We want to do this because otherwise when creating the Webi doc used for the Dynamic Recipients list you may have some issues when it tries to identify the column names.

2. Schedule to Excel  the Document created at step 1

This will create the data provider for the final Dynamic Recipient List source document.

This step is necessary, because the condition is based on a variable, and the Publications engine is not able to process variables within a Webi Document. This is the reason why we need to create this intermediate Excel Data Provider, so that we will have raw data and not variables.

To do so:
Logon to BI Launchpad, go to the folder where you saved the Webi Document that will be used to create the data provider for the  final Dynamic Recipient List source document.

◈ In the Recurrence, select Run Now. (Later you will re-schedule it and here you will enter the required settings.  Bear in mind that this must be refreshed before the publication will be sent.)

◈ In the Formats select the output format: Microsoft Excel

◈ In the Destinations section, enter: File System

If you have only one node, better to save on the server itself, otherwise it is preferable to save the output xls in a shared location. In either case, you need to be sure that the specified user name and password are correct and that account has r/w access to the folder you are scheduling the xls to.

(Remember to activate the File System destination for all the Adaptive Job Servers in the landscape or you will get an error)

Now save it to enterprise with a name like “Dynamic Recipients Final”, and once again, go to the BI Launchpad and test that the document can be refreshed without any issues.

3. Create the WebI Document to be used as Dynamic Recipients list for the Publication

In Web Intelligence, create a new WebI document, based on Excel Personal Data Provider.

The source file is the XLSX file created by the schedule at step 3.

This document will be practically identical to the one created at Step 1, the only difference is that the field with the email address is no longer a variable, hence this can be used as a Dynamic Recipients List.

Once created the document, save to enterprise with a recognizable name, i.e. “Dynamic Recipients Final”

4. Create the Publication

From the BI Launchpad, navigate to the folder where you will store the publication, right click and Select New > Publication.

In the General Properties, enter the Title

in the Source Documents enter the Report you created at point #3

Now, from the left menu, select the Dynamic Recipients option, and specify the Source for Dynamic Recipients, then click on OK, after which you must select the Query and Map the fields.

You must also flag the checkbox “Use entire list”.

Next step is to configure the Personalization, which will be used to deliver the personalized reports to the users.

Last step is to complete the Destinations, see example in the image below.

You can also refine the publication advanced settings according to your requirements.

PS: It is not in the scope of this document to explain how Publications work, for more information please review the Business Intelligence Launch Pad User Guide.

Now you can test the publication, and adjust where necessary.

5. Fine tune the recurrence for the various schedules

Once you have successfully tested the various steps, it is time to adjust the recurrence of the various schedules, so that you have consistent results.

It is not in the scope of this document to go too much in depth into the details; however, it is suggested to put in place measures that prevent data inconsistency.

For example, the Publication should run after the Dynamic Recipients list has been refreshed, and before this can happen, the XLSX with the pre-processed recipients list must have been produced.

You can accomplish this with scheduling based on events, which is covered in the BI Launchpad User guide (7.1.8).

Continue reading

Percentage scenarios in SAP Business Objects Web Intelligence

Introduction:

This document will provide the overview on how to display the percentage change of KPI of any dimension by period wise.

I have developed this report when the end user wants to see the percentage change of KPI year wise or quarter wise for consecutive periods.

Scenario:

We need to show the particulars and its business amount for each quarter and we need to calculate the percentage change in each Quarter.

Percentage Change: (Amount (Current Year) – Amount (Previous Year) / Amount (Previous Year)) * 100

Currently as per WebI specifications, we can provide the period wise KPI’s in cross tab. So, if we need to display the percentage change periodically, we need to add the Percentage Column on right of KPI. This in result the percentage column will be added after every period as shown in screenshot below.

Formula used to calculate Percentage Change:

But we don’t need to display percentage change column after first period and also it will not give the proper calculation amount.

We need to create a report with particulars, amount for first two periods then percentage change column and amount with corresponding percentage change sequentially as per below requirement.

System Requirements to achieve this scenario:

◉ SAP Business Objects 4.0 SP2 FP13 (Web Intelligence Rich Client or BI Launchpad)

◉ DB2 C Express V9.0 (free from IBM).

Steps to achieve:

1. Create a Universe based on the required table and add the necessary objects

2. Create a WebI report on top of that universe.

Here we are going to filter out the amount of Maximum year from the report and add it in the Vertical Axis and need to create some formula to achieve the percentage change.

3. Create a cross tab with particulars as Vertical Axis, Period as Horizontal Axis and amount field as body axis

4. Create a variable to filter out the maximum period from the report.

5. Right Click on the block and add that Variable in Report filter.

6. Insert a new column next to the particular dimension and add the following formula to get the amount of maximum period.

7. Give the heading for first column as Nofilter (Max(Period)) as per requirement.

8. Now insert a new column on the right of Amount field with the name of Percentage Change.

9. We will get the layout as per requirement.

10. Now we need to get the amount of maximum year when we need to calculate the percentage for maximum period.

11. By using below formula, we can get the amount of maximum year.

12. While calculating Percentage Change, we will be getting the previous period value in that percentage change column as we inserted the Percentage

change column next to previous period. To get the Current Period value we need to get the amount of previous column value.

13. To get the previous column value we can use the WebI default function Previous([Amount]).

14. So now, If it is a maximum year then we need to take Amount of Max Period in Calculation for current period else we can take previous period value.

15. Now we got the Current Period Value, so we can calculate the Percentage Change Value as per formula.

16. Apply the Percentage Change variable to Percentage Change column.

Now we will get the report as per Requirement.

Continue reading

Conditional Scheduling in SAP BI Web Intelligence

Unlike in Crystal Reports, In SAP Web Intelligence, it is not possible to schedule based on conditions.

However, often times you can accomplish this with the Publications as follows.

Introduction

In our sample, we will use the eFashion sample Universe / Database.

The sample business case is:

If Sales Revenue for a given City drops below certain threshold (2 Million USD), I want the board of directors to be informed via email and a personalized report to be sent to this group.
However, if the Sales for a City are more than 2 Million but lower than 3 Million I want the store manager to be informed.

We assume in this sample that you are familiar with both SAP BI Web Intelligence, and with SAP BI Publications.

Let us now see the steps in detail:

1. Create a Webi document that will be used to pre-process the Dynamic Recipients list.

Here you put only the basic stuff that is needed to build the list for the recipients.

In this sample, I entered in the report body the fields City and Sales Revenue.

Then I have created a Variable, type dimension, with the formula:
=If [Sales revenue] <3000000 Then “areamanager@mydomain.com”

ElseIf [Sales revenue] <5000000 Then “boardofdirectors@mydomain.com”

Else “”

Then I added this variable to the table.  This is what I got:

Then I filtered to remove the rows than do not need an alert (Right click, Filter, Add Filter, etc. as per below image)

This is the filtered output.

One last suggested set of steps is to:

– set the page margins to 0  and

– remove the report title

– align the table to the top left immediately under the report header.

We want to do this because otherwise when creating the Webi doc used for the Dynamic Recipients list you may have some issues when it tries to identify the column names.

Then save it to the Enterprise, and test it from BI Launchpad, you want to be sure that it works

2. Schedule to Excel the Document created at step 1.

This will create the data provider for the final Dynamic Recipient List source document.

This step is necessary, because the condition is based on a variable, and the Publications engine is not able to process variables within a Webi Document. This is the reason why we need to create this intermediate Excel Data Provider, so that we will have raw data and not variables.

To do so:

Logon to BI Launchpad, go to the folder where you saved the Webi Document that will be used to create the data provider for the  final Dynamic Recipient List source document.

• In the Recurrence, select Run Now. (Later you will re-schedule it and here you will enter the required settings.  Bear in mind that this must be refreshed before the publication will be sent.)
• In the Formats select the output format: Microsoft Excel
• In the Destinations section, enter: File System

If you have only one node, better to save on the server itself, otherwise it is preferable to save the output xls in a shared location. In either case, you need to be sure that the specified user name and password are correct and that account has r/w access to the folder you are scheduling the xls to.

Now save it to enterprise with a name like “Dynamic Recipients Final”, and once again, go to the BI Launchpad and test that the document can be refreshed without any issues.

3. Create the WebI Document to be used as Dynamic Recipients list for the Publication

In Web Intelligence, create a new WebI document, based on Excel Personal Data Provider.

The source file is the XLSX file created by the schedule at step 3.

This document will be practically identical to the one created at Step 1, the only difference is that the field with the email address is no longer a variable, hence this can be used as a Dynamic Recipients List.

Once created the document, save to enterprise with a recognizable name, i.e. “Dynamic Recipients Final”

4. Create the Publication

From the BI Launchpad, navigate to the folder where you will store the publication, right click and Select New > Publication.

In the General Properties, enter the Title,

in the Source Documents enter the Report you created at point #3

Now, from the left menu, select the Dynamic Recipients option, and specify the Source for Dynamic Recipients, then click on OK, after which you must select the Query and Map the fields.

You must also flag the checkbox “Use entire list”.

Next step is to configure the Personalization, which will be used to deliver the personalized reports to the users.

Last step is to complete the Destinations, see example in the image below.

You can also refine the publication advanced settings according to your requirements.

PS: It is not in the scope of this document to explain how Publications work, for more information please review the Business Intelligence Launch Pad User Guide.

Now you can test the publication, and adjust where necessary.

5. Fine tune the recurrence for the various schedules

Once you have successfully tested the various steps, it is time to adjust the recurrence of the various schedules, so that you have consistent results.

It is not in the scope of this document to go too much in depth into the details; however, it is suggested to put in place measures that prevent data inconsistency.

For example, the Publication should run after the Dynamic Recipients list has been refreshed, and before this can happen, the XLSX with the pre-processed recipients list must have been produced.

You can accomplish this with scheduling based on events, which is covered in the BI Launchpad User guide (7.1.8).

Continue reading

SAP BO Webi report development on HANA views

SAP BO Webi report development on HANA views:

This document explains step by step creation of Hana views, Universe in IDT and development of Webi report on top of the Hana views universes. This document will give understanding of the complete development/creation of end to end scenario from Hana views to report.

In this scenario we are considering Purchase application flow and analysis of report for purchase users assuming that Purchase related data extracted thru SLT from ECC system.

Applications & Versions used for this scenario:

SAP Hana Studio 2.0.7

Information Design Tool 4.1

SAP BO Webi 4.1

This Document explains below major topics:

1. Creation of Attribute View
2. Creation of Analytical View
3. Creation of calculation View
4. Creating and Publishing universe using IDT (Information Design Tool)
5. Creation of Webi Report

Extracted Purchase order header, Item and account assignment tables and also Material, Vendor, Company code and G/L account master data tables in to HANA from ECC using  SLT.

As a best practice need to follow the proper naming conventions for creating any views. (ex: Attribute view –starting with AT followed by table name)

1. Creation of Attribute View:

1.1. Select the respective package –>  Right click –->  New a Select Attribute View

1.2. Select the Mara table from the schema and drop it in Data Foundation in view and double click on the selected fields for output display.

Save and activate the view. Attribute view AT_MARA created on Material Master data table – Mara:

Similar way creates other attribute views and by selecting the respective master data tables from the schema, and enable the fields which we want to see in output in each view for below.

Vendor Master – LFA1 – Attribute View

Material Master – MARA – Attribute View

Company Code master – T001 – Attribute View

G/L Account master – SKB1 – Attribute view

Once all attribute views are created it displays as below:

2. Creation of Analytical View:

Below Purchase order tables are from ECC thru SLT.

EKKO : Purchase Document Header

EKPO : Purchase order Item

EKKN : Purchase Data (Account Assignment)

2.1. Right click on your package — Select New – Analytical View:

Enter the details

2.2. Drag the EKKO table in to data foundation and AT_LFA1 and AT_T001 attribute views in to star join.

Join the appropriate fields of EKKO table with both attribute views based on the requirements.

EKKO.company_code = AT_T001.Company_Code

EKKO.vendor = AT_LFA1.Vendor

2.3. In Semantics keep the Key fields, correct the Attributes and measures if required.

Save and activate the view.

Similarly create the other two Analytical views, all three analytical views will displays as below:

3. Creation of Calculation View:

3.1.  Right click on your package — Select New – Calculation View:

Drag two Aggregation nodes and select AN_EKKO and AN_EKPO analytical views in each node respectively.

Select required output fields in each Analytical view

3.2. Connect the two aggregation nodes to Join Node and connect the related common fields.

Drag the Projection node and select required fields.

3.3 Select the key fields and adjust the dimensions and measures.

Save and activate the view.

Right click on the view and select Data Preview, we can see the data output of calculation view CL_Purchase

Now Calculation view is ready need to create universe to expose this data thru webi report.

4. Creating universe using IDT (Information Design Tool)

4.1.  Logon to IDT, In Repository Resources under the respective system expand right on Connections Node.

Select ‘Insert Relational Connection’

Enter the connection name:

Click on Next, and then select the database middle ware drivers under SAP node

Click on Next, and then enter the User name, password details of Hana system and click on Test connection button.

Then click on Finish, Now connection established to Hana system.

Expand the Connections node there we can find the new connection ‘Purchase_Flow’.

4.2. Create project ‘Purchase_Flow’ by clicking on File à Newà Project and enter the project name.

Click on Finish. New project ‘Purchase_Flow’ created in Local Projects node.

4.3. Now select Purchase_Flow connection, right click and click on ‘Create Relational Connection Shortcut’.

It will popup Select a Local Project window; select our ‘Purchase_Flow project.

Relational Connection connected to project as shown below:

Select Project à right click à select New Click on ‘Data Foundation’

Enter the name of Data Foundation

Select Multi Source Enabled as IDT will support.

Check for the user authentication details..

Click on Next and select the connection which was created earlier.

Click on Next, displays the connection parameters.

Click on Finish.

Open the Purchase_Flow_DF, expand the connection node, select _SYS_BIC  schema node where all Hana views columnar table will store. Select the CL_Purchase Calculation view table.

Save it.

4.4. Now create Business layer. Select Purchase_Flow project Right click à New à click on Business Layer.

It displays the below pop up screen

Select Relational Data Foundation and click on Next.

Enter the Name and click on Next

Select the respective data foundation and click on Finish. There is check box if system will create classes and objects, if you want to create on your own need to un check this.

Purchase_Flow_BL business layer created as below, review dimensions and measures change if required.

Select change the data type if necessary

Save the Business Layer. We can check the query output by clicking on Queries tab.

4.5. Click on Insert Query, it will add displays the classes and objects of Business layer

Drag and drop required fields to click on refresh to check the data in first instance.

Close the query.

4.6. To publish as universe Right click on Purchase_Flow_BL business layer à Publish à To a Repository

Click on Next

Click on Next

Click on Finish, Universe published successfully.

5. Creation of Webi Report :

Open BI Launch pad by entering the login credentials

Expand Application and select Web Intelligence

Click on New and select ‘Universe’ as data source.

Select our Universe from the list

Purcahse_Flow_BL universe will open in Query Panel as below:

Drag and drop required dimensions and measures in Result objects and if you want to filter any filed also.

Query filtered based on Vendor, Click on Run Query, pop up display to select Vendor to see the data.

Once required Vendors are selected click on Ok, report will display.

Change the Report Name as Purchase Data Flow Report

You can format the report with Refresh Date, page no, logo and chart as per requirement.

Continue reading