Database Reference
In-Depth Information
Figure 11-37.
The FactSales table now has a derived measure
Note that this differentiates calculated from derived members. Derived members are stored in the folders
managed by SSAS, but calculated members are never stored. Instead, each time a client executes a query against
the cube or dimension, the calculated member expression is evaluated. This can have a performance impact
since the aggregations have to be evaluated on each query request.
This might lead you to believe that the derived member is always the best choice since you likely want the
best query performance for your end users. But this is not always the case, because sometimes the calculations
will be incorrect when you use a derived member instead of a calculated member.
This has to do with the order of operations used to evaluate the expression. In other words, when we multiply a
price by the quantity, does the multiplication happen after the summation of the individual quantity values, or does it
happen before? As you know from learning mathematics in school, the order of operations directly impacts the results.
In the end, this means you must always test them to verify that the values are correct, whether you are using
derived or calculated members. If the results are the same, then you can choose to use a derived member for
its increased query performance. But, remember that it will be at the cost of taking longer to process (since the
values have to be aggregated and stored during the processing). If the values are different, you need to figure out
which is the correct value and use the member that is correct, regardless of performance.
In the upcoming exercise, we create both derived and calculated members for comparison and then validate which
one is correct. Before we do that, let's talk about how you can create a copy of a working cube for testing purposes.
Making a Test Copy of a Cube
Microsoft has made it easy to create a copy of your cube. You can simply right-click the cube in Solution Explorer
and choose Copy from the context menu. After that, highlight the Cubes folder, right-click the folder, and select
Paste from the context menu to create your copy, and a dialog window appears that allows you to name your new
cube. In Figure
11-38
we have defined the cube name by adding ForTesting to the existing name.