Inconsistent Behaviour in ime Comparison Calculations

I have two independent cubes that both use the same technique of a Time
Comparison dimension, i.e. a single row dimension that joins to the fact
table via a constant surrogate key. Calculated measures are then added to
this dimension to create members for Previous, Delta, Delta % etc. This is
well established technique that I’ve used extensively before.

My problem is that I get inconsistent behaviour when comparing the results
from the two cubes. The relevant cube details are:

Cube 1:
• Time Dimension Hierarchy: Year / Quarter / Month
• All measures (except Count) defined to be LASTNONEMPTY
• When this cube is queried with the Time Dimension Hierarchy on rows and
the Time Comparison dimension on columns with any of the LASTNONEMPTY
measures the values returned are exactly as expected at each level of
drilldown. i.e. at the Year level the value for Previous shows the previous
years’s value. Similarly at the quarter and month levels the correct
behaviour is observed.

Cube 2:
• Time Dimension Hierarchy: Year / Quarter / Month / Day
• All measures (except Count) defined to be LASTNONEMPTY
• When this cube is queried in a similar way the values return do NOT
reflect what is expected as with the first cube.
• In this case the values returned at the Day level are as would be
expected, i.e. the Previous column shows the value from the previous day.
However, for all other levels above Day, the values shown in the Previous
column are the LASTNONEMPTY values from the Day level rather than the
equivalent previous values at those levels.

Both Time Dimensions are defined as Type=Time although in the problem cube
this property appears in normal font whereas in the good cube is shows in
bold. I’ve no idea whether this has any bearing on the problem. Certainly
cube 2 seems to behave as if its Time dimension is not being recognised as a
time dimension.

Does anyone have any idea why this difference in behaviour should occur?

Thanks,

Jim

Jim,

So if they're independet cube, they're not sharing the exact same Time
dimension, only in name.

Some troubleshooting ideas (assumed this is 2005):
Is it possible that the attributes themselves do not have the proper
(or similar) *attributes types* assigned to each of the attributes
(e.g., does the Quarters attribute have a "Quarters" attribute type
assigned to is). Maybe there is a difference there?

Is it possible that each cube's specific (UDM) related hierarchy or
attribute properties are not exactly the same for that dimension? As
in the 2 tabs that appear in the bottom-left corner of the BIDS
interface under the larger "Cube Structure" tab. Would it be possible
to "comment out" all of each cube's MDX Script (just apply a big /*
and */ around the whole thing) and eliminate that something in the MDX
script may be driving a difference in the cube's cell values?

If all that fails (again assuming 2005), you could try to go into the
XMLA and compare the 2 cube's text for those 2 respesctive Time
dimensions and that would lay out all of the properties side-by-side
and just compare them in MS Word text comparison or use Visual Studio
Source Safe or some other similar utility to compare and that might
highlight any material differences.

-exologic

::: Pain with Essbase? Use CubePort, The BI Porting App :::
::: Go from Essbase to Microsoft Analysis Services :::
::: web: exologic.com/products.htm :::