Dave's Adventures in Business Intelligence

I don’t like outer joins in my reporting universes. Never have. Sure, if I am creating a universe against an application system I might consider using outer joins because of the normalized nature of the data. But if I am reporting against a warehouse schema of some kind, I really prefer to use inner joins. That way I avoid any potential performance issues caused by outer joins, but more importantly I avoid questions about report data. That being said, outer joins do have a specific purpose, and if I need to use them in my universe I certainly can.

One of the biggest challenges with outer joins (other than potential performance issues) is explaining to a user why their query results changed because they added a condition to their query. Remember that users don’t (typically) look at the SQL, so they won’t know that I have created an outer join. It can be confusing. Fortunately I have options as to how my outer joins are executed, so once I determine their usage requirements I can change the way my universe behaves.

Defining the Problem

For this post I will am going to use a very simple universe with only three tables, shown here.

This universe joins a customer to an order, and an order to order lines. In my database I have one customer that does not yet have any orders. If I run a query against the current universe structure, this new customer will not show up. My requirement is to show all customers, whether they have orders or not. This must be true even if I put a condition on the order table. That’s where it gets tricky. 🙂 Continue reading “Handling Conditions on Outer Joins”

A while back I was on a project where the users wanted to set up reports that initially displayed about six different dimension objects and a bunch of measures. They also wanted to have the flexibility of dragging a different set of dimension objects on the report and either adding to or replacing an existing dimension. The idea was good. The amount of data brought back was a problem. I was able to fix that with some interesting prompt objects in the universe.

The Problem Definition

For the example I will present in this post I will once again use my version of Island Resorts Marketing universe which I have converted to Oracle. I will create a report that initially shows the Resort and (for simplicity) a single measure (Revenue). The report will be designed to let the user drag on additional details like Service Line and Service. But I will design my objects in such a way that if the user doesn’t want to see the information at that level of detail they don’t incur the overhead (row count) simply because the object is present in the query. In order to accomplish this, I will prompt the user with a list that includes the tokens ‘Resort’, ‘Service Line’, and ‘Service.’ The user will select the lowest level of detail they expect to use on the report. In this particular example the selections are hierarchical, meaning that selecting ‘Service Line’ implies that the Resort data will also be present. There is another option ‘None’ that can be selected if they want to deactivate the entire list.

Note that XI 3.1 offers a new feature called Query Stripping (in service pack 3) that works for BW and other OLAP queries and does this process automatically. It is not (yet) available for relational databases. Continue reading “Too Many Objects? Too Many Rows? Try Prompting For Level of Detail”

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”

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”

We are using the OpenDocument() function to “drill” from one document to another. In order to make report maintenance easier I have created some objects in the universe that contain the proper syntax for the URL required to access this function, as well as one that contains the report path. This way even if we change our folder names or structure I can change the universe and do not have to update every report on the project. This has worked very well for us.

Until my current project.

On this project the primary distribution channel was PDF sent via email. Our users said that the links were not working. And of course every time I tested by logging in to Infoview the links worked just fine. After further investigation by another team member, it seems that our Report Path (in the format [Folder],[Sub Folder] was being truncated at the comma. As a result, the OpenDocument() function was looking for the reports in [Folder] and ignoring the full path. That was a bit of a problem. Continue reading “Fixing Report Path For Adobe PDF Viewers”

This has to be the parameter with the worst. Name. Ever. But let me start at the beginning.

Some databases require you to use actual column names in an ORDER BY clause. Like this:

select first_name, last_name, phone
from employee
order by last_name, first_name

Other databases let you take a shorter approach and sort by the position of the column in the select clause, Like this:

select first_name, last_name, phone
from employee
order by 2, 1

To be honest, I don’t like the shortcut. I would rather see explicit column names in my order by because that way I know exactly what is being sorted without having to refer back to the select clause. Another advantage is that if the objects in my select ever change, my order by is not affected.

There is a parameter found in the .PRM file for each database named SORT_BY_NO. When you see that name, what do you think it is? Every time I see it I assume that it is used to determine whether the SQL will contain numbers in the ORDER BY clause like order by 2, 1 instead of order by last_name, first_name. But that’s not what it does at all. Instead of doing what I described above, this parameter is used to determine if a query can be sorted by a column that does not appear in the select clause. That makes sense, doesn’t it? 🙄 It should be called SORT_BY_IN_SELECT or something. But it’s not, and here’s how it works. Continue reading “SORT_BY_NO=NO? Very Confusing…”

Five easy steps to crash your Teradata system:

• Step 1: Upgrade to Teradata version 13
• Step 2: Recognize that with this version a “distinct” query no longer returns sorted results
• Step 3: On the advice of Teradata, reconfigure your box with the “regression” parameter that makes distinct queries behave the way they did in 6.2
• Step 4: Send a Business Objects LOV query to the database that includes a DISTINCT keyword and a where clause with a couple of constant values
• Step 5: Watch the system reboot

That’s about what happened to us a few days ago. It wasn’t pretty. It took a long time to get our production box upgraded (and this after seeing development and Q/A roll through the upgrades with flying colors). Once the upgrade was finally completed, we had catch-up work as far as batch processing to do. Once that was complete the users got back into the system… only to see it sporadically reboot.

With a personal computer or laptop, a sporadic reboot is often a loose connection or faulty piece of hardware. We had not experienced anything like this on our database servers. Ultimately someone figured out that the following query was at fault:

select DISTINCT table.column FROM table WHERE table.column in ('A','B')

That’s a fairly innocuous query, isn’t it? At first someone thought the table was corrupt. Nope, it checks out fine. Next someone suggested that the data in the table was bad. Nope, I can query it just fine. Then we thought maybe the fact that there were some special characters in the where clause was the problem. Nope, they work fine too. Finally it was narrowed all the way down to the fact that we had a DISTINCT clause with the where clause and the regression parameter set on our database. Continue reading “Want To Crash Teradata? Give It Some LOV…”

Earlier this year I attended SAP TechEd 2009. Many of their sessions were lecture only, but they also provided a number of two or four-hour hands-on sessions. I selected one specific session in order to learn about improvements in the process used to build universes against SAP data sources like BEx queries. But of course I could not leave it at that. 🙂 I got to the session a bit early and started poking around on the laptop to see if I could get some hints as to what we were going to cover. While poking around I found a universe named “Foodmart” so I opened it. It was… interesting. Continue reading “Foodmart 2000 Universe Review – Part I: Introduction”