Dave's Adventures in Business Intelligence

I mentioned a few weeks ago that I had found a way to “downgrade” a multi-sourced universe to a single-source. I wanted to create a beautiful blog post with screen shots to walk everyone through the process, but have not managed to carve out the time. 😳 Rather than continue the radio silence, I’m going to write up the steps and hopefully come back and make this post prettier later on. I also have a request to make which will be at the end of this blog post, so please read to the end, and if you can help, please do so.

Background

We, like I am guessing many Business Objects legacy customers, have been slow to dip our toes into the “multi-source” waters offered by Information Design Tool (IDT). When we did our major upgrade to BI 4 a few years back, we evaluated whether we needed to convert all of our UNV universes to UNX, and decided not to, for a variety of reasons. But for new development we are typically looking at IDT as our universe creation tool.

As any IDT developer should know, the first decision you need to make when building a data foundation (DFX) is whether it’s going to reference more than one connection. That question has been advertised as irrevocable; once you make that decision, there is no option to change it. If you start with a single connection, then your DFX attaches the appropriate database library so that you can access all of the database-specific functions. If you instead tell IDT that you’re going to use multiple connections, then the DFX attaches the ANSI library supported by the federation engine. From there you move forward with importing tables, creating joins, and so on. If at some point you decide that your single-source Oracle universe needs another connection, or if you want to take a legacy UNV universe, convert it to UNX, and then upgrade to multiple connections, well, it just doesn’t work.

Our Specific Issue

We had at least two different projects that — when presented with the choice of multiple connections or not — chose to create a multi-source DFX even though they only currently had a requirement to connect to a single Oracle database. The idea was based on a “just in case” mentality, and it’s hard to fault them. If at some point they ever wanted to use multiple sources, it would be nice if they were already on a platform to support that. After making the multi-source connection they built their universe (and their reports or dashboards and so on) and went on their way.

Then our support team started hearing from one of these projects… their dashboard which worked fine in the development and the quality testing environments was very unreliable in production. It would frequently hang at the “Initialization…” phase. This was a production operational dashboard, so having it offline was causing a lot of stress.

We tried everything. We re-migrated the universe and dashboards (there were two, both exhibiting the same symptoms) from the testing environment. We checked our server settings. We created trace logs and sent them to SAP for examination. Nothing jumped out as a problem, and nothing we did fixed the issue.

The Brainstorm

By the time I got involved, things were … exciting. 🙂 There was a lot of pressure to find a solution, and get these dashboards fixed. I opened the universe to review it, and nothing seemed out of the ordinary. The only thing that jumped out at me was that it was defined as a multi-sourced universe with only a single source. There are a couple of ways to determine this. First, the SQL code will include a “catalog” reference, something like this:

@catalog('PROD')."OWNER"."TABLE"."COLUMN_NAME"

Secondly, each connection (even if there is only one) will have a color code, which will be visually displayed as a colored box next to the connection name. Here’s what that looks like:

Multi-source connection

Single-source connection

As I mentioned, we were getting desperate. We were at the point where we just needed to change something and see if it helped. One of the thoughts I had was to re-create the universe as a single-source universe. By doing that we would connect directly to the database rather than going through the data federation process on the server. It was a good idea, in theory, but the obstacle standing in the way was that I was told there is no way to “re-point” a dashboard query to a new universe. 🙁 In Web Intelligence I can easily take a data provider and switch which universe it is using. Creating a new universe would not be an issue. But we didn’t want to have to recreate the dashboard queries.

My challenge became figuring out a way to rebuild the universe in such a way that we did not have to re-point any of the dashboard queries. Could I do it?

IDT Universe Structure

A universe created in IDT has multiple components. The first component is the data foundation (DFX). This layer contains everything needed to connect to the database (or databases) and the framework for building SQL (joins, contexts, and so on). The next step is to create a business layer (BLX) on top of the DFX. This is where we build out the classes and objects… basically the user interface for our universe. I knew that I could switch a BLX from one data foundation to another, but could I switch a BLX from a multi-sourced DFX to a single source? Or vice-versa?

I decided to try.

I was pleasantly surprised.

Here’s what I did.

The Steps

I opened the problem universe. It was fairly simple, with about a dozen tables, and no contexts required. I used IDT to create a new DFX, this time a single-source using the Oracle connection found in the original universe. I quickly imported the required tables, redid the joins, and saved my new DFX. Then I opened the BLX associated with the problem universe and re-pointed it to the new DFX I had just created.

What I hoped would happen was that the object definitions and classes would remain in place, even if the SQL code was incorrect. (The @catalog syntax I showed earlier, remember?) I could then go through the objects one by one and remove the catalog reference, then I would do a full integrity check to find out which objects were using ANSI syntax that was not supported by Oracle.

What actually happened was even better. The catalog references all disappeared, like magic. 😀 I was able to jump immediately to the integrity check, and only had to fix a few objects before I could export the universe. The entire process took less than an hour.

Here’s a key part for this entire process: when a universe is exported from IDT, you don’t right-click the DFX, you right-click the BLX. The selected BLX is “compiled” with the associated DFX and a UNX is created. When I went through this process, because I used the existing BLX as the basis of my universe, when I exported it remained the same universe rather than creating a new one! That was critical to this effort, as the entire point was to recreate the universe without having to rebuild the dashboards.

The Aftermath

That all happened about two months ago. Since then, this particular project has not had any downtime with their production dashboards. From this I learned that it is, in fact, possible to “downgrade” a universe from multi-source to single-source by swapping the BLX to an appropriately built DFX.

We had an opportunity to do this a second time, with a significantly more complex universe. The process did not go as well. 🙁 For some reason after re-pointing the BLX to the rebuilt single-source DFX there were still some references to the original multi-source DFX. We could not export the universe until those references were resolved, and the only way we found to remove the reference was to delete some of the classes. Deleting classes was a problem, as now we have to recreate them, and that means they’re no longer the “same objects” but “new objects” with different object ID values.

However, in this case, the target documents were all Web Intelligence reports, so we simply created a new BLX, meaning we created an entirely new single-sourced version of the universe. Once that was done we re-pointed the Web Intelligence reports to the new universe. As before, the reliability of these reports improved significantly. I am going to research the unresolved references at some point. When I have time. 😉

Now the big question: Can I promote a single-sourced universe to a multi-sourced version? The general consensus (and I think the official position from SAP) is no, you can’t. You have to build a new universe and then re-point all of your reports.

But I can now say that the answer is yes, I can. 🙂 I have gone through one test case, and it was successful. It’s a lot more work because the @catalog syntax does not appear magically, unfortunately. I took a very small universe and essentially did the process outlined in this blog post in reverse… creating a new multi-source DFX and then re-pointing and existing BLX.

In what I believe to be the generally expected process to do this I would have to create a new universe and re-point my reports.

In this process outlined here, I can create a new DFX and re-point the BLX. It’s a similar amount of work on the universe side (as I still have to touch every objects) but I don’t have to re-point my reports or dashboards, and that’s worth the effort. It makes sense, in a way. I can re-point a data provider and not have to update the report… now I can also re-point a BLX and not have to touch the data providers either.

A Request

As mentioned at the beginning of this post, I have a request. I would love to hear from folks that are using multi-sourced universes with only a single connection, and see if your experience matches ours. Drop me a line in the comments, and I’ll contact you privately. Thanks!