( Geek2Live.net )

Yesterday I presented at the SAP Australian User Group’s (SAUG) Business Objects Special Interest Group in Perth, on Summary and Highlights of the MBO 2009 Conference.  As quite a few SAP BO users in Perth weren’t able to make the conference, I wanted to highlight several key presentations I enjoyed.

Here is a PDF of my presentation to view.

Some other highlights of the conference were:

• Pioneer is due in 2010 (combining Voyager with Bex Analyzer)
• Support for 64bit environments coming soon
• New release of BOE due in 2010 as well (XI 4.0?)
• SAP BWA (in-memory datastore) will be updated to allow hardware acceleration of other data sources, not just SAP BW
• BO Text Analysis (as presented by Plaut) looks very interesting, turns unstructured data (blogs, emails) into structed relational data
• Timo Elliott’s recommendation – don’t just implement software, aim high and look to change the business
• Dave Rathbun presented on universe tips and Web Intelligence essentials, such as calculation contexts (often misunderstood), multiple data providers and sub-queries
• Data Services 3.2 due in Q3 2009, will support 64bit Linux, and next release of DS will support 64bit Windows
• Data Services 4.0 will potentially migrate to use the BOE architecture – fingers crossed on my part

It was a great conference with a variety of users and partners present, and I hope to be there at the MBO 2010 conference.

- Josh

I recently had to provide a list of best practices in Universe Design – partly compiled from the SAP BusinessObjects training material, as well as my own experiences.  I believe that the universe is the foundation of the BusinessObjects system.  With poor universe design, required reports are often difficult or even impossible in some cases.

Poor universe design can also lead to wrong data, non-optimal SQL generation, incompatible objects and/or Cartesian products (or very wrong data).

I’ve categorised the best practices into two groups, End-User and Cosmetic methods, as well as Functional or Optimisation methods.

End-User / Cosmetic

1. The universe should be created from the end-user perspective, not from a developer’s perspective. This should be kept in mind whether designing new universes, or modifying existing universes.
2. All metadata (class names, object names and descriptions) should use business terminology. This allows the end users to easily use the universe without understanding the underlying database structure. It also speeds up development time, as header cells do not need to be edited. Users can then also search the universe when building a report to quickly find an object. For example, instead of calling a dimension object ‘Commit Cuml’, it should be called ‘Cumulative Commitment’.
3. All development objects should be removed before promoting to Production. Universes should not contain old, legacy or development objects (and classes) – these should be removed before promoting to Production. If absolutely necessary, these should be hidden and placed in a ‘Legacy Objects’ class so no mistake can be made regarding their status.
4. Objects should not be both dimensions and measures unless absolutely necessary. This is confusing to the user, and unnecessary. Providing row-level data is possible by introducing a unique object, like the ID.
5. Object formatting should always be specified, especially with measures. Formatting like dates, currency and so forth is important to provide objects that the user doesn’t need to format themselves.
6. Date fields should be expanded using SQL functions, and split into separate objects like Calendar Year, Calendar Month, Calendar Month Name etc. A library of date functions for both T-SQL and PL-SQL should be built up and re-used where possible.
7. Classes and objects should be based on user requirements, not table-centric. Classes might contain objects from many different tables, and should not be based on the columns within a table.
8. Custom hierarchies should always be defined and done according to the end-user’s requirements. This prevents users from drilling using default hierarchies, and allows control over what users can drill into.
9. Pre-defined conditions should be used to accelerate report development and encode difficult query filters into an object that the user can use easily.
10. Object names should be unique, and never repeated, regardless of the object classification.

Functional / Optimisation

1. SQL aggregation functions should always be present in measures. All measures should have both a SQL aggregation function (ie SUM, COUNT and AVERAGE), then a projection aggregation function assigned (respectively SUM, SUM and NONE). This ensures that GROUP BYs are used and the processing power of the database is leveraged, returning fewer rows quicker. This also has additional performance benefits at the data provider level, where less RAM will be used on the server, and lower network traffic of data.
2. Tables should not be unconnected. This will generate a Cartesian product for the end-user, and leads to very confusing universes. If tables have no way of being joined at all, they should be placed in separate universes.
3. Left outer joins should be avoided where possible. This is sometimes impossible with transactional schemas, but left outer joins lead to NULLs and slower SQL queries. If left outer joins are necessary, universe objects should contain ISNULL functions or similar so that users aren’t presented with NULL values when using objects.
4. Cardinalities on joins should always be set to allow for loop and SQL trap detection, this includes self-joins that should be marked as 1-1. Always set cardinalities manually, not automatically.
5. Contexts should be used, and if too complex, the universe should be split up. This allows for path navigation and allows a universe to have more functionality.
6. Loops and SQL traps should always be resolved to avoid Cartesian products and incorrect data. This is a very important component of universe design, especially with transactional schemas.
7. Integrity checks and refreshes of the database structure should be done regularly to ensure the universe has the up-to-date view of the database.
8. Views should be used in preference to derived tables, and kept to a minimum. This ensures optimal SQL generation.
9. When aliasing tables, the original should be placed in the top left of the schema window and noted with ‘Aliased Table – Do Not Remove’. This will ensure that an original table isn’t removed by mistake, which will also remove all aliases of the table as well.
10. The query limits should always be set, to ensure that runaway queries or Cartesian product queries don’t bring down the database. Both length of query time and number of rows should be set. The limits will differ per database.
11. Multiple SQL statements for each measure should be turned off, to generate optimum SQL.
12. Index awareness should be used where possible, to generate optimal SQL.
13. List of values should be disabled for all dimension and detail objects that don’t require LOVs, to avoid users doing queries like all IDs for a given table.

