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:
- In Excel 2013/2016, navigate to the Data menu and choose From Other Sources > From Analysis Services from the ribbon as shown below.
- Then provide the Tabular Model server name and choose Windows Authentication. Click Next.
- 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.
- 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.
- Click OK to start viewing the data. The image below illustrates how I have created a Power View visualization using my Tabular Model .
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.
Then you will see a list of existing connections similar to below.
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.