BusinessObjects universe design best practices

Having relocated from the Silicon Valley to Bangalore a year back, I’m now working in an MIS – strategic reporting role. In my role to evangelize the use of BI best practices and tools, one of the foremost is that of universe design.  As a matter of fact, I’m currently being involved in formalizing a BI policy around the tools we use most – Oracle, Informatica and SAP Business Objects (along with migration from our legacy BO to the XI platform!) – so a lot of my current work is related to best practices, design guidelines and preparing unit test checklists for my team of developers.

So here goes my list of universe design best practices. Being the cornerstone of the Business Objects semantic layer, the universe design becomes one of the most important (next only to the data warehouse design if there is one, and foremost if there is none) aspects of getting the right data out there in time for analysis and decision making.

The best practices are grouped by the reporting area they belong to.

Universe design: object creation

  • Object and class naming should be in business terms – so that it makes sense to the end-user. This also reduces development overhead since reports can use descriptions out-of-the-universe, instead of editing headers or creating report level variables.
  • All objects should have help text or usage information – corollary from above.
  • Object formatting should preferably be done at the universe level.
  • Pre-build condition objects in the universe rather than forcing users to build conditions for reports.
  • Build logic into objects – translate code, common calculations etc rather than forcing users to do it in report variables.
  • Avoid using WHERE clauses in the object definitions; use CASE statement instead. In most cases, using WHERE clause will return incorrect results when similar objects are included in the result set, due to combined restrictions imposed by the multiple WHERE clauses.
  • Use aggregation in all measure objects – to push the aggregation to the database wherever the performance bottleneck is likely to be BO server and the database performance is optimal. Generally the database is much more powerful at doing aggregation calculations, and this also reduces the volume of data to be transported over the network.
  • All measure objects should include aggregation functions for projection. When this is not included, BO will not automatically roll-up the data in the report, which could result in incorrect data and analysis.  Note that in the 3.0 version of Designer, a new feature – Database Delegated projection function is available to take care of these anomalies while doing “averages” for instance.
  • Use Custom LOVs or cascading prompts to display LOVs where hierarchies and numerous values are involved.
  • Use relative date objects for scheduling e.g. Today, Yesterday, Previous Month etc. Create a separate class to contain these reporting objects – this helps in improving maintainability.
  • Use dynamic HTML in objects where required to avoid users having to build it in report variables – end users wouldn’t like to code hyperlinks themselves, but would love to have an object which when clicked can lead them to Google Maps for example.
  • Use contexts in universes having multiple fact tables – this helps in getting your measures (built from multiple fact tables) right.
  • Use derived tables to define measures dependent on multiple fact tables.
  • Use derived tables to reduce complexity of queries to be written by users or in place of views or procedures. A note of caution here: Use derived tables sparingly. If you have access to the database or DBA and can get views or tables created for the same purpose, go with it rather than using derived tables. This is not only to push the logic and work closer to the database, but also to take care of the performance and maintainability aspects. Exceptions to this include cases where your derived table may include a prompt which would restrict the number of rows returned and thus improve performance over a conventional view.
  • Reuse code with @Variable. Reuse interactive objects with @Where (if you use them at all).
  • Use @Prompt syntax for conditions and interactive objects where input values are likely to change or absence of prompt would lead to inaccurate values or unacceptable query response times. Also make sure regularly used conditions e.g. current year / latest date should not have prompts to avoid annoying users.
  • “To limit the number of objects created to avoid user confusion, build interactive objects with @Prompt syntax followed by additional OR clause to include “”All”” condition.

E.g. ‘ALL’ IN @Prompt(‘Enter Value or ALL’,’A’, ‘Class\Object’,multi,)

OR

Table.Column IN @Prompt(‘Enter Value or ALL’,’A’, ‘Class\Object’,multi,)”

Universe design: resolving join and performance problems

  • To resolve a chasm trap, define a context for each table at the “many” end of the joins.
  • To resolve a fan trap, create an alias table for the table producing the multiplied aggregation. Create a 1:1 join between the original and the alias tables. Modify the select statement to use the columns from the alias table instead of the original table.
  • Use of contexts should be evaluated w.r.t. use of aliases for resolving join issues, to take care of maintainability of code.
  • Integrity checks on the universe structure, parsing of objects, joins, contexts, detecting loops etc is mandatory. If you wish to use Business Objects to help you detect fan traps or chasm traps – you must set the cardinality on the joins. Do not rely on BO to suggest the cardinality – this is often erroneous, based on the records sample that BO fetches for each table.
  • Uncheck the “Multiple sql statements for each measure” option in universe parameters, if this is not required for resolving any join problems. This option should be checked if the measures being retrieved in the same query involve different tables. “Prevent Cartesian product” should be checked, as should there be limits placed on the number of records returned and the time for the sql connection – to prevent runaway queries which can bring the database down to its knees and cause an outage for all users.

Universe design: optimization / miscellaneous

  • Use shortcut joins wherever possible to reduce number of tables used in a query
  • Use aggregate tables /materialized views with aggregate awareness set up to improve query performance
  • Use keys instead of labels where possible to take care of index awareness benefits of performance and uniqueness
  • Use the JOIN_BY_SQL parameter to shift process from BO server to database wherever the bottleneck for performance is the BO server and the database performance is optimal.
  • Update the .prm files to enable access to custom SQL functions and improve help text
  • Do not use derived tables instead of aggregate tables.
  • Turn off LOVs for all dimension and detail objects that are redundant or not required. This prevents performance problems when users inadvertently click on the “Values” and the query sets to return all the IDs or other irrelevant data.
  • Consider using linked universes with a master kernel universe to ensure consistent dimensions across multiple universes

