Cubes and Universes

Mark Miller recently posted a link to a whitepaper comparing the BI offerings from Business Objects and Microsoft. This is something I had been looking for for quite a while and I think it does a good job of presenting a comparative history of both platforms. The content remain fairly high level, though. What I’d like to do here is to discuss some of the conceptual differences between a Business Objects (BO) universe and a Microsoft SQL Server Analysis Services (SSAS) cube.

As you read through this brief comparison, please keep in mind the following:

  • The product versions I have in mind as of this writing as Business Objects XI Release 2 and Microsoft SQL Server Analysis Services 2005.
  • Although the cube terminology is part of the Business Objects model, I use that term as a proxy for the SSAS implementation unless specified otherwise.
  • These opinions are based on my own experience only. I’m not voluntarily biased towards any one of these platforms but I certainly know less about BO than about SSAS.
  • A lot could be said about MDX versus SQL, but this is beyond the scope of this paper.
  • This site currently lacks a comment posting facility but I will take into account feedback received via the contact form.

Abstraction

A cube and a universe each serves as an abstraction layer that sits on top of a relational data source. Both allow you to model dimensions along which you want to perform analysis, attributes that populate these dimensions and measures that you aggregate along those dimensions. Both also allow you to arrange a dimension’s attributes in a hierarchy, although each platform’s implemention of the latter differs (more on that below). One of the major similarities between a cube and a universe is that both require the involvement of business users at the modeling stage to ensure that the resulting model is scoped right, structured as it should be and user-friendly.

A cube is not always what it seems

Business Objects uses the term “cube” common to most On Line Analytical Processing (OLAP) solutions, but slightly tweaks its definition. To Business Objects, a cube is a data structure assembled by the application server each time you refresh a report, which in turn runs a query against the relational database. In that sense, a BO cube doesn’t necessarily contain every dimension, attribute and measure defined in the underlying universe. When you need an additional item in your analysis that you hadn’t originally selected, you need to go back to your report definition and query the database server again – which refreshes your cube. On the other hand, an SSAS cube being your core abstraction structure, you necessarily have everything available in it at any point in time.

Aggregation

Being no more than a logical layer that sits on top of your relational data source, a BO universe doesn’t pre-compute any aggregations. Aggregations are implicitely computed each time you refresh a report, and only apply to the subset of information that you’ve selected. An SSAS cube can also be used without pre-computing aggregations. It offers alternatives, though, such as pre-computing a specified percentage of all potential aggregations in your cube to speed up retrieval.

Note to the reader: It would be interesting to run a comparison of the performance implications of both architectures.

Two special kinds of aggregation (count & distinct count) are available in cubes but not in universes.

Writeback

An SSAS cube can be setup to allow writeback. Writeback is the ability to modify a grid cell from the front-end of your choice and see the change reflected in the cube (and stored in a relational table for further processing). The propagation of the front-end change to actual cube cells can use complex allocation algorithms when the input doesn’t happen at the lowest possible level. A BO universe doesn’t allow writeback.

Semi-additive measures

Some measures are simply not meant to be aggregated across specific dimensions, particularly time. Consider for instance inventory figures, staffing information or account balances. These produce perfectly coherent results when summed along a department or country dimension but couldn’t possibly be added up along a time dimension. A Business Objects universe provides very little functionality for handling semi-additive measures, being limited to basic aggregation functions such as sum, average, minimum and maximum. Even using an average instead of a sum doesn’t help much in an inventory or accounting scenario since the function would apply to all dimensions. An SSAS cube on the other hand offers several aggregation functions that were designed precisely to deal with semi-additive measures, such as FirstChild, LastChild, FirstNonEmpty and LastNonEmpty. These functions leverage the very powerful hierarchy modeling functionalities offered by a cube.

Hierarchies

One of the most striking differences between a cube and a universe is what hides behind the way hierarchies are implemented in each case.

Business Objects implements hierarchies as a simple navigation mechanism that determines the sequence a user is automatically walked through when proceeding with a series of drill-down queries. No particular meaning is attached to the definition of a hierarchy, which therefore cannot be used as an active element in any kind of selection or analysis. SSAS hierarchies pack quite a lot more punch than that.

An SSAS hierarchy is interpreted by the server, which comes to understand basic relationships between its members. the cube knows the parent of a particular member of a hierarchy, its children, or its siblings. It knows that January 2005 is a year – or twelve months – before January 2006. This opens the door to a whole lot of possibilities.

