Dave's Adventures in Business Intelligence

This is the third 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. As with my other presentations there is a PDF file that can be downloaded from my conference presentations page. The first post introduced the concepts of recursive (as opposed to hierarchical) data and provided a couple of examples. The second post reviewed some of the different design challenges that I have seen in working with recursive data models. In this post I will introduce four different possible solutions and present a scorecard for each, showing how well it solves the issues presented in the prior post in this series. Links to both prior posts are presented at the end of this entry. I have also included Oracle SQL scripts that can be used to create and populate the tables used in this post.

This post will cover slides 22 through 30 from the presentation and will describe the first two solutions (one with two variations) outlined in the presentation. Continue reading “Universe Models For Recursive Data Part III: Alias Versus Flattened”

This is the second 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. As with my other presentations there is a PDF file that can be downloaded from my conference presentations page. The first post introduced the concepts of recursive (as opposed to hierarchical) data and provided a couple of examples. In this post I will review some of the different design challenges that I have seen in working with recursive data.

I decided to identify and cover four different examples of recursive data configurations. These included Clean, Unbalanced, Ragged, and Lateral. As I mentioned in the first post, I am going to use some basic human resources (HR) data for my examples. For this post, in order to show samples of each of the four challenges, I am going to represent my recursive data using a tree. The branches of the tree show the relationships between people. The nodes of the tree contain the information about each person. The data might include their name, hire date, and position (title) within the company. In order to properly interact with my recursive data I have to be able to work with both types of information: relationships and node data as well. If you are not sure what I mean, please continue reading, this will make more sense later on.

This post will cover slides 14 through 21 from the presentation and will describe each of the different recursive challenges that I identified. Continue reading “Universe Models For Recursive Data Part II: Design Challenges”

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”

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. Continue reading “Constants, Formulas, and Variables, Oh My!”

When working with the reporting suite from Business Objects there are many different calculation engines. A report developer can create custom formulas or variables in Desktop Intelligence, Web Intelligence, and of course Crystal. A universe designer can build custom objects using database functions in the universe. An ETL architect can design special query transformations. So where do you do the work?

This post covers slides 6 through 9 from my 2009 GBN presentation titled “Return of the Variables” which can be downloaded from my conference page. Continue reading “Calculation Options”

A frequently asked question on BOB is:

How can I calculate the number of business days between two dates?

The easiest answer is to use a calendar table with a flag for business days because that takes care of everything quite easily. You can even mark holidays along with weekend days and make the results more useful.

But suppose you don’t have a calendar table. Suppose you don’t care about holidays, you simply want to count the number of Monday – Friday days between a range of dates. Can you do that?

The answer is coming if you read the rest of the post. 🙂

Note: This technique was shown in public on Monday at the GBN conference. I had written this blog post some time ago but waited to release it until after the presentation had been delivered. The presentation has been uploaded to my blog and is available for download from the conference page.

Continue reading “Calculating Business Days Between Two Days Via Report Functions”

I have uploaded the latest pdf files for my presentations from the 2009 GBN conference. You can download them from my conference presentations page. I did two presentations this year.

Return of the Variables

Presentation Abstract

It’s time to pull out some variables again as we revisit a popular topic from past conferences. Report variables can provide a more creative, efficient, and effective means of completing the detailed analysis required for your reports. The focus of this presentation is building documents, using report functions, and reviewing variable tricks that can be used to solve reporting challenges. The presentation this year will focus primarily on the SAP BusinessObjects Web Intelligence product and include some of the newest features that have recently become available, but there will be some goodies for users of prior versions as well.

Universe Models for Recursive Data

Presentation Abstract

Recursive data can present a special challenge to SAP BusinessObjects developers because SQL is not natively able to process the relationships. This presentation will show several different methods for modeling recursive data along with the pros and cons of each. All of the methods shown have been used on real-world projects. Attendees will gain a better understanding of the complexities of dealing with inventory models, organizational hierarchies, and other types of recursive data.

As time allows I will be adding detailed blog posts for each of these presentations. I am cheating a bit this year. 🙂 I have already written two blog posts that detail most of the “Variables” presentation:

• Using ForceMerge() To Fix Unbalanced Data Providers
  This post covers slides 13 through 18 from “Return of the Variables” and was originally released here on my blog back in September.
• Calculating Business Days Between Two Days Via Report Functions
  This post covers slides 19 through 33 of the same presentation and is already written. I need to make a few tweaks and will likely publish it on Friday. Until then the link above won’t seem to be valid, but it should work after the post is published.

That leaves only the initial portion of the presentation to write up, and I hope to have it done fairly soon. The detailed posts for the Designer presentation will take a bit longer.

I am tired. When conferences are outside of my home area (Dallas) I generally spend the days at the conference, the early evenings doing conference-related items, and the late evenings catching up on work. When the conference is here in Dallas, I spend the days at the conference, the early evenings doing conference-related items, the late evenings catching up on home stuff, and the even-later evenings catching up on work. I hope the conference is somewhere else next year, I’m exhausted. 🙂

