PivotTables – Steve Novoselac's Blog

Office 2010: Excel 2010, What-If Analysis aka Microsoft Finally has Built in Cube/OLAP Writeback!

Digging into the blog post from earlier this summer I wanted to see what was new and exciting in Excel 2010.

Recently I have been working on an cube and we want to be able to budget right from the cube. There are also many other cubes/scenarios where the ability to writeback to the cube would be awesome. Some BI tools have had this for many years! Microsoft had something similar with a Excel 2002/2003 add-in, but it has been removed. Also, there are many 3rd party tools to allow this. You could also write your own macros or VBA/.NET code to do this as well, but what was always missing was the ability to do writeback directly from an Excel (OLAP) PivotTable. With Excel 2010, this functionally finally shows up.

First, you need a cube, and you need to enable writeback on a partition. This will automatically create a table in your database where you have your data warehouse. Lets say you have a table FactBudgets, and you enable writeback, SSAS will create a table WriteTable_Budgets. This is a trivial example I went through to test this functionality, but I just wanted to exhibit the feature of the writeback.

Turn on writeback, deploy and process your SSAS cube, and then open Excel 2010. Connect to your cube, and then in the PivotTable ribbon menu, on options, there is a button to turn on “What-If Analysis”. Turn it on. 🙂

Once you turn on the setting, you can then begin writing back values to your cube, right from Excel. If you click on a cell in your writeback value, you can just change it. See on the screen shot below, the value I changed, the little purple triangle, tells me it has a changed value from what is in the data source.

You can see its telling me the value changed, I can Discard the change and other options. After you have your values set, you want to publish them, which you do back on the PivotTable ribbon menu:

As you can see, Microsoft has finally created a viable solution for writing back values into your OLAP cubes without the need for 3rd party software or coding. Finally!

One thing to note, if you try to write back to a value that is in a partition that doesn’t have writeback enabled, you will get an error.

Now, think of the possibilities with SSAS OLAP writeback and Excel, now that we can actually use it out of the box!

Microsoft BI Client Tools: Creating PivotTables in Excel 2007 that work in Excel 2003

Recently I blogged about easing the transition to Excel 2007 from Excel 2003, by changing your PivotTable design and options.

This post is going to be about creating PivotTables that connect to OLAP cubes (and PivotTables in general) that will work in either client, or sometimes called “Compatibility Mode“

If you create a PivotTable in Excel 2007 (a “Version 12” PivotTable) and save the xlsx file to an xls (Excel 2003 or “Version 10” PivotTable), you can still open the spreadsheet in Excel 2003, but you cannot refresh the data – it will still work if you open the .xls in 2007 though.

What that means is that the version 12 PivotTables will not downgrade to version 10 PivotTables and won’t be refreshable in Excel 2003. This presents a problem if you still have people you want to share the PivotTable with that are using Excel 2003, and you are using Excel 2007. What you need to do is create the PivotTables as version 10 PivotTables, but from Excel 2007, and this is how you do that.

The easiest way to create the version 10 PT in Excel 2007 is create it in compatibility mode. To do that, open a new Excel 2007 workbook and save it as Excel 97-2003. Open that file in Excel 2007 again and you will be in compatibility mode. Once you create a PivotTable and save it, you can then share it with anyone that just has Excel 2003 and they can open it and refresh it. Simple, huh?