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
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.
Boyan Penev on Microsoft BI 
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.
LikeLike
Sorry I missed it ! [wrong continent you see 🙂 ].
Will there be a webcast made available?
I’d really like to see it.
J
LikeLike
Nobody recorded it, so no – no webcast. Maybe a thing to bug Darren about organising…
LikeLike
Thanks for the presentation! I saw someone recording it on his phone 🙂
LikeLike
If we can get the video it would be great 🙂
LikeLike
In order to run the supplied MDX scripts for training purposes you think you could provide login details to your testing Analyses Services server?
LikeLike
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.
LikeLike
Third row from the front, isle seat, male, dark hair. You could try sending an email to all attendees.
LikeLike
Will try to track him down 🙂 Thanks – you are extremely observant!
LikeLike
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!
LikeLike
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).
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
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.
LikeLike
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
LikeLike
@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.
LikeLike
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?
LikeLike
@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.
LikeLike
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
LikeLike
which Vm Software do you recommend? Which Vm ware does priorities?
Cheers thanks for all your help
LikeLike
@Chris
I’m sorry but I have no idea. I guess you can start with Hyper-V being a Microsoft product.
LikeLike
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
LikeLike
A simple and intleilgent point, well made. Thanks!
LikeLike