Casabase and Power Pivot – A Comparison

We have previously compared Casabase to Oracle Essbase because of the similarities with how you interact with the data in Excel and the speed at which you can access sign-based aggregated data across multiple dimensions in Oracle Essbase ASO. However, a more apt comparison might be to Microsoft Power Pivot, which is also an Excel add-in and has a relational database as the underlying data store. We don’t necessarily see Casabase as a competitor to Power Pivot; it’s more of a complement, adding functionality that is difficult, if not impossible, to do in Power Pivot. A quick comparison of major functional components between Casabase and Power Pivot can be seen in the table below:

Power Pivot is an amazing BI tool, but it does not natively handle dimensionality. This is its greatest limitation. And it’s a glaring, painfully-obvious one to anyone coming from OLAP tools, where being able to instantly aggregate across 10 or more dimensions is out-of-the-box functionality. When someone new to Power Pivot sees the accepted workaround for getting even ONE dimension to roll up based on parent/child relationships, “disbelief” is likely the first thought that comes to mind. We implemented this concept on an Essbase cube that we were converting to Power Pivot. Since this cube contains financial data (one of the 15 dimensions is Account), we ended up creating literally thousands of lines of DAX code to correctly handle the signs (plus or minus, depending on whether the account is an expense or not). This was necessary not only to ensure the Accounts dimension rolls up correctly as an income statement, but to get all the signs correct across the other 14 dimensions.

At the end of the day the Power Pivot model worked. But just barely – it was unusably slow and had to be abandoned. Most people, when faced with such a complicated technical workaround, simply decide that it’s not even worth trying. This, in turn, leads to one-dimensional solutions that can’t take advantage of the inherent power of multidimensional analysis.

Casabase is designed for exactly this type of use-case. It allows you to instantly aggregate your data across up to 25 dimensions – natively, without a single line of code. Deploying multidimensional applications is orders of magnitude easier with Casabase. These applications give users the power to drill down into their data and discover new insights and relationships among dimensions.

In addition, Casabase frees you from the restrictive confines of an Excel pivot table:

As you can see, interacting with your data is much easier. You just double-click on a member to zoom in to the details behind it (you can specify to Children, All Levels, or Bottom). The Casabase sample database (CASAsamp) is based on the Microsoft Contoso dataset. Using it for illustration, you can double-click on Contoso North America to see that Contoso United States and Contoso Canada roll up to it. You can continue to zoom in all the way to the lowest level.

Double-clicking to zoom in is a slight improvement over clicking on the + sign of a pivot table to expand. But the real power of Casabase is in its free-form nature. By this, we mean that you can type a member name directly into a connected Excel worksheet and hit the Retrieve button. This is a very handy shortcut if you are already familiar with the data. If you’re not though, you can use the Casabase member panel (shown on the right above) to search for members. You can even drag and drop them onto your Excel worksheet. You can also move members around on the retrieval using regular copy and paste commands. Pivot tables don’t allow such freedom.

One of the main reasons that pivot tables never became mainstream was due to their restrictive nature. They go against the fundamental, freestyle nature of Excel, where you can type anything (numbers, texts, hyperlinks, etc.) directly into any cell. Once set up, pivot tables are immutable. Yes, the data in them can be refreshed, but the basic structure cannot.

Speaking of pivot table limitations, they also don’t allow you to input data, except for the rare instance when they’re connected to an Analysis Services multidimensional cube. Casabase, however, does let you input data. You can retrieve data, change the data in the retrieval, and as long as every member is at the lowest level of the tree you can save the data back to Casabase. Power Pivot also cannot handle the volume of data that Casabase does, and even small amounts of data with more than a couple of dimensions require thousands of lines of DAX formulas to replicate the sign-based multidimensional aggregation that Casabase provides natively.

If the free-form Excel retrieval and the availability of sign-based aggregations across multiple dimensions sounds interesting, head on over to our download page to take advantage of the free 14-day trial of Casabase. As always, we’d love to hear from you with any feedback on our Contact page.