I have a fact table with the following columns:
WorkerName OrderId NumberOfPackagesPerOrder
Note that neither
OrderId is unique in this fact table.
Multiple users could work on a single OrderId
Also note that
NumberOfPackagesPerOrder only depends on the
OrderId, i.e. for every
OrderId, the number of
NumberOfPackagesPerOrder will be the same.
I am trying to build a cube to report hierarchical report from this data by employee hierarchy:
ManagerLevel1 [Total orders completed] [Total Packages Shipped] Managerlevel2 ..... ManagerLevelN Worker
Since multiple workers could work on the same order, I need to avoid double counting
PackagesPerOrder for manager levels (non leaf nodes).
How to do this? What MDX Script do I need to properly
NumberOfPackagesPerOrder by summing these only after
DISTINCTing these by
Note1 - the employment hierarchy is defined recursively - the levels are not static.
Note2 - managers, those on a non-leaf nodes, could also potentially ship packages.