Creating bill item dimension ?

View previous topic View next topic Go down

Creating bill item dimension ?

Post  MrBlueSky on Fri Aug 03, 2012 11:35 am

Hello,

I'm modeling a customer billing process for mobile operator. My grain is a single bill line item for a given service line, bill cycle and either service (Call, SMS...) or fee (subscription, discount, option...).

My facts and dimensions are the following :

Bill cycle
Customer
Service line
Rateplan
Date

And I hesitate between :

adding two dimensions, "services" (calls, sms...) and "fees" (subscriptions, paying options and discounts)

Or using a single dimension "bill_item" combining both dimensions. Which approch do you think is best ?

In the first case, when the line item is a service the fees dimension is not applicable and vice versa.

In the second case I will have to duplicate the "services" dimension which already exists and is used in other fact tables. I thought of using a view "bill_item" combining "services" and "fees" dimensions but there is a collision between the two dimensions surrogate keys.

I attached the model I designed for the second case.


MrBlueSky

Posts : 4
Join date : 2012-08-03

View user profile

Back to top Go down

Re: Creating bill item dimension ?

Post  BoxesAndLines on Fri Aug 03, 2012 1:41 pm

Services and Fees look like two unrelated dimensions. Unless you are building a junk dimension, I would go with two separate dimensions. BI tools can hide non applicable dimensions for a given fact metric.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Creating bill item dimension ?

Post  M. Khan on Fri Aug 03, 2012 7:13 pm

It looks to to me that there would be a single row per bill in the fact table as it is also showing a column to capture total amount. How would you handle if service provider needs to add more line items to the bill. Are you planning to then change the grain of the fact table .... Changing the grain could impact already released reports and therefore would create rework.

M. Khan

Posts : 11
Join date : 2012-07-24

View user profile

Back to top Go down

Re: Creating bill item dimension ?

Post  MrBlueSky on Fri Aug 03, 2012 7:45 pm

M. Khan, the total_amount is the total line amount. Exemple of a bill :

Billed item inpack_amount outpack_amount subscription_amount total_amount
Line 1 Subscription 0 0 50 50
Line 2 Calls0 10 010
Line 3 SMS0 5 0 5

The total billed amount in this case is 65

BoxAndLines, have you more details on how BI tools can hide non applicable dimensions for a given fact metric ? and how to handle the forreign key to the dimension in the fact table ? null ? not applicable entry ?

MrBlueSky

Posts : 4
Join date : 2012-08-03

View user profile

Back to top Go down

Re: Creating bill item dimension ?

Post  M. Khan on Sun Aug 05, 2012 7:09 am

MrBlueSky wrote:M. Khan, the total_amount is the total line amount. Exemple of a bill :

Billed item inpack_amount outpack_amount subscription_amount total_amount
Line 1 Subscription 0 0 50 50
Line 2 Calls0 10 010
Line 3 SMS0 5 0 5

The total billed amount in this case is 65

When the above information(i.e. three rows) is loaded into fact table, does the loading process transform them into one row?

MrBlueSky wrote:M. Khan, the total_amount is the total line amount. Exemple of a bill :
BoxAndLines, have you more details on how BI tools can hide non applicable dimensions for a given fact metric ? and how to handle the forreign key to the dimension in the fact table ? null ? not applicable entry ?

You can add a row with -1 surrogate key(i.e. normally called inferred member) in dimension table, and use it as FK in fact table. In description column in dimension for inferred member, "Not Applicable" or any other standard verbage as per your envirnment can be used. As a best practice, never leave FK as null.

M. Khan

Posts : 11
Join date : 2012-07-24

View user profile

Back to top Go down

Re: Creating bill item dimension ?

Post  MrBlueSky on Sun Aug 05, 2012 8:15 am

No M.Khan.

In the fact table I will have the 3 rows. The billed item in the 1st row is a fee (subscription) then it should link to the fee dimension and link to nothing in the service dimension.

The 2nd and 3th rows are services and should link to nothing in the fee dimension.

As said BoxesAndLines, the 2 dimensions are unrelated except in the billing fac where they represent both a billed item. My purpose is not to build a junk dimension but only hide the complexity for end users, if the first appraoch can be easily used for reporting and analysis it's OK for me.

MrBlueSky

Posts : 4
Join date : 2012-08-03

View user profile

Back to top Go down

Re: Creating bill item dimension ?

Post  ngalemmo on Mon Aug 06, 2012 1:42 am

What are the attributes like for the three different charge item dimensions (subscriptions, fees, etc)?

It seems to me much simpler to store them in one dimension and have one set of measures, then construct a view with the three sets of measures for the users. It avoids a mess with the foreign keys.

The problem with what you propose is it is not simpler for the users. They have three different dimension tables and only one applies to any one row. The need to deal with outer joins and null values.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: Creating bill item dimension ?

Post  MrBlueSky on Mon Aug 06, 2012 3:32 am

Attributes like for the different charge item are :

Services
-name
-desc
-Service category
-Service direction
-Service roaming flag
-Service measure unit

Options :
-name
-desc
-option group
-option category

Fees :
-name
-desc
-fee type

In the case I opt for the Charge item dimension what do you think is the best, just creating a view "charge item" drom the three dimensions or loading data in a separate physical table ?

I don't really understand what you mean by

construct a view with the three sets of measures for the users.

In both approaches the fact table will have the same set of measures, each subset of measures apply to one specific charge item (n/a measures will be equal to 0) and the total_amount apply to all as it's the charge amount for the line (equal to the sum of all subsets of measures)

MrBlueSky

Posts : 4
Join date : 2012-08-03

View user profile

Back to top Go down

Re: Creating bill item dimension ?

Post  Jeff Smith on Mon Aug 06, 2012 2:37 pm

I think it's a single Bill Item Dimension.

You can always have 2 rollup points in the Bill Item Dimension (1 for a service and another for a fee item) if you need an aggregate of the fees.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Creating bill item dimension ?

Post  Sponsored content


Sponsored content


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