Dave's Adventures in Business Intelligence

This is the first of several posts that will review my presentation “Universe Models For Recursive Data” which was originally presented at the 2009 GBN conference, then at the North Texas / Oklahoma ASUG chapter meeting, and finally at the Mastering BusinessObjects conference in Melbourne. After presenting it three times it seemed like an appropriate time to (finally) get started writing up the blog posts. As with my other presentations there is a PDF file that can be downloaded from my conference presentations page.

This post will cover slides 6 through 13 as a basic introduction of recursive data and challenges presented to universe designers.

Defining Recursive Data

Sometimes there is confusion about the distinction between hierarchical and recursive data. Hierarchical data does not present a big challenge for BusinessObjects. It can be something related to time (Year, Quarter, Month, Day), geography (Country, Region, State, City), or something more specific like an accounting structure (Business Unit, Account, Sub-Account). What makes this hierarchical structure work easily is that each element is stored in a different place. It could be in a different column in the same table (flattened) or even in different tables (snowflake). As long as I can drill from one column to another in the hierarchy everything works fine.

Self-referencing or recursive data may initially look like a hierarchy. The key difference is that all of the elements are stored in the same place. There are keys that relate one row in a table back to a different row in the same table. That’s how recursive data is different from hierarchical data.

Why is recursion is a problem for BusinessObjects? The language used “behind the curtain” is SQL, and SQL does not natively support recursion. Some database vendors offer extensions (for example the CONNECT BY PRIOR structure in Oracle) but these are not used by BusinessObjects.

How common is recursive data? It is certainly not unusual. Consider any of the following:

• Company organizational structure
  Object levels: President – Vice President – Director
  Object type: Person
• Inventory BOM (Bill of Materials)
  Object levels: Product – Assembly – Sub-Assembly – Component
  Object type: Inventory item
• Project Management
  Object levels: Project – Task – Sub-Task
  Object type: Project entry
• Multi-Level Marketing (MLM)
  Object levels: Founder – Recruit – Recruit Level 2
  Object type: Person

In each of the above examples the type of object (or node) type is the same at any level. For example, a company organization chart is made up of people. Some people are at different levels, and there are therefore relationships from one person to another. In order to show all of the relationships from the top of the company to the bottom (or the bottom to the top) I have to keep going back to the same table. That is recursion.

Because it’s easy to think about a company organizational structure I used that example for the rest of the presentation.

Note: The Motors database is used in the standard Universe Designer training course and will not be presented in its entirety in the download package for this presentation for copyright reasons. However, I will be providing the standard HR table and all of the modified versions used in this presentation. Continue reading “Universe Models For Recursive Data Part I: Introduction”

I was fortunate to once again be invited down to the Mastering BusinessObjects conference in Australia. I hope this conference continues to do well because I certainly enjoy going. Two weeks prior to this conference I was at ASUG Annual Conference / SAPPHIRE, and the difference is substantial. SAPPHIRE is HUGE, which might explain why their name is in ALL CAPS. 😀 The Mastering conference is much smaller (they’re only just getting started, really) and feels much more intimate. I talked to quite a few folks and got to hear some really interesting stories.

And like last year I got a few pictures… Continue reading “Mastering BusinessObjects 2010 Review”

There was a post opened recently on BOB asking about Teradata Query Banding. DBAs are always interested in knowing as much as they can about who is running a query, and query banding (think of banding a bird to track its flight patterns) does just that. In later versions of XI we have the BEGIN_SQL parameter that can be used for this. In XI R2 and earlier versions the BEGIN_SQL parameter does not exist, and the END_SQL parameter really isn’t appropriate. Can we still use this feature?

It turns out that earlier versions are still able to use this feature by plugging the proper syntax into the ConnectInit parameter, which is part of the connection definition process. Continue reading “Using ConnectInit For Teradata Query Banding”

If you procrastinated just enough to miss the deadline, or if you were inspired by some extra stupendous ideas just after the submissions window closed, you are in luck. 🙂 The call for speakers for the fall BusinessObjects conference has been extended until June 13^th.

To repeat something I mentioned earlier (a tweet from Jamie Oswald)

Did you know that speakers at #SBOUC get free admission. That’s like making $1,000/hr. Go submit an abstract. http://ow.ly/1KefI #ASUG #BOBJ
10:40 AM May 19th

That’s what I had yesterday. And the day before. Which was also yesterday. Confused? I know I was when I got home.

I got up about 5:30 am local time in Australia. I was in the airport by 7:30 am, and checked in for my flight by 8 am. They had extra security checks… I got flagged. That meant I was pulled out of the boarding line and was one of the very last people to get on the plane. Which I was able to do by 10 am. We took off, flew over the Pacific Ocean, and landed in California at 10 am. That meant that I landed in LA before I had taken off in Australia. I guess getting to do Friday over (twice) made up for the fact that I lost my Saturday on the way out.

In any case, I got home safely and with some interesting tips and exciting memories from the Mastering BusinessObjects conference in Melbourne. The spring conference season is over (at least for me) so now it’s time to start planning for…

… summer vacation. 😆

Today we closed the Mastering BusinessObjects 2010 conference. I presented my “Return of the Variables” talk, and afterwards I had someone come up to me and saw what I always love to hear: “You just solved one of my problems.”

Tom Nather did a very detailed session on setting up the configuration for single sign-on (SSO). I’ve been away from the server stuff for a while, so it was good to see the process laid out so cleanly.

