Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Fact Table, Date period but no specific date

Fact Table, Date period but no specific date

Fact Table, Date period but no specific date


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.

RE: Fact Table, Date period but no specific date

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.

RE: Fact Table, Date period but no specific date

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

RE: Fact Table, Date period but no specific date

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

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?

ilias K.

RE: Fact Table, Date period but no specific date

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.

RE: Fact Table, Date period but no specific date

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
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.

RE: Fact Table, Date period but no specific date

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.

RE: Fact Table, Date period but no specific date

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.

RE: Fact Table, Date period but no specific date

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close