Random Thoughts

Views on life

Posts Tagged ‘BusinessObjects Enterprise’

How to setup report bursting (data driven publication) in BusinessObjects

Posted by Hemanta Banerjee on February 23, 2011


One of my favourite features of BusinessObjects is the data driven publication or report bursting. This allows the administrator to very easily setup personalized reports to be delivered to the users at scheduled intervals. For example if I want to send out a pipeline report with all of their active opportunities to all my sales managers every week the report publication feature would allow me to do this with just 1 single report as opposed to creating multiple reports, one for each sales manager.

So how do we set it up. It is quite simple. Let us say we have a report that shows the internet sales orders by country as shown in the report below.

image

Now I want to create a publication such that the sales administrators for each of the regions get the report for their respective territories. So first I set up another report (called recipient report) which list all the users and their email addresses as shown below.

image

Instead of hardcoding the users I have decided to make it very parameterized by using the database and crystal reports. In a database table I maintain the contact details for each of sales administrator and the territories that they manage.

image

This approach allows me to change the distribution at any time without having to worry about modifying any of the reports or scheduled jobs. Now using Crystal reports I have developed the report shown above, which is what I am going to use as the input for the scheduled job.

Now comes the process of setting up the scheduled job. In CMC (or Infoview) right click on the folder where you want to save the job and create a new publication job.

image

 

Now give the job a name and also select the report that you want to send to your users.

image

 

The next step is setting up the recipients of this report. You would use Enterprise Recipients if you wanted to send the report to the users of the BusinessObjects system. However this is not mandatory. As shown below you can also email the report to other users who do not have access to your BusinessObjects server. These could be your employees or even your customers/business partners.

image

With Dynamic recipients I can email reports to anyone as long as the mail server information is setup correctly in my BOE system. You would need to set this up in a couple places and I will cover this in a later post. For now assuming it is setup we can use the recipient report created earlier and map the user name and emails from that report.

image

The next step is personalization. I want the sales administrator for Australia to only get the orders from Australia. This is setup in the personalization section of the job. In this case the report is grouped by DimGeography.EnglishCountryRegionName and I use that split the report.

image

As shown above I can use the country name from the report and mapping to the country name for the recipient I am instructing the job to split the report by  DimGeography.EnglishCountryRegionName and use the Recipient Mapping column to deliver to the right user.

image

After setting the other aspects such as format and schedule, you can go ahead and test this publication job. You can either deploy it directly or you can test it first. I would recommend testing it, whereby you can give your email address and the job would run but instead of emailing the reports to the users it would be delivered to you.

image

That’s all there is to setting up publications. Now coming to setting up the mail servers, there are a couple of services for which we would need to set the email servers. The easiest way to get it to work is to first use a local email server to test the functionality and then moving to the corporate mail server. You would need to setup email as a destination for the following

  • Adaptive Job Server
  • Destination Job Server
  • Program Job Server
  • Publication Job Server
  • Crystal Reports Job Server

For each of the service right click and select destination as shown below

image

And set the details for the email server.
image

Advertisements

Posted in Administration, BusinessObjects, Distribution, Publication | Tagged: , , , , | 7 Comments »

Linking Dashboards and Reports in BOE Dashboard Builder

Posted by Hemanta Banerjee on January 13, 2011


One of the common requests I have found on the BOB board relates to how can we link WEBI/CR reports with XCelsius dashboards. The common scenario is where we are showing some summary information in an XCelsius dashboard and I want to show some related detailed report using WEBI or CR on the same dashboard.

In BusinessObjects Enterprise XI 3.1, Edge XI 3.1 and Crystal Reports Server 2008 v1, the new Interportlet Communication (IPC) feature in Dashboard Builder allows the transfer of information between distinct analytics. Now you can pass parameters from an Xcelsius dashboard to another XCelsius dashboard or to Crystal Reports and Web Intelligence. Using this framework, I will show 2 simple ways to link between:

  1. Two XCelsius dashboards
  2. Between an XCelsius dashboard and Web Intelligence/Crystal Reports

XCelsius to XCelsius linking

