Lost in the Universe

I joined the world of BusinessObjects consulting in mid 2002, and, since then, I’ve worked with many, many clients, and reviewed well over a hundred universes. During that time, I have collected a lot of Best Practices in universe design. I’m certainly not alone in that, however. Every universe consultant has a list of Best Practices, and many of them have published those lists. So, universe developers have plenty of opportunity to find common lists of Best Practices in universe design. Therefore, I am avoiding publishing such a list, at least for the time being.

This article is just the opposite. You see, over the years, not only have I developed a list of Best Practices, but I’ve also developed a list of Worst Practices, common mistakes in universe design. That’s what this article is about. I’m going to take a look at some of the most common errors in universe design, and make suggestions on how to correct them.

Creator of the Universe

So, the first, and by far the common mistake, is making sure that the right people are involved in the development of the universe. No, that wasn’t a typo. I said people, not person. Even if there is only one person in your organization who is qualified to develop universes, that person doesn’t work in a void, all by themselves. They need to get many other people involved in the process.

Many years ago, while at a client site to do training on Desktop Intelligence, I met the consultant who was building their universe. He showed me the universe. Admittedly, not working there, and not understanding their business model, I was not prepared to understand their universe completely. But, after a brief conversation with this other consultant, I learned some startling facts:

  • He had never taken the Universe Design class
  • He had a meeting with users, once, prior to beginning his development
  • Once development started, he worked in isolation, until he had fully developed the universe

I knew this situation could only end in disaster, so I sat off to the side of the room while the new universe was presented to the users. The results? The users went ballistic, nearly starting a riot. The design of the universe didn’t make sense to them, didn’t meet their reporting requirements, and confused them. So, what should have happened?

80% of the time spent developing a universe should be spent long before opening the Designer application. During that time, the developer is doing research to uncover the requirements for for the universe. This includes meeting with the following people, as much as possible:

  • Application developers. These are the people responsible for the application(s) that load data into the database(s) that we will be using. For example, if we’re building a universe on customer service data, we should learn about the application(s) that the Customer Service Reps (CSR) use to enter data. We want to find out if data is being typed in, or selected from menus. We want to learn if there’s a chance that data could be entered incorrectly.
  • DBA. This is a big one. The DBA will be one of our best resources during the entire development cycle. We need to understand where the data is. Are we building on a transactional system, data warehouse, or data mart? Are there aggregate tables? Are there database triggers that might affect data flow?
  • Users. I recommend assembling a small group of power users. These are people who will be developing reports from this universe, for other people. Keep them involved throughout the process. This is critical. This universe is for them. So it must meet their needs. Let them see your work frequently, offering feedback. By doing this, you allow them to take some ownership for the universe. They will also be your evangelists to all the other users, once the universe is published.
  • Management. That’s right! You must meet with management. It’s important to understand what they will gain from this universe. No, they won’t be building reports. But they will be consuming reports built from this universe. So, we need to understand, what are the business questions that they will be looking to answer from this universe? Remember, this isn’t your universe. This is their universe.

You’d be surprised at how few universe developers actually do the proper research. But trust me, you don’t want to publish a universe, only to find out that you have to rework the entire thing. You also don’t want to waste time “selling” it to the users. If you properly engage your power user team, they will do the selling for you.

It’s All About The Users

That’s right. No matter how much time I spend on a universe, it’s not about me, it’s about the users. So I need to make the universe as user friendly as possible. Whatever I can do to make life easier on the users, I do. If I need to spend an hour developing something that saves a user five minutes, I’ll do it. I want happy users.

Here’s an example. Have you noticed that, when building a new query in Web Intelligence, the top class is open by default? I hear from users all the time that they find this annoying. Here’s a couple ideas on how to deal with that. First of all, you can put your smallest class at the top of the universe. This will make it less annoying.

Better yet, create an empty class at the top of the universe.

An empty class cannot be opened. This will completely resolve the annoyance.

Along those same lines, do your universes have so many classes and subclasses that the users get confused? This makes it hard for the users to find what they need in the universe. Well, perhaps instead of many subclasses, you can use separator objects. What is a separator object? Well, it looks like a Dimension object, but is used to separate groups of other objects.

The names of these objects makes them look different than the other objects, and sets them apart as separator objects. Visually, they act as separators, but make the universe less confusing than using subclasses. However, we need to be sure that users don’t try to use them in queries, as these objects have no Select statement in them.  So, we open the object and look at the Advanced tab. Uncheck all three options on that tab.

This will prevent users from using these objects anywhere in their queries. Your universe has taken another step toward user friendliness.

Here’s another common mistake that I see quite often, and really leaves users feeling lost in the universe. Make sure that your object names are user friendly.  See if you can spot the problem with these object names.

