Dave's Adventures in Business Intelligence

I have done conference presentations before. I have done more than one presentation for the same conference. I have even done a double presentation (a longer two-part session stretched over two hours) before. But never have I done two unrelated sessions, in different rooms, in back-to-back time slots on the same day. 😯 I did my first session as a co-presenter with Brian Durning, where we talked about picking out the proper client tool to use. As soon as I got finished there, I had to shut down my laptop and get to my next session, which was all about managing changes in a universe design. I was quite pleased with the turnout for both sessions, and also pleased with my delivery. Some might think that after so many years of doing this it becomes second nature… and some parts do. But every year is a new subject and a new challenge, and I always want to do my best to share what I have found to be useful.

Brian and I had a lot of fun with our first session. The fact that we work together (and occasionally play together) really helped with the presentation style, which was very easy and laid-back. We created three specific (and exaggerated) scenarios that showed that Web Intelligence was the best tool for casual or interactive reports, Crystal is the best for reports with prompts, and Xcelsius provides the only tool with “what if” capability. I will be posting the presentation file here shortly.

The second session from me today was related to the concepts of change management for universe designers. I am often asked questions like:

• What is different between these two universes?
• What changes were made in “break fix” that now have to be applied to development?
• What are you getting ready to move into production?

I concluded the presentation by doing a live demonstration of a universe change detection script that I have written in VBA. I took several suggestions for changes in a universe from the audience (change an object name, remove some joins from a context, and change a join from a shortcut to a regular join) and my script found them all. Whew! I will be posting that presentation here shortly, and the script should follow soon after.

There is also a lot to talk about regarding BOB and ASUG and other developments, but that’s worthy of a separate blog post to come later.

That means that the good news is that I’m done with presentations for this conference! The bad news is that I have not done much of anything else (other than saying “hi” to some old friends and meeting some new ones) so I don’t have anything to report. Tomorrow I hope to get to some other sessions and be able to share some tidbits from the conference.

Until then, it’s time for a good night’s sleep. 😎

I’m not participating in this podcast, but Jamie Oswald gives me a nice (and appreciated) shout-out. Along the way Jamie, Eric, and the rest of the crew talk about “must see” presentations and other plans for the BusinessObjects user conference coming up in a few weeks. It’s worth a listen.

SBOUC Pre-Conference Podcast

I am happy to be able to (finally!) report that my universe “diff” tool (first mentioned a long time ago) is nearing completion. I have been using this partially completed tool on my own for many years, but the only things it compared were objects and predefined conditions. As such it was somewhat useful (mostly to me) but since it was not complete I did not release it even internally to my employer. When I started to extend the code to compare joins, tables, contexts, and other universe components I soon realized that the way I had originally written the code was not very modular. I was going to have to either write specific routines to compare each universe component, or go back and rewrite what I had in order to make it more generic. I didn’t do either of those things. 🙂

As of today I have completed the rewrite and am now going to extend the comparison to other universe components. I expect to have the final code ready to demonstrate (and release) during my talk Change Management Strategies For Universe Developers at the fall BusinessObjects conference. And of course once I release it there, I will also set up a way to download the code here on my blog.

Why use this tool rather than some of the other tools that are available? For one thing, it will be free. Free is good. 🙂 It will be released under the GNU GPL license, which allows me to retain the copyright on the code while ensuring that the code can be distributed without restrictions. Support for the code will be on an “as time allows” basis here on my blog. For more details on the GNU GPL license (if you are not familiar with it) I have included a link at the end of this post. Basically it says that I retain copyright of the code, but anyone can use it however they see fit. So there are no restrictions on using it for personal or company use, and it can even be redistributed. The only requirement is that the code remains free of all restrictions and that my copyright information be retained in a visible fashion.

I’m excited to be able to finally complete this project. When I submitted the abstract for the conference I was hoping that it would get accepted, and that would be the incentive for me to get around to completing the code. It seems to have worked. Now all I have to do is complete my slides and get them submitted (a week late). I look forward to being able to share the results and the code in Orlando.

Related Links

• GNU GPL V2

Yesterday I mentioned my first talk that I will be giving at the fall BusinessObjects conference this year. Brian Durning and I are going to compare and contrast Crystal and Web Intelligence. But that’s not all. In the session immediately following that one I will be giving a talk about change management for BusinessObjects universes.

