Dave's Adventures in Business Intelligence

In the first post in this series I talked about different places I could place complex calculations. I reviewed some pros and cons of doing calculations during ETL, in the universe, or on a report. In this post I am going to focus only on report calculations. I will cover the formulas versus variables debate and show a couple of tricks to make working with variables a little bit easier along the way.

This presentation covers slides 10 through 12 from my 2009 GBN presentation titled “Return of the Variables” which can be downloaded from my conference page.

Constant, Formula, or Variable?

A constant is a value that is never going to change. Constants are probably quite rare, to be honest. I could hard-code a sales tax rate into my report but it is far more likely that the tax rate will come from the database. Same for commission percentage or project deadline dates or any other data along those lines. In any case, they’re not really complex and they’re certainly not calculations so I will move on. 🙂

That leaves formulas and variables. How are they different? How are they the same?

Both can use the full range of report engine functions. Both can be used just about anywhere on the report. Is there any reason to select one technique over the other?

Yes, yes there is.

The Four Cs

Years ago when I wrote my first Variables presentation I came up with what I called “The Four Cs” to describe why variables are better than formulas. They are:

• Convenience
  A variable can be used with every report feature. I can’t build an Alerter using a formula, as shown here:

  

  I can’t rank on a formula. In Web Intelligence I get a list of query objects and a list of variables, but not formulas. The list probably goes on from there but that’s probably enough important features to prove the point. Variables can be used in places where formulas cannot be used.

• Clarity
  Simply put, a variable is a formula with a name. A name defines the purpose of the calculation. It is much easier to understand a complex calculation like this:

  =((100-[Discount Rate])/100)*[List Price]*[Qty Sold]

  … when that formula is also named “Sale Value After Discount” and saved as a variable.

• Complexity
  Sometimes it’s hard to figure out the syntax for a complex calculation. I will often break something down into simple steps and store the results as variables. Because variables can be reused (formulas cannot since they don’t have a name and therefore cannot be referenced) I can check my work at various points and make sure I am seeing the expected results. In another post related to this conference presentation I showed an alternative way to calculate the number of business days between two dates. If you review that post you will see that the formulas get quite complex indeed! 😯 You can bet that I didn’t come up with the final version without working carefully through each step. Using variables, of course.
• Consistency
  Once a variable is built it appears on the list of report “components” saved as part of the document. I have touched on this aspect of variables already in this post. Because variables appear on this list they are reusable throughout the report. Because I can reuse them throughout the report I can edit them in one place and have the changes propagate everywhere I use that particular calculation. If I spend the time and effort to create and test a complex calculation, it’s nice to know that it is reusable and will therefore provide consistent results throughout my document.

Wish List: Universe (or Universal) Variable Repository

At one point I had started writing a VBA script that would allow me to create report variables and check them into a “variable repository” that I stored in a Microsoft Access database. The idea was that once a variable was created I could then easily reuse it in other documents. This worked fine for desktop documents. Web Intelligence documents were not supported at all since the utility was based on VBA. It would be nice if we could see this feature supported directly in the universe framework.

Building Variables

Building variables in a document is fairly simple. I can click the proper button on the toolbar (shown below).

After clicking this button I am presented with the following screen that allows me to create my local calculation. (Note: this screen is from XI 3.0 and is different from what you will see in prior versions.)

This screen allows me to enter a name for my variable, set the qualification (dimension, detail, or measure), and access the full range of report functions.

There is another way that I can create a calculation; I can use the formula bar much like I can in a spreadsheet. Here I have activated the formula bar using the appropriate button and have clicked inside the edit area and started to create my formula.

Is this a problem?

Formula Challenges

For this demonstration I have created the following simple formula.

=[Revenue] / 100

I can use this formula in a report block. In fact I can copy it to multiple report blocks. The problem is, after I have done this I have three (or more) different copies of the same formula. If I click on the initial cell where I created the formula and edit it (perhaps I meant to type 1000 instead of 100 earlier) then only that instance of the formula is changed. The rest of the formulas are stored elsewhere in the document and are not affected by the edit.

This is a problem.

Converting a Formula to a Variable

If I made this mistake and want to convert my wayward formula into a variable I can do that. There is a button on the formula toolbar here:

When I click that button I am presented with the following dialog box that allows me to assign a name to my formula.

Once a formula has a name it becomes a variable and shows up in the document data tab.

Summary

This part of the presentation was probably considered to be fairly basic by most (if not all) of the folks in the room. However, I always repeat this same information every time I deliver a variables presentation. Why? Because of what happened years ago when I presented “Variables and More… Fourth Edition” at the conference in Las Vegas. I thought that by then everyone knew about the toolbar button that provided a way to convert a formula into a variable but it seems that was not the case. The very last question that I took from the audience was from a woman who wanted to know what to do with a formula she had created by mistake. I quickly created a formula and then showed how to convert it into a variable using the process I outlined here. I then found out just how many folks didn’t know about this technique, as it seemed the entire audience went “oooh, aaaah” and proceeded to give me the highest rated presentation marks that I have ever received. 😆

It just goes to show that anything and everything that you know is probably worth sharing. It is very likely that there is somebody, somewhere that doesn’t know what you consider to be basic information. Share those simple tricks and they are much better off. Plus you earn some good karma. 🙂

So go ahead, answer a few questions on BOB. Start a blog. Share what you know.