Fellow SAP Mentor Ingo Hilgefort did a keynote this morning where he outlined current (SP3) and future (XI 4) product features, concepts, and directions. He was (rather predictably I think) asked about Desktop Intelligence, and just like every other time I have heard this question asked, the answer was: “It’s gone.” With folding now in 3.1 and the data manager coming in 4.0 it really seems like it’s time to read the writing on the wall and move on. The only big remaining “gotcha” for Desktop Intelligence users is the lack of scripting support (VBA) in the rich client. In many cases what I have seen scripting used for is now covered by the standard scheduling or other processes.

I had some really good conversations with folks here in Melbourne, and I thoroughly enjoyed my time here at the conference. It’s so much easier (it seems) to connect with people in a smaller setting.

After the conference Tom invited me to go with his family to eat dinner on a train. Who was I to refuse? 🙂 It was a wonderful dinner, served on a trolley car that drove around the city for an hour and a half while we ate. The food was wonderful, the company even more so.

Tomorrow I am delivering a full-day seminar titled, “From Universe Development to Report Generation: The Untold Story.” It should be fun. 😎

Yesterday was the first full day of the conference, and it went very well. Timo Elliott did his typical excellent job of opening the conference with a keynote full of possibilities. He remains very enthusiastic about the social aspects of BI, which is something that I think I agree is coming even if I’m not exactly sure when or how it is coming.

The second keynote was from Rubbermaid. They have done some interesting stuff with dashboards (which was fun to see) but one of the most impressive things to me was the statement that their company has at least one product in 90% of American households. That’s some serious market penetration.

I thought my talk went well. I had some great conversations afterwards about working with recursive data, including one interesting challenge related to investment funds. No, not a ponzi scheme, it’s a legitimate question. 🙂

The evening keynote was from Glenn Singleman. I had never heard of him, but he was an excellent story teller. He started out his career as a doctor in the emergency room. One day a co-worker asked him to go “canyoning” on the weekend, and when he asked what it was (and found out it involved hiking through deep canyons and the occasional descent over a waterfall) he was inclined to pass. A different co-worker then asked him to take his weekend shift so he could go, and Dr. Singleman decided that he would rather go hiking in wet canyons than work yet another night shift. The decision changed his life. He’s now an adventure documentary film maker along with being a doctor, and the adventures he has had…

He and his wife recently set a number of records by base jumping off of the highest cliff they could find, something over 6,000m (23,000 ft). From their web site:

On May 23rd, 2006 that strength and persistence prevailed, and they achieved their dream. Glenn and Heather successfully climbed Mt Meru in the Indian Himalaya and BASEjumped in wingsuits from a ledge at 6604 metres. It was a new world record, a world first for a woman and a husband and wife team.

And here I thought remodeling a house was the scariest thing a husband and wife could do together… 😆

Related Links

• BASEClimb.com

I had a (long) flight from Los Angeles to Melbourne; the flight went very well. By that I mean I slept through most of it. 🙂 They also had a “tail cam” which was really fun once the sun came up. They had a camera mounted in the tail so you could see the view from behind the plane. It was especially fun to watch the landing from that perspective.

The folks at Eventful Management have once again done an outstanding job. I took a quick tour of the facilities, and everything is quite nice. We have over 200 folks attending the conference this year which is up from last year. I like smaller conferences because it’s much easier to connect with folks. I’ve already met Josh in the lobby, and Tom and his lovely family in the lounge area. I will be going with Tom on a tour of the city later today, then tonight there is a speaker orientation meeting. The opening keynote and reception follow.

Maybe they’ll have more of this:

I initially had some difficulty coming up with ideas to submit for the fall BusinessObjects conference, but I did eventually come up with a few. I was really hoping to get a pre-release copy of XI 4.0 to play with so I could talk about some of the really cool new universe stuff, but that didn’t happen. Once I got over that disappointment I decided there were still some interesting things to say about our current technology. Besides, I imagine that quite a few companies will not upgrade to XI 4.0 for quite a few years anyway.

I submitted to the Universe track as well as the Webi/Deski track this year. Those are my primary areas of interest and therefore the subjects I most enjoy speaking about.

If you haven’t submitted any ideas yet, there are still a few days left! Take an hour out of your weekend and see what you can come up with. If you get accepted to speak you don’t have to pay to attend the conference. As Jamie said:

Did you know that speakers at #SBOUC get free admission. That’s like making $1,000/hr. Go submit an abstract. http://ow.ly/1KefI #ASUG #BOBJ
10:40 AM May 19th

Related Links

• Call for Papers for 2010 fall conference

There have been a number of posts recently in the Semantic Layer forum on BOB about shortcut joins. When will they be used? How many can be used? Why won’t this particular shortcut get used? Do I have to add shortcuts to contexts? Lots of questions.

I am going to try to clear up a couple of those questions now. First here is a summary of everything I need to know about shortcuts:

• Shortcut joins do not provide an alternate path.
• Shortcut joins do provide a shorter path.

By the end of this post I hope that the reader will understand the difference between those two statements. There are two rules for how and when a shortcut will be applied:

• A shortcut join will only be used if it eliminates tables from the query.
• A shortcut join is applied after the SQL has been generated (meaning after a context selection has been made, if required).

I will talk about these two items as well. But first, how do I create a shortcut join in my universe? Continue reading “Everything About Shortcut Joins”