OLAP Catastrophic Failure

I’ve never needed to use an OLAP cube before recently. I’ll admit that I didn’t see what all the fuss was about. But I came upon a problem for which an OLAP worked perfectly. Theoretically.

In practice (Excel 2003), I experienced error after error. I rebuilt the cube from scratch several times. And, in the end, was unable to get it to work on any machine except the one I created it on. And even then, it didn’t work the next day.

I’ll admit that I’m not an OLAP expert. I’ll even admit that I’m mediocre at best with regular pivot tables. So it’s possible that I just don’t know what I’m doing. But if I can’t figure it out, I weep for those with less Excel experience trying to make it work.

Despite my problems, I really, really wanted to make a blog post about it. I went through the tremendous trouble of creating sample data that was not only psuedo-random, but relational and normalized. No small feat, that. And just when I was that close…

I give up. I’ll try it on 2007 later, but for now I am 100% beat. You’ve squashed my soul, Excel. I hope you’re happy.

Posted in Uncategorized

13 thoughts on “OLAP Catastrophic Failure

  1. In 2007 the ability to create OLAP cubes has been deprecated.
    You may face this issue if you have multiple versions (2003/2007(10)) installed in the same machine

  2. I feel for you, Dick. If it makes you feel any better my own experience with creating an OLAP cube in Excel led me to much the same place as you – I gave up in disgust and haven’t tried again since. Having said that I find pivot tables built off tables/flat files to be sufficient in most cases.

  3. Dick,
    When you’ve recovered, could you explain a bit what you mean by ‘building an OLAP cube’?

    Sam,
    Why has this been deprecated in 2007?

    Brilliant error message, you have to admit :-(

    /Roy

  4. Dick,

    although a first OLAP-encounter can be frustrating, admittedly, to dispel it might be a premature judgment. In fact, once having got used to working with data in / from cubes, one sourly misses it’s ease when it’s NOT available in another working environment. While it may not entirely replace the work with conventional lists or databases, OLAP can indeed enhance a spreadsheet’s capacity dramatically, at least from a controller’s (or similar profession’s) perspective.

    The term OLAP describes more of a particular way to organize data than a particular product. There are also various technologies available from different vendors. Thus, what Microsoft calls ‘OLAP’ within Excel so far does not necessarily says all about what is possible, and what external OLAP Add-Ins have to offer. Ease of use, as well as data-modeling capabilities (such as “write-back” and plenty of other non-native Excel features) from 3rd party suppliers can go far beyond those in Office 2003/2007.

    Actually, one wonders why Microsoft hasn’t harnessed and built in more of OLAP technology until now. However,’Power Pivot’, announced in conjunction with Office 2010, may change that. Hopefully you don’t give up too early, Dick. In case you dare a second attempt, you might want to try, for example, the free(within limits) “Palo” Excel Add-In (see http://www.jedox.com).

    Kind regards,

    H.G. Lamy

  5. Bob,
    Okay, you mean an offline cube file.

    Sam,
    My Excel 2007 Help says:
    “You can create an offline cube file (.cub) with a subset of the data from an OLAP server database. Use offline cube files to work with OLAP data when you are not connected to your network. A cube enables you to work with larger amounts of data in a PivotTable report or PivotChart report than you could otherwise, and speeds retrieval of the data. You can create cube files only if you use an OLAP provider (OLAP provider: A set of software that provides access to a particular type of OLAP database. This software can include a data source driver and other client software that is necessary to connect to a database.), such as Microsoft SQL Analysis Services Server version 2005, which supports this feature.”
    So it looks more restricted than deprecated?

  6. In Excel 2007, I can get to Microsoft Query via the Connection Properties dialog > Edit Query (and canceling the wizard). However, there’s no menu option >File >Create OLAP Cube, as was described in Bob’s reference.

  7. That’s right. MSQ in 2003 lets you build a cube, but in 2007 MSQ doesn’t have the option (though in every other way it seems identical to the MSQ edition that comes with 2003). 2007 can still connect a Pivot Table to a cube made with SQL Server Analysis Services. There are also a bunch of new functions with 2007 that let you reference a cube by formula. I think it’s pretty terrible Microsoft forces you to have other software to build a cube if you have Excel 2007. Makes learning the technology inaccessible to many.

    Or maybe it was done because an MSQ cube is prone to catastrophic failure?

  8. With Excel 2010 round the corner and PowerPivot attached to that does anyone have a reason they would continue to develop new offline cubes? I know that people with 2003 and 2007 (i think?) would have issues, but from a ‘clean sheet’ perspective are there still any advantages to using .cub?

    I only ask as I did a devlopment 2 years back for a reporting dashboard that was SQL Server 2000 -> .cub -> Excel 2003 and struggled to get anything to work. The cube would lock when refreshing causing users to get error messages, the learning curve was a pain as the documentation was minimal (Timothy Zapawa’s ‘Excel Advanced Report Development’ devotes about 20-30 basic pages to it, better than the Excel help file at least) and whilst I was impressed by the possibilities, the difficulties in setting up and supporting the blasted thing made me want to scream!

  9. Does Power Pivot allow you to create the .cub file? Someone asked the advantages to using .cub – one main advantage that I want is the ability to nest fields. Such as Division / Department. I can collapse the datsa to division to see the top line and expand to department when desired. I do have Excel 2010 but I don’t have Power Pivot so I don’t know if it will do what I want. I have access to a minimal version of Excel 2003 that I use to create a .cub file. But it is slow and not easy to delagate to others.
    I distribute several pivot tables each week to users that do not have access to the same source data that I have. I send the .cub file along with the pivot and they can customize the pivot the way they want to see it using the same updated cub file. If I just send a new pivot table it will overwrite and they will have to spend time each week to get the date the way they want it. Any guidance would be appreciated. thanks!!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.