Random Thoughts

Views on life

Posts Tagged ‘BOE’

Migrating from development to production in BOE (Lifecycle manager)

Posted by Hemanta Banerjee on December 8, 2010

One of the cool new features in BOE XI 3.1 is the new lifecycle manager (LCM) module. Migrating reports and other objects from development to production has been a challenge in all of my previous implementations and I am happy that BusinessObjects provides some out of the box functionality to make this process really simple. When using LCM for migration you create jobs. Once a job is created it can be used several times to promote content from one deployment to another.

Please note that LCM is a separate installation and requires the following Services:

  1. Central Management Server: LCM job itself is an object which is saved in CMC.
  2. Adaptive Processing Server: The LCM job server is added to the Adaptive processing server
  3. Web Application Server: LCM is a web application
  4. Input FRS : This is a server you need available after installing, as jobs that you create in LCM are saved in the Input FRS and CMS repository.
Administrative Settings

Access to the LCM application is set by managing the security for the application in CMC as shown below.


Also before creating new jobs you need to add the source and target BOE systems in the LCM application using the administration options shown below.


Creating the LCM Job

To migrate objects, you have to first have to specify which content you want to promote. This is done in LCM tool with 3 main steps:

1. Create a Job: A job is collection of objects that can be moved from one BOE environment to another.

When you create a new job you must logon to the source system and an Input FRS should be running and enabled as the Job is saved as an Object in the CMS database and as a file in the Input FRS.


In the example above I am migrating objects from my BOE environment as the source to a BIAR file.

2. Add Objects: Add the required objects from the CMS repository that should be migrated. In this example I am migrating all the reports related to the sales and finance department.


3. Add the dependent objects : Objects such as universes, connections, images and other dependencies on which the primary objects depend on also have to be added to the job. LCM will automatically compute the dependents when you click on “Manage Dependencies”.


And now all the objects and their dependents are selected in the job as shown below.


Now my job is ready for the next step which is promotion. You can promote content when deployments are connected and also when they are isolated. When deployments are connected you can directly migrate to the destination. When they are isolated you use a LCMBIAR file to transport the content.

Promoting to target

But before promoting you need to set a bunch of options.


1. Map Connections: You need to map all the universe, QWAAS URL and Crystal report database connection mappings to the target.


2. Schedule: To set how often the job should run.


3. Security: You can specify to promote the security of the objects in the job as a best practice only promote security when changes have been made, typically with a significant revision of the application.

4. Test: As the last step you can test what would happen when promoting the job, without committing the objects to destination

To promote a job you can schedule it or you can run it manually. The figure below shows the 2 scenarios of connected and isolated environments.


Posted in Administration, BusinessObjects, LCM | Tagged: , , , , | 3 Comments »

Drill down and Drill across in WEBI

Posted by Hemanta Banerjee on November 27, 2010

While BusinessObjects provides a fairly simple way to drill down using hierarchies there are times where this is not good enough. For example if we take the Motors example, lets say when the user drills down from the showroom, they want to see a report that displays the demographic information and models for that particular showroom. This is usually hard to do just by using hierarchies and this is where hyperlink drill down is very useful.

But before we go to Hyperlink drill down (or as I call it drill across), let us quickly cover hierarchical drill down. The screenshot below shows 2 ways to define hierarchies in the universe.


The natural hierarchies which is automatically defined by the universe is based on the order or objects within a class. For example in my universe I have Year->Qtr->Month->Date as my time hierarchy. Now if I enable drill in my report I can drill down to specific dates as shown below.


I can also define a custom hierarchy as shown in the figure 1 where I have combined the showroom and the model into a single hierarchy such that when the user drill from the showroom they can see the models in the showroom.


Now coming to drill across. For example when I click on the revenue below I want to jump to another report that shows the sales by model in that country for the selected year as shown below.


Setting that up is fairly simple. I have to create 2 reports as shown below.


The 2nd report accepts the showroom country and year as prompts. Now I have to go to the summary report i.e. Showroom by year and setup the drill across. You can setup a hyperlink drill down by right click on the cell and selecting “New Hyperlink” as shown below.


When setting up the hyperlink you would need to associate the objects from the summary report to be sent as parameters to the prompts in the detailed report as shown above.

Posted in BusinessObjects, WEBI | Tagged: , , , , | 1 Comment »

Outer Joins in Universe (BusinessObjects)

Posted by Hemanta Banerjee on November 15, 2010