From a navigation standpoint, front-ends can allow you to specify things like “all months of the year 2006″, or “descendants of the southwest region at the city level”. To achieve the same result, a front-end to a universe requires you to proceed in two steps – first selecting the members to display (cities) and then applying a filter on regions.

From a computation standpoint, it’s extremely easy to specify for example a Year on Year (YoY) growth, rolling average or Year to Date (YtD) measure.

Another byproduct of this powerful implementation of hierarchies manifests itself when dealing with facts of different granularities. A classical scenario would involve a budget at the product line level (coarse) and actual sales figures at the product level (fine). While a universe would artificially multiply the number of occurences of the coarsest figure to match the number of rows of the finest figures, a cube could be setup to allocate the coarsest figure down to the level of the finest figure, using the latter as a key.

Default members

By default, querying a Business Objects universe returns anything that hasn’t been explicitely filtered out by the user. In other words, each user should be absolutely consistent in specifying constraints prior to extracting information for analysis. This applies particularly to the time dimension where, combined with the lack of functionality for handling semi-additive measures, results can look incoherent indeed…

An SSAS cube allows default members to be specified for any attribute of any dimension. This means that without further action on his part, the user of an inventory cube could by default see yesterday’s end of day inventory positions when he first drags that measure into his front-end.

Named sets

Dynamically-defined sets of dimensions members can be setup in an SSAS cube and shared by all users. These could specify things like “top 10 customers based on last month’s sales”, “top 5 support reps based on last week’s count of issues resolved”, etc. Such sets can then simply be dragged as row or column headings in a grid-like report, provided this is supported by your particular front-end. Business Objects universes don’t offer a similar functionality.

Security

SSAS Cube security can be enforced at the data level, with permissions assigned by database, cube, dimension, member or even dynamically by cell. The Business Objects platform can control access to particular universes, but otherwise the model is report-based, with security enforced at the report level.

Attribute sorting

BO universe attributes are sorted independantly of each other, resulting for instance in April showing up before January. What SSAS cubes allow you to do is to specify for each attribute whether it should be sorted according to its key, its value or yet another arbitrary attribute.

Conclusion

Within the narrow scope of the current comparison, and without questioning the overall value of the Business Objects platform as a whole, I would consider SSAS cubes a superior modeling alternative to BO universes. There is no reason, by the way, why you couldn’t build SSAS cubes and make them available both directly for certain purposes and via a BO universe for other purposes – although you’d lose some of their benefits in the latter case.

If nothing else, the superior handling of hierarchies, semi-additive measures and default members would seem like sufficient reasons to favor SSAS cubes. Performance has got to be a decisive factor in certain high volume scenarios, but I don’t have figures available to back this up so this is more of an open issue.

Feedback is most welcome – let me know what you think!

