Prompting for One Month – Displaying a Range of Months

An OBIEE dashboard designer recently asked how to turn a prompt for a single month into a filter for a range of months. For example, if the user selects “Aug” in the prompt, the results should include not just August but also the three months preceding and following August (i.e. May through Nov).

One way to do this is to take advantage of sequence numbers for months (giving an easy way to calculate a range of months) and presentation variables.

Here’s the prompt setup.

Notice three things. The user’s selection of MonthNumber sets the value of a presentation variable called MoNum. Notice also that the formula for MonthNumber is not simply the column name itself but an expression: MonthNumber + 0. Finally, note that MonthNumber is constrained so that it is effectively set by the user’s choice of MonthName.

For the query itself, the tricky part is setting the filter. There are two things to note here. The first is the use of “SQL Expressions” in the filter. The second is the syntax for the presentation variable in these SQL expressions.

Of course, instead of a hardcoded value of 3 in these expressions, you could also use another presentation variable. To illustrate, I changed the prompt to have an edit box that enables the user to set a presentation variable called “Range”.

Notice here that the “Column” is not a column at all but simply the number 1. To generate this, click any numeric column in the left panel, then change the formula to 1.

On the dashboard, the prompt and query work together as shown here.

The set up for the filter on the query includes this Range variable with a default value of 1. (This 1 is not connected at all to the fact that 1 was used in the formula in the prompt).

Finally, you may be wondering why the formula for MonthNumber was set to MonthNumber+0 in the prompt. The reason is that if the formula in the prompt is the same as the formula for the column in the query, then the MonthNumber itself gets set as the filter. Here’s an example. The user selects May (month number 5) with a range of 2. The query shows the results for May only.

Looking at how the query was modified by the prompt, you can see that the filter used just the value of MonthNumber in the prompt. The presentation variables were ignored.

Don’t Overstate Data

 

One of the tricky things for users of relational databases is forming queries without overstating (or understating) results as a consequence of table joins.

Take this example that comes from an OBIEE user who had database tables containing information about opportunities and attachments, both of which related to accounts. The OppAcct table looked like this, showing that each account had multiple opportunities.

The AcctAttachment table looked like this, showing that each account had multiple attachments.

This user wanted to be able to report on the data from these tables showing opportunities, attachments, and amounts for each account. Results would look like this for the BP account, achieved by a query that joined these two tables together on Account.

The user thought these results were OK and proceeded to model this data in OBIEE. Initial results looked good – just like her SQL query.

However, when she removed Attachment and OpptyID columns from the query, Answers overstated results by a factor of 2.

What's gone wrong here? There are two possible sources of the problem: the data modeling in the database and the metadata modeling in OBIEE. We will look at both.

In the database model, there isn't a source of the distinct set of accounts. Someone familiar with dimensional modeling would phrase it differently: there isn't an account dimension table. Using the AccountAttachment table as a source of accounts leads to overstatement because each account may have several entries in this table. BP, in fact, has two, which leads to the doubling of the amounts for each opportunity when this table joins to the OppAcct table.

The user made these tables sources in her OBIEE business model (the “middle layer” in the OBIEE administration tool). She knew that an OBIEE business model requires, at a minimum, two logical tables (a logical dimension table and a logical fact table). Since the only aggregatable column is Amount, OppAccount was the best choice for the logical fact table source. She made AcctAttachment the logical dimension table source, since it had no aggregatable columns. She was following basic principles correctly.

Dimension table sources need to join to fact table sources. She created a physical join on AccountAttachment.Account = OppAcct.Account. Making this a key/foreign key join involved “lying” to the OBIEE administration tool about what the key of the AccountAttachment table is. It isn't Account, since the same Account exists multiple times in the table. And lying about dimension table keys is a good indication that trouble lies ahead.

In the business model, the administration tool requires that every logical dimension table has a logical key. This was another opportunity to lie, and lies here are another portent trouble.

Suppose she didn't lie here specified that the logical table key consists of Account plus Attachment. When doing a consistency check, she would have seen this Warning.

Obviously, the two tables do join. So the warning is misleading. What the administration tool is really warning you about is that the columns defined as the logical key do not match the columns in the physical join between these sources.

One way to solve this problem is to introduce a source of distinct accounts. You could do this in the database, and of course that's the best way, but you could also (for relatively small data sets) introduce a SQL-based table in the physical layer of the metadata.

You could join this “table” to the other two data sources – and now there would be no reason to lie about joins or keys.

The SQL table now is usable as a source in the business model.

Now aggregations of Amount will be correct, as the BI Server will drop the AcctAttachment table out of the physical query when it is not needed.

This is a very simple example, of course, but it illustrates some fundamental principles for successful OBIEE projects. First of all, develop a dimensionally sound physical data model. Time and effort expended in developing a data model for BI is a sound investment. Whenever an OBIEE project seems to get overly complex, it is a good indication that the underlying data model is not optimal. Secondly, be careful to tell the truth to the administration tool. The admin tool may allow you to get away with it, and some queries may even be correct. But more often than not, trouble lies ahead.