MDX : MDX Tutorial , MDX Query , MDX Script , How to in MDX, MDX Select Query

http://siddhumehta.blogspot.in/2009/03/mdx-mdx-tutorial-mdx-query-mdx-script.html

Tuesday, March 17, 2009

MDX : MDX Tutorial , MDX Query , MDX Script , How to in MDX, MDX Select Query

I'm reading: MDX : MDX Tutorial , MDX Query , MDX Script , How to in MDX, MDX Select QueryTweet this ! inShare

I have seen developers searching for a last minute MDX query / script to create a calculated member on the cube. Many start in an organized fashion with a start from some simple MDX tutorial, and learning tuples, namedsets, axis, MDX Select Query Syntax etc... But still after that, many queries remain unsolved, and the real answer comes after a hands-on experience of some real-time project when one really starts applying functions, tweaking or nesting queries and parameters.

Any material found in MDX should be treasured. By far I have just seen 3-4 books on SSAS flavor of MDX. Below are the links to code-snippets which are FAQ's or used frequently while either querying the cube or creating calculated members on the cube.

Hope this helps all the last minute MDX requirements of those developers who don't have time to spend in learning all the syntax, structure and theory behing MDX syntax just to solve a little query or requirement on a cube in production or last-minute patch.

1.   How do I find the bottom 10 customers with the lowest sales in 2003 that were not null?

2.   MDX query to get sales by prod line for period plus number of months with NON EMPTY sales?

3.   How do I group dimension members dynamically in MDX?

4.   MDX-How do you write query that returns measure ratio to parent value?

5.   In an MDX query how can I get the top 3 sales years based on the order quantity?

6.   MDX-How can I compare members from different dimensions that have the same key values?

7.   MDX - How do you extract first tuple from the set?

8.   MDX - How do you extract first member from the tuple?

9.   MDX - How do you compare dimension level name to specific value

10.               MDX - How can I get attribute key with MDX

11.               MDX - Query to get sales by product line for specific period plus number of months with sales

12.               How do I create a Rolling 12 Months Accumulated Sum that can show a trend without seasonal variations

13.               MDX - How can I setup default dimension member in cube calculation script?

14.               MDX - How do you make calculated measure to be default cube measure

15.               MDX - I would like to create calculated measure that instead of summing children amounts,uses last child

16.               MDX query for the count of customers for whom the earliest sale in the selected time period (2002 and 2003) occurred in a particular Product Category

17.               How to calculate YTD monthly average and compare it over several years for the same selected month

Download MDX Tutorial - Code Snippets of Various FAQ Queries (PDF)

1.   How do I check for division by zero and null to avoid -1.#INF in cells

2.   How do I check for the top level in a dimension to avoid division by an nonexsting parent(-1.#INF)

3.   MDX - How can I use dimension members/measures in query when sometimes they might not exists

Download MDX Tutorial - Code Snippets of Error Handling (PDF)

1.   MDX - How to use the Filter function in a SCOPE statement

2.   MDX - How to create a SCOPE statement based on the Member property

3.   MDX - How do you specify SCOPE For Dimension to include all members before certain member

4.   MDX - How do you specify SCOPE For Dimension to include all members after certain member

5.   MDX - How Can I define SCOPE statement for all measures in one measuregroup

6.   MDX - How Can I define SCOPE statement for all but one measures in one measure group

7.   MDX - How Can I define SCOPE in MDX for all measures in multiple measuregroups?

Download MDX Tutorial - Cope Snippets SCOPE statement (PDF)

1.   MDX - How do you get Last month in the time dimension

2.   MDX - Query example that returns list of months from start of year up to specified month

3.   MDX - Query to show the first day of the last date month in the cube

4.   How in the report can I order date dimension members in descending order?

5.   MDX - query to get the last Month loaded into a cube

6.   MDX - Query to get the first month of the last year loaded into a cube

7.   MDX - How do you write query that uses execution date/time ( NOW() ) as a parameter?

8.   MDX - Query to get latest months and previous years same months data

9.   MDX - Query to show year level data for all years except last one, and month level data for last year

10.               MDX - How to create calculated member for AVG sales over last 3 years based on NOW()?

11.               MDX - How do I calculate sales for 12 Month to date?

12.               MDX - How can I get Last (Previous) Year to Date (YTD) values?

13.               MDX - Query to get count of months with sales amount > 0 in defined period

14.               MDX - How do you calculate monthly average of one year, optionally including empty months?

15.               MDX - How do you calculate monthly average of one year, optionally including empty months?

Download MDX Tutorial - Code Snippets for Time Dimension (PDF)