Sql-Server

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.

2011-08-11

Ola has updated his index optimization scripts again. The update relates to logging to a table.

You’ll find the scripts here: http://ola.hallengren.com/

2011-07-19

When using the Usage Based Optimization wizard (or the Aggregation Design Wizard), on the screen that asks you to enter counts. There is a button that says “Count” but after using it, you might still be confronted by the message ““Estimated Count or Partition Count must be specified for cube objects that require counting”. It’s not overly helpful, given you have just asked it to count them itself.

If you see this, look through the list of dimensions and find any that have red squiggles under them. Then open each and find the attributes that also have squiggles and update those counts. Make sure that all squiggles disappear.

2011-07-06

I spent last week on campus in Redmond with the SQL Server Analysis Services Maestro program. It was great to have a chance to focus on SSAS for a week. As part of that, I did quite a bit of reading as I had quite a bit of travelling time. Ironically, I re-read a few books.

The first was Marco Russo, Alberto Ferrari and Chris Webb’s book Expert Cube Development with Microsoft SQL Server 2008 Analysis Services. I’ve often told BI classes that I’ve been teaching that this is a really good book and highly recommended. Re-reading it confirmed that. I thoroughly enjoyed reading it again.

2011-06-21

Another book that I re-read while travelling last week was Fast Track to MDX. I still think that it’s the best book that I’ve seen for introducing the core concepts of MDX. My colleague Mark Whitehorn, along with Mosha Pasumansky and Robert Zare do an amazing job of building MDX knowledge throughout the book.

I had dinner with Mark in London a few years back and I was pestering him to update this book. The biggest limitation of the book is that it was written for SQL Server 2000 Analysis Services, yet it still provides an excellent introduction to MDX. The knowledge from the three authors and Mark’s entertaining writing style make this still a “must-have” book for anyone learning MDX.

2011-06-21

Yet another book that I started re-reading last week (but haven’t finished again yet as it’s so large) is Microsoft SQL Server 2008 Analysis Services Unleashed by Irina Gorbach, Alexander Berger and Edward Melomed.

This book has always left me with mixed feelings. The authors clearly offer expert level knowledge on the topics (as they were part of the development team for the product) but I struggle with the “readability” of this book. As an example, each time a concept is introduced, it is done so with accompanying XMLA code snippets, etc.I suspect that a lot of readers would be put off by the XMLA. Most people just don’t think in XMLA.

2011-06-21

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.

2011-06-01

Since I’ve been posting book reviews, I’ve already had a few questions about the Kindle and what I think about it.

I have the 9" DX with 3G and love it. The 3G is so useful. You don’t need to have a 3G provider account and it just seems to work almost everywhere I travel to (including even China). I’m not sure how Amazon make that work but it’s great. I see a book, click to order it and moments later the book is on my Kindle.

2011-06-01

Our buddy Ola Hallengren has released another version of his database maintenance tooling. (Free !) I’ve seen quite a lot of people using Ola’s scripts rather than standard database maintenance plans, and with happy outcomes.

In the new version, he has optimized performance in IndexOptimize for databases with large number of indexes or SQL Servers with large number of databases. Ola said that the time for rebuilding or reorganizing indexes is the same, but the time for investigating indexes has been reduced.

2011-05-20

Skype sent down an auto-update today. Looks like it’s now 5.3.0.111. Regardless, every time I started Skype, it said:

Exception EXMLDocError in module ezPMUtils.dll at 000E8FCA

Reboots, etc. didn’t fix it. What did fix it was moving the file ezPMUtils.dll from the folder C:\Program Files (x86)\Skype\Plugin Manager to the folder C:\Program Files (x86)\Skype\Plugin Manager\MLS. Note that at first I tried copying the file to that folder but that did not fix the issue. Moving the file, however, did “fix” the issue in that the error no longer occurs. But of course, the extras manager also no longer functions. It would be really great to see *something* from the people at Skype about this issue.

2011-05-15