Universe Design Best Practices

I’ve always avoided writing a blog on universe best practices, primarily because there have been many others who have written some excellent blogs on the topic. However, I have had quite a few requests to write about it, and have finally agreed to compile my own list of Best Practices for universe design. After all, the universe is one of the best advantages SAP has over other BI tools. So, a well thought out, well built universe is key to a successful deployment.

SAP BusinessObjects includes two tools for building and maintaining universes: Universe Design Tool (the legacy tool formerly known as Designer) and Information Design Tool (the newer tool, first released with version 4.0). These Best Practices will, for the most part, apply to both tools. Where they don’t, I’ll be sure to point that out.

I categorize all Best Practices as follows:

  • User Friendliness: These are practices that will make a universe easier for end users to use. After all, what we do is for their benefit. Anything we can do to make the universe easier for the users, we should do. Keep in mind that happy users will say nice things about you to your boss. 😉
  • Designer Friendliness: At some point, another developer will take over the universe(s) that you have built. Let’s build our universes in such a way that they won’t have to reverse engineer everything we did, just to figure it out.
  • Database Friendliness: We want our universes to return the correct results, as fast as possible. So, it’s best to design them so that they generate the most efficient SQL for the database.

User Friendliness

A universe is only as good as the users think it is. No matter how brilliantly I design a universe, if the users don’t like it, it’s not a good universe. In fact, good universe design is critical to the success of the entire BusinessObjects deployment. If users don’t like the universes, they will abandon the system, and get the data they need some other way. But the more they like the universes, the more they will use them, and the more they will promote them among their co-workers.

Who is the best person to build a universe? Perhaps a Database Administrator (DBA)? Actually, while a good DBA will be critical to the process, they don’t speak the same language as the users. So, while they can build a universe that is technically excellent, the users won’t be able to figure them out. I usually prefer to have a Business Analyst, who know SQL, be the one to create the universe. It tends to be more user friendly this way. But, in order to do a good job, you must involve other people. So, with that in mind, here is a list of Best Practices for making your universes user friendly.

  • Before you start building the universe, gather a team of power users, perhaps 3 – 5 people, who will assist you in making the universe user friendly. Throughout the process, they can review your design and make sure that folders, objects, descriptions, etc., make sense to them. As an added benefit, these power users will be your marketing team when the universe goes into production.
  • Develop and use a naming convention for everything that the users see. Universe names should reflect the name of the system from which they draw data. Folder and object names should be user friendly. Never use underscores in anything the user sees. And never merge words together, such as PhoneNumber. Put a space between the words. And don’t use abbreviations except where it makes sense to users. For example, use Phone Number, rather than Phone No.
  • Objects should be organized in folders in such a way that it makes sense to the users. Design your folder structure ahead of time, and have your Power User team verify it for you. Do not create a folder for each table. Users don’t care about tables.
  • Within a folder, objects should be arranged alphabetically or hierarchically, depending on the needs of the users. You want it to be as easy as possible for the users to find the objects they need.
  • Never have more than three levels of folders. If users have to open more then three folders to get to the objects, they will get very frustrated. Use separator objects, rather than folders, to organize objects, especially when there are only a few objects. What is a separator object? It’s an object that doesn’t serve any purpose other than to provide visual separation between groups of objects.
    Separator ObjectsIf you create separator objects, make sure that they cannot be used in Results, Conditions, or Sorts.
  • Never create a folder for one object. Find another place for it.
  • If the same object appears in multiple universes, use the same name for that object in all universes. For example, don’t call it Client in one universe, and Customer in another. If it’s the same thing, call it the same thing.
  • Never use the same name for multiple objects within the same universe. If, for example, you have Year in multiple folders, which is probably a bad idea to begin with, give them different names, like Fiscal Year and Calendar Year.
  • Make sure that the universe has a user friendly name and description. Check with your Power User team, to make sure that these make sense to them. And remember, no underscores.
  • For date objects, never, ever create Automatic Time Hierarchy objects. Take the extra five minutes to create them the correct way. If you don’t know what Automatic Time Hierarchy objects are, don’t worry. You don’t need to know. These objects were a bad idea to begin with. In fact, IDT doesn’t even have this feature.
  • Only create objects that are needed for reporting. Don’t create an object just because there’s a column in a table. If it isn’t required, don’t create it. This will keep the universe smaller, and easier for the users to use. You can always add more objects later, as the need arises.
  • Make sure that your contexts have user friendly names and descriptions. And make sure that users aren’t prompted to select a context, unless you want them to have the option to select a context. You can add tables to objects to force them through a specific context. I find this very useful.
  • Lists of Values (LOVs) can be very helpful, or very painful, for the users. Make them as user friendly as possible. Build them from dimension tables, not fact tables. If it’s a large list, use Database Delegated, so the users can easily search for what they need without having to scroll through a long list. Use a static LOV for lists that never, or rarely, change.
  • Never use default hierarchies/navigation paths. They rarely make sense. Create at least one custom hierarchy. If you don’t, the default hierarchies will be made available.
  • Every object, without exception, should have a description. Make sure that the descriptions are approved by your Power User team. For measure objects, include the calculation in the description. Be thorough, but not verbose. Believe it or not, verbose object descriptions can affect query performance. Don’t ask why. I don’t know. But I’ve seen it with my own eyes, and confirmed it with SAP.
  • Format all numeric and date objects in the universe. Of course, users can change the format in the report, if they wish. But if you do it in the universe, users won’t have to reformat the data every time they build a new report.

