Microsoft Office Tutorials and References
In Depth Information
FIguRE 10-1 Using the list price, we get a complex PivotTable.
If you use the standard OLAP cube, you cannot slice data using the discounted price. This
information is stored as a measure and not as an attribute of a dimension, and the PivotTable
would let you slice data based on dimensions only. Nevertheless, using PowerPivot, you are
now going to learn how to overcome this limitation.
To get the discounted price for each single sale, you need to load data directly from the
database to gather the original reseller sales data so that you have access to each line of
the orders with PowerPivot. Moreover, after you load the original data, you need to create
a banding table and to define some sort of relationship between the sales and the banding
table. As you will see, this relationship cannot use the product code only because the price
band for a specific product may vary over time.
Let us show you an example to make the scenario more clear. If you define a price band to range
from $20.00 to $30.00 U.S. dollars (USD), you might find that the same product is sometimes
sold at $28.00, sometimes at $31.00 because of discounts or price changes over time. So we
want to separate these sales into different bands, even if they belong to the very same product.
In Figure 10-2, you can see that after you load the Fact_ResellerSales table into PowerPivot,
the discounted prices make the product with code 471 fall inside different price bands as the
real price changes over time.
FIguRE 10-2 The same product sold under different bands.
Now that the scenario is clear, it is time to search for a data model that makes banding
affordable. As we said earlier, to separate prices into bands, you first need a place in which to define
the bands. An Excel table is the perfect place to create the price bands, as in Figure 10-3.