Custom aggregation for non-leaf level nodes to avoid double counting

Question

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.

score 0 · Accepted Answer · edited May 23 '17 at 11:33

0

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

More details here https://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.

edited May 23 '17 at 11:33

Community

1

answered Jun 12 '12 at 04:06

user1234883

151
5

Custom aggregation for non-leaf level nodes to avoid double counting

1 Answers1