{"id":1669,"date":"2013-07-16T00:00:00","date_gmt":"2013-07-16T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/using-microsoft-excel-to-retrieve-ssas-tabular-data\/"},"modified":"2021-05-17T18:33:17","modified_gmt":"2021-05-17T18:33:17","slug":"using-microsoft-excel-to-retrieve-ssas-tabular-data","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/using-microsoft-excel-to-retrieve-ssas-tabular-data\/","title":{"rendered":"Using Microsoft Excel to Retrieve SSAS Tabular Data"},"content":{"rendered":"
\n

Until recently, most of the talk about tabular data revolved around PowerPivot, an Excel add-in that brings powerful in-memory data crunching to the spreadsheet environment. Creating a tabular database in PowerPivot, and by extension, within Excel, is a fairly prescribed process that involves retrieving data from one or more data sources-most notably SQL Server databases-and creating one or more tables based on that data. <\/p>\n

Despite all the attention paid to PowerPivot and its tabular databases, there has been relatively little focus on how to retrieve tabular data directly from a SQL Server Analysis Services (SSAS) instance into Excel, whether through PowerPivot or directly into a workbook.<\/p>\n

In this article, we explore how to use Excel to view and retrieve data from an SSAS tabular database. This is the third article in a series about SSAS tabular data. In the last article, “Using DAX to retrieve tabular data<\/a>,” we discussed how to create Data Analysis Expressions (DAX) statements to retrieve tabular data, working from within SQL Server Management Studio (SSMS) to run our queries against an SSAS tabular instance. Some of what was covered in that article will apply to retrieving data from within Excel.<\/p>\n

For this article, I provide a number of examples of how to import tabular data into Excel. The examples pull data from the AdventureWorks<\/code> Tabular<\/code> Model<\/code> SQL<\/code> 2012<\/code> database, available as a SQL Server Data Tools (SSDT) tabular project from the AdventureWorks CodePlex site<\/a>. On my system, I’ve implemented the database on a local instance of SSAS 2012 in tabular mode.<\/p>\n

Viewing Browsed Data in Excel<\/h1>\n

We got a taste of how to browse data from within SSMS in the first article of this series, “Getting Started with the SSAS Tabular Model<\/a>.” In Object<\/code> Explorer<\/code>, right-click the database and then click Browse<\/code>. When the Browse<\/code> window appears, you can view a list of database objects and use those objects to retrieve data by dragging them into the main workspace. For example, Figure 1 shows data from the Country<\/code> Region<\/code> Name<\/code> and City<\/code> columns of the Geography<\/code> table, as well as from the Internet<\/code> Total<\/code> Sales<\/code> and Internet<\/code> Total<\/code> Units<\/code> measures of the Internet<\/code> Sales<\/code> table. <\/p>\n

\"1835-clip_image002.jpg\"<\/p>\n

Figure 1: Browsing tabular data in SSMS<\/b><\/p>\n

Notice in Figure 1 that I also created a filter that limits the amount of data returned. (You define filters in the small windows above the main workspace.) In this case, I eliminated all sales that include the value Bikes<\/code> in the Category<\/code> Name<\/code> column of the Product<\/code> table. This way, all non-bike merchandise is included in the sales figures.<\/p>\n

What’s shown in Figure 1 is pretty much the extent of what you can do in the Browse<\/code> window in SSMS. To be able to fully drill into the data, you need to use Excel. Starting with SQL Server 2012, Excel is now the de facto tool in SSMS and SSDT for analyzing tabular data. Unfortunately, that means you need Office 2003 or later installed on the same computer where SSMS and SSDT are installed, not a practical solution for everyone. Even so, having Excel installed makes analyzing tabular data a much easier process.<\/p>\n

Part of that simplicity lies in the fact that the SSMS Browse<\/code> window and the SSDT tabular project window both include the Analyze<\/code> in<\/code> Excel<\/code> button on their respective menus. The button launches Excel and let’s you browse the tabular data from within in a pivot table.<\/p>\n

When you first click the Analyze<\/code> in<\/code> Excel<\/code> button, you must choose which perspective to open in Excel, as shown in Figure 2. As you might recall from the first article, a perspective represents all or part of the data model. The Model<\/code> perspective includes all database components.<\/p>\n

\"1835-clip_image004.jpg\"<\/p>\n

Figure 2: Selecting a perspective when sending data to Excel<\/b><\/p>\n

Once you’ve selected a perspective, click OK<\/code>. Excel is launched and opens to a new pivot table that automatically connects to the SSAS tabular data source. Figure 3 shows the pivot table before any data has been added. Notice in the right window you’ll find the PivotTable<\/code> Field<\/code> List<\/code> pane, which contains a list of all your database objects, including tables, columns, and measures.<\/p>\n

\"1835-clip_image006.jpg\"<\/p>\n

Figure 3: Importing tabular data into an Excel pivot table <\/b><\/p>\n

To get started with the pivot table, drag the fields and measures you want to include in your report to the Column<\/code> Labels<\/code>, Row<\/code> Labels<\/code>, and Values<\/code> panes. The data will then be displayed in the pivot table. You can also create filters by dragging fields to the Report<\/code> Filter<\/code> pane. Figure 4 shows the pivot table after I added numerous database objects. Many of these objects are the same columns and measures that were added to the Browse<\/code> window shown in Figure 1, except that now I also break down the sales by years.<\/p>\n

\"1835-clip_image008.jpg\"<\/p>\n

Figure 4: Working with tabular data in an Excel pivot table<\/b><\/p>\n

To configure the pivot table shown in Figure 4, I added the following database objects to the specified configuration panes in the right window:<\/p>\n