refresh Excel report based on Offline-SSAS-Cube (a *.cub-File) not possible

  • Hi,

    we can't refresh the Excel-reports based on a *.cub file even if there is a newer .cub-file.

    Background:

    We use SQL Server 2008R2 and Excel 2010.

    We've a SSAS-Database containing some cubes;

    they work and refresh fine.

    Now the users want to have an offline Cube.

    therefore I created a local "*.cub"-File with the help of the "CREATE GLOBAL CUBE" statement.

    the *.cub file is copied over network to the users, they can open it with Excel and create their reports. Works fine.

    But the next week, a new .cub file is copied to the users (same location as the old file).

    If the User opens his report from last week it will not refresh.

    if the User hits the "refresh" button in Excel, Excel tries to rebuild the .cub-File which takes hours because of lots of data and slow network.

    if more than one person tries to "refresh" the network is overloaded. that's why I generated the cub-files via "create global Cube" once and distribute it once a week during non office hours.

    but, as already mentioned, the Excel-Reports based on these distributed .cub-files will not refresh.

    searched a lot, found the question sometimes, but no answer for it.

    Could someone please help me?

    Regards Andy

  • hmm, no answer jet...

    hope the description of my problem is understandable.

    It would be nice to know, if someone has the same problems than I?

    How do you handle offline-Cubes?

    Or, are offline cubes not used by anybody?

    Greetings Andy

  • Refresh means something different in the case of offline CUB files in Excel. Short answer, is don't use refresh, but simply change a slicer in your query, that'll cause Excel to repull the data. There is macro code out there that demonstrates how to force a refresh without actually changing the slicer, but be warned it's generally a little incomplete. (Putting on the finishing touches of my own extension now.)

    Excel treats the CUB file more as a type of "cache", rather than the offline cube it should be. CUB files seem to be depreciated, with no replacement in sight. (Pity). I highly recommend that you consider purchasing CubeSlice, since it does a much better job of creating CUB files (use the ASSL model only). Again, it's not a perfect solution, but the best available.

    In general, avoid any strategy that relies heavily on offline CUB files, since it's not well supported. You'll run into limitations and performance problems in many instances.

  • thanks for your answer.

    yes, I've heard that cub files will not be developed any further. very sick, as they are a good (and the only one I know) way of being able to make flexible reporting offline. Hope they will live long enough 'till the offline specification is not requested by our users any more.

    Does anyone know another way to make flexible offline reporting for end-users with a reasonable speed?

    Thanks for the Tipp with CubeSlice, I'll have a look at it.

    What's the advantage of the assl model? Read about smaller cubes and performance improvements, at the moment this is not a problem within our "create local cube" solution.

  • Sorry for the late reply. Holidays.

    For anything but the most trivial examples, you'll find that the ASSL model is the only thing that works. Unfortunately, you'll find that CubeSlice depends to a significant degree on the infrastructure implemented by Microsoft. They've designed and developed numerious extensions to compensate for the limitations and inconsistencies in the Microsoft model, but unfortunately if you run into any complex cases they haven't accounted for, you'll be on your own. (There hasn't been a tremendous amount of industry interest in this product, pity since it's the best solution out there I've found.)

    In general, you'll find that the answer will be to remove features and simplify until it works.

    As to any other solutions, if you find anything, please post an update. In general, the world seems to assume 100% connectivity, which just isn't practical. (Imagine if your PC didn't work at all, if you didn't have a connection! Shudder.....) Coupled with Excel's general "chatty" nature, I don't see much relief.

    There's some "screen slicer" solutions out there (they capture the various parameter selections and store it on the remote device), but I don't find they scale well (i.e. 100 products x 100 stores = 10,000 screen captures).

    The BI industry is dying for a true offline BI experience, suppoprted on various mobile devices, but I haven't seen anyone step up to the plate yet.

    Good luck and let us know of any successes.

  • If I were you, I would do the Refresh processes on the SQL side.

    Redeploy the Excel Cube every time the scheduled SQL Refresh runs.

    -April

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply