• Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About
Microsoft Data & AI

Power BI

Excel 2013 Prof Ed Data Source Comparison

5/24/2015

0 Comments

 
Picture
I was asked by a client today which Power BI tools in Excel could connection to a multidimensional cube.  Microsoft is constantly adding new data sources and functionality -- especially in Power Query -- and past versions of Excel had some MultiD limitations.  However, today if you are running 2013 Professional Edition, you are in pretty good shape!  To keep this straight in my own mind, I, of course, created a table, but before you get to that, please be aware of a couple of items.

1.  Make sure you have the latest version of Power Query installed.  You can tell if are up-to-date when the <Update> button in your Power Query ribbon is disabled.

2. It is important to understand that Excel uses different drivers depending on what tool you use to connect.  For instance, a
 connection to a SQL Server Database through one of these paths …                 
               Excel à Data ribbon à From Database à SQL Server
               Excel à Data Ribbon à Connections à Add à Browse for More à New Source
… results in Excel using an OleDB driver.  You can see this when you edit the connection and take a look at the connection string.

Picture
A connection to SQL Server through Power Pivot results in Excel using a SQL Server Native Client 11 driver.
Picture
A connection to SQL Server through Power Query results in Excel using a dot NET driver.
Picture
Please keep this in mind as you review the following chart that was current as of June 15th, 2015.  Although you can access the same data source multiple ways, be aware of what is really happening in your connection string.
Picture
Picture
Picture
0 Comments



Leave a Reply.

    Categories

    All
    Power BI
    R Scripting

    RSS Feed

    Tweets by @SqlServerSME
Powered by Create your own unique website with customizable templates.
  • Home
  • Modeling for BI
  • DBA
  • Azure
  • SSIS
  • SSAS
  • SSRS - Reporting
  • PBI - Analytics
  • Consulting
  • About