Data Import In November CTP

Data Import In November CTP

  • Comments 9

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 http://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.

Leave a Comment
  • Please add 5 and 3 and type the answer here:
  • Post
  • Pingback.

    Link to this post was added to our website:

    http://powerpivot-info.com/post/151-powerpivot-data-import-in-november-ctp

  • Any thought on connecting to SAP BW? That would be an excellent addition!

  • Hope the Powerpivot gets RTM'ed soon

  • These data import options are great - since viewing PowerPivot at the SP Conference in October I've been a huge fan and playing around a lot with the technology. Today I was demonstrating the import functionality and BI capabilities with PowerPivot to a number of potential clients, and showing how easy it was for end users to import, manipulate and transport data, when suddenly I thought to myself - what does this mean for data security? Of course controls still need to be in place, but PowerPivot provides the means for end users to very easily download lots of data, and the 'BI for the Masses' push encourages organizations to make more raw data sources available to employees. What controls  (if any) are there to limit the damage an employee can do with Excel, PowerPivot and a high-capacity memory stick? Of course the threat of data threat isn't new with PowerPivot, but what is new is how easy it is for users to obtain local copies of large data sets.

    Best regards,

    Nathaniel Suda

  • Hi,

    I'm trying to get information out of my oracle database but no luck so far. Do you have any advice?

    Thanks,

  • I have been trying to install ODAC for a 64 bit client, but I can't get the installation done. Everytime I try to install I get an error. Do you have any tests done connecting PowerPivot to an Oracle database? From your post I can conclude you did some testing. It will be very helpful if you can post something about setting a connection from PowerPivot to an Oracle database. Thanks in advance!

  • I was able to import data from Oracle Database.

    Powerpivot addin in Windows 2008 64-bit box.

    Oracle in Sun/Unix Server.

    Used oracle 10.2.0 client drivers.

    --Raghav

  • Hi Raghav,

    I am curious about the data volumes in your import from Oracle. Did you get any feeling of how fast/slow the import was?

    I understand that thanks to smart internal data compression PowerPivot enables both keeping and manipulating huge data volumes, but what about the time it takes to retrieve those volumes from external databases, like Oracle in particular? Or is it simply the limitations of OLEDB that can become one of potential bottlenecks affecting retrieval time...

    -- Peter

  • Can someone actually create a walkthrough on getting powerpivot to work with Oracle?  People claim this works, however I haven't seem one person say more than a sentance onthe subject...

Page 1 of 1 (9 items)