Hello Experts,
i have a fact table named Fact-Transfer People and joined with two dimension table (Dim-HRMS and Dim-HRA) in
then in logical layer , i creat Level-based Hierarchies for Dim-HRMS and Dim-HRA as below.
Dim -HRMS: Element num Dim -HRA: Element num
Total 1 Total 1
--LV0 10 --LV1 50
--LV1 100 --LV2 100
--LV2 200 --LV3 300
--LV3 300 --Detail 1000
-LV4 400
--LV5 500
--LV6 600
--LV7 700
--Detail 1000
then in logical layer, i creat fact logical table Fact-Transfer People with 11 logical table sources(same fact table in physical layer) as below.
Fact-Transfer People Content(Logical Level)
Sources: Dim- HRMS Dim-HRA
Fact-Transfer People_L0_HRMS LV0 Total
Fact-Transfer People_L1_HRMS LV1 Total
Fact-Transfer People_L1_HRA Total LV1
Fact-Transfer People_L2_HRMS LV2 Total
Fact-Transfer People_L2_HRA Total LV2
Fact-Transfer People_L3_HRMS LV3 Total
Fact-Transfer People_L3_HRA Total LV3
Fact-Transfer People_L4_HRMS LV4 Total
Fact-Transfer People_L5_HRMS LV5 Total
Fact-Transfer People_L6_HRMS LV6 Total
Fact-Transfer People_L7_HRMS LV7 Total
when i creat a meaure named Headcount with SUM aggregation rule and mapped all fact logical table source,
i see BIEE choose the Fact-Transfer People_L0_HRMS table source,because it is the highest table source with the minium element number.
but then i set the aggregation rule to count(distinct) in this fact table i thinnk BIEE should choose the lowest table souce (Fact-Transfer People_L7_HRMS)
for measure in count(distinct). but biee choose the Fact-Transfer People_L3_HRA in actual. so i wonder why biee choose this table source for this measure.
it is not the lowest table source with the maximum element number.
note: all the logical table have the same priority group number 0.
can anybody helps me,thanks a lot!