Random Thoughts

Views on life

Archive for March, 2010

Deleting a user in the OBIEE catalog

Posted by Ananth Sridharan on March 29, 2010

I had recently run into this issue with deleting users in OBIEE and my initial reaction was that the solution to this should be trivial. Boy, I was so wrong. I have seen a few posts that help delete users from the catalog, but that would require you to stop the heart before you perform the operation.

It’s frustrating, especially when you have deleted a user in the catalog that you didn’t want to in the first place! If you haven’t got there, or tried that yet, please don’t. But for those who have, this post helps to fix the issue without bringing down any of the servers. Here’s how:

The first few screenshots aims to illustrate the issue, and progressively take you to the solution.

Problem description

Before you delete a user, all is well, and the catalog manager would show the user folder in the correct state:

Delete the user from the web and…

..you will notice that there is a <Deleted Account> tag against the user folder in the catalog. This will not allow you to delete the 1338802 folder – that’s the problem!

And worse, if this user logs back in, the user will have access to all the shared stuff, but no access to his/her personal catalog folder!!!

Most of us will then try to delete the folder from the catalog – this would initially seem like a no brainer till you hit upon the issue wherein you are unable to delete the folder.


Change owner from “System Account” to Administrator for the user folder you wish to delete:

Change permissions on the folder: This one is a bit tricky – Select <Deleted Account> and move to right, select Administrator from the right and move to the left. Select “Apply Recursively” and click on ok (next 3 screenshots)

Now you are nearly ready to delete the folder. As a last step, rename the folder, and then delete it:

Problem solved!

This was about a not so nice issue with OBIEE. What will follow next is a really cool capability of OBIEE: Data federation, or is it fragmentation???

Posted in Administration, OBIEE | Tagged: , | Leave a Comment »

How to display KPI’s using an Accordion menu in XCelsius

Posted by Hemanta Banerjee on March 26, 2010

One of the questions that came up in the forums the other day was how to display multiple KPI’s for a set of years using the accordion menu. It was further complicated by the fact that some of the KPI’s were absolute figures such as sales and the other KPI’s were percentages such as growth rate.

Seemed like a good problem to solve and I had some free time today, so thought if tackling this. The dashboard looks like follows

What is cool about this is Y axis reflects the nature of the KPI. For sales and Inventory it will show in $’s and for others the axis shows % values. There is a little trick that I used for and I will share it in a later part of this document.

Let us start with the basics. In order to build an accordion style dashboard we need the data in a particular format. The screenshot of the excel file I started out with is below.

For each year I am capturing the trend for the KPI’s. I could have arranged the excel the other way around as well and it would have still worked. Once I have the data I just need to drag and drop the accordion menu and the graph from the pallete and I can be off and running.

For the accordion menu the settings are simple enough. See below

I have set the insertion type as column, which essentially means that when the user selects one of the KPI’s from the menu, the entire column for that year is copied and inserted into the destination (cells C88:C100).

And I also bind each of the years with the category as the source data.

Essentially when the user picks the category, XCelcius knows which data block to go to. Then when the user clicks on the specific KPI, the data for all the months for the KPI is copied to the target.

The chart reads from the target and displays the results. If all the KPI’s had the same scale then that’s all I would need to do.

In my case there is an additional level of complexity. I have KPI’s with very different scales and the chart does not display those correctly (not sure if that’s the way it is supposed to behave). To solve that problem I did a little trick… Instead of

Instead of 1 chart, I have 2 charts… and I set the dynamic visibility of the charts to be driven based on the KPI selected.
I have 2 sections in excel, one to display % values and another to display absolute values and I drive the charts from different sections. Since the accordion can only fill one section, I have used formulas to populate the data in the 2nd region.

Now I define a flag using an excel formula

=IF((C88 = “Sales”), 1,IF((C88 = “Inventory”),1,0)) that controls which chart will be displayed.

Using this approach I am able to hide one chart, and show the correct chart based on the selection. Not sure if there is a better way, but this works J

You can get the XLF file at http://www.box.net/shared/gg9f6lnh90

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

How to handle missing data in time series analysis

Posted by Hemanta Banerjee on March 26, 2010

Saw a post today on the clearpeaks blog on ways to display time series charts with missing data. This is something that all of us run into and I wanted to try out some of the suggestions posted on the blog and actually implement those using XCelsius to see whether they can be done in real world.

So here is the situation. As you can see below I have sales for the various months of the year with data for Mar-June missing. If I do nothing and try to draw a chart it is actually misleading since it does not tell


the viewer that some of the months is missing. It is better if we include all the months and show missing data by a broken chart as shown below.


This is better since at least the viewer of the chart knows that the data is missing for some of the months.

This is of course not ideal since we would like to use the data we have to extrapolate and have a better visualization that shows the trend of sales for the full year including the missing months.

So how is done?

We added an excel formula to calculate a rolling average function… we could have used other functionality and even get the backend database to calculate the missing months.



With this new layout we could draw out the chart, and we do not have missing data… however I feel that we are again misleading the user since there is no way for the user to know that Apr to Jun is made up data.

Ideal would be if we could draw that section of the chart in a different color.

This is something that I thought would be easy but turned out to be a little bit complicated.

In the end I ended up doing a hack, which I am not sure if it is a scalable… In the table on the right, the yellow section is the data section. That has all the data from the database as well as the rolling months. Using that I have created 2 series, 1 which contains the real data (red) and another which contains the calculated months (green). For the calculated months series I have also included the boundary months to make sure that I have a continuous line.


Then I setup 2 series in XCelsius, the 1st pointing to the red portions, and the 2nd pointing to the green section of the spreadsheet.



And I have the final chart I need.


If someone has a better way of doing this using XCelsius would love to hear.

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

Building a static dashboard using XCelsius

Posted by Hemanta Banerjee on March 23, 2010

BusinessObjects™ Xcelsius Enterprise 2008 enables you to transform Excel workbooks into interactive dashboards with the ability to provide interactive what-if analysis. An Xcelsius dashboard provides insight into complex data and delivers confidence to those who will use it to make decisions.

Data is often so complex that workbooks are needed to organize it in a meaningful way. But even with excel workbooks usually the amount of data is so huge and raw that it becomes very difficult to draw conclusions quickly. For example which is easier for most audiences to understand?

A static Excel worksheet?

Or a dashboard created using XCelsius?

The process of creating a dashboard using XCelcius can be distilled into the following steps

Let us start with a simple example where we will be creating a dashboard using some data already present in excel.

Step1: Start XCelsius.

The screenshot above shows the various components used when designing a new dashboard using XCelsius.

Step2: Import an excel document.

In this step we will be importing an existing excel document to be used as the source of data for the dashboard.

Click on Data->Import to import an excel file. As you can see we can also import from other sources including web services and other live data sources, something that we will be covering at a later point.

Select the excel file for use within your dashboard and click OK.

The excel document is now imported within XCelsius and now can be used to draw the dashboard.

Step 3: Add the chart. We will be drawing a line chart for each of the regions that shows the sales for the year 2006. In order to do so drag and drop the line chart control to the canvas.

We now need to set the properties for the chart to read the data from the specific range in the imported spreadsheet.

You can also go to the other tabs in the properties pallete to further set additional behavior such as drill down, appearance etc. We will be looking into this in more detail in subsequent posts.

Click on the preview button to see how the chart looks on the dashboard.

Step 4: Export the dashboard

Goto file export to export the dashboard in either SWF form to be embedded in websites, or in PDF/PPT/Word form. The dashboard can also be exported to BOE for sharing with other users within your organization.

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

%d bloggers like this: