Dave's Adventures in Business Intelligence

I’ve been inactive on BOB for a while as well as here…recently I was reminded that some of the old questions still come up periodically. For example, how do you reset a running total? It’s not hard, but the syntax is not immediately obvious. Continue reading “Resetting a Running Function”

…the more they stay the same.

I’ve been fairly quiet here for a long time. Part of the reason is I’ve been busy with family and work. My older son will be a senior in high school this year, and my younger is just starting driving lessons. But something happened a few weeks ago that made me want to write again.

At work I’m now overseeing the Tableau team. We still have a lot of Business Objects users, but Tableau has become the “flavor of the month” that everyone wants to use. Tableau certainly has the right idea in that their product works identical whether viewed on the web or via a mobile device, and their mapping options are really nice.

But at the core, it’s still a data presentation engine.

A few weeks back, I was trying to solve a problem that I knew how to solve in Web Intelligence. Specifically, I wanted to generate a grand total that would return the same value no matter where I dropped it on my dashboard. In Web Intelligence this is done via Calculation Context and it looks something like this:

=Sum([Revenue]) In Report

By adding the key words “In Report” I am telling the tool that I want to see the grand total. I’ve covered calculation context before (Calculation Context Part I: Overview) and it remains one of my more popular posts. It’s definitely the one that has the most people asking, “Where’s part 2?” Other calculation options in Web Intelligence include “ForEach” and “ForAll” to go along with “In” that I have already mentioned. Here are the definitions I wrote the first time around:

• In is used to specify exactly which dimensions to include in a context. Other dimensions in the block are ignored. Adding / removing elements from the block does not impact this calculation unless a removed dimension was specified in the context. In that case a #MULTIVALUE error is displayed.
• ForEach is used to include a dimension in a context. The calculation context is still affected by other values in the block.
• ForAll is used to exclude a dimension from a context. Other dimensions of the block will still be considered. Adding or removing values from a block might change the value, but it will always ignore the ForAll items.

Why bring this up again? Because Tableau has exactly the same options. 😆 Instead of Context they call their option Level of Detail. You activate a Level of Detail operation by using curly braces { } around your calculation. Suppose you want a grand total, it looks like this:

{Sum([Revenue])}

That is the equivalent of “In Report” for Web Intelligence. What about the others?

Tableau provides FIXED, INCLUDE, and EXCLUDE as additional tags for their Level of Detail operations. Each of those is a direct equivalent to a Web Intelligence option.

FIXED is the equivalent of IN. These two statements are an equivalent way to request sales by year, even if other dimension values are present in the block or worksheet.

{FIXED [Year]: Sum([Revenue])}
=Sum([Revenue]) In [Year]

INCLUDE is the equivalent of ForEach. These statements are going to make sure the Year is included in the calculation, even if it’s not part of the displayed block or worksheet.

{INCLUDE [Year]: Sum([Revenue])}
=Sum([Revenue]) ForEach ([Year])

Note that if there are multiple Years then Web Intelligence will show a #MULTIVALUE error, and Tableau will show a * as the result.

EXCLUDE is the match to ForAll. These formulas will specifically ignore the Year value when generating Total Revenue, even if it’s present in the block or worksheet.
{EXCLUDE [Year]: Sum([Revenue])}
=Sum([Revenue]) ForAll ([Year])

The person who was starting to explain the Level of Detail function in Tableau started out by saying, “Now this is one of the more complex techniques that Tableau offers, so it may take a bit to get a handle on it.” As soon as he started the technical explanation I cut him off, saying, “Oh, it’s just like Calculation Context. I’m good.”

🙂

So yes, the more things change, the more they stay the same…

For a while now I have been whining about the fact that there were features dropped from XI 3.1 during the upgrade to BI4, including how the dimension merging process works. Another gap? In XI 3.1 we had the ability from the query panel to run only a selected data provider, leaving the others alone. When working on complex multi-query documents this could be a big help, especially if some of the data providers had longer refresh times.

A few days ago I was grumbling about this yet again and discovered a way to refresh a single data provider! It’s not perfect (nor was it the most obvious workflow for me) but it does work. I had one data provider that was scanning a huge multi-billion row fact table. To supplement this data I needed to run an additional query against an Excel data provider. I had to make several changes to the Excel file in order to help my data match up correctly, and each time I updated the XLS I had to refresh the entire document in order to see the changes… which was annoying and time consuming. Now I have a solution.

Note: Since I’m talking about joining with Excel, clearly I was using the rich client application. However the same technique outlined below works online with multiple universe data providers as well. Continue reading “Yes, Virginia, You Can Refresh One Data Provider At A Time”

The actual origin of the concept of a “red herring” is unknown, but that doesn’t stop it from causing grief while trying to diagnose a performance issue. If you are not familiar with the concept, a red herring is something that initially appears to be relevant but ultimately is proved to have nothing to do with the actual issue. It’s a popular technique for mystery novels… and in tech support calls.

Case in point: Today I had to help someone who was wondering why their report took over thirty seconds to display a prompt window when there was only one prompt in the document. Clearly it was a prompt issue, right? Or something related to the list of values definition for that object? Continue reading “Big Universe + Security Profiles = Slow Query Generation”

One of the things that I really wish SAP had left alone during the rewrite of Web Intelligence between XI 3 and BI4 is the merging interface. The way you merged dimensions in XI 3.x was brilliant, and gave the report developer an excellent interface to use to manage their merged dimensions. In BI 4 for some reason it looks like they took their design ideas from Desktop Intelligence instead. I was reminded of this today when I tried to “unmerge” (demerge?) two dimension objects in BI4. Continue reading “Unmerging Dimensions in Web Intelligence”

Several years ago I wrote a post that has generated a fair number of follow-up questions and comments. The post was related to splitting user response values onto separate rows and it used some basic string operations to do that. The important distinction is that the values were on different rows but remained in a single cell, which meant the output was suitable for a header cell.

Recently I got a comment that posed this question:

In one of my reports there is prompt to select the Fiscal Year and the user can select multiple LOVs. Prompt Name is “Year”. Say for example the user enters 2011,2012 and 2013. On using the function userresponse the report will show 2011;2012;2013

My requirement is to identify minimum value from the LOVs that the user has entered. In this case the report should show the mininum value as 2011. Can you please guide me on how to achieve this?

Continue reading “Pivot UserResponse() Values Into Rows”

One of the frequent requests that I see goes something like this:

I have a hierarchy set up. When I drill to the bottom of the hierarchy, I want extra detail objects to show up.

This seems like it should be the default behavior, right? If I take the time as a universe designer to properly classify objects as dimensions or details, and also take the time to set up hierarchies, then it would seem that reports would recognize and utilize that information.

Unfortunately they don’t. That means I have to use some report functions and set up some variables and do some creative formatting to make it work the way I think it should. I first showed how to do this with Desktop Intelligence way back in 2000 at the Business Objects conference in Washington D.C. Today I will update the technique and show how it can be done in Web Intelligence. Continue reading “Drilling to Details”

I recently started participating more on the SAP SCN forums. One question in particular served to remind me that not everybody has a decade (or more) of experience with the tools, and sometimes we need to answer beginner questions too.

For example, someone asked a question along the lines of the following:

I have a document with three data providers but there are four reports. How can I know which data provider goes with which report, and why are there more reports than data providers?

That wasn’t the exact question, but that was essentially what they were asking. For someone that is new to BusinessObjects it’s a reasonable question to ask. Especially if they come from a spreadsheet background where everything is right up in front. I thought it might be interesting to set up an Entity – Relationship Diagram for the various document components to help clarify how things work together. Continue reading “ER Diagram For Web Intelligence Document Structure”

In some cases I have created reports that were in drill mode that were not really drillable. For example, I showed a technique a few years back that would let a report developer swap measures out on a chart using a drill filter. It has been working great for quite a few years now. However, as a side effect any dimension on the report blocks that participates in a hierarchy becomes drillable. Sometimes I don’t want that, and there is an easy fix. Continue reading “Want To Remove Drill Indicators? Use A Variable…”

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. Continue reading “What Does Extend Merged Dimensions Really Do?”