Database Reference
In-Depth Information
Testing Your Reports
Do not expect your reports to be perfectly accurate. It is always important to validate your data. In Chapter
11
,
we cautioned you about the possibility of making incorrect aggregations when we reviewed the Calculations tab
of the Cube Editor. The issue concerned members that were added to the cube as either derived or calculated
members. It is very important to make sure your reports are correct; therefore, let's take a moment to review the
differences between these two member types.
A derived member is added by including SQL code in an SSAS data source view. When the data is pulled
from SQL Server into the cube during processing, the expression that makes up the derived member is executed.
Think of it this way: a first aggregation is performed by SQL Server, and the results are stored on the hard drive
as part of the cube. After that, a second aggregation is performed when a report is created using the cube.
For example, if we created a derived member that multiplies the vales for sales quantity by a title's price, that
expression would look something like this:
[FactSales].[SalesQuantity] * [DimTitles].[TitlePrice]
Imagine that we have a sales quantity value of 100 and a title's price value of $30. The expression would
evaluate to $300. And this new value would be stored in the cube as a derived member. After the cube stored the
values, it would later aggregate to get totals and subtotals when the report was created. Figure
15-23
shows an
example.
Figure 15-23.
Aggregates with a derived member
Things are a bit different with calculated members. A calculated member never stores data in the cube.
Instead, it stores only the MDX code for the expression. The evaluation of the expression happens each time a
report is made. Figure
15-24
highlights this process.
