# 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

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:

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

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!

As you may have noticed, Pragmatic Works is once again running another promotion. This promotion is a little different though in that it’s probably the best deal we’ve ever had on our software/training bundle.

If your company purchases at least a single, lonely license to any of our incredible software, inlcluding Task Factory, BI Documenter, BI xPress, and/or DTS xChange, and you get a free seat in either our SSIS or SSRS class. The really great thing about these classes is that you can attend the class remotely via GoToMeeting. The classes are about 4 hours a day for 4 days which allows you to perform your daily duties and still attend the training class.

My personal favorite bundle is the BI xPress/SSIS Training Class bundle. If you’re interesting in learning SSIS, this would be the perfect combo to get you ramped up well past beginner level and headed towards an advanced level. Our SSIS Training Class starts out at a beginner level and progresses all the way through some advanced SSIS concepts. This plays right along with the strengths of BI xPress. Once you take our SSIS Training Class and learn to use BI xPress, you’ll be developing and implimenting SSIS packages at an incredible pace.

I’d highly suggest any of you at least consider this deal before it expires at the end of this month! Help me help you! To get more information, contact Sales@PragmaticWorks.com. You won’t regret it!

# Recursive Queries and The Power of Common Table Expressions

Yesterday I was working on a requirement for a client that asked me to show a list of Organizational Units and the every single parent Organization above every Organization. The source table gave each Org Unit’s ID with their parent Org Unit’s ID. In Oracle’s PSQL, this requirement was met using the Start With and Connect By statements. In TSQL, however, there is no such thing. To meet this requirement, I could have written multiple select statements Union’ed together with differing amounts of joins back to the OrgUnit table to go up in the OrgUnit hierarchy multiple levels, but this would have been extremely painful, time consuming, and the query would have performed very poorly. Instead, I used the magic of Common Table Expressions (CTE) to build a simple query with a recursive join.

If you’re not familiar with CTE’s, check out this link about using Common Table Expressions.

For this example, I’m using the DimEmployee table in the AdventureWorksDW database. So let’s take a look at the query and then we’ll discuss it.

```;with a as
(
/*This select statement is our anchor statement.
This will bring back our top level employees that do not
report to anyone.*/
Select EmployeeKey,
/*If the ParentEmployeeKey IS NULL,
then we want to set the EmployeeKey
as the ParentEmployeeKey*/
CASE
WHEN ParentEmployeeKey IS NULL THEN EmployeeKey
ELSE ParentEmployeeKey
END as ParentEmployeeKey,
FirstName + ' ' + LastName + ', ' + Title as Boss,
FirstName + ' ' + LastName + ', ' + Title as Employee,
0 as [Level]
From DimEmployee
where ParentEmployeeKey is null

Union All

/*This select statement joins our source table back
to our CTE using the ParentEmployeeKey.*/
Select e.EmployeeKey,
e.ParentEmployeeKey,
a.Employee as Boss,
e.FirstName + ' ' + e.LastName + ', ' + e.Title,
[Level] + 1 as [Level]
From DimEmployee e
/*This is our recursive join back to our CTE.*/
inner join a
on a.EmployeeKey = e.ParentEmployeeKey
)

Select * From a```

The first thing you’ve probably noticed is that my CTE contains two select statements union’ed together. The first select statement is whats called our anchor statement. The first select statement only brings back our top level employees that do not report to anyone. In the case of AdventureWorksDW, there is only one top level employee (Ken Sanchez, CEO), but your situation could have multiple top levels.

The second select statement returns all employees, but it does something very interesting. The select statement actually join DimEmployee to the CTE it is contained in, thus creating a recursive join. By joining DimEmployee.ParentEmployeeKey to the EmployeeKey in the CTE, we are able to recursively join our results back to itself over and over until no records with a ParentEmployeeKey are left.

But if you remember, my requirements were to show every ParentEmployeeKey in the hierarchy of each Employee. So our first CTE alone isn’t going to cut it. To get all ParentEmployeeKey’s in the hierarchy, we will need a second CTE with a recursive join. Here is the select statement that returns those results.

```;with a as
(
/*This select statement is our anchor statement.
This will bring back our top level employees that do not
report to anyone.*/
Select EmployeeKey,
/*If the ParentEmployeeKey IS NULL,
then we want to set the EmployeeKey
as the ParentEmployeeKey*/
CASE
WHEN ParentEmployeeKey IS NULL THEN EmployeeKey
ELSE ParentEmployeeKey
END as ParentEmployeeKey,
FirstName + ' ' + LastName + ', ' + Title as Boss,
FirstName + ' ' + LastName + ', ' + Title as Employee,
0 as [Level]
From DimEmployee
where ParentEmployeeKey is null

Union All

/*This select statement joins our source table back
to our CTE using the ParentEmployeeKey.*/
Select e.EmployeeKey,
e.ParentEmployeeKey,
a.Employee as Boss,
e.FirstName + ' ' + e.LastName + ', ' + e.Title,
[Level] + 1 as [Level]
From DimEmployee e
/*This is our recursive join back to our CTE.*/
inner join a
on a.EmployeeKey = e.ParentEmployeeKey
)

, b as
(
/*Once again this is our anchor statement.
We are only bringing in Employees who report
to someone besides themselves.*/
Select EmployeeKey,
ParentEmployeeKey,
Boss,
Employee,
[Level]
From a
Where ParentEmployeeKey != EmployeeKey

UNION ALL

/*
This is where it gets tricky. In this recursive join,
we join on the ParentEmployeeKey's in CTE a with the
EmployeeKeys in this CTE, but we display the ParentEmployeeKey
of this CTE. This allows us to show the parent of the parent recursively.
Essentially we are going back up the hierarchy to display the boss of each
level in the hierarchy.*/
Select a.EmployeeKey,
b.ParentEmployeeKey,
b.Boss,
a.Employee,
b.Level
From a
inner join b
on a.ParentEmployeeKey = b.EmployeeKey
)

/*
In the final select statement, we must once again query our first CTE, a,
to gather the employee that do no report to anyone. Those employees are at
level 0.
*/
Select EmployeeKey,
ParentEmployeeKey,
Boss,
Employee,
[Level] as LevelsDownFromTop
From a
Where [Level] = 0

union all

/*
Then we query CTE b to get all the other employees along with every employee
above them in their hierarchy.
*/
Select *
From b
Order by employeekey, [Level]
```

Essentially what we are doing in our second CTE, called “b”, is we are joining on the ParentEmployeeKey’s in the first CTE, “a”, with the EmployeeKey in b. This allows us to show the parent of the parent so we can go up the hierarchy and return all the bosses in an employee’s hierarchy. Here is a screen shot of some of the results.

As you can see, the third record shows that Guy Gilbert reports to Jo Brown. One level up from Jo Brown, we see that Guy Gilbert reports to Peter Krebs, who is Jo Brown’s boss. And finally we can see that Guy also reports to Ken Sanchez, since Ken is the CEO.

# 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 .

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:

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.

# Greetings, SQL World!

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.