Devin Knight and I stumbled onto another weird SSAS bug today. We had a measure group that had two measures. The first measure was semi additive (Last Non Empty) and the second measure was a normal additive type (Sum). The additive measure would not display along side the semi additive measure, whether I browsed the cube in Excel, BIDS, SSMS, or wrote an MDX query.
Well as it turns out this is a known issue that was identified some time ago. SQL Server 2008 SP1 solves this issue: http://support.microsoft.com/kb/969099 .
So if you’re ever on site working in an environment you are unfamiliar with and you experience goofy behavior where semi additive and additive measures in the same measure group do not display properly, check the SQL Server AS version! The version to look out for is 10.0.1600.
While working on an SSAS project today, I was scripting out some MDX calculations in the cube (year to date, month to date, previous year’s month to date, and previous year to date). I wrote the calculations and they looked perfect, but when I tried to browse the cube in BIDS after deploying, I received this very perplexing error:
After stumbling around for a bit and doing some research, I eventually discovered that if I clicked the “Show Empty Cells” icon (seen below),
my calculations appeared to be working correctly. Apparently this is some kind of bug in the cube browser in SSMS and BIDS. You’ll notice that if you connect to your cube from Excel and browse the cube using a Pivot Table, you are able to successfully browse the cube without any strange errors. I hope this saves someone else the 45 minutes I spent trying to figure out what in the world was going on.
As my first post on my new blog site, I figured I’d give a bit of an introduction. I’m a business intelligence consultant with the Pragmatic Works crew in Jacksonville, Florida. Lately I’ve been creating BI solutions utilizing SSRS, SSIS, SSAS, and SharePoint. My experience stretches through a variety of industries such as health care, manufacturing, transportation, retail, and insurance, just to name a few.
My primary purpose for this blog is to discuss SQL and BI related topics and to interact with the professional SQL community. I look forward to making new friends, learning lots of new things, and sharing my experiences!
You can find me writing here on my blog, consulting with any of the many clients of Pragmatic Works, or tweeting @SQLDusty.