Fact Table, Date period but no specific date

[iliask]

Hello,

I'm new to Data Warehousing. I'm building an BI application and I'm having some problems. My fact table has records with no specific date but with date from date to period.

Let's say I have customers that come on a date and leave on another day

My fact table has records like this:
Customer DateFrom DateTo
1 01/01/2009 31/03/2009
2 01/02/2009 30/04/2009

I cannot connect these records with a Time Dimension.
So when I want to browse how many customers I have for each month, or week, I just cannot do it.
Worse, I could also have customer categories to browse.

What would be the solution to such a problem?
What would be the best solution?

Perhaps I could create another table with sum of customers per day?
Is this a correct way to solve such a problem.

Thanks, ilias K.

 

[RiverGuy]

First question: what types of measures do you have in this fact table? Is it simply just a "customer count?"

Secondly, what you do depends on how the data is to be analyzed. If the questions to be asked are along the lines of "how many customers did we have by departure date?" then your current design will support that by joining the fact table to the "DateTo" column.

If your analysis is going to focus on "how many customers did we have per day?," then you need to redesign your fact table to store one record for each date the customer was there. You can take care of this in the ETL process.

 

[johnherman]

You need to define business rules. What constitutes a customer for a particular month? If they started the month as a customer, ended as a customer, were a customer at any time in the month, or must be a customer for the whole month?

Once you have defined, you can leave the data as is and put a view overtop the data using a CASE statement.


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[iliask]

Thanks for your answers. I want to give more information so if you can help me get the best solution.

The situation is like this. The table in the OLTP has customers (patients) with arrival date and departure date.
I need to know how many customers I had a year, half year, quarter, month, week.

So when I'm looking in a month I need a count of all customers that where there this month. So if a customers arrival and departure date has even one day in the month i want to count him.

Even worse I have a customers with no departure date. They have only the arrival date and I do not know when they are leaving. This is most of the cases

example
Customer A, Arrival date 10/01/2009, Departure 15/03/2009

Customer B, Arrival date 10/01/2009, No Departure date

So A will be counted for month 1, 2 and 3
A will be counted for lets say week 4 but not for week 1.
The same goes to B. And when out current date is 10th of April he has to be counted too but there is still no departure date.

If the fact table is Customer, Customer Category, Arrival Date, Departure Date then I cannot connect it to a Time Dimension.

So, how should my fact table be?
Like the OLTP table and there is some trick?
A table with a record for each customer per day? And what about those who have no departure date yet?
Or is there a better solution?

Thanks
ilias K.

 

[MDXer]

I would make the grain of the fact table be day where you would have a single Date key that would represent a single date. For each day someone is checked in they get a record. This could be implemented as part of your ETL process. AS for those without a departure date you could add the Rull that Null in this column could be Today as part of your etl as well. This would then allow occupancy reporting at all levels of time.

I have implemented this method in 2 hotel systems I built and never had an issue.

 

[Hans63]

I say your fact table is fine.

why can't you join it to a time dimension? As long as you fill (or convert) the empty departures with a special date (far in the future), you can join by specifying:

arrival_date <= last day of month
and
departure_date >= first day of month

These last and first day of month are typical attributes of a date/time dimension, but can be functions or pre-calculated dates as well.

 

[RiverGuy]

I vote for my original post, and MDXer's solution as well -- one record per customer per date.

Either way, I'm not sure that I would use a far reaching future date for customers who have not yet departed. If you ever need to do analysis for "how many patient days did we have in the first quarter," then you run the risk of overstating the value unless you always remember to include a WHERE/ON clause to limit the Date dimension join criteria to be less than or equal to the time of the last ETL load. I would prefer to keep all of the special logic in the ETL, and be able to run more generic queries against the star schema.

The only issue I could see you running into with having one record per customer per date would be the case where you are asked to include another measure in the fact table which applies to the entire stay. You would either have to design a new fact table for those or distribute them evenly across all dates of the stay. However that would also be a problem using the other design as your join would duplicate that measure.

 

[Hans63]

I say that it is dependent on the specific case. If you have many long stay patients, my suggestion works very fine. If you manly have short stay patients, the solution from riverguy and MDX-er works best.

There is not a solution that is the best for all situations. Look at all given approaches and choose which one suits you best in this case.

In all cases you have to be careful in your reports to avoid double-countings. This is inherent to working with time dependend data.

 

[iliask]

Thank you all for your answers.

I'm thinking of going the way creating one record per customer per day. So if I want to give access for others to get data it will be more easy.