Conclusion

Having compiled this list in a short time, some of the best practices are notably short.  For my next post, I will endeavour to single out a few of the above methods that aren’t covered as often and focus on them in more detail.

If you have any suggestions, I’d love to hear them.

- Josh

Update

I received quite a few comments on additional best practices, so I’ve included them here.

• Lock the universe when editing to prevent other users from editing the same universe.  This can be controlled with the security model, so only the user who locked the universe can unlock it.  However, beware that using the Import Wizard still allows you to overwrite a universe that is locked (Jansi).
• Use universe parameters to control how the SQL is generated.  You can control SQL generation so it is ANSI-92 compliant, as well as use JOIN_BY_SQL to allow separate sub-queries to be combined at the query level instead of in the report (Andreas).
• All ratios should be set as database delegated/smart measures.  This ensures that the ratio isn’t calculated incorrectly (ie average of an average) and is calculated back at the database (Andreas).
• Generic time objects for Today, Yesterday, This Month, Last Month etc. should be provided (as dimension objects as well as filters).  These dimension objects can be used in filtering objects (Some Date Object = Today Object).  Using filters may depend on the number of dates present in your data, however you could still create filters for the most common date objects (Andreas).
• As a rule of thumb do not use the WHERE clause in any measure, dimension, or detail object. Use CASE WHEN … SQL syntax instead.  Combining two objects that have WHERE clauses filtering the same column(s) will return zero results, which the user won’t expect (Andreas).

I also recently stumbled across the same kind of post over at the Business Intelligence blog.  There are some differences in our lists, so you may wish to refer to that post as well.

Updated 22/07/2009.

- Josh

It feels like every time I write a blog post, I have to apologise for how long it’s been.  Once again, I’m sorry for not blogging for so long (this time it’s only been 6 months!).

So I wanted to give you all an update on what’s been happening in the last few months, as well as try and kick start my blogging career yet again.

One recent event was that I finally completed the certification exams for BOCP-BODI, that is, Data Integrator.  You may remember I posted about how difficult the first exam was – well the second was a lot harder, and I guess I was a little lucky with some of the questions.  I’ve found the certification exams are really a good measure for how well you know the software; if you’ve only been using it for a short time, don’t expect to be able to pass exams easily.  You only need to look at ‘the certification thread‘ on the BOB Forum to get an idea of how many people think they need help with certifications.

So I was very proud to be able to get the certification, and it means that I now have all four certifications that SAP BusinessObjects offer.  Hopefully they’ll release more in the near future.  I’d love to see a certification around Universe Design, as I feel that is much more important that the end reporting tool, like Web Intelligence.  Do you think there is a need for any other certifications?  Maybe Xcelsius? =)

Next up, I attended the 2009 Mastering Business Objects conference recently, held in Sydney Australia.  It was a two day conference, held at the Manly Hotel, on the beach.  Key highlights for me were meeting three great people, Timo Elliott, Dave Rathbun, and Jamie Oswald.  It was also great that most of the people I met were on either XI R2, or XI 3.x.  I did Twitter through most of the conference, so skip back a few pages and check out some of the live tweets at http://twitter.com/joshyf.

Myself with Dave Rathbun

Lastly for this post, I’d like to expand a little on a recent Proof of Concept that I undertook for a utility in Western Australia.  The scope was reasonably large given the time frame.  It was a three week engagement, with reporting required off SAP BW cubes and Oracle 10g databases, using the SAP BusinessObjects XI 3.1 full stack.  This included Web Intelligence, Crystal Reports, Xcelsius, Polestar (soon to be called Explorer), Live Office, BI Widgets and more.  The POC was a great success, with a lot of value add being provided because of the quick development time with BusinessObjects.

The client especially loved how they could leverage the investment in the Universes with Polestar.  You have a universe, just index it and voila.  They also really appreciated the drill-through ability – we demonstrated drill through from Xcelsius into both Polestar and Web Intelligence.

That about wraps it up for this post.  If you like the content I put up, I’d love to hear any suggestions about how I could improve my blog posts.  Would you like more technical content, more walkthroughs?

- Josh