Contributed by Herb Hess, BOCP & BOEP. When Herb isn't building world-class Business Objects Enterprise solutions or teaching, he takes his kids to the beach or roller skating and serves as Mayor in exile of Haddonfield, NJ.

Advanced Universe Design: Improve Skills, Database Performance, and Business Performance

Designing a Universe is not a simple task. We’re not talking about some metaphysical act of creation here, simply building the easy-to-navigate interface that let’s non-technical managers and executives build their own reports and easily interact with dashboards from BusinessObjects Edge/Enterprise.

In this article we’ll explore a course offering that goes beyond what many Universe Designers already have mastered. The results obtained after taking this course include reduced Universe Design hours needed and improved database responsiveness. In non-tech speak, less time spent waiting for your report or dashboard to deliver the answers you need and more time spent analyzing the results to improve your business.

If you are not familiar with what a Universe is or what it can do for your business, here is a simple explanation.  Most software programs do a great job of collecting data.  Most data becomes hard to extract and difficult to validate after it is in the database.  A Universe resolves the complex issues surrounding getting to the data, saving your IT staff lots of time and providing trusted and easily accessed information to those who need it most, your employees.

CNE offers education on Universe Design through our course DM310V30, BusinessObjects XI 3.0: Universe Design.  Fundamental skills required to become proficient are developed in this course, and students leave confident that they can now enable better reporting at their own companies and organizations.

Often, the students I meet in this introductory course have no prior experience with Universe Design.  They often have a specific project to undertake or are joining a team of others to begin developing Universes for other business units to use.  While they are often satisfied, if not a bit saturated, by the material presented, students who have already designed Universes often ask questions that are answered by DM351V30, BusinessObjects XI 3.0: Advanced Universe Design. 

What are some of those questions that students pose?  Better yet, what are the questions that Universe Designers should be asking in order to see measurable database query performance and corresponding increases in business performance as well?

First, I’ll share with you some student questions.  LOV’s (List of Values) are the pick lists presented when prompts are used in Business Objects reports.  Many students want to know how to add choices such as “All” to the top of the list, or how to organize the list using an index such as the first letter of the last name, when prompting for a long list of customers.  Topics such as these offer a quick payback in end-user satisfaction.

Universe Designers often know of or have written custom functions in the underlying database.  These functions often calculate answers that are specific to the needs of the business, from currency conversion to productivity ratios, etc.  There is no need to re-invent these functions in the Universe itself.  Learners develop the knowledge needed to allow the Universe to directly call on these custom functions.  This reduces Universe design development time and enhances the information found in the reports.

One significant topic which has been moved into the Advanced Universe Design course is Aggregate Awareness.  As we all recognize, the longer it takes to deliver a report after hitting the ‘View’ button, the less interested our audience will be in waiting for the results.  Aggregate Awareness makes your Universe aware of the ability to pull results from pre-aggregated tables (sometimes known as summary tables.  If your database does not currently have summary tables, we’ll review another topic called derived tables where you can simulate the performance gain from having summary tables, without actually building these database objects.)

Results are viewed quickly, without losing the ability to drill down into the more detailed level of the report.  Aggregate Awareness builds the logic that ties that definition of a data object, such as Net Sales to both the summary tables and the detail level tables within a database.  When a user drills down from Company to Division to Region to Office to Customer, Net Sales may be recomputed from two, three, or more different source tables.  The report viewer or analyst will never be aware that this is taking place.

Many learners come to class looking for relief from long-running database queries.  These queries often crowd out other report processing which could support better decision-making, or they may simply slow down the flow of information to users when it is most urgently needed.  Advanced Universe Design reveals techniques which speed up queries by removing unnecessary joins from queries, using Primary and Foreign Key fields for lookups instead of non-indexed fields, using Analytic Functions found in Oracle and SQL Server 2005 to develop statistics across groups of records, and passing hints and other pre- and post- processing commands to the database.

Finally, we’ll examine the issues surrounding moving Universes between various environments such as Development, Test or QA, and Production.  Often our students find that they have been moving Universes and connections between these environments in a way that causes them to have to reattach reports to Universes after each move.  Our course will deliver techniques that promote proper Universe and connection object movement while preserving links between these objects and the reports that rely on them.

I hope we’ll see you in class.

Tell us what you thought about this article at cnenews@cne.com.