Dave's Adventures in Business Intelligence

I have known for years that using the “Tables” button on a universe object can be problematic. The technique is designed to allow a developer to include extra tables in an object definition without directly referencing them in the select statement. This could be done for a number of reasons, and it’s one of the reasons I designed the time-sliced solution the way I did, just to avoid this table update issue.

Recently I discovered the same “feature” is present in the measure projection function assignment logic as well. I wrote about how important it is to properly coordinate the SQL aggregate and the projection function some time back. I have also talked about how the database delegated projection function works. This week I had the need to create a whole set of database delegated measures, which went fine. Then later I had to update the SQL statement for these measures to correct for a divide by zero issue. After publishing I went back and checked, and every single one of these measures (about 90 of them) were reset back to a Sum projection function. 😡

It seems that the projection function works the same way that the tables button does. Every time I touch this object, I will have to remember to reset the projection function. To avoid that, I used the @Select() function for all of the time sliced objects and had them reference the base measure object, which was itself using the @Select() function to reference a measure “bit” object. I wasn’t sure I could nest one @Select() inside of another, and I was actually fairly certain I had tried that before. But apparently in 3.1 it works now.

My time-sliced objects generally have this formula:
Base Object: Sum(@Select(Measure Bit))
Time Slice Object: Sum(@Select(Measure Bit)) * @Select(Time Slice Bit)

Now I’ve done this:
Base Object: Sum(Select(Measure Bit))
Time Slice Object: Select(Base Object) * @Select(Time Slice Bit)

Notice that in the second case there is no Sum() function on the time slice object? That’s because there already is one in the base object, and I cannot nest aggregate functions. But nesting @Select() seems to work okay.

The bottom line is that there are certain automatic features of the universe designer software that I wish I could turn off. Most of the time they’re fine, but when they keep resetting important items on object definitions, and do so without notifying me that they’re doing it, that’s irritating.