CRM DW, measuring product and services subscription

View previous topic View next topic Go down

CRM DW, measuring product and services subscription

Post  hugoV on Sat Jul 05, 2014 11:28 am

I'm designing a DW for an utility company.

The main products sold are electricity (several packages) and gas (several packages).
The company also sells some services, allowing customers who have electricity or gas to subscribe to support services (in case you have a problem with some household equipment).
There are a few "things" that a costumer may subscribe like:
- "Direct debit" (DD)
- "electronic invoice" you get the invoice by e-mail instead of snail mail
and others.

All the products and services are sold by the same line of business.

We need to track if in day X customer had electricity or gas or DD (for example).
Would you handle DD just as another product/service or as an attribute of the customer/contract?

We can have a transaction fact table to control the date when the product/service was sold.

but we also need to efficiently know on any given day what are the nš of active electricity subscriptions?
Would you leave this information on a SCD with begin and end dates or a daily snapshot fact table?

products have several different attributes (they are heterogeneous) should I create a mini dimensions for each product or, since in total we will probably have less than 100 attributes, leave them all on the main product table?


Thank you,
Hugo

hugoV

Posts : 6
Join date : 2014-07-05

View user profile

Back to top Go down

Re: CRM DW, measuring product and services subscription

Post  nick_white on Wed Aug 13, 2014 7:23 am

I noticed that no-one had replied to this so thought I would give it a go...

I, personally, wouldn't describe DD, electronic invoice as products/services so wouldn't model them as such. I can think of two ways of handling them:
1) As customer attributes. As long as your customer dim is SCD2 you can then tell who had what attributes at any point in time
2) As Billing attributes on a Billing Dim - in which case you could create something like a Customer profile fact (possibly factless) with associated dims of customer, billing, effective date(s), etc.

Probably easier to have them as customer attributes as then you can filter other facts by these customer attributes - rather than adding the Billing DIm to existing facts.

For your products/services I would create a customer product/bundle fact table. If you have a known list of products/services (and is it not too big) you could include them all as measures on one fact table e.g. Electricity Count = 1, Gas Count = 1, Support Services = 1 (plus this design makes it a bit easier to answer queries such as "customer has gas but not electricity). If you have many products/services and/or they change frequently then have a product/service dim and in your fact table you'd just have one measure e.g. service taken = 1/0. Whichever way you go the fact table would have effective start and end dates

Hope this helps

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum