Business-Intelligence

With SQL Server, there often seems to be an over-emphasis on the items that provide bullet points for the marketing brochure. Yet, small but useful changes to the product can make a more profound impact on DBAs/developers than the new items that are highlighted by those bullet points.

One of the things I loved about Reporting Services in SQL Server 2008 R2 is the amount of focus they provided on ease of use. In particular, the addition of data bars, lookup functions, pagination control, rendering targets in expressions, domain scope, etc. made a huge difference to anyone that really needs to build reports.

2011-09-11

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

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

I know this post has the potential for religious-level debate but it’s time to make it anyway.

The more I’ve been working with Analysis Services lately, the more it grates on me that the BI community still seem to be the last ones hanging onto table prefixes. They’re not doing “tblSomeTable” but they are using “dim”, “fact”, etc.

Hasn’t the time for this long gone now?

Most of the argument seems to be about finding tables in a list of tables. You could do that via schemas if you really wanted to. But as Adam Machanic pointed out recently, from 2005 onwards many-to-many dimensions blur these lines anyway.

2008-06-16