Some highlights from my third day at the conference…

Webi 3.x Has Cool Stuff

I attended Sandy Brotje’s presentation called “What’s New for Me?” for my first session. I already knew quite a bit of what was new myself, but different people place emphasis on different features so I decided to see what she had to offer. I thought she did a great job of summarizing but also personalizing the new features that were important to her. I should send her a link to my blog post about losing an old friend because she didn’t know what the new measure icon was. 😉

Explorer… Again?

I went to a presentation by Dallas Marks (check out his blog if you haven’t already) about Explorer. He did a short demo (using eFashion, imagine that! :lol:) but then went into the back-end process showing how to install it. I left a bit early because I ended the day with…

Recursive Data in Universes is Hard

… my final presentation of the conference. I am fairly certain I submitted a far more imaginative title, but what came back was “Universe Models for Recursive Data” instead. It was a bit misleading because only one of the four solutions I showed can be done completely in the universe, and it’s the weakest of the four solutions. I had a good crowd for the last session on the last day of the conference, they laughed at my recursive jokes, and I gave out a bunch of BOB hats.

Good times. 😎

So what’s next? There was a slide being shown as a backdrop during the keynote sessions that asked a question about whether there would be a Business Objects focused conference next year, and the answer provided was apparently a bit confusing. My understanding is that there will be a conference next year like this year (and prior years) but under the ASUG name. Since GBN is merging into ASUG it will no longer be the GBN Annual Conference but something similar with ASUG in front instead. I hope we can get more details out about this soon (like dates and the location) and will post here as soon as I hear anything concrete.

It’s 10:00 pm, do you know where your Dave is? He’s at home, finally, after a long but interesting day at the GBN conference. I am going to again post a few highlights from today and save some of the in-depth thoughts for later.

Computers Evolve During Stressful Times

The keynote this morning was delivered by Vicki Farrell, the Manager of Marketing Strategy from Hewlett Packard. She presented a couple of interesting points, one of which was that computer technology seems to get the biggest boost during times of economic stress. It’s at this sort of time that people really start looking to get more out of technology as a way to make up ground. She finished out her presentation with a picture of the garage where HP started. (I am sure there is a rule from HP that requires that 😉 )

Text Analytics Are Cool

I attended a presentation on text analytics that was interesting enough. I never seem to get anything that I can use in my job from these presentations, but the concept is one that I am quite interested in so I try to attend them. The presented talked about monitoring sentiment by scanning both internal project documents and external web sites. She did get a bit confused when someone asked about RSS; she thought that was some sort of sound file.

Semantic Layer Usability Testing Was Interesting

Unfortunately it was also covered by a non-disclosure agreement so I can’t talk about it. 🙁

Too Many Universe Design Sessions

I say this with my own universe design session coming up tomorrow, but I really think we had too many sessions this year. The technology hasn’t changed that much since last year (other than some improvements in SAP BW support in 3.1) and I saw a lot of the same stuff in the few designer sessions I did attend. My stuff is not the typical Designer material so I hope it will be well received by the folks that manage to stay for the last presentation slot on the last day of the conference.

SAP Mentors Rock!

One of the best sessions I attended was delivered by fellow SAP Mentor Ginger Gatling from the SAP Labs. She did a great job of presenting – in less than an hour – what SAP is, what they do, what they had for BI before they purchased Business Objects, how the product suite fits together now, and ended with tips on how people with different Business Objects skills could leverage them in the new environment. Yes, all in one hour. And she was entertaining and energetic while doing it too. 🙂

Catching Up With Old Friends is Fun

I have chatted with quite a few BOB members and other friends I have known for years through our common interest in Business Objects. That is one of the best parts of the conference for me every year. 😎

Lots of interesting things happening at the GBN conference today. I will share a few nuggets now, many of which I hope I will have time to expand on in the future. In no particular order…

Data Federator Combined With Semantic Layer

One of the things I heard last week was reiterated in public at the GBN conference today, that being that in XI 4.x (no scheduled release date, but could be 2010) the Data Federator product will be embedded directly into the semantic layer. What does this mean? Among other things, it means we can have multi-source universes.

Explorer (Accelerated) Is Still Hot

The keynote from John Schwarz featured yet another demo of the accelerated version of Explorer.

Xcelsius Is Still Hot

It’s hard to turn around without seeing another presentation for Xcelsius.

Old Stuff Still Works

My presentation had slides in it that I lifted from the very first Variables presentation in 1997. It still works. Steve Krandel updated a presentation from five years ago. It still works.

Text Analytics Should Be Hot

Several years ago Business Objects acquired a company that did really cool stuff in the unstructured data (text analytics) space. They should be getting more press; their systems have been integrated into Data Services now. I am planning on going to a text analytics presentation tomorrow.

I also signed up for a “usability session” on the new semantic layer tomorrow. It should be another good day.