An XCelsius dashboard used as a source document has to be configured with a Portal Provider Connection. The target analytic also has to be configured with the Portal Consumer Connection. We will send parameter data between two analytics with the use of a simple example: sending information from a list box selector in one analytic to drive a chart in another dashboard. The steps are

Create the provider analytic

1. Create the list box control for the years: As you can see below I have created a simple list box control with the years.

image

The selected item from the list box is placed in cell B2 in my example (marked in yellow). I have also set the size of the canvas to fit the component.

2. Create a new portal data connection as shown below.

image

Choose provider as the Connection Type and give a name to the range (in my example Year). Since we are passing a single cell of data, select Cell  as the range type and the cell B2 which is the target of the list box selection. Also go to the usage tab and instruct the data connection to communicate with the consumers when the value in the target cell (B2) changes.

image

This will ensure that whenever the user makes any selection on the list box, the label will be passed to the target/consumer of the portal connection.

Create the target analytic

1. In the target dashboard I have created a simple dashboard where I have a chart combo box.

image

In the properties of the combo box I have set the combo to read the current selection from a cell. When the selection in the cell changes it will read the row of the data corresponding to the selected year and place it in the target row which drives the chart.

2. Define the portal data connection of type consumer as shown below.

image

Keep in mind that the Range name has to be the same for both the provider and the consumer. Also since the provider is going to pass a single cell I have selected the range type as Cell and have it update cell B2 which drives my drop down and chart.

Combine them in the dashboard

Now log on to InfoView and create a new Corporate Dashboard. Drag your “Provider” and “Consumer” analytics onto the dashboard. Also activate “Content Linking”.  This can be done by setting the properties of the Provider analytic. Choose the Provider analytic as the “Source Analytics”, and Consumer as the “Target Analytics” as shown below.

image

XCelsius to WEBI/CR linking

An XCelsius dashboard used as a source document for Crystal Reports and Web Intelligence has to be configured with the FS Command connectivity. The parameters are then assembled in an OpenDocument URL by the Dashboard Builder framework. For more information about the OpenDocument syntax, see the documentation:
http://help.sap.com/businessobject/product_guides/boexir31/en/xi3-1_url_reporting_opendocument_en.pdf

To illustrate this example I have a summary report in XCelsius and use WEBI to show the detailed month wise breakdown.

Create the provider analytic

The steps to create the provider are the same. In fact I have used the same analytic that I used in the previous example. The only difference is that since the dashboard builder framework uses the OpenDoc URL format to pass the parameters I need to massage the parameter so that it can sent to WEBI as shown below.

image

In cell D2, I take the selection from B2 and use excel formula (="&lsSYear="&B2) to create the parameter for the data connection.

Now go to the data connections and add a new FS Data connection as shown below.

image

The trigger also has to be set in the usage tab, so that the data connection sends the new value whenever the value in the cell D2 changes.

image

Now export this to SWF and save it in the infoview portal.

Create the WEBI Report

The steps to create the WEBI Report is fairly simple. As shown below I have created a simple WEBI report which takes in the year as the prompt.

image

The only thing to keep in mind is the name of the prompt should correspond to the formula set in the provider analytic. For example in my case the open doc param formula in the provider analytics is ="&lsSYear="&B2 which corresponds to the Year prompt in WEBI.

Save WEBI report in the infoview portal and test the content linking as shown earlier.

If you want more details you can check out the user guides at

OpenDocument User Guide: http://help.sap.com/businessobject/product_guides/boexir31/en/xi3-1_url_reporting_opendocument_en.pdf
Dashboard Builder User Guide: http://help.sap.com/businessobject/product_guides/boexir31SP3/en/xi31_sp3_dashboard_user_en.pdf

Posted in BusinessObjects, Dashboard, WEBI, XCelsius | Tagged: , , | 4 Comments »

How to do Year Ago or Prior period type comparison in WEBI

Posted by Hemanta Banerjee on November 3, 2010


Again a post inspired by the BOBJ board which talks about a generic requirement whereby making it easy for adhoc users to perform relative time period based analysis as easy as possible. The users should be able to select the date using a prompt or otherwise and ask for values for either the selected period or some other reference period such as Current Year Last Week or Prior Year by simply selecting a different measures named as such from the Universe.

