21 Feb 12 0:58
I have the next scenario:
I have a factories table, with factoryId, NumEmployees, NumFemaleEmployees, NumMaleEmployees and countryId (among others). Then I have this other table called province workers with ProvinceWorkerId, FactoryId, WorkerCount, Province (text). I am working on a OLAP cube, and I have created a fact table with this info:
FactoryId, NumEmployees, NumMaleEmployees, NumFemaleEmployees, CountryId, WorkerCount, Province.
My issue, is when I try to make sums. I end up with registries like:
FctyId #Employees #Female #Male Cty WorkerCount Province
1 100 70 30 2 12 Arizona
1 100 70 30 2 30 Nebraska
1 100 70 30 2 20 Texas
2 200 60 140 2 30 Arizona
When I get the sum of WorkerCount, everything is ok, but getting the NumEmployees sums, does not work, because it is retrieving the repeated data. I don't know how to do in MDX to sum up by FactoryId, I mean adding that measure.
I have two limitations right now, I can only use one fact table, and the measures must be in that fact table (because of the control that I am using). Maybe I have a design error (this is my very first cube), so any suggestions would be welcome, and if this has been answered before, the link could help me a lot.
Thanks, and have a good day.