When you create an object automatically from the CLIENT_ID column in the database, the name becomes “Client Id”. Don’t leave it like that. Make it “Client ID” instead. It just looks like what the users will expect.

Now look at the next object. It has two things wrong. First of all, object names should never have underscores in them. In fact, underscores should never be used in anything seen by a user. Why? Because it sends a message to the users that this universe is technical. It scares them. Not like a zombie at their front door would scare them. More like the kind of scare that you get when you fear you won’t understand everything you need to. In the IT world, we like to use underscores. But for end users, just avoid it. The second thing wrong with that object is that the name is just not user friendly. Never use “No” in place of “Number”, even if that is how it is labeled in the database. The word No is the opposite of Yes, and confuses users. Come to think of it, why do we use No for Number in databases? That doesn’t make sense to me, either.

OK, now look at the third object. If the column in the database is labeled “Addr”, name the related object “Address”. Again, it’s all about being user friendly. Remember, the object name becomes the default column name on the report. Don’t cause the user more work than needed.

One more BIG thing about objects. This is one of my pet peeves. Every object should have a description. Every object. No exceptions. Even if you think the name of the object is self explanatory, add a description as well. You can even color code the descriptions to make them even more user friendly. Remember that group of users we talked about earlier? This is the group that will be your helpers throughout the process. Have them help you with the descriptions. That’s a good way to make sure that the descriptions make sense to them.

OK, let’s talk about Lists of Values (LOVs). I had a hard time with these when I first started developing universes (Way back in the second millennium). First of all, try to run all of your LOVs against dimension tables, even if the related object is running against a fact table. This will make the LOV generate and refresh much faster. This will be important until such day as we can schedule LOVs to be refreshed overnight.

Cascading LOVs work nicely in Web Intelligence. They don’t really create a cascading LOV. They create a hierarchical LOV. But that works well. The hierarchical LOVs that we create in Designer don’t work well in Web Intelligence (They were designed for Desktop Intelligence). So, unless you’re using Deski, avoid them. Note that there is no way to do a true cascading LOV for Web Intelligence. Also, if you have a LOV for an ID field, it can be very helpful to add a second column to the LOV, which displays the description for each ID.

One of the last areas of a universe to get attention is Hierarchies (soon to be called Drill Paths, in BI 4.0). I’ve seen many universes in which no attention was ever paid to Hierarchies. But they are needed for any serious analysis of data by your users. So spend some time on them. My first rule of Hierarchies is this: Always use Custom Hierarchies. The default Hierarchies are not all useful, and the only way to eliminate them is to use Custom Hierarchies only. Even if you have no requirements for Hierarchies, create one Custom Hierarchy, just to eliminate the default ones.

Now let’s talk about Contexts. Are your Contexts user friendly? Yes, you read that right. Contexts should be user friendly. First of all, it should be extremely rare that a user gets prompted for a Context. Usually, if a user is getting prompted for a context, it means of of the following is true:

  • The user has included no measures in their query. Remember, measures usually, though not always, come from fact tables. So, if a measure is used in the query, the Context should be inferred, and the user won’t get prompted.
  • Something is wrong with the universe.

If you have users getting prompted for a context, is there something you can do to prevent that? Perhaps. You can infer a Context for specific objects by adding additional tables to the From clause of that object. Open the object, click on the Tables button, and Ctrl-Click on the required tables needed to force the object through a specific Context. Adding in a related Fact table should do the trick.

Now don’t flame me for saying this: in rare cases, it’s OK for users to get prompted for a Context. A Context prompt is no different than any other prompt. It simply allows the user to choose an option at run time. So, if this going to happen, make sure that your Context names are user friendly (No underscores, and use proper case), and they have user friendly descriptions. Make it as easy as possible for the user to choose the correct context.

By the way, if you have Contexts in your universe, and this universe will be used by Crystal Reports, you’ll need to make one slight change to the universe parameters, since Crystal Reports doesn’t support multiple SQL statements per query like Web Intelligence does. Open the universe parameters, and go to the Parameters tab. Find the JOIN_BY_SQL parameter, and change it to yes. This will cause the query to generate one SQL statement with a FULL OUTER JOIN between multiple Select statements. This allows your universe to work with Crystal Reports, and, as an added bonus, it makes the universe perform better for Web Intelligence, as the multiple Select statements will be joined at the database rather than in the report.

One final thing on user friendliness: Make sure you format ALL numeric objects in the universe. Don’t make your users format them on the report. Do it for them. All measure are numeric, so all of them should be formatted in the universe. But don’t forget the dimension or detail objects that return numeric data. They should be formatted, as well.

Be Nice to Your Co-Workers, Too

You may, or may not, be the only universe designer in your organization. In either case, it’s best to design your universe so that other designers have an easy time of taking over where you left off. This will also help you when you come back to this universe in six months to do maintenance on it.

We’ll start with the Comments field. You’ll find it on the Summary tab of the Parameters window. This field is not visible to the users, so whatever you put in there is communicated to other designers, or to yourself. As a consultant, I use this field to add my name and contact information, so designers can contact me if they have any questions about it. You can also use this field for release information, or anything else you feel other designers might want to know about this universe.

Speaking of Comments, you can also leave comments in the Structure Pane of the universe. Just click in the Structure Pane, and start typing. This is another great way to communicate to other designers. For example, if you create a Derived Table or Alias Table, you can type comments next to it explaining what it is used for, or why you created it.

One of my favorite tricks for documenting universe releases is to create a hidden class in the universe called Release Notes. In this class, I create an object for each release of the universe. The object name is the release date, and the object description is information about that release. Since the Release Notes class is hidden, this information is available only to other designers, not the users. Also, if you save the universe as a PDF, it includes these objects. So your universe documentation includes information on each release.

Have I written enough here? I hope you find it helpful. OK, it’s your turn. If you have any thoughts or additional tips, feel free to leave them in the comments.

Advertisements

14 Responses to Lost in the Universe

  1. Jamie Oswald says:

    Very helpful. It’s so sad we still need to have these posts!

  2. BO_Chief says:

    Michael,

    Very informative and innovative. Great …. that’s giving us new ideas.

  3. yoav says:

    Thanks Michael for this article.

    Methods I use to make life easier:

    1.Add the End_SQL parameter in order to get the user, doc name & universe in the DB tracking tables.

    2.Do not allow Cartesian product – tick it as “prevent”.

    3.Classes that do not have a logical \physical relation to other classes – right it in the class description & mark the class with special characters in order to make the user a legend.

    4.For better performance don’t allow descriptions to be drag to the condition, create an LOV with the code & description or use if possible index awareness.

    5.Build a time table that contains all the “hard” time functions (last month to current week) – reduces the need to build functions & can be used instead of dimension LOV’s.

    6.Build pre-defined filters, a lot of them! Show the users that almost everything can be dragged.

    7.Don’t create objects with the same name! Give if needed a prefix or a more orientated business name.

    8.Implement sub queries & exist/not exists logic in pre – conditions in the universe for more complex questions

    • Great stuff, yoav. Thanks for sharing those tips.

    • John Clark says:

      yoav, what variables do you use in the END_SQL parameter for the user, document name and universe? I like this option and want to implement it in our universes.

      • Hi, John. Use the following variables:
        User: @variable(‘BOUSER’)
        Document: @variable(‘DOCNAME’)
        Universe: @variable(‘UNVNAME’)
        Query: @variable(‘DPNAME’)

      • John Clark says:

        Never mind. I found what I was looking for.

        –user:@variable(‘BOUSER’) –universe:@variable(‘UNVNAME’) –doc name:@variable(‘DOCNAME’)

      • Yoav says:

        John you are fast 🙂

        I have added another variable that might be usefull:

        @Variable(‘DPTYPE’) = free hand\deski\webi report

  4. Naresh Ganatra says:

    Very well written !! Keep up the good work Michael !!

  5. Nix says:

    Along the llines of your ‘separator objects’ I use — (3 dashes) followed by the tab character using Ctrl-V after copying it from Notepad (open notepad press Tab, press Ctrl-A, press Ctrl-C).

    The first separator has no tab character and then each one after that I add an additional tab character.

    Designer sees them as unique names but they show up in WebI as just —, i.e the tab charatcers are not seen.

    So you get something like

    Object 1
    Object 2
    Object 3

    Date Object 1
    Date Object 2

    Measure 1
    Measure 2

  6. WILLIAM MILLER says:

    I have implemented the concept of dynamic dates in a universe. Dynamic dates is where the object is a Date like “Last Monday Date” the object is normally used in a filter (where clause) so the query never needs a prompt for a date or worst yet edit the query. So when building a query after the condition relation you select an object. i.e. “Assigned Date Equal to Last Monday Date”. This approach saves people time and makes scheduling a breeze.

  7. Naresh Ganatra says:

    I like the idea of the empty class Micheal, though it seems to work only when you have atleast one object (which could be hidden)
    And it matters only for the Advanced mode , query panel.

    Is that consistent with what you have seen ?

    Regards,

    • Naresh, I have noticed this with certain versions of BusinessObjects. It seems to work fine in some versions, and not in others. I guess consistency is too much to ask for. 🙂

  8. Pingback: Performing a Universe Review « Michael’s BI Safari

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: