Data Import In November CTP

  • Article

New Data Sources

This post takes a closer look at the variety of sources PowerPivot can to import data from. In our previous posts we introduced Import Wizard as well as importing from Data Feeds.

In the November Technology Preview, there are numerous enhancements.

First change you see is PowerPivot window offers you shortcuts to the most popular sources of data. First three buttons on the ribbon lets you choose your sources: Importing data From Database, From Files, or From Data Feeds:

image image image

To see a full list of data sources, “From Other Sources” shows this:

image

The list is fairly quite long:

  • Microsoft SQL Server
  • Microsoft SQL Azure
  • Microsoft Access
  • Microsoft SQL Server Analysis Services
  • PowerPivot workbooks published to Analysis Services running in SharePoint Integrated Mode 
  • Oracle
  • Teradata
  • Sybase
  • Informix
  • DB2
  • DataFeeds
  • Excel workbooks
  • Text files

In addition, as we previously mentioned in the Data Import post, users can go through “Others” and choose to import data from any OLEDB or ODBC data source as well. The list of sources you see above is just the list of sources we test with.

Since PowerPivot uses Analysis Services internally, the latest OLEDB provider for Analysis Services will be installed. Aside from this, installing PowerPivot will not automatically install all data source providers that PowerPivot supports. For example, you may need to install any additional ODBC or OLEDB providers on the the client that is creating a workbook using this driver.

Most likely after installing PowerPivot for Excel on your machine you will have connectivity to following sources:

  • Microsoft SQL Server
  • Microsoft Access
  • Microsoft SQL Server Analysis Services
  • PowerPivot workbooks published to Analysis Services running in SharePoint Integrated Mode
  • Microsoft SQL Azure
  • DataFeeds
  • Excel workbooks
  • Text files

Connectivity to Access, Excel workbooks, and Data files is provided by the ACE OLEDB provider (Microsoft Office 12.0 Access Database Engine OLE DB provider) brought along with installation of Office products.

Connectivity to Analysis Services, PowerPivot workbooks and Data Feeds is built in into PowerPivot product. SQL Azure Database connectivity is via managed SQL ADO.NET provider that you most likely have on your machine as part of .Net Framework installation.

Note that as we discussed in the Data Refresh blog, in order for Data Refresh to succeed on PowerPivot for SharePoint, your IT Admin will need to install the right provider. In addition, any of the data sources used in your workbook need to be accessible from the SharePoint farm for Data Refresh to succeed.

Now lets briefly describe a few providers.

Importing Flat Files

Choosing to import data from flat file brings up a “Connect to Flat File” dialog where users pick a path to the file, the separator used as column delimiters, and whether the first row contains column headers. An advanced option allows encoding and locale specification:

image

The Preview pane allow exclusion of unnecessary columns as well as specification of filters which reduce data loaded into PowerPivot.

Importing from Analysis Services and PowerPivot

PowerPivot supports importing data from SQL Server Analysis Services database as well as from another PowerPivot workbook published to PowerPivot for SharePoint.

Users have a choice of either importing from an Excel file or connecting to PowerPivot for SharePoint to extract the PowerPivot data from a workbook. Note that through the former route, only data on a worksheet can be imported. This is typically a subset of all the data which exists in PowerPivot.

Selecting “From Analysis Services and PowerPivot” menu item brings connection dialog for connecting to both Analysis Services and PowerPivot data. The reason these are grouped together is that internally PowerPivot leverages Analysis Services and, in fact, both Analysis Services and PowerPivot support the same (XML for Analysis) protocol and the same (OLEDB Provider for Analysis Services) driver. The only difference between connecting between them is the server name specified.

  • For Analysis Services, the server name is the name of an Analysis Services server or an IIS gateway to it. 
  • For PowerPivot, the server name is the URL to the workbook published to SharePoint. 

Since MDX is the query language for Analysis Services and PowerPivot, the query specified for extracting this data can be designed using the MDX Query Designer. This is the same designer provided in Reporting Services as well and allows a drag and drop construction of the query:

image 

Importing from SQL Azure

SQL Azure allows users to host relational data in the cloud. At the time of this writing, SQL Azure is in the Community Technology Preview (CTP) status. More information is available at https://sql.azure.com.

PowerPivot has support for SQL Azure built in. This connectivity is very similar to an on-premises SQL Server, the connection dialog allows users to enter a path and credentials to access the SQL Azure database. Under SQL Azure, the path to a database is accessible on the admin page.

Using Other Providers

As such, PowerPivot supports selection of specific provider through the connection dialog. The list of providers will depend on what is installed on the machine: 

image

Conclusion

This concludes a quick walkthrough of the connectivity enhancements introduced in November CTP. As usual, we look forward to getting feedback on this and PowerPivot features on our PowerPivot for Excel or PowerPivot for SharePoint forums.