Dave's Adventures in Business Intelligence

The “Extend merged dimension values” is designed to provide more flexibility in how Web Intelligence handles merged data. In Desktop Intelligence we have for years complained that there is no way to control the merge (join) process. No matter what I did I got a full outer join. In the XI versions of Web Intelligence, I now have three different ways to reference my merged values. In this post I will show those three options and then show the impact of the extend merged dimension setting.

Defining the Problem

I am going to create a couple of very simple queries from the Island Resorts universe to demonstrate this feature. Each query is specifically structured so that it is missing some data found in the other query. For example, here is my first query.

And the data returned by this query.

And here is my second query.

And the data returned by this query.

I am going to call the first query my “left” query and the second query is my “right” query for the rest of this post. I have specifically restricted my left query to 2007-2008 years of data, and my right query to 2006-2007. That means the two queries have one year (2007) in common and each query also has unique data (left has 2008 and right has 2006).

Since both queries are from the same source I do not have to do anything to merge the shared dimensions. Both the Resort and Year are automatically merged as shown here.

And finally here is the resulting block of data shown in my Web Intelligence report. This is the most common presentation for the data. Note that it shows data from all three years of sales results that I selected.

Using the Left Query to Drive The Results

In Desktop Intelligence (more details below) this is as far as I can go. I don’t have any options that let me control the merge process. In Web Intelligence I do. What if I want my first query (left) to drive the results? All I have to do is open up each merged dimension object and use the dimensions that come from the left query. If I do, here’s what the block looks like.

What is going on here? The left query had 2007-2008 data only. I see annual data for both of those years for all three resorts. However, the 2008 rows are missing the Number of guests values. They don’t exist, because the right query only goes up to 2007. In this case I have told Web Intelligence that my “left” data is more important, and to show right data only where it exists.

Using the Right Query to Drive The Results

Next I will turn things around and use the “right” dimensions instead. Here is the block that results from that operation.

Now my revenue column is sparse (has missing values) while the Number of guests column is completely populated. That is what I expect given that I am using the right dimension objects this time.

Those are the three options that I have in Web Intelligence. I can use the merged dimensions (the most typical requirement) or I can use the dimensions from a specific data provider. If I compare the three blocks (merged, left, and right) I can see exactly how Web Intelligence is working. How does Desktop Intelligence work?

Merging Data in Desktop Intelligence

In my example Web Intelligence report I have selected the Resort object in two different queries. After the automatic merging process has run I end up with three different ways to reference the Resort values on a report. Specifically I can reference Resort(left), Resort(right), or Resort. The last option is the merged result. In Desktop Intelligence I am missing that last choice. Is that a problem?

I have a screen shot that shows what the merging process looks like in Desktop Intelligence. One complaint that I have is that I have to click on each dimension object individually to see if it is linked or not. In the screen shot shown below I am only showing that the two year values are linked.

Next I notice that I have no way to refer to the merged result set as I do in Web Intelligence. I have to pick either the left or right value. Does it matter? It turns out that it does not, as shown here. I have selected the right version of the Year object, yet all three values are showing up.

In Desktop Intelligence the results from the left and right queries are merged together. Every value from the left query appears on the right, and every value on the right appears on the left. It is a union of the two data providers. As a result of this union operation, it doesn’t matter which side I pick. I can swap the left and right dimension values and still get the same result. It is always a full outer join.

Is it possible for me to reproduce the way Web Intelligence behaves in Desktop Intelligence? In the example I am using today, the answer is yes. However, I need to look at the measure values instead of the dimensions since I cannot alter the merged dimension behavior. If I want to show the left data, I can create a filter so that only those rows that include a Revenue value will be displayed. Here is how that filter looks.

Dimension values will never be NULL in Desktop Intelligence because of the way the values are shared as a result of the merging process. If I can identify a measure, as I did in this case, I can essentially create a left or right block based on my requirements. The process is not intuitive, nor is it effective in every case.

Extend Merged Dimension Values

So now to the point of this post: what if I would like Web Intelligence to act more like Desktop Intelligence? That’s exactly what the setting “Extend merged dimension values” does for me. Remember in my description of Desktop Intelligence I said that merged dimensions result in a union, where every value from my left query appears in my right dimension and vice versa. That means that I will always see data from either side, no matter which dimension I pick. Here is one way to invoke this setting. I am right-clicking on a report tab and I see this menu.

After selecting Document Properties the following menu appears on the side of my document.

The setting is off by default. What happens if I turn it on?

Here is what my left block looked like before the setting was turned on.

And here is what it looked like afterwards.

And here is the right block.

They look exactly the same. And that’s how Desktop Intelligence would have behaved in this situation. Each dimension has been extended to include the values from the other. My left year includes right values, and my right year includes left values. I have recreated the Desktop Intelligence behavior in Web Intelligence. I am not sure this is a good thing, but I can do it. 🙂

Conclusion

If I only use the merged dimension (meaning I use Resort instead of Resort(Left) or Resort(Right)) then this setting doesn’t do anything for me. The results will be the same with the setting on or off. But if I need to pick the specific values from either the left or right, this setting allows me to recreate the behavior found in Desktop Intelligence.

One caveat: this setting is a document setting, not a report setting. That means if I turn it on (or off) it affects the entire document. That may or may not be an issue. It all depends on what I am trying to do.

This post was written in response to a specific topic on BOB. I started to write the answer there but realized it would make much more sense with screen shots and more text.