Friday, January 3, 2014

Connecting to SQL Server 2012 Express from Power Pivot - 1

Previously we have seen connecting to a database from Power Query. Microsoft also has Power Pivot which also can access SQL Server 2012 to model a data source. In what way is Power Query different from Power Pivot?

There is a functionality difference between the two. Power Query is more like SQL Server Integration Services while Power Pivot is akin to SQL Server Analysis Services. Even a cursory look at the
'ribbon' would reveal this as shown:

In Power Query UI you do not see analysis related controls.

As to connecting to data you do see there is some overlap. However, Power Query can access lot more types of data sources than Power Pivot.  These are data sources that Power Query can access,

The following image shows data sources that Power Pivot can bring in,

 Also, review the user interface differences between the two tools, one provides more analytical support than the other,

However both Power Query as well as Power Pivot can connect to SQL Server 2012 Express by clicking on the From Database drop-down.

Go to Part 2 here: