How to connect to tabular models in Excel

Background

The Enterprise Data Warehouse (EDW) uses Analysis Services tabular models, which are pre-aggregated datasets meant to provide reporting that is consistent, fast, and easy to access. This article describes how to access data from the EDW tabular models using Microsoft Excel.

Connecting to a new tabular model

If you've never connected to a particular tabular model before, follow these steps.

• Open a new workbook in Microsoft Excel, then click the Data tab on the main ribbon.
• Select "Get Data" > "From Database" > "From Analysis Services"

A dialog box will appear prompting you to enter the name of a server and to specify your credentials so that you may connect to the database server that hosts the tabular models.

• In the "Server name" box, type in "BI1"
• Under "Log on credentials," select "Use Windows Authentication"
• Click "Next"

Another dialog box will appear prompting you to select the specific tabular model you wish to connect to. The dropdown list contains a list of databases you have access to, and each database may contain one or more data models. In the example below, the "Admissions Model" database contains only one tabular model.

• Select the model(s) you want from the list, then click "Finish."

After selecting "Finish," Excel will save a connection file on your machine that will allow you to connect straight to the data model in the future. Excel will then connect to your tabular model and allow you to begin building your report.

Connecting to an existing tabular model

If you have connected to a particular tabular model on your machine before, you don't need to create a new connection in Excel each time you want to access it. Instead, follow these steps:

• Open a new workbook in Excel, then click the Data tab on the main ribbon.
• Select "Existing Connections"

A dialog box will appear prompting you to select an existing connection file on your machine.

• Select the existing connection you want, then click "Open"

Once clicking "Open," Excel will access the existing tabular model connection on your machine and you'll be able to start building a report.

Next articles to read:

Using tabular models to build an Excel report

Measures Explained

Hierachies Explained