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

Fact Table Design for Hours Worked

Hi,
I am trying to design a cube with Date, Time and Employee dimension
which can answer following questions:
- how many employees were working at 10am on 15 June 2007? I
- how many man hours were pais between 10am -11am on 15 June 2007. If
somebody has punched in at 10:30 then he should be counted and .5 man
hours.
One solution i can think of is every minute of everyhour for every
employee entered as a row in fact table. In this case if a employees
works for 8 hours that will create 8*60= 480 records in fact table. So
this may not be a good solution.
Ideally it would be better if we can enter one record in fact table
with start time and stop time. But, how can we put corrective
transaction if we need to cancel or change the original transactions.
Any help will be appreciated.
Thanks
Maheshyou can use 2 hour dimension:
Start Time and End Time
to answer:
> - how many employees were working at 10am on 15 June 2007? I
you select the day and all the start times up to 10am and all the end times
after 10am and you have the result.
for the second one, you have to create some formula.
maybe you can create a new measure group which contains only the start time
/ end time , and, for each combination, the duration in minute of the
selected period (ie you select 10h30 and 11h45, the measure returns
75minutes) (call this measure NoMinutesInPeriod)
now, in the cube, you have to create a formula which apply a ratio if the
start time or the end time is between the selected period of time.
measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
the datediff formula will be more complicated then a simple datediff.
but maybe this idea is a good starting point for you.
or anybody else has a better solution... please share your ideas :-)
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
> Hi,
> I am trying to design a cube with Date, Time and Employee dimension
> which can answer following questions:
> - how many employees were working at 10am on 15 June 2007? I
> - how many man hours were pais between 10am -11am on 15 June 2007. If
> somebody has punched in at 10:30 then he should be counted and .5 man
> hours.
> One solution i can think of is every minute of everyhour for every
> employee entered as a row in fact table. In this case if a employees
> works for 8 hours that will create 8*60= 480 records in fact table. So
> this may not be a good solution.
> Ideally it would be better if we can enter one record in fact table
> with start time and stop time. But, how can we put corrective
> transaction if we need to cancel or change the original transactions.
> Any help will be appreciated.
> Thanks
> Mahesh
>|||Thanks Jeje. Your solution seems to be the best solution. The only
problem with this is the difficulty to add corrective transactions.
For example, if hours worked is entered as 8am - 5pm on June 5 for an
employee. On June 25 if it is changed to be 10am - 5pm, how can we
enter a new corrective transaction on June 25 without changing the
original transaction. We do not want to change the original
transaction to use Incrementa Processing on cubes.
Any response will be very much appreciated.
Mahesh
On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
> you can use 2 hour dimension:
> Start Time and End Time
> to answer:> - how many employees were working at 10am on 15 June 2007? I
> you select the day and all the start times up to 10am and all the end time
s
> after 10am and you have the result.
> for the second one, you have to create some formula.
> maybe you can create a new measure group which contains only the start tim
e
> / end time , and, for each combination, the duration in minute of the
> selected period (ie you select 10h30 and 11h45, the measure returns
> 75minutes) (call this measure NoMinutesInPeriod)
> now, in the cube, you have to create a formula which apply a ratio if the
> start time or the end time is between the selected period of time.
> measure.NoMinutesInPeriod / (datediff('m', starttime, endtime))
> the datediff formula will be more complicated then a simple datediff.
> but maybe this idea is a good starting point for you.
> or anybody else has a better solution... please share your ideas :-)
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182525993.318614.225100@.a26g2000pre.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -|||if you want to "cancel" a transaction already loaded in a cube.
you have to add another "transaction" with "negative values"
for example:
initially you load the cube using this:
June 25 / 8am - 5pm / +1 (+1 work)
you want to "cancel" this transaction, and replace by a new one:
June 25 / 8am - 5pm / -1 (-1 work)
June 25 / 10am - 5pm / +1 (+1 work)
so the total in the cube is:
June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
June 25 / 10am - 5pm / +1
but using this approach you can't use the "non empty" because the cell is
not empty, the cell contains a total of 0.
Also remember to do full processes at a regular basis because an incremental
loading reduce the cube performance.
good luck!
"Mahesh" <shrestha.mahesh@.gmail.com> wrote in message
news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
> Thanks Jeje. Your solution seems to be the best solution. The only
> problem with this is the difficulty to add corrective transactions.
> For example, if hours worked is entered as 8am - 5pm on June 5 for an
> employee. On June 25 if it is changed to be 10am - 5pm, how can we
> enter a new corrective transaction on June 25 without changing the
> original transaction. We do not want to change the original
> transaction to use Incrementa Processing on cubes.
> Any response will be very much appreciated.
> Mahesh
>
> On Jun 22, 11:46 am, "Jeje" <willg...@.hotmail.com> wrote:
>|||Thanks you very much for your kind response, Jeje.
Mahesh
On Jun 25, 9:27 am, "Jeje" <willg...@.hotmail.com> wrote:
> if you want to "cancel" a transaction already loaded in a cube.
> you have to add another "transaction" with "negative values"
> for example:
> initially you load the cube using this:
> June 25 / 8am - 5pm / +1 (+1 work)
> you want to "cancel" this transaction, and replace by a new one:
> June 25 / 8am - 5pm / -1 (-1 work)
> June 25 / 10am - 5pm / +1 (+1 work)
> so the total in the cube is:
> June 25 / 8am - 5pm / 0 (+1 + -1 = 0)
> June 25 / 10am - 5pm / +1
> but using this approach you can't use the "non empty" because the cell is
> not empty, the cell contains a total of 0.
> Also remember to do full processes at a regular basis because an increment
al
> loading reduce the cube performance.
> good luck!
> "Mahesh" <shrestha.mah...@.gmail.com> wrote in message
> news:1182783852.227881.60220@.g37g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -