Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a fact table with the following columns:


Note that neither WorkerName nor 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]

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 SUM NumberOfPackagesPerOrder by summing these only after DISTINCTing these by OrderId?

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.

share|improve this question
up vote 0 down vote accepted

The right approach for the problem is to use many to many relationship in SSAS.

More details here and here

Then no MDX is required.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.