MDX queries can be very wordy. Many functions have more than one parameters. All of these make formatting MDX queries for easier reading extremely important.
I recently shared a free tool with a co-worker. I know several free tools. Here are two that I used very often.
- I am not sure who created the first tool. The second tool is created by Mosha Pasumansky. It also has a desktop version. For me the online tool works perfectly.
- Thanks to all the authors.
- I personally like the MDX Studio Online better, because it produces the one-line-per-element style with comma at the beginning of the line. This is the style I’ve been using in all my TSQL codes.
- Here is an sample result from the MDX Studio Online:
It also gives you a Parse and Analyze option.
Experiment with the formatting MDX queries for easier reading. You will eventually develop your own formatting style.
Here is a blog I posted , SSRS #60 – Steps to implement a data-driven subscription, a few month ago.
A friend recently had a challenge of the following requirements:
- Produce 4 reports daily/monthly from 4 existing SSRS report.
- Use prior day/month as reporting day/month, but use default or All for all other report parameters.
- Send all 4 reports in 4 PDF files to business in one email.
My friend made the following design decisions:
- use the 4 existing SSRS report; do not create new reports so maintenance of the reports reside in one report.
- use data-driven subscription to meet the dynamic requirements of report parameters.
- use Windows File Share option to push the report to a shared location on the server.
- once all 4 reports are ready on the shared drive, send them out to business as attachments in email.
- It turned out that using the Windows File Share option can be very confusing. Here are a few tips my friend shared with me. Thanks to her tips.
1. The data source choice in Step 1 has nothing to do with the access to the shared location on the server.
- The choice of the data source in this case is used by the Report Services to retrieve the dynamic subscription data from your SQL table. It has nothing to do with the account you are going to use (in the later step) to deliver the report to the shared location.
2. The account you use to deliver to the shared location, must be a network account. It cannot be a SQL account.
3. One some servers, the account you use to deliver to the shared location, must be a domain service account.
You must communicate with your system administrator to get the appropriate write access to the shared folder.
Unlike TSQL, a simple MDX query can include the All level in a dimension to allow use to see a measure aggregated across ALL members in the dimension. This is a powerful and welcome blessing given to us from the Analysis Services and MDX engine.
However, this blessing can be annoying in the Reporting Services when we want to do the aggregation within the Reporting Services by using the grouping feature.
In the SSRS reports I designed, I often use the three-part naming convention for dimensions to exclude the ALL level in a dimension. This allows me to get measure values only for the members in the dimension hierarchy without the aggregation at ALL level.
Here is an example of the three-part naming convention for dimensions:
The first part is the name of the cube dimension. The second part is the name of the attribute hierarchy. The third part is the name of the default level for the attribute hierarchy, which is the same as the attribute hierarchy.
If you have a user hierarchy for a dimension, the above still applies with the second part being the name of the user hierarchy and the third part being the level you want. The difference is that the level name will be likely different from the user hierarchy.
So these will be the general usages for both attribute hierarchy and user hierarchy.
Take a look at the following four MDX query results.
1. Using the three-part name to exclude the ALL level
2. With only two-part name (dimension and hierarchy), only ALL is returned.
(ALL is the default member for the hierarchy in the design.)
3. With two-part name and AllMembers function ALL level and all the children members are returned.
4. With two-part name and Children function, the ALL level is also excluded.
(So the three-part name is not the only way.)
So next time when you sit down to write MDX queries, do not hesitate to experiment and draw conclusions for yourself.