We have now created multidimensional and tabular models in the previous chapters. The data is ready, so let's visualize it. We will start our data visualization in Excel. Microsoft Excel has been the most prolific analytics tool on the market for years. While that is not its primary focus, its utility, simplicity, and reach far exceed those of its nearest competitor.
In this chapter, we will connect our models to Excel, build out some reports and dashboards, and explore the differences between these models in Excel. We will wrap up the chapter with some advanced visualization techniques that are unique to Excel when working with analytical models built in SQL Server. We will be creating two Excel workbooks, one for each model. This will allow us to compare how the models interact with Excel.
When you are finished with this chapter, you should be comfortable with connecting both multidimensional and tabular models to Excel and building basic reports with their data. You will also learn some advanced techniques that will help with the creation of dashboards and unique visualizations in Excel.
In this chapter, we're going to cover the following main topics:
In this section, you will need to deploy and have running the multidimensional model (WideWorldImportersMD) that you created in Chapter 3, Preparing Your Data for Multidimensional Models, Chapter 4, Building a Multidimensional Cube in SSAS 2019, and Chapter 5, Adding Measures and Calculations with MDX. You will also need the tabular model we expanded in Chapter 6, Preparing Your Data for Tabular Models, deployed and running (WideWorldImportersTAB). We will not be working with the workspace version of your tabular models. You will also need Microsoft Excel to work through the hands-on work in this chapter. All of the examples in this chapter will be using the latest version of Excel in Office 365 ProPlus at the time of writing (the May 2020 release). Because Excel is updated continually via a subscription model, some examples may look different for you.
Let's get started with Excel by creating two workbooks that will allow us to work through the design process with both model types. Our workbooks are called WideWorldImporters-MD.xlsx and WideWorldImporters-TAB.xlsx. This matches the naming convention we have used throughout this book. Now that we have our workbooks created, let's get connected to our models.
Multidimensional models (or cubes) and Excel have been working together for a very long time. One of the great things about multidimensional models is the high level of interactivity and capabilities that Excel seamlessly supports. This functionality propelled cubes to the forefront of ad hoc analysis in businesses throughout the world. Let's begin:
You may recall in Chapter 3, Preparing Your Data for Multidimensional Models, we created a perspective that was a subset of our cube. This is a scenario where perspectives can support a better user experience in Excel. It is very common for cubes to be large and overflowing with objects. Perspectives allow you, as the designer, to organize logical groups of objects for your users so they can work easily with the analytics you have prepared for them.
As you can see in the preceding screenshot, we have a number of options to work with. Some options are not available when working with direct connections to SSAS, such as viewing the data in a Table and the choice to Add this data to the Data Model.
Note
The data model is the underlying Power Pivot model in Excel. We cannot use that with direct connections to Analysis Services models. This makes sense as they are effectively the same structure. Analysis Services models, both tabular and multidimensional, should be considered ready for use and do not require additional mashup or manipulation in Power Pivot.
Out of the four options in the following list, the first two are the key options to consider after creating your connection:
i) The first is how you want to view the data. The default is to create a PivotTable report. This is the most common pattern to start working with the data. This effectively allows Excel users to interact with the data from the cube in an ad hoc manner.
ii) The second option will create the PivotTable and a matching PivotChart. While interesting, you will typically find that it is easier to work with the PivotTable and then add the PivotChart later.
iii) The third option here is to Only Create Connection. If you use Excel to create a lot of reports or dashboards, you will likely use this option more frequently, as you can use the connection to create multiple visualizations in Excel where and how you want to.
iv) The other option is to choose where you want to create the PivotTable or PivotChart. The default will be the cell selected when you choose Get Data from the menu. If you don't select a cell, you will typically see the $A$1 cell selected on the first sheet of the workbook. You can change this location by selecting a different cell in the workbook. If you have chosen to create a connection only, you will not be able to choose the location because no object is being created.
Let's now connect Excel to our tabular model.
The process to connect to the tabular model is nearly identical to connecting Excel to the multidimensional model. We will call out the steps here and refer to the preceding screenshots where relevant:
You should now have two workbooks connected to your Analysis Services models. As you can see, Excel shares the connection method between the two models. This typically keeps the learning curve low if you are transitioning between the model types. Let's start exploring the visualization options in Excel.
For the remainder of this chapter, we will be creating the visualizations and queries primarily using our multidimensional model. We can do this because Excel interacts with both models in a similar way. Where differences appear, they will be called out to make you aware. Let's get started.
Before we get into the details of designing visualizations in Excel, let's break down the PivotTable Fields panel in Excel. How data is presented from each model type does vary here and we will look at those differences.
The PivotTable Fields panel in Excel is made up of two main sections, first, the Fields section, and second, the areas they are put into in the PivotTable, called Areas to Place Fields, as shown in the following screenshot:
Let's understand the features of the PivotTable Fields panel:
When we created our multidimensional model, we placed the measures on colors that we created into a folder called Color Analysis. You will find these measures in the Values group in the PowerPivot Fields panel. This group contains a folder called Color Analysis. You can see how these folders help organize calculations for ease of use for users.
Implicit calculations are turned off
In our tabular model, we have turned off implicit calculations. Only the measures we create in the model can be added to the Values section. In older versions of tabular models, this is not the default. If that feature is turned off, you can drag numeric fields from the tables that aren't in measures and Excel will create an implicit calculation.
When you add fields to the area, the assumption is that we are adding row and column headers in the PivotTable like a matrix visualization. The values to be calculated, dropped into the Values section, are effectively sliced by the row and column combination. The last section is the Filter section, which applies to the PivotTable we are working in. Now that you have a basic understanding of the PivotTable Fields panel, let's create some visuals.
The activity of creating a PivotTable is the same using either model. For the following steps, we will be using the multidimensional model in our WideWorldImporters-MD workbook. When we originally created the connection, a simple PivotTable was created in the workbook. We will use this as our starting point. The next steps will create a PivotTable visualization with the color analysis we did in both models. The steps are as follows:
There are two more levels in the hierarchy, State Province and City. This is the concept of drilling down. The creation of hierarchies in either type of model is intended to give users an easy-to-use, well-defined drill-down path. As you can see, the calculations represent the percentages of blue or black items in the context of the row or region level.
i) Find Salesperson and Employee in the field list.
ii) Drag Employee to the Columns area and drop it above the Values item in the same area. If you drop it below Values, you can drag it above Values or you can click the down arrow to move it in the direction you need.
If you selected the Employee field in the field list, it may have dropped Employee into the Rows areas. You can drag it over to Values or select Move to Column Labels to move it to the Columns area.
During the creation of our models, we have various ways to hide fields from the tools that are interacting with the model. In multidimensional models, it is common to add fields to a hierarchy and then not show the base fields. This optimizes performance and cube size. We can also explicitly hide fields in the model design as well. We have similar options in the tabular model. We can choose to hide fields from the users explicitly.
You will notice in our tabular model that all fields remain visible, whereas we hid some fields in our multidimensional model. The City dimension highlights this difference well. In our multidimensional model, we created two hierarchies that contained all the fields. These fields are not visible outside of the hierarchy, whereas, in our tabular model, the same hierarchies exist but a More Fields list is available as well. This list contains all the fields. This has different design options and needs to be planned for your model creation.
There is little performance impact, if any, from including all the fields in tabular models. In multidimensional models, hierarchies support better aggregated performance based on storage and you need to be more intentional about what you expose in your model.
As we wrap up this section on PivotTables, you must understand that the value of creating models is that users can plug into and view data as they wish. You can repeat this experience in the tabular model workbook. The primary differences are the numbers (we have additional filters on the tabular model calculations) and the fact that the Date table is used to get the Calendar hierarchy. In the tabular model, we do not start with role-playing dimensions. Next, we will add a PivotChart to our workbook.
We will now add a PivotChart to our workbook. Add a sheet to your workbook to get us started, then proceed with the following steps:
There are a few parts of the PivotChart you need to understand before moving forward:
i) You can see that the fields we have added to the chart are also represented as field buttons. They allow you to modify the chart in various ways. If you right-click on the buttons, you can change the order, move them to a different area, or remove them altogether.
ii) You can change the hierarchy level as well. Click the down arrow to the right of the Invoice Date.Calendar button. This will expand the hierarchy selector, which allows you to adjust the level or filter levels out if you prefer. You can drill up or down with the plus (+) and minus (-) buttons in the lower right of the chart. This is not very readable in our scenario, but the option is there if you choose to use it.
iii) The plus symbol at the top, outside of the chart area, allows you to choose the chart elements you want to see in the chart. Let's add a Chart Title with the name Black & Blue Analysis. The paintbrush below the plus (+) button allows you to change the color scheme and style of the chart. We will not change ours at this time, but you should still look at the options.
You now have a nice line chart that shows the variance in the percentages of blue versus black items sales by calendar year. We will use this chart in the next section as well.
Slicers in Excel are buttons that allow you to filter the contents of your workbook in a highly visual and touch-friendly way. Slicers have been in Excel for quite a while. In this section, we are going to add an Employee slicer to the Black & Blue Analysis chart. The last step in the section will show how to apply this slicer to the PivotTable we created. This works because both items share a connection. Let's begin:
At the top of the slicer, there are two buttons next to the title. The first toggles the multiselect option. The second clears any filter applied. The slicer is single-select by default.
Slicers can be used with any field or fields that interact with the data in your Excel PivotTables and charts. While dates can be filtered with slicers, we will demonstrate a timeline with the PivotTable. One last point on slicers: you can create the same slicer with tabular models. The process is identical to the preceding steps.
Timelines are special filter controls that support date fields. They have the ability to build out the date hierarchies natively. This means that the data types of the field need to be of the date data type (for example, Date or DateTime).
Date tables in our models
This control requires that the table or dimension be designated as the date or time table. In the multidimensional model we created, the Date dimension was designated as a Time type in the dimension's properties. In our tabular model, the Date table was marked as the date table. These property settings are picked up by the control. When working with the multidimensional model, you will see two options for the timeline control, Invoice Date and Delivery Date. Both of these are built on the same Date dimension as role-playing dimensions. This functionality is not supported the same way in tabular models. Even though we created a calculated table that supports the delivery date, only one table can be marked as the date table so only one option is available from the tabular model.
Let's look at the steps to add timelines:
The timeline control has some cool features we want to highlight here. Let's start with the dropdown that shows MONTHS right now. If you select the down arrow you will see YEARS, QUARTERS, MONTHS, and DAYS as options. This changes the granularity of the highlighted bar in the visual. You can select one or more months in the bar that is shown in the middle. When you make a selection, the All Periods label in the upper left will display what is selected, such as Feb 2016.
The scroll bar at the bottom of the visual lets you scroll through the available options. Be aware that the dates shown in the visualization cover the date range supported in your date table. For example, if you select Aug 2016, the PivotTable will no longer contain data as our dataset does not contain data past the middle of 2016.
You can use the same steps to connect this filter to the PivotChart we created that we used when connecting the slicer. Review the preceding steps if you want to experiment with connecting this to the PivotChart as well. You can remove the filter by clicking the button with the filter and the red X on it.
Also, like the slicer control, the timeline control can be added using the same process with your tabular model. We are going to put this all together in the next section.
The focus of this section is to turn our work into a full dashboard for our users. We will explore some more advanced techniques to make our dashboards more user friendly and interactive. We will continue to focus on the multidimensional model and highlight the differences that occur with tabular models.
We are going to combine everything we have created so far into a single sheet and make various enhancements along the way. Let's get started.
Let's move the PivotTable and the filter:
Let's now move the Employee slicer:
We are going to make a number of changes in this dialog over the next few steps. Name is the name of the field we pulled from the model. For our purposes, we can keep the name.
The filtering section has three options:
i) By default, Visually indicate items with no data and Show items with no data last are selected. Items with no data are grayed out and moved to the bottom of the list with these settings. You can keep the slicer data in order by deselecting the last option. This will still gray out options with no data but not move them to the bottom.
ii) If you select the top option, the other two options cannot be selected. The first option, Hide items with no data, will completely remove slicer items from view if no data exists. You will need to determine the best option for your users based on the content to filter. We will leave this setting on its default. Click OK to close the dialog and save our setting changes.
This wraps up the slicer settings. Let's continue modifying our Excel dashboard.
We will now adjust the PivotTable:
i) The first button, Field List, will show or hide the PivotTable Fields pane. You can use this option if you are not planning to add any additional data to the PivotChart.
ii) The second button is the Field Buttons drop-down list. The field buttons are the gray areas in the preceding screenshot. The list contains the area for each set of field buttons. We don't have a reason to leave any field buttons on our chart. Choose Hide All to remove or hide the buttons on the chart.
Once all these steps are completed, your sheet should look similar to the following screenshot:
So now what? We have cleaned up our dashboard with slicers, timelines, PivotCharts, and PivotTables. The same steps can be used for the tabular models. We will now look at one other advanced design feature, which will allow us to add some nice visuals to fill in the space between the filters and the PivotTable.
This section covers the CUBE functions available in Excel. This functionality allows you to operate on data from Analysis Services without using PivotTables or PivotCharts. These techniques are advanced and require basic Multidimensional Expression (MDX) skills. However, we will walk you through the simplest way to learn and use these functions initially.
We will use these functions to create the following three single-value visualizations on our dashboard:
In the next sections, we will walk through the steps to add these measures and apply the timeline filter to them.
Let's begin by adding PivotTables:
We will now convert PivotTables to formulas:
=CUBEMEMBER("Wide World Importers MD","[Measures].[Black Items]")
CUBEMEMBER is one of a set of functions that can use the connection to refer to a value in the cube using MDX syntax. In this case, the formula returns the name of the member, which is Black Items. The field below this uses a different formula:
=CUBEVALUE("Wide World Importers MD",B$1)
It is using CUBEMEMBER to determine the value to display in the cell. In our use case, we need to merge these into a single formula.
=CUBEVALUE("Wide World Importers MD","[Measures].[Black Items]")
=CUBEVALUE("Wide World Importers MD","[Measures].[Black Items]",Timeline_Invoice_Date)
If we wanted to add the Employee slicer, we would add the name to the formula as well. The formula is building an MDX calculation based on the intersection of the members we have chosen. By not including the Employee slicer, these values will have the values for the filtered period regardless of the salespeople who may be selected. This adds flexibility to the design.
=CUBEVALUE("Wide World Importers MD","[Measures].[Blue Items]",Timeline_Invoice_Date)
This time we get a Convert to Formulas warning message. This warning message prevents users from unintentionally converting their PivotTables. This operation is irreversible, so Excel is confirming the change.
We have the option here to convert the report filters as well. There are times you may want to keep the filters in place. For example, if we wanted to continue to filter values in our formulas using the filter as is, then we would leave this box unselected. However, in our case, we want to get all the parts of the PivotTable converted to formulas so we can build a filtered value for our dashboard. Select the Convert Report Filters option and click Convert to complete the process.
=CUBESET("Wide World Importers MD","{[Item].[Color].&[Blue],[Item].[Color].&[Black]}","(Multiple Items)")
The set is named Multiple Items and is used in the CUBEVALUE function by referring to the cell in the function options ($B$4 in our workbook) as follows:
=CUBEVALUE("Wide World Importers MD",$B$4,$A$6,Timeline_Invoice_Date)
By using what we have discovered here, we can complete the custom CUBEVALUE formula for our dashboard:
=CUBEVALUE("Wide World Importers MD", CUBESET("Wide World Importers MD", "{[Item].[Color].&[Blue],[Item].[Color].&[Black]}"), "[Measures].[Total Excluding Tax]",Timeline_Invoice_Date)
Now that we have our new metrics copied into fields, we can format them (it is helpful to turn Gridlines and Headings back on during this process. Be sure to hide them when you are done formatting these values):
This completes the basic dashboard for Excel. You can create the same dashboard using the tabular model data as well. The functionality is the same. Using the OLAP Tools functionality effectively sends MDX to the tabular model as well. As a result, you will see that the naming conventions used with the tabular model are the same as those used with the multidimensional model.
For example, the Measures dimension is used in the tabular model formulas, but that actual dimension is not in the tabular model. This is handled by the communication protocols and drivers between Excel and SSAS.
In the next section, we will explore some options that can be used to share your completed dashboard.
Now that you have this awesome dashboard created, how can you share it? It is easy to share it by sending it to others via email, but you always risk them making changes to the data or design. If you want to share this with users while limiting their ability to edit, there are several good options such as OneDrive, SharePoint, and even Power BI workspaces. The next sections help you prepare for deploying your workbook to be shared.
In order to share using one of the key services such as Power BI or SharePoint Online, you need to have access to these services and the services need to have access to the location of your model. Both services require Microsoft 365 subscriptions to use. The SharePoint solution will be similar to an on-premises deployment if you have that available.
When deploying to an online service, you need to make sure that the credentials you will be running under have access to the database. In all of our examples here, we have been running entirely locally. When you move to an online service, your credentials need to have access to your local server in order to refresh the data. You will be able to push the Excel sheet to SharePoint or OneDrive, but any data refresh will require Active Directory in order to complete the authentication process.
You can deploy your workbook to OneDrive (personal or corporate), SharePoint, or Power BI. However, in order to properly share your Excel workbook with a live connection to Analysis Services, Analysis Services must be on the Active Directory or Azure Active Directory domain for the easiest and most optimal deployment.
If you have created your dashboard in an Active Directory-supported environment, you should be able to refresh the data as required. If you are working in a disconnected development environment, this may not be possible. While you can deploy the workbook, none of the interactive functionality will work because the query is not using the correct credentials.
Use the following steps to deploy your dashboard to OneDrive:
This is just one approach you can use to share your dashboard with others. As you may have noticed in the link, this is the equivalent of sharing your dashboard on SharePoint.
In this chapter, you saw the various types of interaction you can have with multidimensional and tabular models when working with Excel. You created PivotTables and charts and supported these with timeline and slicer filters. These skills you learned will help you to visualize your data using Excel and both multidimensional and tabular models. You are also now able to enhance your Excel workbook visualizations to make them more appealing to your users and focus on the data to support your business scenario.
In the next chapter, we will use Power BI Desktop to live-connect to our models and create a similar dashboard. When the goal is to visualize the data in your models for users, Power BI has more visual capabilities than Excel.