Bill Cycle Modeling

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?

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

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?

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

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

Thanks!

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.

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.

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

agreed.