Creating bill item dimension ?

[MrBlueSky]

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.

[BoxesAndLines]

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.

[M. Khan]

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.

[MrBlueSky]

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	Calls	0	10	0	10
Line 3	SMS	0	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 ?

[M. Khan]

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	Calls	0	10	0	10
Line 3	SMS	0	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?

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.

[MrBlueSky]

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.

[ngalemmo]

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.

[MrBlueSky]

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)

[Jeff Smith]

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.

[Sponsored content]