I have created Power View reports based upon SSAS Tabular models many times, but I typically go through SharePoint to get my data connection from a BISM connection file. I am now working on a project where I need to create Power Views connected to a tabular model without using SharePoint. The way to do this was not immediately obvious to me, so I thought I would share this information. If you click Power View on the Insert Tab, you will get the following message: “Power View needs data to work with. Select a range of cells that contain data and then on the Insert tab click Power View.” Excel is assuming you want to you Power Pivot as your data source.
Instead, add a new data connection to your workbook that points to an SSAS Tabular model.
- On the Data tab, Choose From Other Sources -> From Analysis Services.
- When the dialog box appears, enter the server name and choose Windows Authentication. Then click Next.
- Select the appropriate database and perspective. Click Next.
- Update the file name and friendly name, if desired. Click Finish.
- Now that you have your data connection in your workbook, an Import Data dialog box will appear. Choose Power View Report and click OK.
A Power View sheet will be created in your workbook. If you need another Power View sheet:
- Go to a regular Excel worksheet (not a Power View sheet).
- Click on the Existing Connections button.
- Double-click on the appropriate connection in the workbook.
- Choose Power View Report. Click OK.
The Office help documentation for this topic is here.
I also made a quick video that shows me creating two Power View tabs connected to an SSAS tabular model in an Excel workbook.
I’ve enjoyed speaking at several SQL Saturdays and some local user group meetings over the past couple of years. This summer I’ve been invited to speak at some new venues.
First up is a webinar through the PASS Business Analytics Virtual Chapter. The PASS BA VC is a great place to find free online training each month. I’ll be talking about The Accidental Report Designer: Data Visualization Best Practices in SSRS on Thursday, June 19th at 11AM CDT. If you are interested, please register here. This is my first webinar, so I am interested and excited to try out this format. Here is the abstract for my webinar:
Whether you are a DBA, a developer, or an analyst, there is a good chance that you will have to create reports for coworkers, company executives, or clients. As with any UI design, careful consideration should be given to your data visualization design to ensure you are effectively communicating the intended message and providing a good user experience. While the principles are applicable across reporting platforms and tools, this session will contain demos and examples implemented in Reporting Services using SQL Server Data Tools. Learn how to make information (not just data) the focus of your report and provide your audience with something better than just shiny!
Session Level: Intermediate
I will be speaking at Mile High Tech Con in Denver, Colorado on July 25th and 26th . It features 3 days of content about data warehousing and business intelligence. This is my first non-PASS event, and I’m looking forward to visiting Denver once again. The cost of the conference is fairly reasonable, so you might consider it if you are looking for a good BI conference to attend. The pre-cons cost extra, but the main conference on Friday and Saturday is $475. You can use my discount code for $40 off of that price: 14SK_MeaganLongoria
I hope you will join me at both of these great events.