THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Power BI Desktop & Excel

The August 2015 update of Power BI Desktop added two important features for existing Excel and Analysis Services users:

In case you didn't try it before, Power BI Desktop can connect to Analysis Services Tabular (connection for Multidimensional will arrive later, but Microsoft is working on it). It is interesting to consider that Power BI Desktop sends DAX queries that are different for Analysis Services 2012/2014 and Analysis Services 2016. The latter has better performance, thanks to the many new DAX functions and other improvements in the query engine. Thus, especially in complex reports, consider a test using the latest available CTP of SQL Server 2016 (at the moment of writing the CTP2.3 is the latest available, but consider that new versions might be released every month).

The other important news is that you can import in Power BI Desktop an existing data model created in Power Pivot for Excel. In reality, you import also Power Query scripts and Power View reports. I found some minor issue when I imported linked tables, but overall the experience I had is very good. After you import the data model, you can refresh it within Power BI. If you used Excel linked tables, you have a Power Query script that reads the same data from the original Excel files when you refresh the data model.

The opposite is not possible, so you cannot import in Power Pivot for Excel a data model created in Power BI Desktop. Since a real pivot table is not present in Power BI today, it would be very useful being able to connect an Excel pivot table to an existing Power BI data model. If you like having this feature integrated and supported, please vote the Ability to connect Excel to Power BI Data Model and create Pivot/Charts suggestion in Power BI support web site.

Now, as it is described in the proposal, there are two ways to obtain this feature:

  • Connect the pivot table to the model hosted on powerbi.com: this would be similar to the connection to a model hosted in SharePoint. I guess that the only existing barrier to implement this feature is the authentication, in fact such a feature is not available in SharePoint online, too. Of course, such a feature would be more than welcome.
  • Connect the pivot table to a local PBIX file: this is a completely different story, and it would part of the scenarios I described in the Power BI Designer API feature request a few months ago (with around 1,400 votes it is the fifth most requested feature). In this case, the implementation might be realized in two ways: by integrating the Power BI engine within Excel, or by connecting Excel to Power BI Desktop. The former is unlikely to happen, because Power Pivot for Excel is already the engine we are talking about and I think that the release cycle of the two products will be always different in order to enable this scenario. The latter i simpler, and actually is already possible and completely unsupported. It would be nice if Microsoft simply enable the support for it.

At this point you might be curious about how to connect an Excel pivot table to Power BI Desktop. Well, let me start with an important note.

DISCLAIMER: the following technique is completely unsupported and you should not rely on that for production use, and you should not provide this to end users that might rely on that for their job. Use it at your own risk and don't blame neither me nor Microsoft is something will not work as expected. I suggest to use this just to quickly test measures and models created in Power BI using a pivot table.

