1. Report Bursting Technique
Report bursting is a technique which is used to send single report to list of users wherein they can view data which is relevant only to them. Firstly, the report gets refreshed and then it is send through email to the list of users in either PDF or excel format. When the report is refreshed, it hits the database for a single time, which is termed as single pass report bursting. The process of customizing the data in report so that individual users can view data which is relevant only to them is termed as personalization.
Normally personalization is achieved based on distinguishing field such as “SKU number” or any such BO dimension object which hold unique values for individual users. Usually two BO reports are required. The first BO report called "Source Report", just a name for our reference, will be the source document which will be distributed to the users. The data source (also called data provider) should be using the distinguishing field “SKU number” along with other BO dimension and measure objects.
The second BO report called “Dynamic List Report” will be used as source for dynamic recipients. This report should typically have “SKU number” and "Email Id".
A publication in BO is created, lets call it as “Pub” which uses the first and second BO reports. The first BO report will be selected as source document in the publication. And the second BO report will be selected as the dynamic recipient. When the publication runs or is scheduled to run. Then “SKU number” is fetched from first BO report and an email id "Email Id" is picked up corresponding to each “SKU number” from the second BO report. Once the report gets refreshed, it is chopped based on the “SKU number” and only relevant (or authorize) part of the data is send to users whose email id belongs to that particular “SKU number”. When no data is available for few “SKU number” in the refreshed BO report, then PDF or excel version of BO report goes out to users as blank files.
If users would have accessed reports by login in to Business Objects Enterprise then they would hit the database several times. The first hit to CMS database would have happened when they would have logged into Infoview (web based portal of Business Objects). The second hit to reporting database would have happened when they would have refreshed the report. Also needless to say, other kind of restrictions would have to be applied in order to enable users to view data which is only relevant to them (or which they are authorized to view). More over when there are lot of users, then refreshing their individual reports will create a lot of load on the reporting databases.
Thus single pass report bursting reduces the unwanted load on the databases. All users won’t have to be added to Business Objects Enterprise. So the number of BO licenses required, for named user, also gets reduced. The publication can be scheduled to run during non peak hours when network traffic is low thereby utilizing the network bandwidth in an efficient way.
2. Components used to achieve report bursting
Each component used to achieve report bursting has been discussed in detail below:
2.1 Publication
A “publication” is a collection of documents intended for distribution to a mass audience. Before the documents are distributed, the publisher or administrator defines the publication using a collection of metadata. This metadata includes the publication source, its recipients, output format and the personalization applied.
2.1.1 Dynamic Recipient inside Publication
A technique of dynamic recipient’s publication is required. The “Dynamic recipients” are publication recipients who exist outside of the Business Objects Enterprise. Dynamic recipients usually have user information (like email id) in an external data source, such as a database or an LDAP or AD directory, but do not have user accounts in Business Objects Enterprise.
To distribute a publication to dynamic recipients, we use a “dynamic recipient source”. A dynamic recipient source is a document or custom data provider that provides information about publication recipients outside of the Business Objects Enterprise. Dynamic recipient sources allow us to easily maintain information for dynamic recipients by linking directly to the external data source and retrieving the most recent data.
2.1.2 Personalization inside Publication
“Personalization” is the process of filtering data in source documents so that only relevant data is displayed for publication recipients. Recipients are dynamic recipients, we can map a data field or column in the source document to data in the dynamic recipient source. For example, we can map a Customer ID field in a source document to the Recipient ID field in the dynamic recipient source.
2.1.3 Formats inside Publication
The output format can be specified for a publication. There are different options like “Desktop Intelligence”, “Microsoft Excel”, ”Adobe Acrobat”, “Plan Text”, “Rich Text” and ”mHTML”.We will be using either “Microsoft Excel” or ”Adobe Acrobat”. The option to select different tabsin the source report for publication is available inside “Output Format Details”.
2.1.4 Destinations inside Publication
Destinations are locations that you deliver publications to. A destination can be the Business Objects Enterprise location in which a publication is stored, an Business Objects inbox, an email address, an FTP server, or a directory on the file system. In this document we are focusing on sending documents as attachment over the emails.
3. Scheduling a Publication
Once the publication is created, it has to be scheduled to run at a specified time. In case the source report contains prompt then we can specify prompt value during scheduling. The scheduled publication in that case will only fetch data based on the prompt values provided for them. For example, if the report prompt has parameter “Enter Last Month of Quarter e.g. 01-Jun-2001:“, then values will be returned only for the specified prompt value.
4. Configuration on Lotus Notes Server
The IP address of server, which hosts Business Objects, need to be added in the Lotus Notes inbound configuration. The environment in which report bursting was performed has Business Objects 3.1 SP3 hosted on UNIX with Solaris 10 operating system. So the IP address of UNIX server was added to the Lotus Notes inbound configuration by Lotus Notes administrator.
It is advisable to check with the Lotus Notes administrator and get the following details which will be required to be provided on the BO XI 3.1 destination Job servers and other Job servers.
1) Domain Name : Enter the fully qualified domain of the SMTP server.
2) Server Name/Host : Enter the name of the SMTP server.
3) Port : Enter the port that the SMTP server is listening on. (This default SMTP port is 25.)
4) Authentication : Select Plain or Login if the job server must be authenticated using one of these methods in order to send email.
5) SMTP User Name : Provide the Job Server with a user name that has permission to send email and attachments through the SMTP server.
6) SMTP Password : Provide the Job Server with the password for the SMTP server.
7) From : Normally BO XI support group email id
😎 To : Just a place holder so could be individual email id to whom each email will be send out. This can be an additional email id other than the dynamic email ids.
9) Subject : <blank>
Screenshot-1 : Settings in the BO XI 3.1 Destination Job server
5. Configuration for Email on BO XI 3.1 Job Servers
To configure the E-Mail sending functionality we need to make the settings for all BO XI 3.1 job servers and destination job servers.
We need to do similar settings (as shown in “Screenshot-1: Settings in the BO XI 3.1 Destination Job server”) on destination tab to all the job servers namely
· Destination Job server
· Adaptive Job server
· Crystal report Job Server
· Desktop Intelligence Job Server
· List Of Value Job Server
· Program Job server
· Publication Job Server
For all these Job Servers go to Destination and then choose destination as Email from drop down and provide the following parameters which are required to be added.
1) Domain Name : Enter the fully qualified domain of the SMTP server.
2) Server Name/Host : Enter the name of the SMTP server.
3) Port : Enter the port that the SMTP server is listening on. (This default SMTP port is 25.)
4) Authentication : Select Plain or Login if the job server must be authenticated using one of these methods in order to send email.
5) SMTP User Name : Provide the Job Server with a user name that has permission to send email and attachments through the SMTP server.
6) SMTP Password : Provide the Job Server with the password for the SMTP server.
7) From : Normally BO XI support group email id
😎 To : Just a place holder so could be individual email id to whom each email will be send out. This can be an additional email id other than the dynamic email ids.
9) Subject : <blank>
On the same tab we can tick Deliver Documents as attachment.
Screenshot-2 : Deliver Document(s) as Attachment
Note-1: The configuration for email on BO XI Job servers depend basically on which type of BO XI reports or components are suppose to be report bursted. For example if we are not using crystal reports then we can skip the configuration on Crystal Report Job server. But to start with it is advisable to enable configuration for email on all BO XI job servers.
Note-2 : When Outlook server (for email) is used instead of Lotus notes then there can be some limitations on number of emails which can be send out (as part of report bursting). However from BO XI server side there is NO limitations on the numbers of emails which can be send out. This has been confirmed with SAP BO.
6. Illustration with Example
It is always better to test the report bursting approach with a sample report. The following steps provide details on creating sample report and testing it. In the example provided below only Desktop Intelligence reports have been used. Though the same functionality also works for Web Intelligence reports when they are created using universe.
A) Create the Desktop Intelligence report "Source Report" which is to be report bursted as elaborated below.
Login to Desktop Intelligence and create report with report name as "Source Report" which uses free hand SQL mentioned below. Give Report tab name as “Report1” and make note of it, as the same name need to be provided in the step-7 while creating the publication.
Once report is created, save the report as "Source Report" and publish it to the repository. Login to Infoview and refresh the report. The report should refresh successfully.
Query to be used in report "Source Report"
select
'115121' as "SKU number",
'Anshuman Kumar' as "Name of Manager",
'2004' as "Revenue"
from dual
Union
select
'116256' as "SKU number",
'Testing' as "Name of Manager",
'2006' as "Revenue"
from dual
Screenshot-3: “Source Report”
B) Create new Desktop Intelligence report “Dynamic List Report” which is the report with email list as elaborated below.
Login to Desktop Intelligence and create report with report name as “Dynamic List Report” which uses free hand SQL mentioned below. Give Query name as “Email_Addresses” (by going to definition as shown in Screenshot-5). Please note the Query name as the same name need to be provided in the step-4 while creating the publication. The details of report “Dynamic List Report” is shown in screenshot-4 below.
Query to be used in report " Dynamic List Report "
select '115121' as "SKU number",
'anshuman.kumar@accenture.com' as "Email Id"
from dual
union
select '116256' as "SKU number",
'testing@accenture.com' as "Email Id"
from dual
Screenshot-4: “Dynamic List Report”
Screenshot-5: Specify Query name as “Email_Addresses” in definition tab of Data Manager
C) Create publication “Pub” as elaborated below:
1)Create one publication” Pub” with description as 'For report distribution'. The steps are mentioned below along with the screenshots in point-a and point-b.
a)Click on New->Publication as shown below:
Screenshot-6: Publication
b) In Title specify “Pub” and description specify “For report distribution” as shown below in screenshot-7.
Screenshot-7:
2) In Source Documents tab, click on “Add” button, browse and select "Source Report" and click on “Ok” button and then click on “save” button so it will appear as shown below in screenshot-8.
Screenshot-8:
3) In Dynamic Recipients tab, choose source for dynamic recipients as 'Web Intelligence/Desk Intelligence' from drop down
a) Then browse to the path on left panel and select the report “Dynamic List Report” from right panel and click on OK button then wait for it to get processed. It takes few minutes to get processed. The same can be found in in screenshot-9 below:
Screenshot-9:
4) Drop down appears for 'Select the data source name for the document' need to choose “Email_Addresses” as shown below in screenshot-10. (The same name has been used in step B of topic “Illustration with Example”)
Screenshot-10:
5) In Map fields from the source document to those used to send the Publication. Choose the following and then click on Save button.
Choose Recipient Identifier (Required) as “SKU number”
Choose in Email in drop-down list as “Email id”
As shown below in in screenshot-11.
Screenshot-11:
6) Choose Personalization tab, then go to filters section, click on Add button then for Report Field enter “SKU number(Email_Addresses)” then in Dynamic Recipient mapping select “SKU number” from drop down and click on save button. The details are shown in the below screenshot-12:
Screenshot-12:
Note : For Web Intelligence report the option will come in drop down but for desktop intelligence we need to provide it manually.
7) Click on Formats and select the Output Format as Adobe Acrobat (for PDF) and select “Report1” in "Output Format Details". Here “Report1” is then name of tab of “Source Report”. The details can be seen below in screenshot-13.
Screenshot-13:
Note: Make sure that you select only Excel or PDF format for this method.
😎 In Destinations tab tick email under Select Destinations:
a) Then in message specify “Sample Report Testing”, and in subject of email specify “Sample Report Testing” as can be seen below in screenshot-14.
Screenshot-14:
b) Then tick “Deliver Document(s) as attachment”, then choose 'Use specific Name' and specify “SampleReport” give space and select place holder as “DateTime” then type .(dot) and select place holder “FileExtension” from drop down. Based on the parameters selected, ‘Use specific Name’ will appear as “SampleReport %SI_STARTTIME%.%EXT%”. Then click on Save button. The details are shown below in screenshot-15. When we use the above settings then the attachment name will appear as “SampleReport time.file extension” .
Screenshot-15:
9) In Additional Options/ Recurrence tab, choose Run Objects as “Now” and click on save button.
Once scheduling gets successful, an email will be send over with PDF file as an attachment. The sample source report after getting report bursted is available for reference in the next section “Sample Report Bursted Report”.
Note-1 : Alternatively SKU number can be used in the section of report. The screenshot of sample report based on “efashion” universe is available below in screenshot-16. This can be used as a “Source Report” along with “Dynamic List Report” to achieve report bursting.
Screenshot-16: Report based on “efashion” universe using SKU number in the section of report
7. Sample Report Bursted Report
The source report is sent out to email ids of end users as attachment. Only the relevant part of data is shown to the end users. For example a sample of source report is available below.
SKU number 115121 is only meant to view Revenue 2004 so that is the reason only that gets shown to him in the his report which gets delivered to him as an email attachment.
Screenshot-17: Report as the way it will appear in Adobe acrobat (i.e. PDF) format when sent to user as attachment
8. Environment Used
The report bursting have been performed in environment which has been setup with the following
1) BO XI 3.1 SP3 running on Unix server which has Solaris 10 operating system
2) Lotus Notes Server for Email
3) Oracle 11
9. References
The reference where made to the following documents.
1) Business Objects Enterprise Administrator's Guides:
xi3-1_bip_admin_en.pdf and xi31_sp3_bip_admin_en.pdf
2) SAP notes 1484530 and 1468122 from SAP Business Objects support.
- SAP Managed Tags:
1 Comment