Session Code: 804
Title: Don’t Lose Control: Change Management Strategies for Universe Designers
Day and Time: Tuesday, October 05, 2010, 2:45PM – 3:45PM

My final material is subject to change as I see how long each section gets, but here’s a brief overview of the areas I am considering covering.

• Impact of database changes
  What happens to my universe when my database changes? How can I address those issues in a way that causes the least impact on my reporting team?
• Impact of universe changes
  What happens to reports when I change my universe?
• How can I compare two versions of a universe to see what is changing?
  This is the big one… several years ago I started a script that allows me to check two different versions of a universe and see what is different. I have been using the script for a while now and it does most of what I need, but it isn’t complete. I plan to (hope to) get the code finished by October so I can distribute it at the conference, and of course here on my blog. The code will be released under the GNU GPL (open source license). Details of that license and what someone can (or cannot) do are found on the GNU GPL home page. I will demonstrate the code and how it works under various change scenarios during my presentation… assuming I get it all working the way I want it to, of course.

These were the ideas I had in mind when I submitted the abstract. Time will tell which of them make it into the final presentation. Anything that doesn’t make it will very likely show up here as blog posts.

At some point, anyway. I mean I only started working on my universe change script in 2007… 😉

I submitted several abstracts for consideration for the fall conference this year. The one I was the most excited about got accepted into the Webi/Deski track, and is called, “Discovery Versus Delivery: A Comparison of Client Reporting Tools.” Here is the formal track designation information.

Session Code: 603
Title: Discovery Versus Delivery: A Comparison of Client Reporting Tools
Day and Time: Tuesday, October 05, 2010, 1:30PM – 2:30PM

What is this talk about? A while back I wrote a post about a very short (single page) slide from SAP that shows how to pick which tool for a particular problem. For this presentation, Brian Durning and I will be doing a demonstration of strengths of two of the most important tools from BusinessObjects: Crystal and Web Intelligence. We plan to cover (and demonstrate!) several scenarios that should help show where the strengths of each tool lie. I will be covering Web Intelligence, and Brian Durning will be covering the Crystal side of the house.

It should be fun. 😎

Related Links

• Related Blog Post: Delivery Versus Discover

A few weeks back I got an acceptance notice for an abstract I had submitted to TechEd this year. TechEd is in Las Vegas and I will be speaking about universe design to SAP folks that want to learn more about how to use BusinessObjects tools outside of their ERP/BW environment. It should be fun.

Earlier today I got notified that I have also been accepted to speak at the ASUG fall conference in Orlando. This is the conference that replace the Insight conferences put on by BusinessObjects these past few years, which were then followed by the two GBN conferences in Dallas. Since GBN has been folded into ASUG now our fall conference is now under their umbrella. If you are a “classic” BusinessObjects customer who wants to continue to see new and exciting things that you can do with your tools, this is the conference to attend. There will be some great stuff coming from SAP employees about XI 4 that you won’t want to miss, as well as (I am sure) a number of “can’t miss” sessions related to XI 3.x (and perhaps even earlier versions) that will help you make the most of your current environment while we all wait for XI 4 to be generally available.

In a few days I will post more details about my abstracts. I look forward to seeing everyone in Las Vegas or Orlando or perhaps even Both. 🙂

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”

I got an email yesterday saying that one of my abstracts has been accepted for the 2010 edition of SAP TechEd. I will be talking about universe design over non-ERP data, showing the SAP folks that you can build universes on more than just cubes. 🙂

The selection process for the fall BusinessObjects conference in Orlando is continuing through July 2^nd. I submitted several abstracts for that conference as well, including one on universe change management that will feature a (hopefully) completed version of a script to compare universes that I started writing years ago. I am using the script today (thus the abstract) but it’s far from completed, and certainly not ready for public consumption.

As a side note, the “Conferences” category on the right menu for my blog has continued to grow. I experimented with a couple of WordPress plugins that would allow blog readers to expand / collapse the categories as needed, but so far neither of them have worked to my satisfaction. Either they didn’t work in all of the browsers I tested, or they looked bad, or they were not as functional as I would like. But hopefully I will get something there soon in order to avoid having that category take over the entire side of the screen.

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”