14 thoughts on “Cubes and Universes

  1. Pingback: Google ranking « Stéphane-Robert Langer

  2. Mike Day

    ‘There is no reason, by the way, why you couldn’t build SSAS cubes and make them available both directly for certain purposes and via a BO universe for other purposes…’

    I’m told that Business Objects Universes can’t use SSAS 2005 cubes, short of investing in new versions of Business Objects. Is this true?

    Reply
  3. Stéphane-Robert Langer Post author

    Mike: It is worth repeating that I know much less about BO than about Microsoft’s BI stack. According to information from BO’s forums however, there’s a productivity pack for Business Objects Enterprise XI R2 (Service Pack 2) that allows you to access SSAS 2005 through a new OLAP tool called Voyager. That would tend to confirm your understanding that you need to invest in a new version, but please bring that up with BO if you’re currently a customer.

    Reply
  4. Donovan Hsieh

    Having developed & used both BO Universe & SSAS cubes in the past, your comparison is fair from the BI modeling perspective. I agree with your assessment that BO Universe is not as powerful as SSAS cubes functionality-wise. However, BO offers other reasons for consideration that SSAS doesn’t have, i.e., scalability running on non-Windows boxes. Many enterprises wouldn’t even consider Windows as production-worthy due to security and performance risks. That said, if I were to choose a platform from the developer perspective, SSAS definitely wins hand-down. However, when IT Ops weighs in, BO probably wins 9 out of 10 times.

    Alternatively, I would choose Cognos (now acquired by IBM) since it has both strength of BO Universe & SSAS cubes albeit with some compromise.

    Reply
  5. Stéphane-Robert Langer Post author

    Donovan: thanks for chiming in and for understanding the limited scope of the comparison. You’re also raising a good point here with regards to the reasons for organizations adopting a particular solution over another.

    IBM/Cognos is certainly a strong contender. Starting from scratch, I guess I’d also be looking at Oracle/Hyperion.

    Reply
  6. James Foreman

    Stephane,

    Nice article, but a few points that nobody has seen to correct:

    Attribute sorting – the date issue is a bit of a non-starter – that’s to do with particular universe design, rather than a flaw in BO itself. If you store your months as string data, you’ll only get an alphabetical sort. If you store your months as datetime data, and apply formatting in the report to make it read April, May, etc then it will sort in chronological fashion. Now when it comes to arbitrary sorting of other column values, I feel you have a point, but that’s something that has to be worked around in the DB layer, regardless of whether you’d be using cubes or universes – in which case you could generate another column for ordering and use that in BO too.

    Your security point is not correct; since (at least) 2000 BO has had the ability to enforce security down to row level in tables; it’s not the case that one has to give all users complete access to the database. From what I remember (it’s been a good 7 years now since I last did admin work on a BO install) this should integrate with Windows authentication & groups, if you want it to.

    Named sets are nice, and that is a failing of BO. On the other hand, a constant frustration for me in using SSAS via Excel is that I don’t get BO’s nice canned filters (eg dynamically generating last year) which gets you to a similar position. What’s more convenient (and maybe MS get round it elsewhere in the stack, but if you’ve seen it then it would be nice to be enlightened) is the object-to-object filtering in BO. If I’m working for an airline and I want to get all the flights we sold where the destination country is the same as the departure country, in BO that’s easy: you have object Destination Country = object Departure Country. In Excel I seem to be stuck with building a big pivot and taking the leading diagonal. I suppose if the predefined set existed, I could use that, but if it didn’t then being able to define these relations on the fly in BO seems more beneficial to analysts who aren’t deep in the IT department.

    Reply
  7. Stéphane-Robert Langer Post author

    James,

    Thank you for your feedback. I stand corrected – your suggestions for attribute sorting make perfect sense. There also seems to be several options for fine-grained security which I really should have researched initially.

    The object-to-object filtering you’re describing seems like a useful option. There are Excel plugins on the market that would allow SSAS end users to apply any kind of complex filter but not without some exposure to MDX – which I agree isn’t as easy as drag-and-drop.

    Reply
  8. Santhosh Shetty

    I see this blog is initiaed in 2007. There are few new features added to the latest release (3.1 sp2 ). Universe now has MDX editor and parsing.
    Some of the fixes are added for SAP BW which have fixed some SSAS integration issues as well. But, there are differences between MDX generted by Excel and universe(from WEBI).
    Never understood why universe creates sets for every measures in the universe when report is just using one of the measures.
    Eventhough it is helping subsequent MDX queries where it is getting the data from session cache, but initial set creation causing performance issues.

    Reply
  9. Michael Schmalz

    Nice read…as the Supervisor of Mfg. Development at a Disk Drive Technology company, this debate is near and not-so-dear to my heart. We have been debating the vritues of Microsoft vs. SAP’s BI tools for way too long…..and Donovan (above) nailed it with the comment “…if I were to choose a platform from the developer perspective, SSAS definitely wins hand-down. However, when IT Ops weighs in, BO probably wins 9 out of 10 times.”
    I hope someone can someday cut thru all of the non-technical politics and make a decision on which direction our company should go….
    Thks again for your insight….mjs

    Reply
  10. Stéphane-Robert Langer Post author

    Michael: Thank you for your kind words. Of course there’s a lot more to SAP’s BI tools than the somewhat disappointing implementation of Business Objects cubes. I’m also not sure if/how that is being merged into SAP Business Warehouse. A strong case could certainly be made for both platforms depending on the situation.

    I hope you can sort things out soon. As I think you suggest, it might (to some extent) be more important at some point to choose a direction, stick to it and deliver rather than to select one tool over another.

    Merry Christmas and a Happy New Year!

    Reply
  11. Pingback: Oracle Essbase 9.3 Developer Certified Expert « Stéphane-Robert Langer

Leave a Reply

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