SQL Server Performance Forum – Threads Archive

MDX help request for making a calculated member

Hi, I am using Microsoft Data Analyzer and am using one of the "pre-built" MDX (offered by Data Analyzer) to find the %age. The MDX statement for %age looks like below in Data Analyzer: Length measure/sum(Members in filter for current dimension,Length measure)*100
I am trying to instead use a calculated measure on the server (for %age) rather than using Data Analyzer’s solution – which is working fine and giving correct results but is slow. Assuming that my Length measure is based on a measure called ‘TotalSales’ how do I convert the rest of the MDX; especially the following part: "SUM(Members in filter for current dimension,Length measure)"
so that I can define a calculated members on my cube. I hope my post is clear and will be very grateful for your help. Many TIA.

I’m not sure if you will find very significant performance gains cause the calculated Members do not store data in the cube, they calculate values as needed and are calculated at query run time, after aggregations. I am not to clear about your question on converting the mdx. Raulie
@hp All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thank you for the reply and the tip regarding no performance gain. Appreciate it. It will be difficult but let me try if I can clarify my my question. Here I go (please bear with me): I guess I can re-phrase my question and ask that does anyone know the equivalent MDX expression NOT statement that will work instead of the following "Data Analyzer expression": Length measure/sum(Members in filter for current dimension,Length measure)*100 I tried to create a calculated member on my cube, named it %age and when i entered the above in the expression, it did the expected – I got an error. The key to my question probably lies in finding out what actually the underlying MDX is for the following portion of the above satement: "Members in filter for current dimension" If I can figure this part out, I can probably make progress. After some luck, I found out that the data analyzer internal syntax for the above part is the statement "~Set:$$CurrentAspect<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />elected~" But that about it….and I am again stuck with converting this to an equivalent MDX statement <img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ /> I hope this makes my post a little more clearer. Any clues? Thanks.

What dimension is this suppose to be?
"Members in filter for current dimension" Raulie
@hp All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Raulie that’s exactly my question i.e. How is Data Analyzer translating this "Members in filter for current dimension" into something like ‘Open Dimension.Current Member’ or something like that. Don’t know what the answer is but it works great and in all dimensions and at any level, I can get the %age and to summarize: the reason for my post is to understand how it works so that I can do it on the cube and thus NOT BE DEPENDANT on Data Analzer since we will probably me moving to a new and better front-end tool. Hope my post is clear. Regards.

Most of these front end tools hide the MDX behind the scenes. I know you can log the MDX queries sent to Excel through Pivot Table Services so you most likely can do the same with Data Analizer. Include this statement into your connection string. This will log all the MDX into a text file. Nice little way way to learn MDX = ) PROVIDER=MSOLAP; LOG FILE=C:OLAPLOG.TXT Raulie
hp

Many thanks Raulie. Your suggestions is working and I am getting the underlying code in a text file. I will now try to go through the resultant file and figuyre out the desired MDX. Many thanks. Really appreciate it! <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

]]>