Random Thoughts

Views on life

About time

Posted by Ananth Sridharan on June 15, 2016


Evolution has perhaps the largest role in shaping your personality. If there is something you cannot understand about a person, why someone would behave in such a manner, blame it on the genes. 4 out of 5, there will be few that will disagree.

It was not until a few years ago, much after I became a father, that I realized that I have inherited quite a bit. Prior to that, I always thought I was completely different from my parents. Now I know that I quite different,  not completely. That eventually, I will realize that I am no different, I will leave to write about for another day!

Experiences, circumstances and influences play a role in shaping our approach to live. When I look back, I can think of a 3, maybe 4 experiences that have made me what I am today. Whether its 3 or 4, we will hopefully find out  by the time I am done writing this post.

The first is my asthma. I developed asthma when I was about 11 years old. For a child that would rather stay outdoors all the time than indoors, this was quite the dramatic introduction to suffering. In those days, an attack had to be counter attacked using oral medicines, and when oral medicines failed, a steroid injection was the next resort. Then came the magic in the form of inhalers, and subsequently, thanks to some irresponsible doctors and loose regulations, I started to self medicate with oral steroids. Felt good. One night stays etched in my memory, a night at IIT Bombay, when I was in my room, living through an attack, and I could not tell whether I was awake or asleep. It was as though I was aware that I was sleeping, was struggling and while the best thing would have been to wake up, I just couldn’t get myself to snap out of my “sleep”. That night was not easy.

The second was the 4 years I spent with my grandparents before I went to college. In the days where giving and taking favors was rampant, and quite easily actually, my grandfather stood out. He once told me how he would get off one bus stop sooner so he would need to pay lesser. It was not how much he saved that mattered, it was how must more he had to pay for that short distance that he was not willing to accept (bus routes had stages comprising of say 8-10 stops, and the fare would change abruptly when the stage changed). This was perhaps my first introduction to “seek to know why you do what you do”. Is it “I don’t enjoy playing cricket” or “I don’t like playing cricket because I am not good at it”?

Life at IIT, and for a short while immediately after is perhaps the biggest influence in my life. It is then that I questioned everything, like most people would, except what came out at the other end was a very different me. I like the phrase “free thinker”, and I think I will stick to that. I think without the constraints imposed by external factors, especially by religion. My outlook to life had entirely changed, and if I had not come from a religious family, I would have ended up a monk.

There is a fourth, my 9 year old son. A present and ongoing influence that has helped me rediscover myself. So much so that it would not be out of line to say that I am like my son!

Posted in Uncategorized | Leave a Comment »

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

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

Drill down in XCelsius/Dashboard Design

Posted by Hemanta Banerjee on February 9, 2011


One of the most frequent questions I get is around configuring drill down in XCelsius (Dashboard Design). XCelsius is primarily a dashboard development tool and it is not meant for analysis. Thus the typical usage of XCelsius does not require the standard type of adhoc drill down analysis that one would normally do within say web intelligence. However there are some instances where you would want a dashboard where the user can select some high level KPI and then have the ability to look into the details of that KPI. This can done using the drill down functionality in XCelsius. Keep in mind although it is called drill down the behaviour more consistent with master-detail type of report rather than a traditional drill down. The video below shows this in more detail.

Click here to download the video

Drilldown functionality in XCelsius (a.k.a Dashboard Design)

 

So how can you build something like this. It is actually quite simple as you can see in the video below.

Click here to download the video

How to enable drill down in a pie chart

 

Combining this with the live office functionality discussed in the last post should allow you to build really powerful and interactive dashboards. You can download a more complete example from here.

Posted in BusinessObjects, XCelsius | Tagged: , , , , | 6 Comments »

Building Live Dashboards using Live Office

Posted by Hemanta Banerjee on February 3, 2011


In my previous post Creating Live Dashboards using QWAAS I had shown how you can use QWAAS (query as a webservice) to build live dashboards. While QWAAS is very powerful it has 2 drawbacks

  1. It is only available as part of BusinessObjects Edge/Enterprise (BOE). Crystal reports server (CRS) users do not have access to it.
  2. Can be confusing for the end user

Live Office is a great utility that comes as part of CRS and BOE and allows embedding reports or report parts within Microsoft Office documents. It is compatible with excel, word and PPT and allows creating live refreshable documents directly within MS Office.

Click here to download video

Using Live office to create refreshable office documents

 

