DV Total Count always calculated in the highest level

Question

Summary

Content

Hello,

I am using visual analyzer Version 12.2.1.1.0.

Module Description:

FactA - ColA, ColC

FactB - ColB, ColA

DimA - ColC, ColC_Desc

FactA joins FactB on FactA.ColA = FactB.ColA

FactA joins DimA  on FactA.ColC = DimA.ColC

MeasureFactB = count(distinct ColB)

I'm using the visual analyzer with the following combination:

ColC_Desc, MeasureFactB

The measure (MeasureFactB) presents the total count of ColB in the fact table and not group by ColBDesc.

For Example:

The presented results:

ColC_Desc    MeasureFactB

ABC                    10

DEF                    10

XYZ                    10

The expected results:

ColC_Desc    MeasureFactB

ABC                    5

DEF                    1

XYZ                    4

(In the OBIEE Analysis reporting all working fine).

Screenshot of the generated query is provided. - as u can see in the picture, the analyzer generates 2 separate queries, so I guess this is the reason the count is calculated at the highest level,

why isn't it generated as a single query?

Please help with this issue.

Thank You.

Version

12.2.1.1.0.

Philippe Lions-Oracle · Answer

Hello Ofer, from reading your post, a few parameters come to my mind where to influence the way this aggregation will render.

One thing you should start with : set a level (by clause) in your viz aggregation. To do this, click on your vizualization and go to properties, in 12.2.1.1 I believe this is a right click. you should see a way to influence your metric aggregation in the properties, and just next to it you should see a 'by' drop down. This drop down lets' you set what attribute you want that aggregation to be done by. In your case, you want that to be Col C Desc or ColC. That should already help.

If this does not work correctly, you probably will also have to tune the 'type of join' by looking at the blending option, still in the viz properties. This should let you influence full inner, or right/left outer. Since you are joining to facts, this may have an importance in your results.

But in general,

In the your model you are joining two facts tables, and then a dimension to only one of these facts. This modeling is risky and will force you into highly tuned reports/queries, and may still bring non desired results. I strongly suggest you either create a common Dimension to both your facts or simply denormalize Fact B and Dim. If you really can't do this, then your example is a good one for modeling using OAC Admintool (RPD metadata). In RPD semantic layer, the modeling capabilities can appropriately define metrics that will adress this use case, this require some experience with the tool though.

Finally, prefer to leverage the last version of DV for such cases if possible.

hope that helps

Philippe