PowerPivot Linked tables

In our previous blog post, we discussed how easy it is to use the Copy/Paste feature of the PowerPivot [aka Gemini] add-in to bring in data from virtually any external source that cannot otherwise be accessed by means of an ODBC, OLEDB or Managed .NET provider. In these scenarios, business users are free to copy new or updated data at any time, but the source of the data is not retained in the model itself, and so multiple copies may need to be manually performed over the model’s life span in order to reflect changes to the sources.

Now consider the following business scenario: an analyst needs to produce a forecast of their company’s sales by product over the next year. Then, he or she must create a report combining the actual and forecasted data, with the forecasts being reviewed and updated at the beginning of each quarter. To do this they will need to thoroughly analyze actual historic data, explore trends, make a number of assumptions, to eventually come up with the forecasted values; some of them might be directly inferred using basic arithmetic formulas; but others could result from far more complex calculations, or need to be keyed in manually.

Read more...