Courtesy of Richard Tkachuk, I’ve just found out about another new MDX function in AS2005: CustomData(). You use it as follows:
- You assign any value to the CustomData connection string property, eg as follows: CustomData=Hello World!
- You can then retrieve this value in your queries, eg
WITH MEMBER MEASURES.TEST AS CUSTOMDATA()
SELECT MEASURES.TEST ON 0 FROM
[Adventure Works]
What’s the point of this? As Richard pointed out to me, it should prove useful in middle tier scenarios. Imagine you have a web application using Reporting Services 2005 which in turn connects to Analysis Services 2005. Anyone connecting to your web application is authenticated using whatever method your company uses, and you can then pass in a user name through the connection string RS uses to connect to AS (RS2005, I understand, allows you to have dynamically generated connection strings); AS can then use the CustomData function to pick up this user name and use it in a dynamic cell or dimension security definition. I’m sure this functionality will come in handy for other things too, but I just haven’t thought what those things are yet…
I have a scenario for you, sort of…
We use SSAS 2000 and are using writeback. In some cases, with virtual cubes, users are denied writeacess hence we need to use admin credentials in the connectionstring. But we would also like to log who made the writeback, and this is where CustomData would come in handy 🙂
There are, of course, several issues: not 2005, not sure how CustomData() works in update cube statement