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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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!