This is actually quite simple once you get around to designing it. All we need to do is make sure we run multiple queries once for each time period and then join the queries at runtime in the BI Server. WEBI provides such functionality in the form of contexts. For example if we define the current period and YAGO (year ago) in 2 separate contexts, then when we pull Sales and YAGO sales in the same report BI Server will automatically run 2 queries and join the results. That’s what I want to leverage.

My example shows how to setup 2 measures, Sales and YAGO Sales. However this can be done for any number of measures as well as any number of reference periods as the process is quite simple. So here is what I have done.

1. For each relative time period we need to define an alias of the fact table in the universe. For example I need current period and YAGO therefore I have 2 fact tables – Sales and YAGO Sales.

image

2. It is also very important to have a proper dates/calendar table that you can use for your time based calculations. In my schema it is the DATES_TABLE with a structure as shown below.

image

3. Now join the 2 facts to the dates table. The main fact table for current period will be joined normally to the dates table, however the YAGO sales will be joined to the dates table with a lag.

image image

 

Ofcourse you would also need to define all the other joins between the YAGO_SALES fact and the other dimension tables for completeness. Now that the 2 join conditions have been defined as you can define the measures from each of these tables. When user say runs the query for October 2010 then Sales revenue will be the value for Oct-2010, however YAGO will be Oct-2009 since YAGO sales will join with the date table with a 1 year lag due to our join condition.

image

Now in order to force the 2 queries we need to define the contexts – 1 for each fact table. This will resolve the loops as well as force the BI Server to issue 2 separate queries to the database.

image

Now in the query as will see the BI Server will issue 2 queries one for each context and combine them in the result set.

image

The beauty of this approach is that it is automatically level based. If the user selects a date in the prompt the results will be for same date last year, for month it will be same month LY, for Qtr will be same Qtr last year and for year will be last full year.

You can use the same mechanism to define last week or last month.

Posted in BusinessObjects, Prior Period, WEBI | Tagged: , , , , , , , | 2 Comments »

How to create start and end date prompt in WEBI report

Posted by Hemanta Banerjee on November 1, 2010


Ran into this question on the BOBJ board asking how to create a WEBI report which can prompt the user to enter the start and end dates for running a report. Seems quite simple and useful so here is the solution.

I am using the adventure works universe to illustrate the process. The query I have created is show below.

image

Created a simple query which prompts the user to enter the year and months for analysis. Note that I will only allow the user to select a single year. If you want to do cross-year analysis then you would need to define a separate object in the time dimension as “Year-Month” which would allow the user to perform cross year analysis. When I run the report I get prompted to enter the year and months for the report.

image

And the results are as shown.

image

Posted in BusinessObjects, WEBI | Tagged: , , , , , | Leave a Comment »

How to setup report scheduling and report distribution for BusinessObjects

Posted by Hemanta Banerjee on October 29, 2010


I came across this posting on the BOBJ board asking how to setup report distribution, but the reports should run only if the ETL process is successful. This ofcourse can be done using event based scheduling in BusinessObjects. So here you go – in this post I will talk about scheduling including event based scheduling. I will also talk about how to setup report distribution using EMAIL or to a folder.

Setting up Calendars

Let us first look at the various options for scheduling reports. BusinessObjects allows for scheduling reports either based on time, custom calendars or events. The simplest is to use the built in schedules provided out of the box.

image

These cover most of the common scenarios such as “1st day of the month” etc. For example the screenshot below shows that the report will run on the 3rd Monday of every month.

image

If none of these meet the requirements you can also setup custom calendars. For example let us say I want to run a report on the 3rd day of every quarter except Q4 where I want to run it on the 9th day of the quarter. This cannot be met by any of the standard calendars, so I will go ahead and define a custom calendar.

image

After setting the name I can go ahead and select the days when I want to run the report. I can either select specific days as shown below

image

I can also choose by day of the month. For example below the report will run on the 3rd and 10th day of every month.

image

Similarly I can also schedule for day of the week. For example below the report will run on the 1st thursday and third thursday and friday of every month.

image

While we can do a lot of fancy things I would recommend that we keep it simple. After defining the calendar we can go any report and schedule it using our custom calendar that we have defined.

image

Setting up Events

We can also schedule reports to be triggered on events. There are 3 kinds of events.

image

Custom Events: They have to be raised by calling the BusinessObjects SDK. Used to integrate reporting with other applications. We can have any application/web service use the BOE SDK to raise an event to trigger generation of a report.

File Events: BOE will looking for specific files, for example below the BOE will wait for a file called etl_complete.txt on d:\temp and will trigger the event if this file is found.

image

Scheduled Event: This is usually defined to define event chains. For example I want to run report 2 only if report 1 was successful.

image

Scheduling

Now let us setup a scenario. I want to run the P&L report only if the ETL is complete. And if the P&L report is run successfully then I will run the Balance Sheet report as well. Right click on the P&L report and select Schedule.

image

Since I want to test this I am scheduling it to “Now”. I am also setting the retry for 20 and retry interval to 60 seconds. This essentially means that the server will wait for 20 mins for the ETL Complete event to occur. 

image

I also setup the events as part of the scheduling setup. I want to wait for the ETL complete event. This event will be triggered by the ETL application by placing a file in d:\temp folder. Also if the report generation is successful I will trigger the P&L Complete event which can be used to trigger the Balance Sheet report.

image

Last few steps are setting the format and the destination which I have set as PDF and the report will be delivered to the default enterprise location for the user. Checking the status of the job I can see that it is waiting for the ETL complete event.

image

As soon as the ETL process places the file BOE automatically picks it up and kicks off the job.

image

And successfully completes the job.

image

Event Chaining

As discussed earlier I can also chain events. For example I have scheduled the Balance Sheet Report to run when the P&L Complete event is triggered.

image

The P&L report is waiting for the ETL to complete and the Balance Sheet report is now waiting for the P&L report to be complete. This is what I mean by event chaining.

image image

 

As soon as the ETL process places the trigger file it first kicks off the P&L report and if that is successful will trigger the balance sheet report.

image

If both are successful they will be delivered based on the settings for the job which in this example was the inbox for the user in infoview.

image

In a separate post I will talk more about how to configure other destinations such as email and FTP servers.

Posted in Administration, BusinessObjects, Custom Calendar, Distribution, Event | Tagged: , , , , , , , | 1 Comment »

How to configure IIS Tomcat connector (ISAPI connector) for BusinessObjects

Posted by Hemanta Banerjee on October 28, 2010


One of the frequent questions I run into on the BOBJ board is around setting up IIS and tomcat connector for BusinessObjects. I believe most of these posts are from customers using BusinessObjects Edge which does not support IIS as the web server. In order for this to work I used the Jakarta connector (AJP13) from Apache which allows IIS to forward specific requests to Tomcat. This is especially useful if you want to enable Windows Integrated Authentication for your IIS server and setup SSO with trusted authentication. This setup would enable the user to logon to InfoView without having to enter any user id or password.

Setting up ISAPI connector with IIS

The AJP13 Jakarta connector (version 1.2.14) can be found on Apache Software’s site at: http://archive.apache.org/dist/tomcat/tomcat-connectors/jk/binaries/win32/jk-1.2.14/. Download the isapi_redirect-1.2.14.exe.

image

This is a windows installer and after installation it would have setup a couple of things. By default the software is installed in “C:\Program Files\Apache Software Foundation\Jakarta Isapi Redirector”.

image

Installing and Configuring the Jakarta Connector on IIS

Step1: Configure the ISAPI connector configuration files. There are 2 main files to edit

workers.properties.minimal – This file provides configuration properties needed to connect to Tomcat. Find the “worker.ajp13w.host” and change the value from localhost to <machine_name>

image

uriworkermap.properties: This file contains all the mappings that will use by the ISAPI connector. There are some defaults. In the next step we will add the BusinessObjects related URL’s here.

image

I needed to add the following to this file as shown above

