sqlssisolapcubemeasures

SSIS Calculation with different total


I'm trying to show the amount of days a company travel has taken. The sales employee doesn't enter the exact amount so it is calculated in SQL and in the data load to DWH it is added to the TravelID. If travelID exists 3 times due to 3 trip lines connected to it, each line gets the amount of 3.

DWH Database

TravelID   1234       TriplineID   2345     #Days  3
TravelID   1234       TriplineID   2346     #Days  3
TravelID   1234       TriplineID   2347     #Days  3

So one TravelID can have multiple TriplineID's

The problem is when i want to make my cube measure, is that it counts up all the 3's. In this case it shows me #Days is 9 for one travel, while it must be 3:

                                     #Days
--TravelID      1234
     --TripLineID   -2345            3
     --TripLineID   -2346            3
     --TripLineID   -2347            3
     Total                           9

I then thought i'd devide it by a Count of the rows. ([Measures].[# Days] / [Measures].[Count])

Then it shows me a partly correct result:

                                     #Days
--TravelID      1234
     --TripLineID   -2345            3
     --TripLineID   -2346            3
     --TripLineID   -2347            3
     Total                           3
Total                                2

Since there obviously are multiple TravelID's, it also devides that Total, while there it must actually make the sum of the previous Totals.

What would be an efficient way to implement this? I spend a whole day cracking my head over this but i can't seem to find a good solution.

Here is a bigger picture of the problem:

                                     #Days
--TravelID      1234
     --TripLineID   -2345            3
     --TripLineID   -2346            3
     --TripLineID   -2347            3
     Total                           3
--TravelID      1235
     --TripLineID   -2348            4
     --TripLineID   -2349            4
     --TripLineID   -2350            4
     Total                           4
Total                                2

Where the 2 here should have been 7.


Solution

  • The only solution i have found up to now, is to create a second fact table. I was trying to avoid this but i didn't see any other way.