This article was originally published on BusinessObjects Expert 

When talking about common problems you see in SAP BusinessObjects universe design, Joshua Fletcher recommends a trio of best practices:

• Ensuring users are working with efficient universes
• Creating technically sound universes
• Improving the performance of universes

In a recent interview with me, Fletcher — who is national SAP BI manager at ASG Group and an SAP Mentor — expanded on his best practice about performance.

“Some universes I've seen, there are no measures in the universe,” he said. “Really, all the queries that will get generated will just return all the results from the universe. Then any aggregation and any definition of an object as a measure, instead of a grouping dimension, have to be done in a report, which obviously doesn't meet the user value.”

For more about universe concerns, listen to my podcast on BusinessObjects Expert with Fletcher by clicking here. If you’d prefer to read the discussion, the transcript of our conversation is below.

Scott Wallask: We're going to talk a bit today about universe design in BusinessObjects, which is a topic that's important to a lot of our readers here on BusinessObjects Expert. First off, Josh, how do you think people have reacted so far to BusinessObjects 4.0's new information design tool? Has that been an effective upgrade from the old universe designer?

Joshua Fletcher: There's been a lot of great functionality added. I started working with BusinessObjects when it was version six. The previous version six, because we're only at version six now, I guess. Coming full circle. I remember working with BusinessObjects version six and the universe was pretty much the same in XI 3.1. There really wasn't any development done around the universe. It's been a long time coming. Most people are really happy that it's had a big revamp. Overall, I think it's a very positive thing. SAP has done a great thing in actually letting you be able to stay on your old version of the universe while you're in BI 4.

All of those old universes that you've got, you don't need to upgrade them to the new universe version when you upgrade to BI 4. You can actually, just leave them in place. All your reports will keep running. If you want to then take advantage of the new functionality, you can then create new universes, or upgrade your existing universes one by one. There's a great stepping stone in order to get to the new universe version.

But, there's also, as there always is when you do this major upgrade in capability, there have been a few small things that are still waiting to be resolved. One of the things that is something that I've found in projects is object formatting.

When you're defining objects in your universe and you need to format those objects with a standard currency format, there's not a way to save a custom format. You can't format multiple objects at the same time. The workflow has actually gone backwards a step in that specific use space.

But, what I've found is great, is that SAP through Idea Place is actually asking for commentary from the community about what is working, what isn't working, and suggesting ideas. I know both of those things I just mentioned are both ideas that have been picked up. The universe development team is working to put them into new versions of the software going forward.

Wallask: For those that don't know,  Idea Place is a spot on the SAP website that you can contribute what you think is an issue that needs to be solved or an idea, and have people chime in on maybe what some of the solutions are.

Fletcher: You can sometimes interact with the developers, right there. The people will be able to comment. For those two that I just mentioned, SAP has actually posted comments on those ideas saying, "Yes. We know this is an issue and we are working to include it going forward." It's a great way to give back information.

Wallask: Aside from the ones you mentioned earlier, the object formatting, are there any other common mistakes you see in universe design these days? If so, what steps can you take to avoid those problems?

Fletcher: I'm trying not to get on my soapbox, but this is something that having gone through many universe projects, I've got one customer in mind when I think about universe design and how to do it best. I walked into a project where we had a lot of existing universes that had been built up over a long time. There wasn't any standard in place. One thing that I really like to share is best practices around universe development. I think that there are three areas that you need to focus on. If you don't focus on all three, then you probably could do better in your universe design.

First and foremost is focusing on making sure your business users, the guys that are actually using your universe to build reports, are getting a universe that they are able to use very easily and effectively. Business value first and foremost.

Secondly, and probably just as important, is technical soundness of the universe: Making sure that any query that gets generated from the universe is technically sound. It's not going to give them wrong results.

Then lastly, performance. If you can cover off the business focus, it's technically sound, then there's a lot of work you can actually do to improve performance in the universe. If I think about a use case from the technical focus in particular, something I like to talk about is proper definition of universe objects and in particular, measure objects.

Thinking back to this project, what we found is there weren't any standards around how to define objects. If we're thinking of measures, their primary purpose is to provide aggregated information.

Some universes I've seen, there are no measures in the universe. Really, all the queries that will get generated will just return all the results from the universe. Then any aggregation and any definition of an object as a measure, instead of a grouping dimension, have to be done in a report, which obviously doesn't meet the user value.

But then secondly, in terms of measure definition, if we are defining things as measures, we need to be defining how they get aggregated at a database level and in the report as well. That's SQL aggregation as well as projection aggregation.

It's important to define both of them so that you're optimizing the query that gets run at the database. An example might be if you don't have SQL aggregation, then it's going to generate a million records. If you do have SQL aggregation, it'll generate a thousand records. When that scales up highly and you have multiple queries, that's going to have a big impact on your database.

Then secondly, in projection aggregation, if you don't define that, once you get the information into your report, if you then try to aggregate that data within the report, if you've got three dimensions and you remove two, it actually won't aggregate the data. It doesn't understand how to.

That is a really important thing in terms of delivering business value. If the data doesn't do what they expect, or they have to do a lot of work to get the data to do what they want, then people aren't going to be as invested in working with your universes. That's definitely a focus point.

The last one I'll touch on is loop resolution. There'll always be SQL traps that we need to work with. There will always be loops that get introduced, especially, if we're working in a dimensional model.

What I would suggest to people is if they don't understand how to resolve loops effectively, then there are a lot of resources out there in the community. The two ways that are most used are aliases and contexts.

I've heard from some people there's some confusion around which one is the best one to use. Well, they actually suit different use cases. That's why I'd suggest that people really understand why a loop needs to be resolved, first of all. What are the different ways it can be resolved? What are the different use cases? Which one meets which situation best?

Wallask: In terms of some of these use cases that you were just talking about, such as trying to establish business value to the end user — at the risk of asking what may be an obvious question, or have an obvious answer, are there effective ways to test that design beforehand, so that if there are problems you can identify them quickly? Is it just a matter of getting a sample group of users in early in the game?

Fletcher: I definitely think the business users need to be involved from the start. As a universe designer, you have a critical job. Really, a universe will get a BusinessObjects or a BI project to succeed or fail. If the universe is done poorly, there's a risk the project might fail. If it's done successfully, the project will be a huge success. Really, you have a really critical role. A big part of that is engaging users as early as possible in defining the business requirements of the universe.

In terms of actually testing universe design, this is something that's actually improved greatly in BI 4. The workflow in previous versions used to be that you actually had to make some changes, export the universe to the server, then open up your reporting tool like Web Intelligence or Desktop Intelligence in XI 3 or 2, and build a report to test a particular change, and then reiterate. Go back and make some changes, and do that over and over. It was quite a clunky workflow.

Now, in the information design tool, unit testing — just creating a query and testing the results of your query generation — can all be done directly in the information design tool. What we're finding with development now is that you can just do a lot more work in the information design tool. Do all your unit testing before you can then actually engage end users to start testing it for you and meeting their requirements.

Wallask: Do you have any final advice for listeners out there who may be either in the midst of a universe design project right now, or maybe have one planned?

Fletcher: Definitely. If you are thinking of moving to BI 4, or you have moved to BI 4, get familiar with what's been added in terms of functionality. What's been improved. There is a lot more available now for a universe designer, and a lot more to get familiar with. But, it really does help in terms of your role. If it's been a while or if you haven't done it before, look at reviewing the process around how you're designing and developing universes, that workflow. How you're engaging with your end users and any standards that you may have, either written down or agreed standards. Document them and review them. Look for ways you can improve them to get a better outcome for your business.

Going back to business users, they're your customers. That's a really important thing to keep in mind. It's not the people that are consuming reports. Your customers are the people that are building reports. If you can make their jobs easier, then they're going to continue to engage you and to give feedback and help you keep the universe meeting business requirements.

Scott Wallask

Scott Wallask was the managing editor for the BI, BusinessObjects, and HANA content of SAPexperts. He has covered SAP for WIS since May 2010, with a focus on SAP NetWeaver BW, HANA, BusinessObjects, customer relationship management, supply chain management, and human resources. He has spent 19 years as a writer and editor, including stints as a reporter at several newspapers in the Boston area. For 12 years starting in 1998, he created in-depth content about fire safety and OSHA regulations in hospitals while working at HCPro, a healthcare publishing company. In 2005, he won a first place award for best instructional reporting from the Specialized Information Publishers Association for his series on fire protection efforts in nursing homes. He graduated magna cum laude from Northeastern University in 1994 with a BA degree in print journalism.

You may contact the author at editor@biexpertonline.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.