#SSAS TABULAR MODEL HOW TO#
#SSAS TABULAR MODEL SERIES#
#SSAS TABULAR MODEL WINDOWS#

Excel is launched and opens to a new pivot table that automatically connects to the SSAS tabular data source. Once you’ve selected a perspective, click OK. The Model perspective includes all database components.įigure 2: Selecting a perspective when sending data to Excel As you might recall from the first article, a perspective represents all or part of the data model. When you first click the Analyze in Excel button, you must choose which perspective to open in Excel, as shown in Figure 2. The button launches Excel and let’s you browse the tabular data from within in a pivot table. Part of that simplicity lies in the fact that the SSMS Browse window and the SSDT tabular project window both include the Analyze in Excel button on their respective menus. Even so, having Excel installed makes analyzing tabular data a much easier process. 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. Starting with SQL Server 2012, Excel is now the de facto tool in SSMS and SSDT for analyzing tabular data. To be able to fully drill into the data, you need to use Excel. What’s shown in Figure 1 is pretty much the extent of what you can do in the Browse window in SSMS. This way, all non-bike merchandise is included in the sales figures.

#SSAS TABULAR MODEL WINDOWS#

(You define filters in the small windows above the main workspace.) In this case, I eliminated all sales that include the value Bikes in the Category Name column of the Product table. Notice in Figure 1 that I also created a filter that limits the amount of data returned. For example, Figure 1 shows data from the Country Region Name and City columns of the Geography table, as well as from the Internet Total Sales and Internet Total Units measures of the Internet Sales table. When the Browse window appears, you can view a list of database objects and use those objects to retrieve data by dragging them into the main workspace.

#SSAS TABULAR MODEL HOW TO#

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.” In Object Explorer, right-click the database and then click Browse. On my system, I’ve implemented the database on a local instance of SSAS 2012 in tabular mode. The examples pull data from the AdventureWorks Tabular Model SQL 2012 database, available as a SQL Server Data Tools (SSDT) tabular project from the AdventureWorks CodePlex site. Some of what was covered in that article will apply to retrieving data from within Excel.įor this article, I provide a number of examples of how to import tabular data into Excel. In the last article, “ Using DAX to retrieve tabular data,” 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.

#SSAS TABULAR MODEL SERIES#

This is the third article in a series about SSAS tabular data.

In this article, we explore how to use Excel to view and retrieve data from an SSAS tabular database. 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.ĭespite 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. 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. Using Microsoft Excel to Retrieve SSAS Tabular Data - Simple Talk Skip to content

Ssas tabular model