Category Archives: SSAS

SSAS Workshop in Phoenix, AZ 6/21 – 6/22

Capture

Coming up next week starting June 21 through June 22, I’ll have the really cool opportunity to teach the SSAS Workshop with Brian Knight of Pragmatic Works during the Expedition Denali Workshops tour in Phoenix, Arizona. This will be my first time teaching at the Expedition Denali tour and I’m so excited. It’s going to be awesome! We’re going to be covering everything from dimensional modeling to building a cube to writing MDX and even Data Mining. It’s two full days jam packed with great information that’ll get your BI team ready and able to start developing an AS solution in your environment.

If you’re looking for some top quality training from industry experts, I’d highly suggest you consider registering for this great training opportunity. We’ve still got seats available for the workshop in Phoenix, so just go here to get registered. If Phoenix is just too darn far away for you, we’ve got a bunch other workshops lined up at other locations such as Toronto, Chicago, Dallas, Charlotte, and more. We’ve got seat available but make sure you get registered soon because these awesome workshops fill up fast!

MDX Puzzles: Super 8–Solution

This week’s puzzle was pretty straight forward, but it required you to use the OpeningPeriod function. To show the count of customers on the first day of the year, we’ll create a calculated measure called “First Day Customer Count”.

WITH MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

In the above calculated measure, the first argument of the OpeningPeriod function specifies at which level we want the first period for and in this case we want the first period at the Date level. The second argument allows us to specify for which member we’d like to see the first day of the year.

Secondly, we’ll create a second calculated measure called “First Day”, which is very similar to our first calculated measure except we’ll be using the .NAME property to get the name of the first period of the specified level.

MEMBER [Measures].[First Day] AS
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

Then finally we’ll reference these to calculated measures in our final select statement.

WITH 
MEMBER [Measures].[First Day Customer Count] AS 
(OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER),[Measures].[Customer Count])

MEMBER [Measures].[First Day] AS 
OpeningPeriod([Date].[Calendar].[Date],[Date].[Calendar].CURRENTMEMBER).NAME

SELECT {[Measures].[First Day],[Measures].[First Day Customer Count]} ON 0,
[Date].[Calendar].[Calendar Year].MEMBERS ON 1

FROM [Adventure Works]

I’d like to say thanks to everyone who participated in this MDX puzzle. We had several great submissions but I can only pick one winner. Super 8’s puzzle winner is Jason Thomas (@de_unparagoned)! Congrats, Jason! I’ll be sending you one cool Kick’n SSAS t shirt in the mail as soon as I get off the road and back home next week.

Stay tuned for the next round of MDX Puzzles!

MDX Puzzles: Super 8

If you’ve followed my blog over on BIDN.com, you’ve probably come across my series of MDX puzzles. If you’re not familiar with the MDX Puzzles series of post, check this out. As always, these MDX queries were written against the Adventure Works DW. Here are the requirements:

Rows:

  • Calendar Year

Columns:

  • Customer Count on the first day of the year (may not be Jan 1 if Jan 1 does not exist)
  • First Date in Year

Hints:

  • Take a gander at this

And here’s a sneak peak at my results:

QueryResults

If you think you’ve got the correct answer, send your query to

 email me

Of all the correct answers I get, I’ll pick one at the end of next week (April 29) and send out one of these really cool shirts, so good luck, peeps!

KickinSSAStshirts

SSAS 2008 Bug: Normal Additive Measures and Semi Additive Measures in the Same Measure Group Don’t Display Properly

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 .

bug

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.

SSAS 2008 Error when browsing cube in BIDS or SSMS: “An error 0xE0040200 occurred. No further information was provided.”

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:

Error when browsing cube in BIDS or SSMS: “An error 0xE0040200 occurred. No further information was provided.”

After stumbling around for a bit and doing some research, I eventually discovered that if I clicked the “Show Empty Cells” icon (seen below),

Show empty cells icon

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.