MDX Gotchas Presentation Materials

I had the pleasure to present in front of one of the biggest and best SQL Server User Groups in Australia and as Greg Linwood proudly (justified so) said – in the world. As I promised I have uploaded the presentation materials to this blog and you can find links to the slides, MDX scripts I run and some useful links below. As a side announcement, Darren Gosbell is now officially in charge of the Melbourne SQL Server User Group. I am sure that his passion for SQL Server will lead to great results and we will be able to enjoy a strong stream of speakers in the coming years.

To everyone who attended tonight – Thank You for coming and for giving me great reviews!

Useful Resources

The Many-To-Many Revolution by Marco Russo

SQL Server 2008 Performance Guide by SQL CAT and the SSAS Product Team

VBA Functions in SSAS by Irina Gorbach

Performance Improvements in Analysis Services on MSDN

Measure expressions: how performant are they? By Christian Wade

MSDN SQL Server Analysis Services Forum

TechNet Tech Center: Analysis Services – Multidimensional Data

SSAS-Info

Books

Microsoft SQL Server 2008 MDX Step by Step – Beginner/Intermediate

MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase – Beginner/Intermediate

The Microsoft Data Warehouse Toolkit: With SQL Server 2008 R2 and the Microsoft Business Intelligence Toolset – Beginner/Intermediate

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services – Intermediate/Advanced

Microsoft SQL Server 2008 Analysis Services Unleashed – Expert

Slides and Scripts

[listyofiles folder=”wp-content/MDX Gotchas”]

Please let me know if you need more reference materials or if you think that some that I have missed should go in this post.

Advertisements

24 thoughts on “MDX Gotchas Presentation Materials”

  1. And thank you to you Boyan for the presentation. Some excellent food for thought MDX catches. hopefully we can see you back again in the future to help expand our understanding of the MDX/SSAS world.

    Like

  2. Sorry I missed it ! [wrong continent you see 🙂 ].

    Will there be a webcast made available?
    I’d really like to see it.

    J

    Like

  3. In order to run the supplied MDX scripts for training purposes you think you could provide login details to your testing Analyses Services server?

    Like

    1. Well, the scripts are written for a cube larger than Adventure Works, which is not exposed to the world. In general the first measure in each script is inefficient and runs in cell-by-cell mode, while the second (and in one of the scripts – third as well) are better and illustrate how to avoid doing the mistakes in the first. You can try replicating the behavior against Adventure Works by changing the dimensions and measures in the scripts and you should get similar results. Maybe the run times will be somewhat closer.

      Like

  4. Hi Boyan, I can’t work out these measures in Adventure Works FilterDatesCount from script “02 – Filter Key Calc” (returns 0 for any year) and Bad Measure, Good Measure from script “03 – VBA_StrToSet” (both return null for any month). Would you be able to help out? I am new to MDX and need to run your presentation for the rest of the team. Thanks!

    Like

    1. In adventure works the keys are different. You will have to check their format (different in different attributes too). The easiest way is to create a new MDX query in SSMS against Adventure Works, then expand the hierarchy you want to work with and then drag-drop the particular member in the query area. It will give you something like [dim].[hier].&[key]. Then you can take the key and use it in the queries. Also, in case the queries are not using [d].[h].&[key] but [d].[h].[name], you can type in the name of the member which you can see in the metadata area (i.e. the actual member name).

      Like

  5. hi, thank you for your help. I conducted the MDX learning session yesterday for our team in Adventure Works cube, here are some questions for you: 1. is data cube dynamic or static, i.e. when data has changed are those changes reflected in the cube; 2. how to check the data quality for instance detect gaps in data; 3. how to deal with gaps

    Like

  6. 1. It depends – you can say it’s static, but it can refresh itself if you implement ROLAP and Proactive Caching. Typically, you would process a cube to get the data refreshed.

    2. Well, either by implementing some ETL logic, or merely by browsing the cube. I guess if you know where to expect gaps you could also create some reports to show you these.

    3. When I spoke about gaps, I meant gaps in your Time dimension. The Time dimension should be more or less static – once you populate it correctly it will remain correct. There is nothing wrong with gaps in the fact tables or in other dimensions.

    Like

  7. Hi
    Thanks for the Presentation on MDX

    Is this the place where I can ask a question.
    Or do you have a forum ?
    Q:
    I am using generate function on the rows to get a heading and a total but heading is the same name as the total.

    The heading is created by a Currentmember. While the total is generated by the hierarchize (descendent SELF_AND_BEFORE). Is there a way to suppress the calculation on the heading or a property in the currentmember.noCalculate.
    Or dynamically change a query scoped member name?

    Hope this makes sense let me know if I have posted this in the wrong place

    Thanks

    Like

  8. Hi Chris,

    You can ask me here, but you can also ask on the MSDN forums, for example, where you’ll get a quicker response in general.

    I don’t quite understand the Total requirement. I am assuming you are trying to get the name of the members as a string, and that you are also trying to get the total to say “Total”, or something like that?

    Can you please clarify the question in re to what you are trying to get for the totals?

    It would be great if you email me (or use the contact form on the right) and send me your MDX, or a sample against Adventure Works with an explanation of the expected result. If it is against Adv Works I can send you some working MDX back and we can discuss why things are happening as they are.

    Like

  9. HI Thanks for the reply I just resolved it with VBA it wasnt worth the the trouble for a heading. Sorry if i wasted your time .
    But I do have another question.
    This is based on pure curiosity. I was thinking there must be a better way then how my current client has set up his hardware.
    I wanted to ask the CAT but they don’t have Forum section in there web page
    Anyway the scenario is we have 4 physical servers

    staging
    Production Sql
    ssas
    ssrs

    So a basic setup. So at night the staging and the production sql server gets thrashed for an hour and the servers doesnt get used for the rest of the day. While during the day the ssas and ssrs servers get thrashed .

    And I am thinking this s a typical middle to large scale Setup.
    Is there a better way we can use the four server resource.Or howwowuld you set up the 4 servers to be used equally during the day and ETL night?

    Hope this makes sense

    Thanks

    Like

  10. @Chris
    I suppose you could install SSAS or SSRS on the SQL instances. You will need to find out what will be better – 2xSSAS+2xSSRS or 3xSSAS+SSRS or SSAS+3xSSRS. I guess you could check the resource utilisation on the SSAS and SSRS servers and depending on that you can decide on the overall configuration.

    After deciding, you should read SQL CATs papers on each scenario – e.g. how to configure the environments. I would imagine you’d be doing some sort of load balancing, but the specifics would be hard to explain here and the CAT team has put a lot of effort in providing best-practise advice for scenarios where you need to scale out.

    In general, I would be weary with memory usage as you’d need to allocate different amounts during the different phases and you can even go as far as switching instances on/off as required.

    The topic is just way too broad for specific advice over this medium.

    Like

  11. Cheers
    Just curious
    In your bigger projects deployment
    Your standalone ETL server what does the server do during the day time after ETL has finished for the night?

    Like

  12. @Chris
    Well, I’ve had a couple where the ETL server was used for a number of different applications, so I have a window and during the rest of the time it either runs other ETLs, or processes stuff as a back-end for other apps.

    Sometimes people are happy with having a large ETL box, which does not get utilised outside of the processing window. Also, sometimes I see the servers being virtualised and given more priority during busier periods.

    Like

  13. Awesome idea VM the box to one of the Reporting Servers and change priorities during the window period and Scale out the reporting server with the spare server

    Like

  14. No worries thanks for all your help I was reading around In this modern age VM software cant allocate resource on the server like i wanted.Maybe in the future.

    Cheers

    Like

Comments are closed.