As I’ve learned in my first BI project using BusinessObjects XI 3.1, universe planning & design is the cornerstone of a successful business reporting implementation. In BO, the term “universe” refers to a file (created using a visual modeling tool called Universe Designer) that serves as a semantic layer between an end user reporting interface and a relational database. It’s the middle tier that translates the details and complexities of a database schema into a business model from which end users build formal reports and perform ad-hoc queries. Attaching to a data source and dragging columns around a visual palette seems simple, but in practice it actually takes a lot of planning and iterative design to build the underlying data model and ETL processes, reflect accurately the cardinalities and relationships of the tables in the universe, and produce a semantically relevant business model to be used by non-technical users. As someone with an application development background, it’s been a challenge to shift my focus to an entirely data-centric mindset. However, it’s provided me with some invaluable insight into BI modeling that I had never considered in the past, and which I believe will make my perspective on applications more well-rounded going forward.
While you are in the weeds of building your universe, there are a lot of design considerations and nuances of the platform that will certainly cause some frustration. I’m eons away from being authoritative enough to provide anything close to a tutorial on the subject, but being in learning mode in this adventure, I do have several tips that I hope can save you some time. Some of these I learned through trial and error, while others I have picked up from SAP’s documentation, user forums and the ubiquitous blogosphere. The tips I share will be more platform-specific and not necessarily about data modeling in general (I’ll avoid topics like the practical application of a star schema and fact and dimension tables). The tip today involves a likely common data measure scenario. A measure is a numeric object based on a dimension that has been aggregated in some manner, such as a count, sum or average. A measure might be something like total sales, number of orders or average age of employees.
When setting up the measure object, you will have an object properties box which allows you to map your measure using a Select statement as well as a Where clause as shown below.
With that said, let’s say you have several measures that refer to the same column from a table but are calculated based on another column, such as category. The end result is that you want users to be able to use all of these measures in a single query so they can be placed on a single report. You may be inclined, as I was, to put your select logic in the select box, and then use the Where box to isolate each measure based on your other criteria. Seems intuitive enough to me, especially since the Where box is right there below the Select box and SELECT and WHERE are right out of SQL 101, right?
When configured like this and consumed together in the query environment the result is an empty data set. When used individually, however, they return the aggregate data as you expect. What gives? The problem is that when the query executes these measures, it generates mutually exclusive Where clauses in the SQL for each measure, which is not what you are trying to achieve. The solution is to put Where logic in the Select statement itself, for example using a SQL SUM function and CASE operator.
Hopefully this tip will save someone some time and provide others new to BO some insight of how the Select functionality can be used.
August 12, 2010
Business Objects, Universe Design