Lets take a simple scenario. I want to generate a report that shows the sales by showroom, and the report should display all the showrooms in the report. For the showroom with no sales it should display the region with a NULL value for sales.


The usual join (also called as inner join) will not work in this scenario. We need what is called as an outer join. If you want to know more about outer joins you can checkout the Wikipedia link here.

To enable outer join you need to first set the ANSI92 parameter to Yes. This will change the query from the simple join to an an inner join syntax with from clause as shown below.


You can also enable the FILTER_IN_FROM parameter. This pushes the where clause of the join inside the from which reduces the number of records in the join condition and will greatly improve performance.


Now we can setup our outer join. As shown below we can setup the right outer join betwen the fact table and the showroom dimension table.


The effect of this is that all showrooms irrespective of whether they had a sale or not will be returned by the query.


So in conclusion while it is easy to setup outer joins in the universe, one should be very careful when using outer joins as it can result in a cartesian product of all rows especially when using full outer join.

Posted in BusinessObjects, Universe | Tagged: , , , | 1 Comment »

Managing performance by using Aggregate tables in the universe

Posted by Hemanta Banerjee on November 9, 2010

In most large data warehouses one of the common strategies employed by DBA’s to speed up performance is to use aggregate tables. Generally aggregate tables contain information that has a coarser granularity than the detail data. For example in a retail datamart I might have information at the transaction level. However most of the analysis will be performed at the daily level by brand. Without aggregate tables the database will fetch the lowest level of data and will perform a group by at the day level for specific brands which can be a very expensive operation. Instead as part of the ETL process I can pre-aggregate the data at the daily level which would reduce the number of rows by a huge factor. The Sales_Receipts fact table would contain this detail data, but the records in that table might also be aggregated over various time periods to produce a set of aggregate tables (Sales_Daily, Sales_Monthly, and so on).

There are multiple ways of managing aggregates. One option is to create aggregate tables in the database as materialized views and let the query optimizer of the database handle the performance using seamless query rewrite. I will describe this in a separate post. In this post I will focus on using the aggregate awareness functionality of the universe.

My sample database tracks the sales of cars. My detailed fact table VW_SALE_MODEL is used to track the sales at the lowest level of detail i.e. client, showroom, model and color.


Using these I can create a family of aggregate fact tables. For example I have created a aggregate table called VW_SALE that aggregates the data at the client and showroom level. Similarly I can create additional aggregates at the year level.


Once I have create the aggregates I need to map them into the universe which is a 4 step process.

1. Add the aggregate tables and setup the joins with the dimension tables. I have not created standalone aggregate tables since I want to make sure that I can leverage the hierarchies defined in the dimension tables. This process is similar to adding any fact table in the universe.


2. Define the aggregated measures using the @aggregate_aware function. The @aggregate_aware function is used to setup aggregate awareness in the universe.

The syntax of the @Aggregate_Aware function is @Aggregate_Aware(sum(agg_table_1), sum(agg_table 2) …., sum(agg_table_n)) in the order of preference. For example agg_table1 should be the highest level aggregate, followed by agg_table 2 and so on. This is used by the universe to pick the best aggregate table to answer the query.


In my example I have stated that either try to get the sales total from the aggregate table or get it by calculating it using the detailed table.

3. Define the incompatibilities. For example in my structure the model and maker classes are not captured by the aggregate. Also the aggregate table only contains information about sales and not about the quantity sold. We need to define these incompatibilities so that when the user generates a query, the universe can quickly scan through the compatibility list to determine the best aggregate that can be used to answer the query.


When I define it as shown above all queries that include Model or Maker will go to the detailed table. All other queries will be satisfied by the aggregate table.

4. Resolve any loops. Since I have joined the dimension tables to both the fact I have created some loops in the universe which I need to resolve. I can do that by creating separate contexts for the aggregate fact and the detailed facts.

Now I am ready to using my aggregates. To illustrate let us go to WEBI and see the impact of our design. When I query for sales by showroom the entire query is answered by the aggregate table.


As soon as I add the maker to the query BO now retrieves the data from the detailed table instead.


The same approach can be used to capture additional aggregates such as Year level or Qtr level and BO will dynamically go from using the summary table to using the detailed table as the user is performing the drill down.

So in summary, aggregate tables are very powerful and necessary in most real implementations and BO provides a fairly simple way to model it within the universe.

Posted in Aggregate Awareness, BusinessObjects, Universe | Tagged: , , , | Leave a Comment »

