Take the 2-minute tour ×
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. It's 100% free, no registration required.

I have a fact table with the following columns:

WorkerName 
OrderId
NumberOfPackagesPerOrder

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]
    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 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
add comment

1 Answer

up vote 0 down vote accepted

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

More details here http://stackoverflow.com/questions/10971593/custom-aggregation-for-non-leaf-level-nodes-to-avoid-double-counting and here http://www.sqlbi.com/wp-content/uploads/The_Many-to-Many_Revolution_2.0.pdf.

Then no MDX is required.

share|improve this answer
add comment

Your Answer

 
discard

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.