Need help building dimensional model to track subscribe/cancel events for customers. 
Author Message
 Need help building dimensional model to track subscribe/cancel events for customers.

Hello,

I am very new to data warehousing, and am looking for some guidance in
solving the following problem.

I have transaction data that records customer subscription and cancellation
events.  For each customer I also have some general demographics information
like income level, gender, and location.

I would like to construct a data mart that can answer questions like:

1. How many customers are in each of these
   groups, and who are they?
    Subscriber for < 3 months.
    Subscriber for < 6 months.
    Subscriber for < 9 months.
    Subscriber for < 12 months.

2. How many customers reactivate a subscription
   within 6 months of cancellation?

I have been reading through "The Data Warehouse Toolkit" and "The Data
Warehouse Lifecycle Toolkit" and they both seem to be suggesting fact-less
fact tables for something like this.  I get the impression that I would need
two separate fact tables, one representing subscription activity and one
representing cancellation activity.

Am I on the right track here, or are there other ways I should be thinking
about solving this problem?

Many thanks

-- Bennett



Mon, 19 Jul 2004 01:49:19 GMT
 Need help building dimensional model to track subscribe/cancel events for customers.

Bennet,

Sounds like you're on the right track here. Some things to think about:

1) Why not use a single fact table, and have a "Subscriber Event" dimension
as part of your cube? You could include an Event Description in the
dimension (Subscribe / Unsubscribe,) which is extensible in the future (in
case you want to track "Inquiry" events, etc. in the future. You could also
include other information about the event, such as Reason for Event, Event
Location, etc.

2) In your Time dimension, you may want to consider storing the date as a
serial number (days since January 1, 1900, for example,) and/or as a Julian
date, to make computation of your Event Age easier.

Lee Coursey

On Wed, 30 Jan 2002 17:49:19 GMT, "Bennett Smith"

Quote:

>Hello,

>I am very new to data warehousing, and am looking for some guidance in
>solving the following problem.

>I have transaction data that records customer subscription and cancellation
>events.  For each customer I also have some general demographics information
>like income level, gender, and location.

>I would like to construct a data mart that can answer questions like:

>1. How many customers are in each of these
>   groups, and who are they?
>    Subscriber for < 3 months.
>    Subscriber for < 6 months.
>    Subscriber for < 9 months.
>    Subscriber for < 12 months.

>2. How many customers reactivate a subscription
>   within 6 months of cancellation?

>I have been reading through "The Data Warehouse Toolkit" and "The Data
>Warehouse Lifecycle Toolkit" and they both seem to be suggesting fact-less
>fact tables for something like this.  I get the impression that I would need
>two separate fact tables, one representing subscription activity and one
>representing cancellation activity.

>Am I on the right track here, or are there other ways I should be thinking
>about solving this problem?

>Many thanks

>-- Bennett

==================================================================
Lee Coursey                  *      http://www.thecrayfish.com/
                             *      http://www.powerpopradio.com/
                             *      http://www.magicmeatball.com/  
------------------------------------------------------------------
LISTEN to my power pop broadcast 24 hours a day at:
http://www.live365.com/cgi-bin/directory.cgi?autostart=powerpop2
==================================================================


Tue, 20 Jul 2004 21:23:46 GMT
 Need help building dimensional model to track subscribe/cancel events for customers.
More info on your existing data structure is needed to make a good
recommendation.  However if you needed to creat a datamart, the design
is not too difficult, you'll need a couple of fact tables and some
lookup tables.  A star/snow-flake schema would suit you fine...

Based on the existing data you have you should be able to do this type
of analysis very easily with a good Business Intelligence tool like
Cognos Powerplay to find relationships within your data...  What it
does is take your data, and create relationships between the data, and
store it in a cube file.  You then create reports against the cube to
do the analysis you mentioned before...

PS I'm a senior business intelligence consultant, and I've done this
same type of analysis for numerous clients...

Another tool that would do the same thing would be Microstrategy...

To push out the reports from the Powerplay or Impromptu, I've used
this product
http://www.dstrat.com/ReportEase/reportease.html  It has worked very
well if you need to do this reporting on a frequent basis.

Hope this helps, email me if you have specific questions...



Tue, 20 Jul 2004 23:37:55 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Counting new customers in a dimensional model

2. Need help with data model - problem tracking product from many warehouse shelves

3. Need help with data model - problem tracking product from many warehouse shelves

4. I need a dimensional model for a subject area

5. OLAP Model Design Techniques --- Dimensional Model

6. dimensional model vs e-r model

7. I need a dimensional model for a subject area

8. Need PC database for order, and customer tracking

9. New to Dimensional Modeling, please help.

10. Dimensional Modeling Help

11. Help, Help, Help I need a template for invoicing, orders and customers

12. Help needed with FMPro and Apple Events or Lasso/Apple Events


 
Powered by phpBB® Forum Software