How to perform YTD (or any Period to date) design in the Universe

Posted by Hemanta Banerjee on November 5, 2010

Yet another post inspired by the BOBJ board. The idea is how to design a universe such that users could enter any date and get both the measure value for that period as well as YTD. Since we want to make it easy for adhoc users we need to do some design work in the universe to make it easy for the users performing adhoc analysis.

So I figured the easiest approach would be to define a separate set of measures for YTD similar to what I did for the YAGO computation in a previous post. So extending on the same example I followed a very similar approach and it turns out to be quite simple. All we need to do is make sure we are able to run multiple queries, once for getting the sales and another one that sums up the sales from the beginning of year to the selected date. So I know we have to define a separate context for the YTD sales, forcing the BI Server to automatically run 2 queries and join the results. That’s what I want to leverage.

1. To make my life easier in the universe I defined a separate reference table DATES_PERIOD that maps the date to its corresponding YTD start and end dates. This not only makes it simple, it also makes it possible for me use the same design for handling non standard calendars such as Fiscal calendar. Also if I want to do QTD or MTD instead of YTD I can use the same approach by just changing the start and end dates.


In this table I have gone ahead and filled up the start and end dates for YTD for every date in the DATES_TABLE my calendar table.

2.  In my universe I first go ahead and define an alias for the fact table called YTD_SALES. Now instead of joining it to my DATES_PERIOD (date dimension) table I have joined it to my DATES_PERIOD table using a complex join as shown below.


This ensures that I will always select all the sales from the time slice (start and end of YTD) rather than selecting a specific date. My universe is as shown below.


In my universe my time dimension objects such as Date or Qtr are driven by the DATES_TABLE. So in order to tie everything up I have joined the DATES_PERIOD to the DATES_TABLE on the date. This ensures that when the user selects the date, the corresponding period will be selected from the DATES_PERIOD and the BI Server will return the sales that fall in that period. This is the key part of the design.

Now I can setup up the rest of the joins with the rest of the dimension tables.

3. Now I have to define a new context for YTD as shown below. This is needed to make sure that when the user selects from Sales and YTD sales they are sent as separate queries.


After setting up the contexts I can define the YTD Sales revenue by pulling in the appropriate field from the YTD_SALES alias table.


Now checking to make sure that the logic is OK. I define 2 queries, YTD Sales till Dec 31-2004 and the sales for 2004. If the logic is correct both should come out same and it does.


Also I can pull them in the same query if I want. I know that there is a sale on 15-Mar-2004. Filtering on that date gives me both the sales value for that date as well as YTD sales.


The reason I love this design is because very versatile and it can be used for any period to date. The only thing to note is that it will work only if the user selects a date. If the user selects a Qtr or Month then the YTD value will be garbage. If you want to prevent this then you can force the user to select a date using a prompt.

Posted in BusinessObjects, Period to Date Functions, Time Sliced, Universe, WEBI, YTD | Tagged: , , , , , , , | 1 Comment »

Auditing III: How to enable auditing for Crystal Reports and WEBI viewing

Posted by Hemanta Banerjee on November 4, 2010

A couple of weeks back I had written an article on enabling auditing in BOE. The same functionality is available in Crystal Reports server as well. However I had missed out what activities can be audited. So writing this post to complete my previous posting. Below are the most of the common scenarios that the administrators want to audit.

Crystal Reports Cache Server: Viewing of Crystal reports is audited by Crystal Cache Server.


Crystal Reports Jobs: Can be turned on by enabling auditing for Crystal reports job server.


Destination Job Server: Will audit all jobs that output to emails, ftp, and file system.


Event Server: Audits all events that are registered on the BOE or Crystal Reports server.


Publication Job Server: Will audit all publication jobs.


WEB Intelligence: Audits access to all WEBI reports.


For steps on how to turn on auditing and how to look at audit data you can go to my previous posts on the same topic.

Auditing I: How to enable audits logging in BOE XI 3.1 

Auditing II: How to import the auditing reports in BOE

Posted in Administration, Audit, BusinessObjects | Tagged: , , , , , | 1 Comment »

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.


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.


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.


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.


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.


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.


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.


And the results are as shown.


Posted in BusinessObjects, WEBI | Tagged: , , , , , | Leave a 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.


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”.


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>


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.


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



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”.


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.


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.


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.


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.


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.


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 »

%d bloggers like this: