Microsoft Office Tutorials and References
In Depth Information
Now, you have two columns, each one representing the color but with one containing NULL
values and the other one containing meaningful descriptions. To make the PivotTable easier
to navigate, you should rename the original Color column something like OriginalColor, and
then hide it from the PivotTable browser and let the user navigate the new calculated color
column instead (which you should rename Color).
Note This solution works fine for removing empty values from all the columns in which they
could appear. Nevertheless, the tedious part of the work is that, for each column that might hold
empty values, you need to hide the original column and add a new column with a formula similar
to the one shown. This work is a bit tiresome, but the reports look much better.
Please note that, in the example, we have enclosed the description for empty values in angle
brackets. It is advisable to do so because, this way, this description becomes the first in the
report; the sorting order places the character < before any letter or number. Obviously, your
needs might be different and, in that case, any description works fine.
understanding How and When to Denormalize Tables
Now, after this small digression on empty values, we go back to the issue of normalized
tables. To help you get acquainted with the concept of denormalization, we provide some
examples of data structures that are (correctly) normalized in the AdventureWorks database,
which we need (still correctly) denormalized in the PowerPivot model.
The first example, which we have referred to earlier, concerns products, categories, and
subcategories. To denormalize the subcategories, you have seen that you should add a SubCategory
column in the Product table and use the RELATED function to ill its value. You perform the
same operation with the Category column, and then after hiding the original tables (that is, in
our example, ProductCategory and ProductSubcategory), you reach the optimal situation: you
can browse a denormalized data model even if the database contains data in a normalized way.
In other words, you load a normalized data model into PowerPivot and then you denormalize it
by using specific functions and relationships.
Although this technique is pretty easy to implement, it has the disadvantage of being annoying
to implement for all the many normalized columns that tend to appear in an operational
database. You might be glad to learn at least one different technique to perform the same operation.
This new technique requires that you use the query builder of PowerPivot to perform the
process of denormalization in the original data, and so load information into PowerPivot in
an already denormalized way. So we are shifting from denormalization in PowerPivot (the
previous technique) to denormalization outside of PowerPivot (the new one).
Having more than one technique to solve the same problem brings you the problem later of
deciding which one to use under what circumstances, but we talk about that later.