Analyzing Tabular Model in Excel 2016

It’s been a long time since I wrote an article on Tabular Model. This week, one of my good readers, Andrew posted me a question. In this article, I want to show you how to connect to your Tabular Model database and use it as the underlying model for either Pivot Table, Pivot Chart, or Power View.

As a side note, I actually was struggling to find Power View button in Excel 2016 as it was missing from the ribbon. To add the Power View button back to the ribbon, read here.

Connecting to Tabular Model in Excel

The steps to connect to a Tabular Model in Excel 2013 are the same as in Excel 2016:

  1. In Excel 2013/2016, navigate to the Data menu and choose From Other Sources > From Analysis Services from the ribbon as shown below.
    Excel > Data > From Other Connections > From Analysis Services
  2. Then provide the Tabular Model server name and choose Windows Authentication. Click Next.
    Data Connection Wizard
  3. You will then see a list of Tabular databases to connect to and choose a specific cube / perspective to connect to. Follow through the next few steps and click Finish.
    Select Database And Table
  4. Import Data dialog box will appear, where you can choose how you want to view the data. i.e. Pivot Table, Pivot Chart or Power View.
    Import Data
  5. Click OK to start viewing the data. The image below illustrates how I have created a Power View visualization using my Tabular Model .
    Sample Power View

 

What the steps above have done is more than just creating a Pivot Table, a Pivot Chart or a Power View sheet for you; in fact, in the back-end it has created a connection. It is a best practice to keep reusing the same connection, rather than creating multiple ones that connect to same database and cube.

Reuse Existing Connections

In case you want to create more visualization using Power View or create more Pivot tables using the same data set, you should reuse the existing connection. To do this, navigate to the Data menu and choose Existing Connections from the ribbon.

Data > ExistingConnections

Then you will see a list of existing connections similar to below.

Existing Connections

Wrap Up

It’s pretty easy to connect / analyze Tabular Model in Excel as outlined above. Check out my MVA course on Tabular Model Jump Start that I did with Patrick LeBlanc for more info on how to get started with Tabular Model.

Thanks Andrew for the question.

4 comments on “Analyzing Tabular Model in Excel 2016
  1. Great post. What I have done in the past is saved it as an ODC connection file. In which I put a few additional parameters to help query performance. Then when someone needs access I just send the ODC file which they double click, opens in Excel with the direct connection to the Tabular model.

  2. thanks for your article.
    is this a live connection or this will import all data to excel workspace?
    if it copies data into the client ram is this a way to have live connection like what we have in /microsoft BI desktop?
    thanks in advance

    • This would be a live connection to the server at the time you curate the Pivot Table / Pivot Chart. You will need to ensure that you configure data refresh so that the data displayed in the Pivot Table / Pivot Chart is not stale , e.g. when the workbook is opened.

      Hope this helps.

      Julie

Comments are closed.