A single Dimension table Or separate the Dimensions?

[raikarleena]

It is a Telecom Fact capturing CDRs.
As every call is associated to a subscription (mob no, unique id for sim, start date,end date... etc) it definately qualifies as a dimension.
I also need to track my nos basd on "Subscription Plan" and it seems to be an attribute of subscribtion where each subscription has 1 and only one plan at a time. It is a perfect parent child relation.
Eg:
subscription a has rate plan "PlanA"
subscription b has rate plan "PlanA"
subscription c has rate plan "PlanB" and so on.
So theoritically "Plan" is an attribute of "subscription". But at application level, the promotions are handled by change in "Plan". That is: say I am "Subscriber A" who replies to opts for the promotion which i receive through sms (Promotion:reply YES to get free calling on weeked. charges $3/month). This is handled by changing my plan on every friday midnight. So user is in "PLAN A" from mon-fri and "PLAN C" on weekends.

I see the following ways to model this. Can I please know which is the best one?
1. Have "Subscription Plan" as an attribute in Subscription dimension. But in case of SCD-2 this would increase my dimension records drastically because of the situution explained above. Thinking of SCD-1 would make the things worst as viewing the report on weekday would show me all numbers for "PLAN A" and vice versa.

2.To Have them as 2 different dimensions. and combination of keys in fact for each CDR would determine the combination.

3. To to have a text column for the plan in Fact.

Reminds me of an article by Kimball which debates on having "product price" in fact or dim and the outcome was that to study the frequency of change and decide accordingly.

What would experts suggest? Sorry for the longg story but wanted to make sure I am clear as I am new to modeling world.

Thanks in advance.
Leena

[BoxesAndLines]

Doesn't the CDR have the rate plan on it? If so, just associate the fact to the appropriate dimension. There's no need to update dimensions unless I'm missing something. The account dimension will group all the CDR's for a given customer.

[mmoayed]

Hi ,

As I understand that CDR files are rated to the DB and Rated application is reading CDR files and rate them based on Rated setting in the billing system.

I think the best way to design is to design Rate_plan_dimesnion that will have all information of rating with start and End date for each rate plan .This dimension have to be SCD-2 as long as this table will not be so big ,from my experience this would increase almost thousands a month in worst cases ,which is still acceptable .

In your case you said if a customer is changing his rate plan every weekend if he replies to opts ,the changes will be in customer profile and the rating application will record the new rate plane was given to a customer and you will just read from the rated calls ,and Rate_plan_dimesnion will have any changes for rating

So, I would think one table for rating will be fine.

Some billing system they have other promotions like free of SMS in weekend as services to be assigned to rate Plan. Thy also have a feature to be assigned to customer and their rate plane engine is subtracting the amount from calls, in this case you will have to create dimension for services.
Based on the mechanism of billing system rating

[BrianJarrett]

If I'm understanding this correctly, it looks like this might be a good case for a mini dimension. Essentially this is what you mentioned in option 2. So essentially you'd have your subscription dimension which has a "Plan" attribute (only one plan can be active plan at a time). So you'd move "Plan" out of Subscription and into a mini dimension of its own, keeping it very light (plan name, effective from/to dates, etc.). This mini-dimension will be updated often (each time the active plan changes) but since it's so small it keeps your subscription dimension from becoming unmanageable due to type2 changes.

Each call record that comes through will join to a subscription. It would also then join to a "Plan" mini dimension that was in effect at the time the call was placed. When your users want to report on the number of calls for a subscription they include only subscription attributes/filters. If they want to know what plan was in effect they'll just join to the Plan table on the surrogate key and filter on effective from/to dates.

Let me know if this helps or if I've misunderstood your requirements.

[raikarleena]

Thanks Everyone!!!!
It Helped a lot!

[Micky]

Hello.

My name is Mykola. I've worked as a solution architect and technical lead on a big DW implementation for a leading telco with more than 20+ mln subscribers.

I would model this in the following way:

1. Create dimension Subscription,
2. Create dimension Rate Plan,
3. In your CDR fact table have surrogate keys for both - Subscription and the Applied Rate Plan.

4. In your Subscription dimension have 2 attributes (or more for promo rate plans, services, products) - like 'Main Rate Plan' and 'Promo Rate Plan 1'. Also you can add a couple of attributes to specify days of weeks and times when Main Rate Plan and Promo Rate Plan are affective.

In such a way your Subscription dimension will be on its own an interesting area for business users to analyse even without joining to fact tables.

You can handle Subscription dimension as SCD type 2 - you update a subscription every time one activates a new service.

In real life the relationships between subscription, rate plans, and services are way more complex because there are tons of additional services like IVR (fun dial), friends & family, call me back and many more that can be complementary to a rate plan and they can be activated and deactivated in any combination. Add here data services like Internet, 3G, calls to 8800 and 8900, etc, and you start to imagine the complications of the matter.

Generally, I would add those services directly to fact table and also have them as attributes for Subscription if it is important for business users to analyse what services are active and how they are/have been used.

Also you can use tactless fact tables to reflect relationships between subscriptions and their services/plans/products activated/used, etc. Such a table is usually called 'subscription history' and it offloads major change flow from Subscription dimension. This table is in a way a dimension on its own.

You have to combine the described approaches and you have to be driven by usage patterns and data contents when making decisions.

Regards,

Mykola Dolgalov

SA, Teradata

[Sponsored content]