Anyone gotten Year-to-Date to work using SSAS 2005 'Define Time Intelligence' wizard?
- From: "duwayne.willett@xxxxxxxxxxxxxxxxxx" <duwayne.willett@xxxxxxxxxxxxxxxxxx>
- Date: 29 Jan 2006 15:26:58 -0800
Hi,
Having a separate dimension for special data calculations
(Year-to-Date, Prior Year-to-Date, Year over Year increase, etc) is
highly-desired by most BI users as is well known. We had a separate
'Periodicity' dimension in our AS2000 cubes, but have been intrigrued
by the SSAS2005 'Add Business Intelligence Wizard's possibilities.
However, I've not been able to get the 'Year to Date' function to
work...
From within a cube, if one starts this wizard and then selects
'Define Time Intelligence', the Wizard assesses the time dimension
hierarchies present, and asks to have one selected. Then it offers to
create a variety of useful calculations, in 3 main categories:
(1) Year To Date, Quarter To Date, Month To Date, 12 Months To Date,
(2) 12 Month Moving Average, 6 Month Moving Average, 3 Month Moving
Average
(3) Year over Year Growth (and %), likewise for Quarter over Quarter,
Month over Month, and Day over Day.
Proceeding, the Wizard modifies the cube Data Source View and Date
dimension to add a new attribute called (in our case) Date.By Fiscal
Year Date Calculations. This new attribute dimension can have a
variety of useful time calculations added as members. This is ideal,
as one could for instance have Measures on rows in a table, and this
'Date Calculations' field on columns.
MDX for the selected Date Calculations is automatically created
(scoped to the cube measures selected).
My problem is that the Moving Averages and Period-over-Period Growth
members work correctly, but none of the 'To Date' calculations work.
They don't seem to work regardless of whether a Year, Quarter, Month,
or Date is selected.
We've constructed the Date hierarchy from a DIM_Date SQL view, with
defined cube dimension levels of Year, Quarter, Month, and Date. We've
set up attribute relationships to link each level to the next highest
(as in the Project REAL best practices document and the SQL2005
Tutorial), and defined the cube dimension as a Time dimension. The
dimension itself looks correct when browsing the dimension and/or cubes
built with the dimension.
But when used for browsing a cube, the Year-to-Date function returns
the default 'NA' for all cells...
Has anyone been able to get this 'Year-to-Date' functionality to work?
The MDX is complicated, and will attach snippets of the auto-generated
MDX below.
/*
Begin Time Intelligence script for the [Date].[By Fiscal Year]
hierarchy.
*/
Create Member CurrentCube.[Date].[By Fiscal Year Date Calculations].
[Year to Date] AS "NA";
Create Member CurrentCube.[Date].[By Fiscal Year Date Calculations].
[Three Month Moving Average] AS "NA";
-- etcetera
-- The following 'Year to Date' calculation does not work,
-- and returns 'NA' for all cells explored so far:
( [Date].[By Fiscal Year Date Calculations].[Year to Date],
{
[Measures].[Total Units],
[Measures].[Total Charges],
[Measures].[Total Visits]
},
[Date].[Fiscal Yr].[Fiscal Yr].Members ) =
Aggregate(
{ [Date].[By Fiscal Year Date Calculations].DefaultMember
} *
PeriodsToDate(
[Date].[By Fiscal Year].[Fiscal Yr],
[Date].[By Fiscal Year].CurrentMember
)
);
--The following 'Three Month Moving Average' MDX works fine
( [Date].[By Fiscal Year Date Calculations].[Three Month Moving
Average],
{
[Measures].[Total Units],
[Measures].[Total Charges],
[Measures].[Total Visits]
},
[Date].[Month].[Month].Members ) =
Avg(
{
ParallelPeriod(
[Date].[By Fiscal Year].[Month],
2,
[Date].[By Fiscal Year].CurrentMember
) : [Date].[By Fiscal Year].CurrentMember
},
[Date].[By Fiscal Year Date Calculations].DefaultMember
);
Interestingly, previously in AS2000 this seemed to be much simpler.
The MDX using our 'Periodicity' dimension (a single-row dimension) was:
Sum(
PeriodsToDate(
[Date].[By Fiscal Year].[Fiscal Yr],
[Date].[By Fiscal Year].CurrentMember),
[Periodicity].[Periodicity].&[1]
)
We have not been able to get this method to work yet in SSAS 2005,
either.
Am probably overlooking something in our implementation. Any advice,
comments, or experiences getting Year-To-Date functionality working in
SSAS 2005?
Thanks,
DuWayne
.
- Follow-Ups:
- Prev by Date: Re: How to extract the underlying MDX
- Next by Date: RE: Improving the Sql environment at our Place ?
- Previous by thread: File system error when deploying an AS2005 project
- Next by thread: Re: Anyone gotten Year-to-Date to work using SSAS 2005 'Define Time Intelligence' wizard?
- Index(es):
Relevant Pages
|