This list is certainly not an exhaustive one – but a work-in-progress. I’d update it as and when I compile more; meanwhile if you feel anything has been left out, drop in a line.

Advertisements

23 responses to “BusinessObjects universe design best practices

  1. Wow, I never knew that Business Objects universe design best practices. That’s pretty interesting…

    Like

  2. Hi, I recently blogged about exactly the same topic, with mostly similar points.

    http://geek2live.net/posts/universe-design-best-practices/

    Always good to see this more technical content being put online.

    Kind regards,

    Josh Fletcher

    Like

  3. Pingback: Universe Design Best Practices | ( Geek2Live.net )

  4. Really, It is very informative. Thanks

    Like

  5. Pingback: BO Universe Design Best Practices « Davydd's Business Intelligence

  6. Thank you very much for putting togather such a nice article. Very helpful. It is like a checklist for me while designing the universe.

    Regards,
    RaviKanth

    Like

  7. HI this resource is very useful for .

    Could you please provide the completed version of your work i.e. best practices, design guidelines and preparing unit test checklists related to Universe and reports.

    it would be greatly appreciated.
    i could provide you my email id as well .

    Thanks in advance

    Like

  8. Create a 1:1 join between the original and the alias tables. Modify the select statement to use the columns from the alias table instead of the original table. ///

    do we need to really used 1:1 or 1: m pls confirm on this

    Like

  9. Its really great post. But I am bit confused about use of Derived table as its use should be as minimal as possible.

    Like

  10. Hi,
    I have a requirement to build canned reports in WEBI XI R3. The requirements are such that most of the reports are on the dimension tables and needs dimension tables to be joined.

    From a starschema prospective we cannot join dimension tables. My way of looking at it is
    1.Build custom tables or views
    2.Free style sql

    I wanted to know your views from a best practice standpoint for my kind of scenario.

    Appreciate your response. Thanks.

    Like

  11. Is there any way around the BOBJ to BW limitation of only being able to connect a universe to a single BEx query? I know that BOBJ best practices tell me to limit the number of universes, which then seems to run contrary to the one query to a universe limitation.

    Any light you can shed on this will be appreciated.
    Thanks

    Like

    • Leslie,
      Not sure which best practices you’re referring to. What’s your use case? Till the universe rewrite with data federation, this issue can only be solved on the report with multiple queries. However note that you would not usually need a BEx query for every report or a universe for every BEx query. Also while BEx queries are usually recommended as data sources, you can also use InfoCubes. The BOBJ-SAP FAQ may be helpful.
      HTH
      Maloy

      Like

      • Hi,

        I love the way you put them together. Do you have any information on how to stabilize the environment. I mean, clean up process of Business Objects folders, naming conventions of the reports, template reports to the users to follow etc. We started cleaning of our enviroment and as you mentioned you are designing the rules for your team.Can you share if possible. I need some guide lines on where to start

        Your help is really appreciated

        thanks,
        Arun

        Like

  12. Hi,
    Its great!!!
    Can you explain bit more on ‘Pre-build condition objects in the universe rather than forcing users to build conditions for reports.’
    What are the benefits of having pre-build condition objects in the universe instead of in the reports?

    It will be helpful, if anyone can answer me quickly.

    Like

    • Zahid Mahmood Awan

      Pre-build conditions will help the users in selecting those pre-built filters for their purpose. e.g. you have the sales measure having dimensions of Cash or Credit (say you have C for Cash and D for Credit to complex it further (though in universe you should have already addressed this issue of making these single characters to proper business terms like Cash or Credit rather using it as C or D)). If user wants to see only Cash or Credit, he can put his filters but it would be easier for the user just to drag from the objects if these conditions are already defined in universe. Furthermore, it also helps re-usability in case of some complex or standard business conditions (e.g. some specific locations which are to be filtered based on some particular business logic). If any change comes to those conditions then no need of going into every report for its modification but it just gets corrected/ modified at universe level.

      Like

  13. hi guys please give the total universe design as soon as possible plz everybody

    Like

  14. What is the best way to create relative date objects for scheduling? Would this be best done at the Database level or the Universe level? If it is at the Universe level, do you have any examples?

    Like

  15. nice post

    Like

  16. good article and excellent information 🙂

    For comparing two universe versions you can try out UMT:

    Universe Management Tool by company named Saama Technologies. I found this tool as helpful and very time saving.

    sharing link below: http://www.saama.com/resources/products/umt

    Hope this is helpful for you. Thanks

    Like

  17. nice post and really helpful.

    Like

  18. Very Good article however, I did not see the use of views instead of connecting directly to tables in universe

    Like

  19. Hi Maloy,

    After some years from your post it’s very interesting yet. You said that you was working on more policies arount informatica, Oracle and SAP. I’m just doing the same.
    Could you have some best practices defined around mappint between OLAP and BO Universe.

    Thanks
    Manel

    Like

  20. Hi Maloy, I am very much impressed by your blog. I was looking at some of the best practices/guidelines that you have mentioned in above post.I am curious to know more on some of this points. Would it be possible to get your email address and I can send my questions via email.
    Appreciate your help and looking forward to get some learning’s from your experience. As I am still a beginner in this BI and Data analytics world appreciate any help that you can provide. Thanks a lot.

    Like

Share your views

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