Microsoft Office Tutorials and References
In Depth Information
using Linked Tables
Now we continue our discovery of the most interesting ways to load data into PowerPivot.
The next topic deals with linked tables. Linked tables are probably the easiest way to load data
into PowerPivot, even if they do not belong to the family of data sources. If you need a table
that is not already loaded in any of your databases, you can simply create an Excel worksheet,
load data into it, and then link the worksheet to a PowerPivot table.
Because the creation of a linked table is so easy, we want to show you not only how to
create a linked table, but also, by means of a full example, how you can use linked tables
to immediately update PowerPivot values and produce interactive reports.
Let us suppose that at the start of 2005, you performed an analysis of the sales increase
month by month, computing values based on your personal experience, external market
analysis, and various meetings with your sales departments. You ended up with an Excel
worksheet that contains, for each month, a percentage that indicates how you thought
sales would vary during the year 2005 (see Figure 5-25). You can find this example in the
companion workbook CH05-02-LinkTables.xlsx.
FIguRE 5-25 Increase in sales in 2005, in an Excel worksheet.
Now you would like to use this information to create a PivotTable that applies these
corrective factors to the sales in 2004, to make some kind of projection of the sales in 2005. The
first step is to load sales from 2004 into PowerPivot, to have a starting set of figures. You
can query the OLAP cube to get the sales amount in 2004, producing, for example, a table
that contains the value for each month, category, and subcategory. The resulting table is
shown in Figure 5-26.