Microsoft Office Tutorials and References
In Depth Information
Loading Data and Models
Chapter 5 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 119
Loading Data and Models............................... 119
As you learned in Chapter 4, the key to producing Microsoft SQL Server PowerPivot for Excel
workbooks is to load data from one or many sources and then integrate them by building
the data model that creates your reports. This chapter describes the data-loading options
available in PowerPivot. You have already used some of the loading features of PowerPivot
to prepare the examples of the previous chapters. Now it is time to move a step further and
analyze in detail all the PowerPivot options for loading data, as you try to figure out which
ones you need to use and how to get the best from your choices.
Probably no single user ever needs all the different features for data loading that are
available in PowerPivot; nevertheless, we think that it is a good idea to take a look at all of these
features so that you know in advance what can and cannot be loaded in PowerPivot.
understanding Data Sources
When you need to load data into PowerPivot, you do that using a data source. Data sources
are interfaces between PowerPivot and databases; they provide the abstraction layer needed
by PowerPivot to communicate with many different media. PowerPivot provides several kinds
of data sources, which can be roughly divided into these categories:
■ Relational Databases PowerPivot can load data hosted in relational databases, such
as Microsoft Access, SQL Server, or any other relational database. The data sources in
this category let you load tables, views, and queries from the server. This is the most
commonly used category of data sources.
■ Multidimensional Sources You can load data in PowerPivot from an OLAP cube
hosted in databases such as Microsoft SQL Server Analysis Services using these kind
of data sources. Currently SQL Server Analysis Services is the only multidimensional
database for which there is a PowerPivot data source. The same data source can also
load data from queries issued to PowerPivot data contained in an Excel workbook
published on Microsoft SharePoint.
■ Data Feeds This category of data sources lets you load data from dynamic feeds,
such as RSS feeds from the Internet, Data Feeds tied to reports stored in Reporting
Services, or any other kind of Data Feed.
■ Text Files Data sources in this category can load data that is stored in
commaseparated text files, Excel, fixed-length files, or any other file format that can be
interpreted by PowerPivot.