Database Reference
In-Depth Information
Limit the number of columns in your Data Model tables
One of the biggest influences on Power Pivot performance is the number of columns you import into
the Data Model. Every column you import is one more dimension Power Pivot has to process when
loading your workbook. If you're not certain you will use certain columns, don't bring them in “just in
case.” You can easily add columns if you find you need them later.
Limit the number of rows in your Data Model
This one is simple. More rows mean more data to load, more data to filter, and more data to calculate
through. Avoid selecting an entire table if you don't have to. Use a query or a view at the source data-
base to filter for only the rows you need to import. Why import 400,000 rows of data when you can
use a simple Where clause and import 100,000?
Avoid multi-level relationships
Both the number of relationships and the number of relationship layers have an impact on the per-
formance of your Power Pivot reports. When building your model, it's a best practice to have a single
fact table containing primarily quantitative numerical data (facts) and dimension tables that relate to
the facts directly. In database-speak, this configuration is called a star schema (see Figure 15-9).
Figure 15-9: A star schema is the most efficient model, with a single fact table and dimensions relating directly
to it.
Avoid building models where dimension tables relate to other dimension tables. Figure 15-10 illus-
trates this configuration, also known as a snowflake schema. This configuration forces Power Pivot to
perform relationship lookups across several dimension levels. This can be particularly inefficient
depending on the volume of data in the model.
 
Search WWH ::




Custom Search