Howdy,

I have a 5-million record fact table with individual Line Items from a Retail Sales RDBMS.

The client wants me to pin transactions down to three different and overlapping periods.

Period One:
The Beginning Date varies, with 500 possible dates. The end date is fixed at December 26th, 2000. I have stored the 500 records for these pairs in a separate table with a surrogate key. The variable date is the date that represents the day that the retail customers signed up for a discount card (YYYY-MM-DD).
Records look like this:

PK Begin End
1: 1999-02-06 2000-12-26
2: 1998-03-15 2000-12-26
3: 1997-08-02 2000-12-26
4: 1999-01-20 2000-12-26
....
498: 2000-03-16 2000-12-26
499: 2000-01-15 2000-12-26
500: 2000-02-21 2000-12-26

Period Two:
This one has the same fixed end date: 2000-12-26 and a fixed Beginning Date: 2000-01-01. There is only one record, appended to the other 500. This record has a key of 501.
The single Record shown here:
501: 2000-01-01 2000-12-26

Period Three:
Same as Period Two, but with another start date, namely 2004-01-15.

The single Record shown here:
502: 2004-01-15 2000-12-26

Now, the end result is that I want a set of data that looks like this in Excel:
The data is Counts of People that Fall in the Periods

Period One
45,987 people (may overlap other periods)
spent between
$5000 and $10000

Period Two
899,723 peple (may overlap)
spent between
$2000 and $4000

Period Three
1,000,789
spent between
$500 and $1000


Questions:

1. Since the same customer can appear in all three periods, with different aggregated amounts spent, do I need to have a resolver table between the fact table and the time table that I created (the one with 502 records).

That's all for now - any advice?

John Smith
The Aggregated One

I can't quite put my finger on it... Is there some linkage between the
dates and the customers? Your example of the table with start and end
dates does not seem to contain enough information. (If I am
understanding things correctly)

I would have thought that the start date for the discount could have
been stored as a member property against the customer. Then you could do
a distinct count and sum, using the member property as the start of the
date range.

--
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Well,

I ended up splitting out three different tables using SQL, one for each
period. Then, I loaded the tables with the User Keys and the other data.

It gets messier. The client also wants spend banding, wherein each grouping
of customer purchases falls within a given range.

So the table for Period One contains:

A. User Surrogate Key - One for Each Customer that meets the period spend
pattern criteria.
B. Aggregate Purchases Per Customer that are within the given period.
C. A series of BOOLEAN columns for the Spend Banding, like this:

Spent 100 - 150?
Spent 160 - 200?
Spent 250 - 300?
Spent 300 - 400?
Spent 400 - 500?
Spent 500 or More?

D. A language code (this is Texas, Señor), like this: ENG, SPN, HBW, POL,
CRE.
E. A State code (NV, TX, NM, CA ...)
F. A Value Card Tier (VODKA, WINE, BEER), where the Big Spenders are VODKA.
G.
Here's a couple of sample records:

23445 369.95 0 0 0 1 0 0 ENG TX VODKA
23445 369.95 0 0 0 1 0 0 ENG TX VODKA
23445 369.95 0 0 0 1 0 0 ENG TX VODKA
23445 369.95 0 0 0 1 0 0 ENG TX VODKA
I am going to treat them as dimensions.


"Darren Gosbell" <xxx (AT) xxx (DOT) com> wrote

Geez, this word processor sucks, I didn't finish,

Here's a couple of sample records:


23445 PERIODA 369.95 0 0 0 1 0 0 ENG TX
VODKA
23446 PERIODA 199.95 0 1 0 0 0 0 ENG NM
BEER
23456 PERIODA 769.95 0 0 0 0 0 1 SPN CA
WINE
23489 PERIODA 169.95 0 1 0 0 0 0 POL NY
BEER


23445 PERIODB 169.95 0 0 0 1 0 0 ENG TX
VODKA
23446 PERIODB 299.95 0 1 0 0 0 0 ENG NM
BEER
23452 PERIODB 869.95 0 0 0 0 0 1 HBW CA
WINE
23479 PERIODB 869.95 0 0 0 0 0 1 UKE NY
BEER

23445 PERIODB 169.95 0 0 0 1 0 0 ENG TX
VODKA
23446 PERIODB 299.95 0 1 0 0 0 0 ENG NM
BEER
23452 PERIODB 869.95 0 0 0 0 0 1 HBW CA
WINE
23479 PERIODB 869.95 0 0 0 0 0 1 UKE NY
BEER

I am very confused about this whole thing, because I know what I want the
pivot table to look like in Excel.

John Smith
The Aggregated One