Database Reference
In-Depth Information
The Time Intelligence wizard
SQL Server Data Tools can build a calculation dimension automatically when we run
the Add Business Intelligence wizard (found under the Cube menu) and choose
the Define Time Intelligence option. Now that we understand the basic technique
of how to build a Calculation Dimension - and the wizard uses, essentially, the same
technique - we can take a look at what the wizard actually creates and understand
its limitations. While Calculation Dimensions are a powerful tool, the way that the
Time Intelligence Wizard implements them is seriously flawed and as a result our
recommendation is that you do not use the wizard at all. We'll now explain why
this is.
Attribute overwrite
The first issue is that the wizard creates a new hierarchy on an existing dimension to
hold calculations, and this leads to some unexpected behavior. Once again, though,
before trying to understand the theory let's take a look at what actually happens
in practice.
Consider the following, very simple query:
SELECT
NON EMPTY {
([Date Order].[Calendar Date Order Calculations].[Current Date
Order]),
([Date Order].[Calendar Date Order Calculations].[Previous
Period])
} ON 0,
[Date Order].[Calendar].[Date].&[20010712] ON 1
FROM
Sales
WHERE
Measures.[Sales Amount]
The query returns the real member from the hierarchy that the Time Intelligence
Wizard creates, Current Date Order, and the Previous Period calculated member,
for the Sales Amount measure and July 12, 2001:
Current Date Order
Previous Period
July 12, 2001
14,134.80
14,313.08
Search WWH ::




Custom Search