Loading External Data into Power Pivot - Microsoft Business Intelligence Tools for Excel Analysts - page 69

Importing tables versus importing views

Views are query objects that are built to extract subsets of data from database tables based on cer-
tain predefined conditions. Views are typically created by someone familiar with the database as a
kind of canned reporting mechanism that outputs a ready-to-use dataset.

There are pros and cons to importing tables versus views.

Tables come with the benefit of defined relationships. When you import tables, Power Pivot can rec-
ognize the relationships among the tables and automatically duplicate those relationships in the
Data Model. Tables are also more transparent, allowing you to see all the raw unfiltered data.
However, when you import tables, you have to have some level of understanding of the database
schema and how the values within the tables are utilized in the context of your organization's busi-
ness rules. In addition, importing a table imports all the columns and records, whether you need
them or not. In order to keep the size of your Power Pivot Data Model manageable, this often forces
you to take the extra step of explicitly filtering out the columns you don't need.

Views are often cleaner datasets because they are already optimized to include only the columns
and data that are necessary. In addition, you don't need to have an intimate knowledge of the data-
base schema. Someone with that knowledge has already done the work for you; joined the correct
tables, applied the appropriate business rules, optimized output, and so on. What you lose with
views, however, is the ability for Power Pivot to automatically recognize and build relationships
within the Data Model. Also, if you don't have the rights to open the views in design mode, you lose
transparency because you won't be able to see exactly what the view is doing to come up with its
final output.

It's generally considered a best practice to use views rather than tables whenever possible. They not
only provide you with cleaner, more user-friendly data, but they can help streamline your Power
Pivot Data Model by limiting the amount of data you import. That said, using tables is by no means
frowned upon, and is often the only option due to the lack of database rights or availability of pre-
defined views. You may even find yourself importing both tables and views from the same database.

5. Select the tables and views you want to import by checking the box.

In Figure 4-4, the FactInternetSales table is selected. The Friendly Name column allows you to
enter a new name that is used to reference the table in Power Pivot.

6. Click the Select Related Tables button.

Power Pivot scans for and automatically selects any other tables that have a relationship with
the table(s) you've already selected. This is a handy feature to have when sourcing large data-
bases with dozens of tables.

Remember that importing a table imports all the columns and records for that table.
This can impact the size and performance of your Power Pivot Data Model. You may find
you need only a handful of the columns from the tables you import. In those cases, you
can use the Preview & Filter button.

Note