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.
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 Responses
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.
Gilbert,
What a great idea; I have used the same technique myself for some of my past clients.
Thanks for sharing the tips here.
Julie
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