Using Offline OLAP to Develop Cube Reports Without SSAS
One feature of Excel 2007 that I think is really cool, and also a little hidden, is the “Offline OLAP” feature.
If you insert a pivot table connected to an OLAP cube into Excel 2007, and go to the PivotTable Options Ribbon Menu, You will see the “OLAP Tools” Button. Click on that and then “Offline OLAP”.
Once you go through the Offline OLAP Wizard, it will create a .cub file for you. What this ends up being is a local disconnected “cube” you can analyze in Excel, if you are on the plane, or in some remote area with no internet connection.
Other uses for the offline .cub files that I have found is this. Let’s say you want developers to develop Web based reports using .NET, maybe using the Dundas OLAP Services controls. If you don’t want to have to load SSAS or set them up to connect to any server so they can just develop locally, the .cub file is the way to go. In their .NET code, they can just change the connection string to point to the local .cub file, and then later when you are ready to go live, you can just change the connection string to the live cube. That way, if you are developing the cube at the same time reports are being developed, you don’t have to worry about uptime, etc. Just send them an updated .cub file every once in a while.
I don’t know much about the details for upcoming releases, like Microsoft Project Gemini, but I have a feeling that it might feel a bit like this, using Offline OLAP, or local analysis.
On a final note, if you really want to get geeky, you can actually create the .cub files from .NET, but that is another blog post :)
Simlar Posts
- Microsoft BI: Creating Local OLAP Cubes using XMLA and ascmd.exe
- Dev and Prod Systems, Using a HOST file to ensure your datasource is pointing the right system
- Office 2010: Excel 2010, What-If Analysis aka Microsoft Finally has Built in Cube/OLAP Writeback!
- Excel 2007, OLAP Cubes: Customizable, User Defined Named Sets in Excel 2007 using VBA
- Microsoft BI Client Tools: Creating PivotTables in Excel 2007 that work in Excel 2003
April 20th, 2009 at 11:05 am
[...] Using Offline OLAP in Excel 2007 – every time I read something about Excel 2007’s BI capabilities, I get more excited. [...]
June 25th, 2009 at 8:29 am
[...] Server Analysis Services (SSAS). They hit it with Excel, or some other reporting tool, etc. I have blogged previously about offline cubes and how you could possibly use [...]