CTP for Power View and SSAS Multidimensional Cubes

When Power View appeared, one of the big outcries was "but what about connecting to existing cubes!".

Great to see that the SQL Server team have addressed that. A CTP that allows connecting Power View to SSAS Multidimensional cubes is now available:


Help the team get this out the door by trying it and providing feedback.

Setting default values for slicers in PowerPivot

I've been doing some work with PowerPivot and SharePoint/Excel Services this week. I wanted the user interface to have slicers for:

  • Year
  • Month
  • Day

But I wanted the slicer to be preselected for the current month. There is no property on the slicers to set a default value. I read a number of websites and the suggestion was to use VBA code to set the value. This works but if you want to have the VBA code run at workbook open, you have to create a macro-enabled workbook, and these are not supported by Excel Services.

So I seemed to be fresh out of luck. However, one of my Spanish colleagues José Quinto Zamora came to the rescue. All you need to do is to select the slicer filter value that you want as the default, before you save the workbook, and every time you open the workbook, the slicer value will be already selected. That's as good as a default value for me. Thanks José!

Hope this helps someone else.

Book Review: Microsoft PowerPivot for Excel 2010: Give Your Data Meaning

I'm loving my Kindle. I seem to be getting through books so much faster. One book that I recently read was Book Review: Microsoft PowerPivot for Excel 2010: Give Your Data Meaning by Marco Russo and Alberto Ferrari.

I really liked this book. It provided quite good coverage of PowerPivot use in Excel 2010 and also spent some time mapping the use of PowerPivot to organizational requirements. Marco and Alberto provided more coverage of DAX (Data Analysis Expressions) than I have seen anywhere else, particularly in relation to the CALCULATE verb.

If I have any criticism of the book, it's probably just the order of the chapters. I can imagine that many people won't want to delve so deeply into DAX and may stop reading before they get to the later chapters. I'd like to have seen much of the DAX material at the back of the book as a type of "advanced DAX topics" section, given that the remainder of the book doesn't really depend upon it.

I was left feeling that there's a need for another type of DAX book, much like the book that Art Tennick wrote for MDX: Practical MDX Queries: For Microsoft SQL Server Analysis Services 2008. In that book, Art provides a large number of "recipes" for how to achieve common tasks with MDX. I'm sure that's also needed for DAX.

Anyway, Marco & Alberto's book is definitely recommended.I'd give it 8 out of 10. (And a big thumbs up to the publisher for making a Kindle version available too).