Fact table design problem

Hello All,

I have a row for each employee, each month in my fact table.

EmployeeID DateKey Other Dimensions... 1 20060101 2 20060101 1 20060201 2 20060201

I need to include bonus information. The problem is, for each month a person may have received more than one bonus amount and type in a month. In some cases they have gotten the same bonus twice in the same month. For example:
EmployeeID DateKey Bonus Bonus Type
1 20060105 $5,000 Team
1 20060107 $500 GoodJob
1 20060110 $250 Incentive

How can I have multiple bonuses and their amounts in the fact table if I only have a single row for each employee?

Thank you for the help, this has been driving me crazy.

-Gumbatman

p.s. I've tried stuff with many-to-many dimensions and also Factless Fact tables to get this, but it is just not working for me. I read Mark Russo's very good article about many-to-many dimensions but I am not 100% that it applies to what I am doing.

Hello! I am not sure that I understand the problem fully but have you tried to add a bonus type dimension.

With a bonus type key, an employee key and a date key you should be able to do the analysis you are describing.

All these relations would be one-to-many from the dimension tables.

HTH

Thomas Ivarsson

|||

Thomas,

Thank you for responding.

Are you saying keep the Bonus amounts (the facts) in the main Fact table or have a secondary fact table?

If it is in the main fact table, how do I connect the Bonus dimension to the fact table when there are multiple bonuses inside a month (which is the grain of that fact table)?

I tried using a Bonus fact table that connects to the main fact table via a DateKey and EmployeeKey. Then I connect the Bonus Dimension to the Bonus fact table. In order to get the Bonus Dimension to be "seen" by the main fact table, I am using a many-to-many connection.

The problem I am having there is I have a Geography Dimension connected only to the main fact table, not the Bonus fact table. I can't seem to get the Geography Dimension to work properly. What I mean by that is that the Bonus numbers show up for everyone, regardless of Geography.

Please let me know if I am not explaining this clearly. I really appreciate the help (and I really need the help). I've gotten to the point were I am ready to hire a consultant on this issue alone.

-Gumbatman

|||

Perhaps you are talking about something not previously known in your first post.

I do not think that you will need two fact tables for this problem. I am thinking about one fact table with one measure like Amount.

The dimension tables will be time, employee and bonustype. If an employee receive the same type of bonus twice on a single day you can aggregate these recordss into one, with Integration Services.

HTH

Thomas Ivarsson

|||

Sorry if I didn't mention stuff clearly in my first post. There is a lot to this issue that I may have forgotten to mention.

However, I am curious about your IS suggestion. I can certainly aggregate the amount into the single row. But how do I show that a person received multiple bonuses in that month?

For example, the Bonus Dimension looks like this:

BonusID BonusType BonusAmount

1 Thank You 50

2 Applause 100

3 Encore 250

On a single row, the employe received two Thank Yous and an Applause - a total of 200. Should my Dimension table look like it does? And this prompts another question, can I do a calculation from a Dimension table?

Thank you again for your help.

-Gumbatman

|||

Hello. I would move the BonusAmount to the fact table and keep BonusId and BonusType in the dimension table.

You do all calculations in the fact table.

Your single fact table will look like this

EmployeeId

BonusId

BonusDate

BonusAmount

HTH

Thomas Ivarsson

|||

Thomas,

Is the Fact table you are suggesting here, a secondary one to the "main" fact table? If so, what is the best way to link the two fact tables, since the data needs to be summarized by month?

If you are suggesting putting the bonus data in the main table, do I just repeat some of the other Dimensions? For example, here is what I am thinking would be my main fact table with bonus information for a single employee in one month:

Employee Key Date Key Geography Key Salary Bonus Amount Bonus Key 1 20060101 55 50,000 50 1 1 20060101 55 0 250 2 1 20060101 55 0 500 3

Since it is additive by month, I have to zero-out the Salary for the additional rows.(At least that is my guess.)

Thank you.

-Gumbatman

|||

No! Only one fact table.

I see that you have a salary measure as well and that it will repeat for all bonus records and that the granularity is actually on a month level?

If this is the case I suggest that you make each bonus type a separate measure and aggregate them to the same level as the salary(month?)

So if the same bonus appears several times during a month it will be aggregated into one single bonus type measure.

HTH

Regards

Thomas Ivarsson

|||

OK. I think I got it now.

But if I add each bonus as a separate measure in the fact table, what happens if a new bonus type appears?

Thank you.

-Gumbatman

|||

It will be a new measure.

HTH

Thomas Ivarsson

|||

Sorry, I should have been more specific...

What is the best way to handle a new measure coming into the Fact table, through Integration Services? I guess I check and then append new columns when a new bonus measure is created?

Also, if I do it by measures, what happens to the Bonus Dimension? I will need to slice and dice by the Bonus types. I am unclear about how I can still show who got a what bonus type, especially if there was more than one in that monthly row.

Thank you again (and for your patience).

-Gumbatman