Date Ranges in cubes

#1

I have a SQL server table with three fields, Customer, Sale_Date and Sale_Amount.

From this, I need to create a cube with measures or calculated members that contain sales totals from certain date ranges, for example
Total Sales 0-30 Days Ago Total Sales 31-60 Days Ago...
Customer 1
Customer
...

Should I create these ranges in a summary table in SQL Server before bringing the data into Analysis Services, or is there a way to group the data into ranges within a cube?

Thanks.

#2

One possible way is to use the Lag function,

Total Sales 0-30 Days Ago: SUM({ [Time].[Day].CurrentMember.Lag(30) :
[Time].[Day].CurrentMember }, [Measures].[Sale Amount] )
Total Sales 31-60 Days Ago: SUM( { [Time].[Day].CurrentMember.Lag(60) :
[Time].[Day].CurrentMember.Lag(31) }, [Measures].[Sale Amount])

Might be a better way but I can't think of it.

<nc (AT) nospam (DOT) com> wrote

Quote:

I have a SQL server table with three fields, Customer, Sale_Date and
Sale_Amount.

From this, I need to create a cube with measures or calculated members
that contain sales totals from certain date ranges, for example:
Total Sales 0-30 Days Ago Total Sales 31-60 Days
Ago....
Customer 1
Customer 2
...

Should I create these ranges in a summary table in SQL Server before
bringing the data into Analysis Services, or is there a way to group the

data into ranges within a cube?

Quote:

Thanks.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Date Ranges in cubes

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

Date Ranges in cubes - 06-07-2004 , 04:56 PM

Re: Date Ranges in cubes - 06-07-2004 , 08:54 PM