Information Design Tool Best Practices

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.
If 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.
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.