Designer Friendliness

As a universe moves from one developer to another, we want to make sure that there is sufficient documentation within the universe, so that it’s as easy as possible for a developer to understand the universe. To that end, here is a list of things you can do to make your universes more Designer Friendly.

  • Keep the Structure Pane (UDT) or Data Foundation (IDT) well organized and clean. I like to place fact tables in the middle, and dimension tables to the left or right. I just find it’s easier to identify fact tables if I do that consistently. Don’t have tables overlapping each other. Try to keep tables that are joined together, close to each other. It just makes it easier to see the relationships.
  • If you create alias or derived tables, put comments next to them to explain their purpose.
  • Use a standard naming convention for Alias and Derived tables. I like to prefix alias tables with A_ and derived tables with DT_. This makes them easier to identify. And never use spaces in Alias and Derived table names, or columns. It can make SQL code tough to read.
  • When you create derived tables, write the SQL in such a way that it’s easy to read and understand.
  • In UDT, LOVs should be named the same as the object to which they belong. This makes troubleshooting much easier.
  • There is a comments box in the universe that only developers can see. Use it. It’s a great place to keep notes about the universe that other developers can use. You can use it to explain who built the universe, why things were done the way they were. Be verbose. You can put a lot of information in them.
  • Create a folder in your universe called Universe Release Notes. In that folder, create an object for each release of the universe. The name of the object will be the release date. The description of the object will be the notes for that release. Make sure the entire folder is hidden, so users can’t see it.
    Lost-ReleaseNotes
  • Always set cardinalities on your joins. It makes it easier to see how tables relate to each other. It also makes the detection routines work more accurately.
  • Only use Derived Tables when necessary. In fact, use them as a last resort. Often times, there’s a better way to do it.

Performance Friendliness

One of the most important things we can do for our users is to make sure that we are creating the universe in such a way that it generate the most efficient SQL for the database. The users will appreciate faster running queries, and so will the DBAs. So, keep these best practices in mind when building universes.

  • Don’t use Aliases to avoid creating contexts. Aliases and contexts have different purposes. Contexts, if properly built, won’t help your queries run faster, but will cause less confusion with the users.
  • Every measure object must have an aggregate function in the Select. No exceptions. If it doesn’t get aggregated, don’t make it a measure.
  • Use Index Awareness wherever applicable. This will allow the queries to search indexed columns, and can sometimes reduce the number of joins and tables in the SQL.
  • Set the ANSI92 parameter to Yes. This will generate more efficient SQL for modern databases. It will also allow the use of full outer joins in the universe.
  • If your database supports it, set the FILTER_IN_FROM parameter to Yes. This will move filters from the Where clause to the From clause and may eliminate some of the processing time at the database.
  • You may also get improved performance by setting the JOIN_BY_SQL parameter to Yes. However, this needs to be tested with some of your largest queries, as it can consume large amounts of database resources in very large queries.
  • In the connection, set the Array Fetch Size to at least 250. But test it at higher levels as well. Depending on the database, and network bandwidth, you could go as high as 1000 and get better performance.
  • Enable Query Stripping in IDT and Web Intelligence. This will remove unused objects from the SQL as it is sent to the database.
  • In the SQL for objects, keep the transformations to a minimum. For example, only use CASE statements when they are really needed. The more you do, the slower the queries will run.
  • Avoid clusters of tables that aren’t joined together. If objects shouldn’t be used together in a query, they should be in separate universes. Otherwise, you risk Cartesian Products.

Conclusion

When it comes to user friendliness, the bottom is this: If there’s anything that I can do in a universe to make it easier for the users, I should do it. If it takes me an hour to do something that will save 5 minutes for a user, then I will do it. If it saves 5 minutes for one user, it will save five minutes for more users. It will be worth the effort to have happy users.

As for designer friendliness, anything I can do to help the next developer understand the universe, I should do. It will also help me when I come back to this universe. It’s good to keep notes in a universe that will refresh my memory as to why things are done the way they are.

And, of course, a universe that performs well will gain appreciation from the users and DBAs alike.

I hope you have found this post to be useful. Feel free to share additional tips in the comments. I love learning from my readers.

Advertisements

11 Responses to Universe Design Best Practices

  1. K von Murphy says:

    I agree massively with this. Two questions:
    – “Believe it or not, verbose object descriptions can affect query performance. Don’t ask why. I don’t know.” => I do ask why. I’m baffled. But what do you mean as “verbose”?
    – Index Awareness: I’ve tried it twice or thrice on star schemas, and I’m not convinced. It has a high maintenance cost, as you must do it on every object on a fact table. Perhaps did I miss a thing.

    • I have a client who had a paragraph in each object’s definition. And the performance of the universe was very slow. Some of that was the loading of the universe, since there was so much text in the universe. But, as we started to shorten these descriptions down to a simple sentence, the loading, and running of queries, started to speed up. We checked with SAP, and they confirmed our findings, although they offered no explanation.
      As for Index Awareness, we only need to apply this to dimension objects, where there is a matching indexed code field in the database. For example, if there is a State field, and a State code field, we can apply Index Awareness to the State object.

      • K von Murphy says:

        Universe loading time seems a plausible explanation. Really strange but nothing surprises me anymore from SAP.

        As for index awareness: you have to setup the PK for all attributes on the dimension, and the FK of all fact tables for alla attributes, have you? Detect makes only a part of the job. And I’m wondering how delegated measures react.

        BTW, you could add a point on Aggregate_Aware on a dimension object (eg: Year is only in fact tables). Some customers love it, but I’ve seen incorrect queries when mixing fact tables in the same query.

      • When I set up Index Awareness, I always do it manually. It helps me to keep track of what I am doing. I will set up the PK on the dimension, and one or more FKs. This usually includess a FK on the fact table. This creates the opportunity to search the FK field in the fact table, and avoid the join to the dimension table, unless the table is needed for the query select.

      • K von Murphy says:

        I’ll try index awareness on my current project. But this really tedious when a dimension (such as time) has countless fact tables linked to it.

  2. Haroon Rasheed says:

    Hi Michael,
    Thank you for putting best practices. I reach to your document in search for finding best practice for design universe for different functional area (e.g. Finance, Marketing, Sales etc). Requirement is that such universe objects should be visible to only relative functional users. for example Finance users can see only Finance related Objects and same for marketing and sales. At the universe level I could not not see anything to set security at folder level in which we can put all objects related to particulat function. Could you please shed some lights what is best practice for this. I am researching on this but could not find anything. Also, object level security in Universe comes in Contolled, Private, Confidential and Restricted which I believe is not very flexible and very high maintenance.

    Thank you in advance.
    Haroon

    • Hello, Haroon;
      What you’re looking for is possible in Information Design Tool, using Business Layer Views. You can create views for each group, and then set security so each group can only see their view.

  3. Nick says:

    Great blog….just when you thought you knew everything about universes, Michael pulls something out of the bag!

  4. Pete J says:

    I love the universe release notes idea. Will be introducing this (and a few of your other suggestions) from now on.

  5. William Peck says:

    this is very good. I need a little help understanding contexts (with 1 year of BO under my belt, and as a hard-core SQL guy). I find them very confusing even as a developer, even when I finally understood their purpose. But users are completely baffled, why should they care which path the engine takes to get the answer ? I suppose the benefit of contexts is you don’t have to anticipate every query and mitigate with aliases. But what is the benefit to users ?

    • Thanks, William. Depending on your design, choosing a different context can result in different answers. If you’ve been through the Universe Design class, you’ve probably worked with the Motors universe. That universe has a context for car rentals, and a context for car sales. If you query Customer Name and Car Model, you will get prompted for a context. If you choose the rental context, you will get a list of customers, and the cars they rented. But if you choose the sales context, you will get a list of customers, and the cars they purchased. In this case, the context prompt allows the users to choose which context they want.

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: