Business Intelligence, SQL Server, and other assorted IT miscellany

Most introductions start with an explanation of cubes and MDX and then work their way toward the syntax and structure of a query and then start to cover the various syntax options in increasing difficulty. While that is a great approach, sometimes you just need to get going right away. Fortunately, MDX actually has a few good visual methods of querying an existing cube to access data without necessarily needing to know the syntax and theory (however, that doesn’t hurt!). This post is going to cover a few of the visual methods of querying a multidimensional analysis services cube.

All that being said, it does still help to understand the structure of your data and the various relationships before trying to query it. I’m using the Adventure Works 2014 Data Warehouse and Analysis Services data sets (previously discussed here). The Adventure Works 2014 Data Warehouse contains a number of different fact tables and related dimensions. I primarily use FactInternetSales (and its related dimensions) which is data for the Internet Sales of Adventure Works. The diagram for the relational data warehouse database can be seen below.

Adventure Works 2014 Data Warehouse Internet Sales diagram

Analysis Services queries do not query the relational database model directly. Instead, they query the model (and data) inside of the multidimensional cube. This cube can have its own data model, which typically will follow very closely to the relational data warehouse model, as can be seen below. The diagram tools inside of SQL Server Management Studio for the relational data warehouse are far more friendly to use than the diagram tools built into SQL Server Data Tools for the Multidimensional Cube!

Adventure Works 2014 Multidimensional Cube Internet Sales diagram

With an understanding of the multidimensional data model which contains the sample data, we’ll be querying it with three different visual tools:

SQL Server Management Studio Cube Browser

SQL Server Reporting Services Query Designer

Microsoft Excel

SQL Server Management Studio Cube Browser

To get started, launch SQL Server Management Studio (SSMS) and set to the type to Analysis Services and connect to your SQL Server Analysis Services server. Once connected, expand AdventureWorksDW2014Multidimensional and then Cubes and right click on the Adventure Works cube and select Browse.

The Browse menu option on the Adventure Works cube launches the visual query designer

The Cube Browser visual designer will now open. There are four main sections of the design window that we’ll look at and I find it helps to think of ordering food in a restaurant to explain them.

The pane on the upper left side of the screen is the menu of available options. Much like a restaurant, these are all the things that the restaurant serves and you can order any of them which you like, however, some combinations make more sense than others and it’s up to you to have an idea of what things go well together (knowing the data diagram above).

The Calculated Members in the bottom left pane are the off menu items. These are the things you’d ask the chef to make special just for you. By default, there’s nothing here and you very much need to know what you want. Things here take more work to produce than the pre-selected menu, but you can get some more interesting results as well.

The top pane contains the filters and parameters. These are little tweaks you might make to your order, such as “hold the onions” or “only sales data for Fiscal Year 2013.”

Finally, the center pane is your plate of delicious data. Dragging dimensions and measures fro the two panes onto the left into the center pane will produce a preview of the data that your query produces. Some combinations of dimensions and measures will work better than others.

With the basic functionality of the visual designer understood, it’s time to answer a business question. “What are the total Internet Sales dollars by category and fiscal year for all of the years for which we have data?” To get the answer:

From the left pane, expand Measures then Internet Sales and find the item named “Internet Sales Amount.” Drag this into the center pane and a single total should appear. We now can answer the question of “What are the total Internet Sales dollars altogether?” On the right track, but not quite the answer to our original question.

Next, expand Date then Fiscal and drag Date.Fiscal Year to the center pane, to the left of the Internet Sales Amount item. We should now have a breakdown of total Internet Sales Amount by Fiscal Year. Getting closer.

Finally, we need to add Product Categories to the query. Expand the Product dimension from the left pane and find the item named Category. Drag this to the right of the Fiscal Year column in the center pane. We now have a result which matches the above screenshot and answers the question of “What are the total Internet Sales dollars by category and fiscal year for all of the years for which we have data?”

Being able to drag and drop items via the visual designer to produce functional results quickly is a pretty handy thing. But, there’s one other huge benefit to the query designer, and that is the ability to view the actual text-based query behind the visual designer. This is tremendously handy for both learning MDX syntax as well as getting a head start in developing additional queries.

To view the query behind the visual designer, press the small “Design Mode” button on the top menu bar. You will be rewarded with a block of difficult to read text.

The visual query designer can show the actual text based query via the Design Mode menu icon

Being able to see the query is great, but it’s completely unformatted and difficult to read. Fortunately, there are two simple options to reformat the query to make it easier to read. One option is to use the Format menu in MDX Studio (if you have it installed). The other option, is to go to http://formatmdx.azurewebsites.net/ and simply paste your query into the window and press the Format button. The bottom of the screen will return your exact query, but formatted to be much more human readable.

Format an MDX query to make it more readable via the Format MDX & DAX website

One word of warning regarding the query view of the visual designer. While the designer can take a visual representation and show you the text-based query, it can’t convert a text-based query into a visual representation. You can modify and save your changes to the text based query, but if you try to go back to visual mode, you’ll receive a warning that it will reset your query back to the initial blank. So, while reformatting a query to make it more readable is great, you shouldn’t paste it back into the designer window unless you’re ok not using the visual design mode for this query any longer. It’s best to take the reformatted query to a new SSMS Window and continue working on it from there, outside of the cube browser and visual designer.

Changing the text in the query designer and switching back to visual mode results in a loss of the query

SQL Server Reporting Services Query Designer

The second method of querying an SSAS Multidimensional cube visually is via the SQL Server Reporting Services Query Designer. To get started, launch SQL Server Data Tools (aka SSDT for VS 2015 or SSDT-BI for VS 2013) and either create a new Reporting Services project or open the Adventure Works 2012 Report Samples project. Next, either create a new blank report or open the existing Sales Reasons Comparisons sample report.

If using the Sales Reasons Comparisons sample report, it’ll already have a data source defined. If not, add a new Data Source to the report which points to the Adventure Works cube. Next, if using the Sales Reasons Comparisons sample report, open the ProductData Dataset. If creating a new report, then add a new Dataset which uses your Adventure Works cube Data Source as its source. Now, we’re ready to begin.

The visual Query Designer can be launched with the Query Designer button, which will open a new window.

Launch the Query Designer via the Dataset Properties

The first thing you’ll notice is that the Query Designer interface is exactly the same as the interface used in SQL Server Management Studio, which is covered above.

The SSRS Query Designer

Again, like SSMS, the visual design mode can be switched to text mode to view the actual MDX query, which shouldn’t be modified unless you no longer wish to use the visual designer.

SSRS Query Designer text mode

Microsoft Excel

The final method of performing Multidimensional queries visually is via Microsoft Excel. Excel can be connected to a Multidimensional SSAS cube in one of two ways. The simplest is to use the Excel icon while in the Cube Browser inside of SQL Server Management Studio (SSMS) as we previously explored (on the menu bar just above the Design Mode icon). After pressing the button, select a perspective, which in this case is Adventure Works:

From the SSMS Cube Browser, press the Excel button and select Adventure Works

(Note: Should you receive an error within Excel about not being able to connect to the server or data connection, see this post for some troubleshooting information). Once Excel opens, you should see PivotTable options on the right and an empty PivotTable section in the cell area.

Just like in the SSMS and SSRS visual designers, select the items of interest from the “menu” of options in the PivotTable Fields section by checking them and/or dragging them to the appropriate areas in the bottom right section to create your query.

Unfortunately, Excel doesn’t have a way natively to view the underlying MDX query like the SSMS and SSRS Query Designers have built in. CodePlex to the rescue. The OLAP PivotTable Extensions is a free Excel add-in available via CodePlex which adds additional MDX-related functionality to Excel, such as the ability to view MDX queries.

Download the add-in from CodePlex and simply run the installer to install the appropriate version for your version of Excel.

Once the add-in has been installed, you will be able to right click within a PivotTable in Excel and select OLAP PivotTable Extensions… to bring up a new window with a number of new options.

On the MDX tab of the OLAP PivotTable Extensions window, you will be able to view the underlying MDX query of your Excel PivotTable. As an added convenience, the “Format MDX query using web service” checkbox will automatically format the query into a more readable form.

The second method of connecting Excel to an Analysis Services Multidimensional cube is to launch Excel and create a new blank workbook. From within the workbook, select the Data tab and then select From Other Sources and choose From Analysis Services. Enter in the name of your server (probably localhost) and credentials (probably your existing Windows credentials) and you will now be connected to the OLAP cube just as if you’d launched it from the Excel icon in SSMS above. Should you receive an error within Excel about not being able to connect to the server or data connection, see this post for some troubleshooting information).

Conclusion

You should now be able to connect to an existing SQL Server Analysis Services Multidimensional cube via a number of different methods — SQL Server Management Studio Query Designer, SQL Server Data Tools / Reporting Services Query Designer, and Excel and build basic queries visually via the drag and drop interface. Additionally, in all three methods, you can view the MDX query which is produced to learn the syntax or modify it as needed.