As shown in the video above the process of creating a live document is quite straightforward. Now we know that XCelsius works with excel documents and using live office we can create a live excel documents. So putting these together we can now create a live dashboard which uses an existing crystal reports to fetch data from the database.

Click here to download video

Using Live Office and Excel to create connected dashboards

 

Using this technique it becomes quite easy for end users to pickup existing reports that already contain the data needed by the dashboard and quickly create live dashboards.

To download HD videos of this post please vi

Posted in BusinessObjects, Dashboard Design, XCelsius | Tagged: , , | 6 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 »

Creating live dashboards using QAAWS

Posted by Hemanta Banerjee on January 7, 2011


Happy new year to everyone. As the first post for the year I wanted to put something that was simple and common. In the last couple of months I have run into several scenarios of customers asking me the best mechanism to create live dashboards in XCelsius. As you know the only way to create live dashboards in XCelsius is by using Web Services. While you can create web services on your own the hard way, with the BOE platform there is a very nifty utility called Query As A Web Service (QAAWS) which allows you to create web services very easily from the universe.

QAAWS is a web service generator. It uses the query builder to essentially to build a query and publish the query to the platform and makes it available as a web service. Here is how you can use it.

Create the Query and Publish to the platform

1. Launch QAAWS and create a new query. Give it a name that is user friendly.

image

2. You can set additional parameters such as authentication mode and timeout at this stage. Usually we would keep them as default.

image

3. As you can see in the steps the next step is to select a universe. Based on your security setting you will be presented with a list of universes that you can use for building the query. The process of selecting the universe and building the query is the same as you would do when using WEBI.

image

In my example I have purposefully chosen a query which has a prompted filter. I can drive this prompt using a drop down box or other selectors from the dashboard.

image

As you can see my query has 1 input parameter and 4 output parameters. Now all I need to do it click on the publish button to publish the webservice to the platform.

image

The URL shown above is the URL for the webservice and I can use this in XCelsius to build my dashboard.

Using the Query in XCelsius

In the data connections dialog of XCelsius you can add a new live office connection. Here you need to enter the URL that we got from QAAWS.

image

We also need to bind the cells to both the input and output parameters. For the input parameter I have bound it to cell C3 of the spreadsheet as shown below.

image

The same way you need to bound the output to your spreadsheet. The only thing to keep in mind is that if your query will return multiple rows of data you would need to select a range of cells bigger than the maximum possible range as shown below.

image

For debugging purposes you can also get the number of rows returned by the query and bind it to a cell as shown below.

image

And after all of this voila you have a live dashboard…

image

Posted in Dashboard, QAAWS, WEBI, XCelsius | Tagged: , , | 6 Comments »

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.

image

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.

image

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.

image

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.

image

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

image

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

image

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.

image

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

image

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

image

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.

image

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.

image

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.

image

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.

image

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

image

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.

image

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 »

BOE Universe: Generating list of values based on data

Posted by Hemanta Banerjee on November 19, 2010


Let us take a simple scenario. Let us say we have a countries dimension which has all the countries. If we want to use this dimension table to get a list of countries where we have customers, as well as use it to get a list of countries where we have offices we can create 2 aliases COUNTRY_OFFICE and COUNTRY_CUSTOMER and create objects from the aliases. No issues till now, except if we try to get a list of countries where we have offices. Since we have used the master country table it will list all the countries irrespective of whether we have office there or not.

Let us see how it works below.

image

In the example above I have a country table and I use it for both client and showroom country. Now if I query for showroom country i.e. countries where I have showrooms here is the query produced.

image

As you can see it lists all the countries in the countries table which is not what we wanted. In order to get the correct list of countries I have to join with the SHOWROOM table so that the countries list is restricted based on the SHOWROOM dimension table. This is done by specifying that whenever the Showroom Country object is used in a query, the Showroom table must also be inferred in the FROM clause of the SELECT statement. Providing that the Showroom_Country table is joined to the Showroom table the object is then guaranteed to only return countries in which showrooms exist.

image

Making this change ensures that we always get the correct set of countries when we query for showroom countries.

image

While this does not seem that critical it becomes very important especially when we want the user to select the showroom country in a prompt for example. We only want those countries to be in the prompt list where we have showrooms and making this change will ensure that we always get the correct list.

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

image

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.

image

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.

image

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.

image

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

image

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 »

 
%d bloggers like this: