Bill Cycle Modeling

View previous topic View next topic Go down

Bill Cycle Modeling

Post  Skipjacker on Wed Nov 10, 2010 2:47 pm

The context is a credit card issuer and the concept of bill cycle.

There are 21 bill cycles. Every bill cycle has associated days in the future, one day per month.
Every Credit Card also has an associated bill cycle.

The confusion I'm having is where to model this.

There's really not much interesting about the "bill cycle" itself. There are no attributes we would like to "group by" or "filter on" so it's not really its own dimension.

Is it ok to have it represented on both the card dimension and the date dimension? what level of pain am I buying for myself?
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  ngalemmo on Wed Nov 10, 2010 3:11 pm

I can see having it on the account, as that determines when the card is billed.

Not sure about the date dimension, unless you have some use for knowing when a particular bill cycle is run... I guess it could be useful if you can use a date on a transaction to identify if it appears on a particular statement.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Bill Cycle Modeling

Post  Skipjacker on Wed Nov 10, 2010 3:22 pm

ngalemmo wrote:I can see having it on the account, as that determines when the card is billed.

Not without a date it doesn't.
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  ngalemmo on Thu Nov 11, 2010 12:03 am

Yes, but the act of billing is an event, and should be reflected by a fact (or facts). I imagine you could also have it as a date attribute if you know in advance when a particular cycle is to be billed. But that may be problematic if it is possible to run two cycles on the same day.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Bill Cycle Modeling

Post  BoxesAndLines on Thu Nov 11, 2010 12:08 am

The bill cycle is a day of the month. The year and month here are irrelevant. Your right, most folks don't report meaningful metrics by bill cycle day. At least in Telco anyways. I'm with ngalemmo in that I'd put it wherever the customer/account attributes are stored.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  Skipjacker on Thu Nov 11, 2010 1:49 pm

BoxesAndLines wrote:The bill cycle is a day of the month.

That's not the way our system works. Day of month wobbles slightly. So it has to be tracked.
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  John Simon on Thu Nov 11, 2010 5:51 pm

Why not just create a factless fact table containing Account and cycle Date if you know it in advance?

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

I like that idea

Post  Skipjacker on Mon Nov 15, 2010 4:11 pm

The factless fact table makes sense but then, I already have a table of dates in my date dimension, so couldn't I add that as an attribute.
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  hang on Mon Nov 15, 2010 6:22 pm

You could if the bill cycles are the same for all credit cards, and in that case I canít see any difference between the bill cycle and holiday indicator in a date dimension.

However if different credit card has different bill cycle, you then need a factless coverage table, as suggested by John, to cater for many to many relationship between credit cards and cycle dates. You donít have to pre-build the whole table initially. You can build it incrementally as future cycle has been determined.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

One other issue

Post  Skipjacker on Mon Nov 15, 2010 6:55 pm

the other possible gotcha is that, since it can wander between days of the month, can it every overlap. If bill cycle 21 is usually the 27th of the month and sometime wanders to the 28th, does bill cycle 22 also wander over to the 29th. Because that could happen, potentially, maybe worth avoiding a date dimension attribute.
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  John Simon on Mon Nov 15, 2010 7:52 pm

Is a Bill cycle date specific to a credit card account?
I.e. Does account A have a bill cycle date of the 1st, and account B have a bill cycle date of the 5th?

In that case, you need a factless fact table.

John Simon

Posts : 186
Join date : 2009-05-29
Location : Melbourne, Australia

View user profile http://jsimonbi.wordpress.com

Back to top Go down

Re: Bill Cycle Modeling

Post  hang on Mon Nov 15, 2010 8:03 pm

Whether the bill cycle should be in the date dimension or not is not determined by where it wanders to in a month. As I said, if you have the same bill cycle for all your customers, then have an attribute in date dimension, and just manually update the field, otherwise you canít because of many to many relationship.

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  ngalemmo on Tue Nov 16, 2010 12:34 am

Bill cycle can be an attribute of both an account and a date. They mean different things.

Bill cycle on an account indicates which cycle the account is billed. Bill cycle on a date indicates when that cycle was or will be run.

You certainly need bill cycle on an account. If you need to support some sort of cashflow analysis where you need to calculate future billings, by all means include the cycle count on the date dimension (assuming only one cycle would be run on any given day).

You don't need a factless fact table since any sort of billing cycle analysis would need charges to be billed, or if it is simply a count of accounts, can be handled by a query against the two dimensions.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Not sure I understand

Post  Skipjacker on Tue Nov 16, 2010 11:54 am

ngalemmo wrote:
You don't need a factless fact table since any sort of billing cycle analysis would need charges to be billed, or if it is simply a count of accounts, can be handled by a query against the two dimensions.


One report the business has asked for is a count of accounts for the next three bill cycles. I don't know what you mean by "a query against the two dimensions". If I don't have some relationship with bill cycle and unbilled (future) dates, how could I answer that?
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  ngalemmo on Tue Nov 16, 2010 10:47 pm

Join the date dimension with the customer dimension on bill cycle.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Great

Post  Skipjacker on Wed Nov 17, 2010 6:27 pm

So we're right back to my original question and the answer is yes, I should do it that way .

Thanks!
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  Jeff Smith on Fri Nov 19, 2010 6:27 pm

Couldn't the billing cycle be a date field on the Account Fact Table? If the Billing Cycle to which a card is assigned can change and you want to keep track of how many cards were in a billing cycle from last year, then it would need to be on a fact table. If no one cares what happened last year or last month, then it can go onto the Card or Account dimension.

There would be no need to create a factless fact table as Credit Card Snapshot fact table would normally be loaded fairly frequently.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  Skipjacker on Mon Nov 22, 2010 12:55 pm

Skipjacker wrote:
One report the business has asked for is a count of accounts for the next three bill cycles. If I don't have some relationship with bill cycle and unbilled (future) dates, how could I answer that?

Billing Fact tables are full of events which have occurred, how do I build the report I mentioned earlier in the thread? There are accounts added to the account dimension which haven't been billed yet, or have changed their bill cycle. Historical records of what has happened doesn't tell me what will happen.
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  hang on Mon Nov 22, 2010 5:26 pm

That's why you need a coverage factless fact table that stores the associations between customer and predefined bill cycle, so that you can report on which customer covered in bill cycle has not been billed yet. Other such scenarios are salerep-customer and product-promotion factless fact tables that are particularly useful to answer what didn't happen.

The last thing you want to do is add rows to your transaction fact table to capture something that has not happened as the table is already very large. There is another post about the usage of coverage factless fact tables: http://forum.kimballgroup.com/dimensional-modeling-and-data-architecture-f6/monster-dimensions-t636.htm?highlight=monster


Last edited by hang on Tue Nov 23, 2010 6:24 am; edited 3 times in total

hang

Posts : 528
Join date : 2010-05-07
Location : Brisbane, Australia

View user profile

Back to top Go down

Re: Bill Cycle Modeling

Post  Skipjacker on Mon Nov 22, 2010 5:27 pm

agreed.
avatar
Skipjacker

Posts : 16
Join date : 2010-11-10
Age : 49
Location : Baltimore, MD

View user profile

Back to top Go down

Re: Bill Cycle Modeling

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