Adding table to data model - linked vs un-linked

[cr731]

When adding an Excel table into the data model, I have a few questions:

1. Is there a correct time to have a linked table versus unlinked?

2. How do I add an Excel data table to the data model without creating a linked table?

 

[Matt Allington]

1. Yes. The general rule is "don't load data via linked table". The reasons are:
* linked tables first store the data in uncompressed format in Excel, and then a second copy of the data in compressed format in Power Pivot. Frankly if your data is 10,000 rows then it probably doesn't matter. But when you get to 100,000+ it will make a big difference to your file size.
* When you use a linked table, the "data" is in the report. This makes it harder to reuse the data in another report.

When you should use linked table is
* when you have small reference tables, parameters etc.

2. Create a dedicated Excel workbook for your data/tables. I normally create a "master data" workbook and put it in a known location with all of my tables for a project.
Then go into Power Pivot, select From Other Sources\Excel File (it is right down the bottom in 2010/2013 - why I have no idea!)

I cover many best practices like this in my book (link in my signature below)