Fact Table or Factless Table: Please Suggest

Hello All,

I am new to DW and as a first assignment I am trying to create data mart for Employee Leaves.

I have created following dimensions tables:

1. DimEmployee
2. DimDate
3. DimLeaveType

And Fact Table:

1. FactEmployeeLeave

In fact table currently there is no measure available (fact less fact table).

Please tell me is above design in correct or do I need to introduce any measure in fact table, if yes what will be that measure.

Target requirement is:

1. Able to find which month employee have maximum leaves.
2. Which employee taking excess leaves and which taking less.
3. Employee Designation wise leave status.

Please suggest me on fact table.

Regards,

Asim

Number of days?

Hello,

In my Fact table one record is one leave. So the Number of days will be always one.

Regards.

You just answered your own question.

Fact being measured is "Days_on_Leave", value on each record = 1.

Hello,

As i can take leave count in fact table, further i would like to know in this
Case which approch is better.

To have fact or to have fact less table.

Please suggest me as per best practice as i am not able decide the best approch.

Regards

Flip a coin.

The one advantage of storing a count is that if, in the future, you allow recording a multi-day absence in a single row, you are covered.

Hi,
Thanks for suggestion.

Addtionaly just want to ask one more question. As u said if i recod multiday leave in fact table i need to have two column in fact table i.e. start day and end day (fk from dimdate).

Is this understanding correct?

Regards.

Not necessarily. If you record the start date and number of continuous days, an end date would be redundant. Having it may make some queries simpler, but not having it isn't a major problem.

Hi All,

Thanks for your valuable suggestions.

Regards.