Or How I Learned to Stop Worrying and Love String Manipulation 😉 .
To kick off this blog with some real content I’m starting small. I recently did some reporting work where I changed the main dataset queries from using a relational database to using a cube. It sped up performance and resolved a lot of calculations that were nasty in T-SQL. However, when I went to implement the new query in the report I ran into a small obstacle. Our parameter lookups were all in T-SQL and needed to stay that way because of some security we had implemented. So I needed to be able to take the parameter value from the database and translate it into a form that the MDX query could handle.
Let’s look at a quick example. I’ll be using the AdventureWorksDW2008R2 Database (which isn’t much different – if at all – from the AdventureWorksDW2008 Database) and I’ll be using the AdventureWorks 2008 Analysis Services project. These are both available online from codeplex.
Generally speaking, our problem isn’t so hard. In our case, the value was an ident that was the same as the member key for the corresponding attribute. Let’s say our report needed ProductCategory as a parameter. The ProductCategoryKey in the relational database for Bikes is 1, and our cube member for Bikes is [Product].[Category].&. So we can transform our parameter value to pass into our mdx query like so:
“[Product].[Category].&”+Cstr(Parameters!ProductCategory.Value) + “]”
Simple! But the plot thickens…
I needed to be able to pass multiple values, which meant I had to take some extra steps before I could parse my string. If ProductCategory is a multi-value parameter, then Parameters!ProductCategory.Value is an object – an array of values. So we need to create a single string out of that array, format that string, and then break it up again back into an array since that is what the MDX query is expecting. Whew!
Here’s the punchline:
That’s a little bit of a mouthful! What’s happening here?
Join() is a handy little function that takes our array and joins the values into a string. We can delimit the values with a string, in this case “],”, like so:
Bikes, Components, and Clothing for our parameter values, our string after using the join function will be “1],2],3”. We can then add an ending bracket to the end via simple string concatenation so now we have “1],2],3]”.
Applying the Replace() function, we replace “,” with “,[Product].[Category].&[“ and now we have “1],[Product].[Category].&,[Product].[Category].&”. Finish with one final string concatenation in the front and we have “[Product].[Category].&1],[Product].[Category].&,[Product].[Category].&”
Unfortunately we can’t just pass this into our MDX query. It’s expecting an array object, not a string! But luckily, the Split() function is the counterpart to Join() and breaks up our string into that array object we want via the commas as a delimiter.
Overall, not a difficult task, but it took me enough time to figure it out that I don’t want to have to re-derive the solution. Now you don’t have to either!