Microsoft Office Tutorials and References
In Depth Information
can be provided by your local administrator of that system and are outside of the scope of
this topic. Moreover, it is interesting to look at the differences between a text file and a SQL
Server query, whereas it of little use to investigate the subtle differences between Microsoft
SQL Server and Oracle, which are both relational database servers.
Data Source Limitations for Refreshing Data
As you will learn later, after data is loaded into PowerPivot, you can refresh it by
reloading it again from the data source. Although you can always refresh data on your
own personal computer, there are some limitations to this feature when you publish
the workbook to SharePoint. For example, if you want to publish your Excel workbook
on SharePoint and you want to schedule the refresh of data directly on the server, you
have to be aware of the existing limitations of the available data sources. If you import
data from text or Excel files stored in a local directory, these files are not accessible by
SharePoint and you cannot refresh this data on the server. Moreover, if you are using a
32-bit version of Excel and you access the database by using a third-party provider, you
have to be sure that the corresponding 64-bit version is installed on the SharePoint server.
Finally, pasting data from the Clipboard is an operation that cannot be refreshed, either
on the client or on the server.
Loading from a Database
We are now going to describe the first option for loading data, which is the From Database
button. This option is probably the most frequently used to load data into PowerPivot. This
is not an accident because the vast majority of the databases around the world are held in
Note In the rest of this section, we show examples using SQL Server because this is probably
the most frequently used database for PowerPivot users. If you need to connect to a different
server, you might need different connection parameters, which you can ask your IT
department to give you.
As soon as you select From SQL Server, PowerPivot opens the Table Import Wizard, which
you can see in Figure 5-2.