Microsoft SQL Server 2008 R2 Specifications download pdf (Page 214)

194 CHAPTER 10 Self-Service Analysis with PowerPivot

Relationships

By building relationships between the data, you can analyze the data as if it all came from a

common source. Relationships enable you to use related data in the same PivotTable even

though the underlying data actually comes from different sources. Dening relationship

between columns into two PowerPivot tables is similar to dening a foreign key relationship

between two columns in a relational database. Excel power users can understand dening

relationships as analogous to using the VLOOKUP function to reference data elsewhere.

In addition to consolidating data for PivotTables, there are other benets of building

relationships. You can lter data in a table based on data found in related columns, or you

can use the formula language to perform a lookup of values in a related column. These

techniques provide alternative ways to eliminate data redundancy, which keeps the workbook

smaller.

When you import related tables at the same time, the Table Import Wizard automatically

detects that they are related and creates the detected relationships. You can also manually

create relationships by using the Create Relationship button on the Design tab of the Power-

Pivot ribbon, as shown in Figure 10-4.

NOTE A column cannot participate in more than one relationship, and you cannot create

circular relationships.

FIGURE 10-4 The Create Relationship button

Filters

After you import data into PowerPivot, you cannot delete rows from the resulting PowerPivot

table. To keep your workbook as small as possible, you should apply lters during the import

process to exclude unneeded rows right away. After completing the import, you can modify

the table properties to add a lter, and then update the table to keep only rows that meet the

lter criteria.

You can also apply lters to the imported data if you want the data to be available for other

purposes later, while hiding specic rows from the presentation layer in the current report.

You can lter by name in the same way that you normally lter in Excel, by selecting from a

list of values in a column to identify the rows that you want to keep. As an alternative, you

can lter a numeric column by value, as shown in Figure 10-5. For example, you can use the

Between operator to apply a lter that will select rows with a value in a range that you specify.

1 2 ... 209 210 211 212 213 214 215 216 217 218 219 ... 235 236

No comments

Microsoft SQL Server 2008 R2 Specifications Page 214