Microsoft Office Tutorials and References
In Depth Information
Although it is certainly useful to see everything during the process of data discovery, several
columns distract us rather than help us. Let us see a couple of examples:
■ The column SalesOrderID in SalesOrderDetail is very useful because it makes the
relationship between SalesOrderDetail and SalesOrderHeader. Nevertheless, selecting it
for reporting purposes produces no useful result. We refer to this kind of column as a
technical column—that is, a column mandatory for the data model to work but that
has no meaning at all for the reports.
■ The columns rowguid and ModiiedDate, in the SalesOrderDetail table, are columns
used by the source system that handles the database, but they do not contain any
useful information either from the technical or from the reporting point of view. We
refer to these columns as useless columns because you can easily remove them
without affecting the data model.
Note Please note that useless columns are not simply columns that you believe are of no use in
the current report. Columns like SpecialOfferID, which is a technical column, contain interesting
information that seems not to be useful at a certain point in time. If you plan to use the same
source for many reports, you need to think twice before tagging a column as useless and maybe
deleting it. But even if you remove a column from the PowerPivot table, you can always reload it
later by changing the table properties.
To make the user experience better, you should hide technical columns and remove useless
ones so that the names shown in the field selector refer only to columns that can be used
in the report to provide useful results. When you remove a column from a table, that
column is physically deleted from the PowerPivot data model, is no longer available for any
operation, and reduces the memory and disk space taken up by the table. On the other
hand, when you hide a column, it still exists in the PowerPivot data model, although the
user cannot select it in a report. It is now clear why we choose to hide technical columns
(if we remove them, we would not be able to use them for relationship, for instance) and
remove useless ones.
To perform this task, you can open the Hide And Unhide Columns dialog box, which you
can see in Figure 2-8, from the Design tab on the ribbon of the PowerPivot window. From
here, you can choose to hide or show any columns of the table.
If you choose to hide a column in PowerPivot, it does not appear in the PowerPivot tables but
is still available in the PowerPivot Field List. This decision might help you achieve a cleaner
view of data when you are browsing in the PowerPivot window.
On the other hand, we are not interested in hiding any columns from the PowerPivot window
because we want to browse all data. We hide columns only in the PowerPivot Field List.