2 facts or 1 fact - Revenue and time billed

[Bisquite]

Hi,

I'm trying to get my head round how to model my fact tables.

I work for a consultancy and I'm building a warehouse to track the revenue and chargeable hours worked by our consultants. I already have one fact table (FACT_TIME_BILLED) whose grain is one row per timesheet and includes the bill foreign key and the billed amounts for each timesheet (one bill covers many timesheets). The billed amounts in this fact table gives us the majority of the revenue figure we want to report on.
However I also need to include bills that don't relate to time sheets in the revenue figure. So my question is, do I:-
a) Create a 2nd fact (say FACT_BILLS) at a bill grain, that includes all the time sheets bills again and the non-time bills, or
b) Add the non-time bills into my FACT_TIME_BILLED, with each of these records pointing to a dummy timesheet record

I suspect that everyone will say a) is the correct approach so as not to mix the grains. But I can't visualise how the 2 fact approach works when building the SSAS cube on top and subsequent reports. ie. How does the user view an overall revenue figure from FT_BILLS then drill down to FACT_TIME_BILLED to view the individual time sheets that the numbers relate to? (Maybe this is easy and it's just that I don't have much experience with cubes and reporting).

Any suggestions/enlightenment welcome.

Thanks,

B/

[ngalemmo]

You could take a more generic view of things and just consider everything as invoice lines. You have a product being billed (in the case of time sheets, its services), you have a quantity billed, a unit of measure (hours, days, each, etc), a unit rate (optional) and an extended amount.

You would have an additional dimensions for the timesheet and for the person being billed for (concievably this could be rolled into the product dimension, but that may be too abstract for the business). These extra dimensions do not affect the grain and would point to 'not applicable' for non-service items. (Note: I would consider the timesheet with its hours by day by category by person a fact table unto itself, rather than a dimension. With a degenerate timesheet ID dimension value in the invoice fact.)

Unless I am missing something, there is no reason all billings could not go into one fact table.

[Bisquite]

Thanks for the reply. I think you've hit the nail on the head there when you describe it as "invoice lines". The bills that don't relate to timesheets will just have one invoice line. So I think one fact is ok.

[LAndrews]

One additional "grain" consideration could be time.

It sounds like your "non-timesheet" revenue is captured at a different grain of time than the services detail ... in other words, timesheet revenue could be weekly, while non-timesheet revenue could be monthly.

There are a few different ways to overcome this

1. Assign the non-timesheet revenue to a single "timesheet period" (e.g. the last timesheet period covered by the bill)
2. Spread the non-timesheet revenue accross all the "timesheet-periods" covered by the bill (effectively allocating the revenue to each timesheet period).

[Sponsored content]