Well, now if you want to experiment, this is the procedure:

  1. Open Power BI Desktop and load the data model you want to use
  2. Open DAX Studio and connect to Power BI Desktop model
  3. In the lower right corner of DAX Studio, you will find a string such as "localhost:99999", where 99999 is a number that is different every time you open a model in Power BI Desktop (the same model changes this number every time you open it). Remember this number
  4. Open Excel (2007, 2010, 2013 - you can use any version) and connect to Analysis Services (in Excel 2013 go in Data / Get External Data / From Other Sources / From Analysis Services), specifying the previous string "localhost:99999" as server name (using the right number instead of 99999) and using the Windows Authentication
  5. At this point you will see a strange name as database, and a cube named Model. Click Finish and enjoy your data using a pivot table, a pivot chart, or a power view report (why should you use the latter in this scenario I don't know...)

I will save your time describing the problems you will have using this approach:

  • If you close the Power BI Desktop window, the connection will be lost and the pivot table will no longer respond to user input.
  • If you save an Excel file created with this connection, the next time you open it the connection should be updated, using the right server name with correct number (if you try to refresh the pivot table, you get an error and you can change the server name in a dialog box that appears).
  • This feature might be turned off by Microsoft at any moment (in any future update of Power BI Desktop).

That said, I use this technique to test the correctness of measures in a Power BI Desktop data model, because the pivot table is faster than other available UI elements to navigate in data examining a large number of values. But I never thought for a second to provide such a way to navigate data to an end user. I would like this to be supported by Microsoft before doing so. Thus, if you think the same, vote for Microsoft supporting it.

Published Monday, August 31, 2015 11:35 AM by Marco Russo (SQLBI)
Filed under: , ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Oxenskiold said:

That's a very neat and surprisingly simple hack Marco. Being able to pivot your data in Excel makes it much simpler to figure out why your visualizations occasionally   look suspiciously wrong.

Thank you for supplying that.

August 31, 2015 7:18 AM
 

100tsky said:

You're great!!!

August 31, 2015 7:22 AM
 

Jason Thomas said:

Nice! Good to know there is some alternative for the moment

August 31, 2015 9:22 AM
 

Marco Russo (SQLBI) said:

Thanks!

August 31, 2015 3:02 PM
 

Stacey Jones said:

Nice job as always Marco!

September 2, 2015 9:02 AM
 

Marco Russo (SQLBI) said:

Thanks Stacey!

September 2, 2015 1:03 PM
 

Daniel said:

Hi Marco,

I've tried the many to many and cross filter features on Power Bi Desktop and they're really nice!

Any idea on how to design them on a SSAS Tabular 2016 CTP2.3 using SSDT-BI on VS 2013? And on any other IDE?

Thank you very much,

Daniel

September 3, 2015 7:50 AM
 

Marco Russo (SQLBI) said:

Wait the new IDE. CTP3... waiting... :)

September 3, 2015 11:08 AM
 

Daniel said:

Oh, bummer!!!

Thanks for the quick response. I tried also on PowerPivot 2016, no luck either.

Regards

Daniel

September 4, 2015 3:14 AM
 

Daniel said:

Hi Marco,

I've finally got VS 2015 SSDT CTP3 and no luck with the new Filter Direction Combo.

It's always one direction, and that's no good ;)

Regards,

Daniel.

PS: My SSAS Database is still CTP2.4.

October 28, 2015 12:41 PM
 

Marco Russo (SQLBI) said:

Daniel, You should use SSAS CTP3 and compatibility level 1200. Power Pivot 2016 does not support bidirectional filters.

October 28, 2015 12:50 PM
 

Daniel said:

Thanks Marco,

I'll download CTP3 as soon as it's available.

Regards,

Daniel

October 28, 2015 1:04 PM
 

Daniel said:

Hi Marco,

SSAS CTP3 and compatibility level 1200 did the work!

Many to many relationships on SSAS tabular finally working.

Thanks again,

Daniel

October 30, 2015 8:28 AM
 

Avi Singh (PowerPivotPro) said:

Marco, I hadn't realized you had blogged about this. Thank you! And thanks for the tip about connecting to Power BI Desktop. I'm still unsure if that is a valid end-user scenario. Perhaps is. But certainly would help consultants, like you and me :-)

March 31, 2016 12:05 AM
 

Marco Russo (SQLBI) said:

Avi, I do not suggest using this technique for end-user scenario - the port changes every time you restart Power BI Desktop and you should manually change connection string every time. But it's very useful to debug Power BI models using PivotTable! :)

March 31, 2016 2:07 AM
 

Amit said:

Hi,

My query is related to managing exceptions.

In power pivot I am creating a relation between name of salesperson and sales. Now, he belongs to India in the first year and shifts to Australia in the second year. then how do I manage this exception while looking at the area-wise sales data?

May 28, 2016 6:14 PM
 

Marco Russo (SQLBI) said:

You should use a table "SalesPerson" containing one row for each version, and sales should point to the right version. You should prepare data for that, or use a calculated column as a last resort. This model is well known as "slowly changing dimension type 2", or SCD2.

May 29, 2016 2:56 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Privacy Statement