I recently built a tool which should help to debug the MDX scripts of an Analysis Services cube in order to track down formula engine related performance issues of a cube. As you probably know most of the performance issues out there are usually caused by poorly or wrong written MDX scripts. This tool allows you to execute a reference query and highlights the MDX script commands that are effectively used when the query is run. It provides the overall timings and how long each additional MDX script command extended the execution time of the reference query. The results can then either be exported to XML for further analysis in e.g. Power BI or a customized version of the MDX script can be created and used to run another set of tests.
The tool is currently in a beta state and this is the first official release – and also my first written tool that I share publicly so please don’t be too severe with your feedback – just joking every feedback is good feedback!
Below is a little screenshot which shows the results after the reference query is executed. The green lines are effectively used by the query whereas the others do not have any impact on the values returned by the query.
A list of all features, further information and also the source code can be found at the project page on codeplex:
Also the download is available from there:
Looking forward to your feedback and hope it helps you to track down performance issues of your MDX Scripts!
Recently I had to setup an Analysis Services cube and expose it to external users. This is usually done by using Internet Information Server (IIS) and creating a new WebSite which hosts msmdpump.dll. This DLL more or less wraps XMLA commands inside HTTP thus allowing external users to access the cube via HTTP. Besides Windows Authentication this setup also allows Basic Authentication and so external users can simply connect by specifying Username and Password in e.g. Excel when connecting to the cube:
There are already a lot of whitepapers out there which describe how to set things up correctly. Here are just some examples:
– MSDN: http://msdn.microsoft.com/en-us/library/gg492140.aspx
– MSBI Academy (great video!): http://msbiacademy.com/?p=5711 by Rob Kerr
They provide very useful information and you should be familiar with the general setup before proceeding here or using the final PowerShell script.
The PowerShell script basically performs the following steps:
- Create a local folder as base for your WebSite in IIS
- Copy SSAS ISAPI files (incl. msmdpump.dll) to the folder
- Create and Configure an IIS AppPool
- Create and Configure a IIS WebSite
- Add and enable an ISAPI entry for msmdpump.dll
- Configure Authentication
- Configure Default Document
- Update connection information to SSAS server
I tested it successfully with a clean installation of IIS 8.0 (using applicationhost.config.clean.install). In case you already have other WebSites running you may still consider doing the steps manually or adopting the script if necessary. The script is written not to overwrite any existing Folders, WebSites, etc. but you never know.
So here is my final script:
The script can also be downloaded here.
The process of setting up HTTP connectivity is the same for Analysis Services Multidimensional and Tabular so the script works for both scenarios, just change the server name accordingly.