/businessobjects/*=wlb
/jsfadmin/*=wlb
/dswsbobje/*=wlb
/styles/*=wlb
/AnalysisHelp/*=wlb
/AnalyticalReporting/*=wlb
/AdminTools/*=wlb
/AnalyticalReporting/*=wlb
/bomm/*=wlb
/BusinessProcessBI/*=wlb
/CmcApp/*=wlb
/CmcAppActions/*=wlb
/CrystalReports/*=wlb
/DataServices/*=wlb
/doc/*=wlb
/dswsbobje/*=wlb
/ExtendedAnalytics/*=wlb
/fim/*=wlb
/Flexstore/*=wlb
/InfoViewApp/*=wlb
/InfoViewAppActions/*=wlb
/InfoViewPCM/*=wlb
/MetadataManagement/*=wlb
/OpenDocument/*=wlb
/PerformanceManagement/*=wlb
/PlatformServices/*=wlb
/PMC_Help/*=wlb
/polestar/*=wlb
/PolestarAppActions/*=wlb
/polestar_help/*=wlb
/polestar_tutorial/*=wlb
/STS/*=wlb
/VoyagerClient/*=wlb
/webservice/*=wlb
/Xcelsius/*=wlb

 

Step 2: Setup the virtual directory in IIS: The installer also defines the ISAPI redirector as a virtual directory in IIS as shown below. Check that execute permissions for this virtual directory is set to “Scripts and Executables”.

image

You also have to define the web service extension if using IIS. Right click on “Web Service Extensions” and define a new Web Service Extension called “Jakarta mod_jk” and required file as isapi_redirect.dll which can be found in the bin folder of Jakarta ISAPI redirector.

image

Now restart IIS using IISRESET.exe on the command line.

Installing and Configuring the Jakarta Connector on Tomcat

Now that you have the IIS ISAPI filter installed and configured on the IIS side, you have to configure Tomcat to accept connections from IIS. To do this, we will configure Tomcat’s AJP13 listener.

  1. On the Tomcat system (INSTALLDIR\Tomcat55) find the server.xml located in Tomcat’s \conf directory.
  2. Edit the server.xml. Search for the port=”8009” and uncomment and change the connector entry to look as follows

    <Connector enableLookups="false" port="8009" protocol="AJP/1.3" redirectPort="8443" tomcatAuthentication="false"/>

Save and restart Tomcat. If you do a netstat-an command on the command line you should see the tomcat connector listening on port 8009.

image

You can now test the connector by navigating to one of the tomcat samples such as the infoview URL on the webserver. For example: /InfoViewApp/">http://<iiswebserver>/InfoViewApp/.

Posted in Administration, IIS, ISAPI | Tagged: , , , , , , | 2 Comments »

How to configure NTLM for Crystal Reports Server/BusinessObjects Enterprise/Edge

Posted by Hemanta Banerjee on October 28, 2010


To simplify administration, BOE supports user and group accounts that are created using external directories such as LDAP, Active Directory and NT. In my previous post I had described the process for configuring LDAP authentication. Similar to setting up the authentication for LDAP the administrator needs to perform some basic setup to configure the server with the information needed to connect to the NTLM server.

Before I go to the setup needed in CMC, let me walk through some of the key concepts in a NTLM deployment. For my testing I am using the local windows users and groups. In order to make the administration simpler define a group in your NT server that will hold all the BOE users. I am calling it BOE_Users.

image

With that done now I can go to my CMC and enable NTLM authentication. The only difference between LDAP and NTLM is that in NT all I need to specify is the NT domain for user authentication.

image

For the meaning of the other settings please refer to the details on the LDAP post here. After selecting update the user and group will be added to the repository and now you can setup the access control for the user/group.

image

For details on how this works and how to enable the drop down allowing the user to select the authentication mode you can go to the LDAP posting. In a subsequent posting I will explain how to enable SSO and trusted authentication with NTLM which will allow the user to logon to Infoview without having to enter their user name and password.

Posted in Administration, Authentication, BusinessObjects, Installation, LDAP, NTLM | Tagged: , , , , , , | 3 Comments »

How to configure LDAP for Crystal Reports Server/BusinessObjects Enterprise/Edge

Posted by Hemanta Banerjee on October 21, 2010


To simplify administration, BOE supports user and group accounts that are created using external directories such as LDAP, Active Directory and NT. In my previous post I had described the process for configuring Windows NT authentication. Similar to setting up the authentication for NT the administrator needs to perform some basic setup to configure the server with the information needed to connect to the LDAP server.

Before I go to the setup needed in CMC, let me walk through some of the key concepts in a LDAP deployment. In my setup I have setup a freeware directory server Apache Directory. This is easy to setup and free so it works best for my testing. For the LDAP client I have used another freeware directory client from the Apache foundation called Apache Directory Studio.

image

You can easily create new users and groups using the directory studio and works great for the testing purposes. Ofcourse in a real production environment none of this would be necessary as you would be connecting to your corporate LDAP server. In order to make the administration simpler define a group in your LDAP server that will hold all the BOE users. I am calling it BOEUsers.

image

Now we can go to CMC and add this group in the LDAP configuration for BOE. You can add new authentication providers in BOE by clicking on the Authentication link in CMC.

image

After enabling LDAP, enter the connection details for the LDAP server. In my case I have installed on the localhost and my baseDN is dc=example,dc=com. You would also need to provide the logon credentials for the user which can be used by BOE to connect to the LDAP server for authentication. In my case I have used the admin ID however this is not essential. You can use any ID with read privileges on the baseDN.

image

You would also need to provide the LDAP group that will be mapped to BOE. I have used the BOEUsers group defined earlier. You will also need to define a couple of other parameters relating to how to map the users. For new deployments choose the options as shown below. If you want to map existing BOE users to LDAP userid’s then you can choose “Assign each added LDAP alias to an account with the same name” in the alias options. Also if your group mapped above contains only BOE users then the option selected in the Alias Update section will suffice. Otherwise if there are other users in the group who would not have access to BOE you can choose “Create new users when the user logs in” option in the Alias update options.

image

Now click on update. If you have selected the options above you will notice that the users from the LDAP group have now been imported as BOE users.

image

And the user has been mapped to the corresponding LDAP alias.

image

Now you can add this user to any BOE group for access control assignment and other security settings. The user can logon by selecting LDAP as the authentication mode in Infoview.

image

Enable Selection of Authentication Mode for Infoview and CMC

By default the authentication drop down is not displayed in Infoview. But you can enable it with a few settings. You need to change some settings on the web.xml file for your infoview application. The web.xml file is stored in the <INSTALLDIR>\Tomcat55\webapps\InfoViewApp\WEB-INF.web.xml and <INSTALLDIR>\Tomcat55\webapps\CmcApp\WEB-INF.web.xml.

To prompt users for the authentication type on the logon screen, locate the <authentication.visible> parameter and change its <param-value> from false to true. You would need to restart the Tomcat application server after this change.

<!– You can specify the default Authentication types here –>
<!– secEnterprise, secLDAP, secWinAD, secSAPR3 –>
<context-param>
<param-name>authentication.default</param-name>
<param-value>secEnterprise</param-value>
</context-param>
<!– Choose whether to let the user change the authentication type –>
<!– If it isn’t shown the default authentication type from above will be used –>
<context-param>
<param-name>authentication.visible</param-name>
<param-value>true</param-value>
</context-param>

Remember to stop TOMCAT and clear the tomcat cache at <INSTALLDIR>\Tomcat55\work\Catalina\localhost. Restart the application server and you should see the drop down for authentication providers in your Infoview logon page.

Now the user can logon to the system with their NT password.

image

How does LDAP Integration work

The diagram below is a summary of how LDAP authentication works between BusinessObjects and the LDAP server. When BOE is integrated with LDAP users and passwords are stored in LDAP and no longer defined in the Business Objects repository. The BusinessObjects clients authenticate against LDAP at runtime. The LDAP users inherit security from repository groups which are mapped to the LDAP group using repository group mapping.

iLDAP attribute (could be “role” attribute) to repository group mapping

image

The BusinessObjects LDAP users belong to group(s) that exist in the Business Objects repository. Access rights are attached to these repository profiles and to their parent groups. The authorization is made in two phases (1) At login, the system retrieves the list of security profiles associated to the user, by querying the LDAP corporate directory (2) Then the system computes the user access rights by combining the access rights associated to user security profiles in the repository.

image

Posted in Administration, Authentication, BusinessObjects, Installation, LDAP | Tagged: , , , , , | 6 Comments »

 
%d bloggers like this: