Recently I had the need to start learning MDX to query against an existing Microsoft SQL Server Analysis Services (SSAS) cube. As this can be a long and difficult journey, I thought it’d be useful to make notes about specific things I learn as well as to list and review resources I find along away. I will be paying special attention to using MDX with SQL Server Reporting Services (SSRS) as this is a very common usage scenario, but one in which there are sparse learning resources.
I’ll try to keep this post updated with an index of resources as I consume them. Some will be free, some will not, I’ll note which are which. But first, some housekeeping:
Everybody learns things a little differently. Regardless of how you learn, knowing how you best learn new things can make all the difference. Personally, I find a lot of verbose text and definitions to be not very effective for me. I’m a visual learner and experimental learner. I need to see and visualize things. Even better when I can get my hands on something and experiment and play with it to see how minor changes effect it. Additionally, I find that it greatly helps if I have a real world objective or problem that I’m trying to solve. Samples and examples that I can take apart and adapt that are similar to the solution for my real-world problem really resonate with me. So, why am I going into all this? When I evaluate resources, those evaluations are based completely on my personal biases toward how I learn. So, what works best for me may not work best for you, it all comes down to how you learn.
Resources and Tools
I’ll be mentioning what I learned from various resources throughout this series. To keep things simple, however, I’ve created a single post to serve as a master list of all resources which I’ll keep updated. The full list of resources and tools can be found here.
One of the most critical thing for someone who learns best through hands on learning is an environment in which you can be hands on. The majority of the resources which I’ve used, as well as any samples I put together, use Microsoft’s AdventureWorks SSAS Multidimensional and Data Warehouse samples. I’ll be spending the majority of this post covering setting up a development environment for SSAS in greater detail.
Environment – SQL Server 2014 Components
The first component necessary for the development environment is SQL Server 2014. This will be the service which holds and data and responds to queries. The following components will be needed at minimum: Database Engine, Analysis Services (Multidimensional), Management Tools. While the Database Engine and Management tools are available for every edition of SQL Server, Analysis services is only available in a handful of editions — none of which are completely free. Fear not, there are two low cost options. One is free, the other is pretty cheap. The first option, the free option, is to use the SQL Server 2014 Evaluation, which is a fully functional (for 180 days) version of Enterprise Edition. There’s nothing stopping you reloading your dev machine (especially if it’s a virtual machine) and starting the 180 day evaluation over once it runs out. The second option is to purchase SQL Server Developer Edition which costs around $50 and is a fully function non-time limited version of SQL Server Enterprise Edition intended for non-production (development use).
Regardless of which edition of SQL Server you choose to use (Standard, Enterprise, Enterprise Evaluation, or Developer), you’ll need to install the Database Engine, Analysis Services (Multidimensional), and Management Tools, at minimum. You may optionally want to install SQL Server Integration Services (SSIS) and SQL Server Reporting Services (SSRS), but they won’t be necessary for this series. As a tip, it is not necessary to install these components on the same machine. If you’re using the time-limited Evaluation version, you may want to install the Database Engine and Analysis services on a disposable server or virtual machine and the management tools on your more permanent workstation. Either way is fine.
The installation instructions for SQL Server are pretty well documented. I recommend following the guide here to perform the installation.
Environment – SQL Server Data Tools / Visual Studio 2015
The next component we need to setup in our development environment is SQL Server Development Tools (SSDT). These will be the management and development tools we use to perform queries. The version we will be installing is built upon (and installs a small version of) Visual Studio 2015. There are a number of older versions of SSDT out there (for Visual Studio 2012, 2013, and earlier) and there is a BI edition as well. Fortunately, Microsoft has started unifying the versions and the current release of SSDT Preview (version 14.0.60203.0 as of this writing) fully supports both the Database and BI tools inside of the Visual Studio 2015 shell.
Download and install the SSDT Preview version from here (the step 2 section) on your development workstation. We will use these components alongside the management tools to interact with the data. Run the setup wizard and proceed through with defaults.
Environment – Adventure Works OLTP Data Warehouse (AdventureWorks Home)
The Adventure Works OLTP Data Warehouse is the relational database that serves as the source of data for our cube. This is our sample data. This can be downloaded as a SQL Server backup file from the Adventure Works download site, named Adventure Works DW 2014 Full Database Backup.zip.
The database backup can be installed via SQL Server Management Studio by performing a restore operation. Full instructions are available elsewhere on the Internet, one such example is here.
Environment – Adventure Works Analysis Services Database (AdventureWorks Home)
The Adventure Works Analysis Services Database is a fully built MDX cube with the Adventure Works data warehouse data already loaded. This is our sample data, which will later be refreshed using the data warehouse data. This can be downloaded as a SQL Server Analysis Server backup file from the Adventure Works download site, named Adventure Works Multidimensional Model SQL 2014 Full Database Backups.zip. The ZIP file contains two versions — one for SQL Server Standard Edition and one for SQL Server Enterprise Edition. Use only the version which matches the version installed previously (Enterprise for Evaluation or Developer).
The SSAS backup can be installed via SQL Server Management Studio by performing a restore operation. Full instructions are available elsewhere on the Internet, one such example is here.
Environment – Adventure Works Analysis Services SSDT Project Files (AdventureWorks Home)
The Adventure Works Analysis Services SSDT Project files are the Visual Studio project files which were used to build the Adventure Works cubes. These can be used to make changes to the structure of the Adventure Works cube and rebuild it. The project files can be downloaded from the Adventure Works download site, named AW Multidimensional Models SQL Server 2014.zip. As with the Multidimensional full backup files, two versions (Standard and Enterprise) are contained within.
The SSDT Project files don’t require any specific installation. Simply unzip the ZIP file and open the AdventureWorksDK2014Multidimensional-SE.sln Solution File from within the Standard or Enterprise directory which will open the source code in SSDT / Visual Studio.
Environment – Adventure Works 2012 SSRS Report Samples (AdventureWorks Home)
The Adventure Works 2012 SSRS Report Samples are pre-built reports which make use of the Adventure Works databases and multidimensional cube. These will be run via SSDT. Despite these being the 2012 version, they work fine with Adventure Works 2014. The Adventure Works 2012 SSRS Report Samples can be downloaded from Microsoft’s Codeplex site.
The SSDT Project files don’t require any specific installation. Simply unzip the ZIP file and open the AdventureWorks Sample Reports 2012.sln Solution File from within the directory which will open the source code in SSDT / Visual Studio.
Environment – MDX Studio
MDX Studio is a free alternative development environment for writing MDX SSAS queries to SQL Server Management Studio. While MDX Studio is older than SSMS, it does offer some useful features which SSMS doesn’t have built in, such as the option to Format MDX code into a more readable format so it is a useful tool to have around. MDX Studio can be downloaded from here and should be installed via the setup wizard with default options.
Environment – Query Capture
Query Capture is a small utility which will connect to an SSAS server, monitor for MDX queries, capture the queries, and friendly up the parameters which were passed to the query. It is a fairly old and unsupported utility and is available from the original source here. The original binary doesn’t work with SQL Server 2014, however, the source code can be used to rebuild the binary in such a way that it will support SQL Server 2014. To do so, you will need to do the following (Note that you may need Visual Studio 2013 or 2015 Community Edition or higher to perform the below steps. Alternatively, if you’re unable to perform these steps, you can download the version I rebuilt from here. To rebuild the program yourself:
- Download and unzip QueryCaptureSource zip file from the original source
- Open the QueryCapture.sln project file and allow the project to upgrade to the latest version.
- In the References section in the top right pane, delete the Microsoft.AnalysisServices reference
- Add a new reference in the References section in the top right pane. Choose Browse and locate the SQL 2014 version of the Analysis Services DLL, which for me is located in: C:\Program Files\Microsoft SQL Server\120\Setup Bootstrap\SQLServer2014\x64\Microsoft.AnalysisServices.DLL
- From the Build menu, select Build Solution and a new .EXE file should be placed in the bin\Debug subfolder of your source folder.
Testing the Environment
At this point, you should have a pretty robust SQL Server Analysis Services MDX environment setup. The last step remaining is to test the various facets of the environment to ensure everything is working correctly.
We first want to test the Adventure Works OLTP Data Warehouse. Launch SQL Server Management Studio (SSMS) and connect to your SQL Server Database Engine, on localhost unless you installed SQL Server on a different machine.
If you’re unable to login, ensure that your SQL Server Database service is started and that you are connecting to the right server name. After logging into the Database Engine, expand the Databases folder and then expand AdventureWorksDW2014 and then expand Tables. You should see a number of tables present. Right click on one of them, such as FactInternetSales and select “Select Top 1000 Rows.” If data is returned in the bottom of the center pane, then congratulations, you have a functional copy of the Adventure Works 2014 Data Warehouse database and are ready to move onto the next step.
Next we want to verify that SSAS is functioning properly. To do so, again launch SQL Server Management Studio (SSMS) and change the Server type to “Analysis Services” and attempt to connect to your server. If you’re unable to connect, verify your server name (probably localhost) and verify that the SQL Server Analysis Services service is started.
Once connected to Analysis Services with SSMS, expand Databases and you should see an AdventureWorksDW2014Multidimensional item. Expand this and then expand the Cubes item. From here, you should see an Adventure Works cube. Right click this and select Browse.
Upon choosing to browse the cube, a new pane with a visual query designer should open. On the left side of the visual designer, expand Measures and expand Internet Sales. Drag the item named “Internet Sales Amount” into the center pane of the window and release. Upon doing so, you should receive a total value. This is the Total Internet Sales in the cube for all products and all time. This means that the cube is successfully returning data.
We’ve verified that the SSAS Multidimensional cube successfully returns results with the data which was originally cached in the cube, however, we also want to make sure that the cube is able to be updated (processed) with new data. Expand the Data Sources item under AdventureWorksDW2014Multidimensional and select Properties on AdventureWorksDW2014.
Within the data source properties there are two items you’ll need to check. For each item, on the very right side of the value, there is a small button which will open a dialog to allow you to change the value. The first is the Connection String which should make reference to the server name in which your Adventure Works OLTP Data Warehouse resides (probably localhost) and the name of the database (probably AdventureWorksDW2014). This will probably be right by default.
The second item to check is the Impersonation settings under Security, which is set to ImpersonateAccount by default and will probably not work by default. Select the button on the right to open the properties for this item.
Change the impersonation option from the default option to “Use a specific Windows user name and password” and enter a valid username and password for the machine which the service is installed. If you have issues processing the cube initially, these credentials are most likely going to be the culprit.
Finally, the moment of truth. Right click on the AdventureWorksDW2014Multidimensional item and select Process. When this starts, the cube will access the OLTP data warehouse to rebuild its internal data structures.
Keep the default options and press the Ok button to begin the cube processing.
The cube processing will now begin. If you have a credentials issue, it will fail pretty quickly. If, however, everything is working, the processing will take 1-3 minutes and will notify you once it is completed.
At this point, you’ve completed the tests of the major components and should have a good and stable development environment for continuing to learn MDX. If any of the tests failed, it’s best to work to resolve those issues now so you can start from a known good point…MDX is hard enough as it is without challenges from the environment as well!
Learning MDX Part 2 – Let’s Get Visual