Dave's Adventures in Business Intelligence

I have been selected to speak at the 2012 SAP BusinessObjects User Conference (twitter tag #SBOUC). My topic this year will be a follow-up of sorts to a topic I did last year called, “Designer Essentials.” In the essentials topic I went over the basic requirements of universe design (setting up a connection, adding tables, building joins, and so on). I also covered additional steps that have to be done in order to ensure the correct results, like making sure all SQL traps are resolved. My session this year covers the next step, which answers the question:

Now that my universe is working, how can I make it go faster?

I will be covering shortcut joins, index awareness, and aggregate awareness. I have talked about all three of these features before (they’ve all been available since version 6.5) but I hope to have time to show a neat trick with the @Aggregate_Aware() function that actually has nothing to do with aggregate tables. I’m also going to touch on the use of in-database solutions like aggregate join indexes (from Teradata). It will be a lot to get through in an hour, so maybe I’ll have to eliminate some of my historically bad puns.

Nah, I’ll leave the puns in. 😛

As an aside, if you’re planning a trip to Orlando there are a couple of quick things to make sure you are aware of. First, the conference is early this year. It’s in September rather than the October/November slots that we’ve seen in prior years. Second, the conference has been extended an extra day, so plan to arrive on Sunday and depart Thursday. Content delivery starts on Monday, September 10th and runs through Thursday, September 13th. The final day (Thursday) is a half-day of additional content provided by SAP covering their complete analytics product line. This additional content is included as a part of the SBOUC conference this year. Steve Lucas announced this last year during his keynote.

Best of all? Wednesday night we have a concert from 80’s legendary arena rock band Foreigner which should be a lot of fun. I grew up listening to Foreigner along with Styx (who I was able to hear earlier this year), Boston, and many other “classic rock” bands. Hm. The term “classic rock” makes me feel old…

As a personal note, this will be my 17th consecutive year speaking at what I consider to be one of the “can’t miss” BusinessObjects events of the year. My first talk was back in 1996, and was quite forgetful to be honest. The next year I delivered a talk titled “Variables and More Variables” and it has gone on from there. Take that, Donald. 😛 Donald did get accepted, and all kidding aside, I consider him to be one of the best BI speakers out there. I look forward to hearing what he has to share this year.

SAP has put out quite a few tutorials on the new Information Design Tool (IDT) in their new community. The table of contents includes topics like “Concept: Navigate the interface” and “Concept: Create a universe” to more detailed tasks such as “Create a data foundation based on a single source relational database” and “Create a shortcut join.” Altogether there are 133 video tutorials currently posted on YouTube. If you’re new to the IDT or are wondering how to do common tasks that you’re used to from the original universe design tool, these tutorials look like they will really help.

Note: at this point I have only watched a few of the videos, and the audio sounds computer generated. But if you don’t have access to training, or are familiar with universe design but aren’t clear where some of your favorite features went, they should prove very useful. Just skip the first 45-50 seconds to get past all of the introductory template stuff. 😎

Official Product Tutorials – SAP BusinessObjects Information Design Tool

While looking for something else, I found a document from SAP that details how “legacy” work flows from the original universe designer can be recreated (or not) in the Information Design Tool. Because of the risk of SAP changing their URL structure (what! that never happens… 😉 ) I have also downloaded the PDF and hosted it here on my blog. If the link above works, please use it as SAP is more likely to have the most up-to-date version. If the link is broken, you can try downloading the version of the document that I am hosting here on my blog.

Download Link: IDT legacy workflows.pdf

One of my favorite Dilbert strips to pass the time until I can get started blogging again:

If only it were that easy!

(See blog subject in case it’s not clear what I’m talking about…)

One of my goals for this blog was to make sure I kept to my schedule of at least two blog posts per month. Normally May would be an exciting month as I would be attending the ASUG Annual Conference which is co-located with the SAPPHIRE event down in Orlando. In fact I think this is the first year out of the past three (or four?) that I have missed it. Conferences always seem to provide a lot of inspiration for posting. However, for a variety of reasons I am not attending the event this year. That means plenty of time left for other stuff, yes?

As it turns out, the last few months (actually some of the development started well over a year ago) I have been busy working on the next iteration of BOB. We’re coming up on our tenth anniversary as a community, and I wanted to spiff things up a bit. Some of the changes will be visible, some are primarily for tuning or optimization, and some are intended to make things more efficient for our fine moderator team. But the point is, there is still a bit of work to be done, and I really wanted to have the release done before the end of May. That means that unless I really can ask the universe for more time, I will be taking a vacation from this blog in order to concentrate on getting the BOB updates finished and tested. That means things may be a bit quiet here for the month.

This year is really tough, with our conference being earlier in the year than previously. But with ASUG Annual Conference / SAPPHIRE in ramp-up mode, it seems that the number of abstracts coming in is running behind the schedule that was expected. As a result, the deadline for submissions has been extended to May 11th. One of the tracks that has the fewest submissions so far is the Semantic Layer! (Oh, wait, I have not submitted any ideas of my own yet… :oops:) Mobile is another area that should be very popular but needs more submissions, so if you have a fun / interesting / frustrating / enlightening story to share in either of these areas, please consider submitting an abstract.

The call for speakers for the 2012 fall Business Objects conference in Orlando is live now. The window for submitting abstract ideas is smaller than in previous years, primarily because the conference is in September this year. The process of getting, reviewing, and selecting the final sessions will also be done on a tighter time frame. Remember that speakers get a free pass to the event, so even if it takes ten hours or so to create your presentation that’s still like earning $150 an hour. 😉

ASUG Call for Presentations, 2012 Fall Business Objects (SBOUC) conference

Just a random image from one of my favorite online comics:

I think I’m going to try this in my next presentation. 😎

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.

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”