Since this week is Thanksgiving and I was busy as mess on Friday, I figured I would wait to post the solution to last week's MDX puzzle until today and take a week off from blogging while I celebrate Thanksgiving with the family. To solve Encounters of the Fourth Kind, you needed to take advantage of two MDX functions: the Ancestor function and the IIF function . So lets start off with a base query that has Reseller Sales Amount on the columns and Products on the rows: SELECT [Measures].[Reselle...
Read More
This week's puzzle utilizes a concept we learned a couple weeks back and introduces a new one we have yet to cover. So good luck on the puzzle and email me at dryan@pragmaticworks.com when you have the answer. Don't forget to get your query in by Friday so you can get your name in this weeks drawing. Rows: Products Columns: Reseller Sales Amount The percent of each category's Reseller Sales that came from each product Filters: None Hints: Use the IIF function to resolve the divide by 0 error Che...
I'll admit that this week's puzzle was probably a little tougher than the past couple, but I think I gave enough hints that you should have been able to figure it out. So here is one way to solve the puzzle: Let's start out with a base query that has Reseller Sales Amount on the columns and products on the rows. SELECT[Measures].[Reseller Sales Amount] ON COLUMNS, [Product].[Product].[Product].Members ON ROWS FROM [Adventure Works] The first requirement was to limit the results to the top 5 prod...
OPENQUERY() allows you to execute a query against a linked server. But what happens if you need to pass a parameter variable into your query against your linked server? MSDN would tell you that OPENQUERY does not accept variables for its arguments, but there is a work-around. There are actually several variations of the method I use, but this is the one I've used and think its easy enough. So for example, say I have a SQL script that uses OPENQUERY() to query a linked server: SELECT fname, lname...
This week's MDX puzzle is actually courtesy of fellow BIDN'er, MartinMason. Thanks, Martin, for the great puzzle and taking the time to help the BIDN community grow in the business intelligence field! I think our puzzle for this week is a little tougher than the past couple, so take your time and do a little searching on the interwebz if you get stuck or send me a message if you'd like some helpful hints. With that said, on to the puzzle! Rows: Calendar year Product Columns: Reseller Sales Amoun...
The key to solving Puzzle 2 was using the PrevMember function. The PrevMember function returns the previous member of the current member on the same level. That means if the current member is March 2002, the previous member would be February 2002. So the first step would be to create a query scoped calculated member using the PrevMember function. And because we want to see the Reseller Sales measure, we'll include that in the set, as well. We'll call it Prior Period Reseller Sales. WITH MEMBER [...
Well last week's MDX puzzle showed us how to use the Ancestor function to navigate the levels of a hierarchy. This week's MDX puzzle is all about navigating within a single level of a hierarchy. If you need a referesher on a hierarchy and how the levels fit into a hierarchy, I suggest you check out my article, MDX 101: Introduction to MDX . I know the last puzzle maybe gave a few of you some difficulty, so this time I'm going to give a few more hints. Now on to the puzzle! Rows: Calendar month C...