How to define counters on "header" level?

[Former Member]

I have a question regarding the implementation of a counter in a graphical star-join calculation view (HANA).

The calculation view retrieves campaign data and lead/opportunity object data that a created as follow-ups for a given campaign. Each campaign also provides the number of target group members but the target group members itself (could be considered as child node of the campaign) are not retrieved in the calculation view. All data are available in the fact table of the view.

The problem is, that the number of target group members will also be aggregated like other key figures.

Lets say a campaign retrieves a number of 179 target members and 7 follow-up objects created based on the target group members (not each target member may lead to a follow-up object). The fact table contains 7 lines for the campaign right now (because 7 follow-up objects are created and members without follow-ups are not visible in the fact table) and each line also contains the value for 179 target group members (see lines below), because it is considered as a property of the campaign. 

Fact table content:

An aggregation on campaign level (enforced by "SELECT "CMPGN_ID" , sum ...) returns a counter value “7” for the distinct follow-up objects but the aggregation for target group member counter returns 1253 (resulting on 7 * 179) .

Aggregated content:

How could I model the counter that is valid for all aggregation levels (e.g. aggregation for organizational units, ...)?

I've also tried to model the counter as field of the campaign dimension view.

The column TGP_MBR_CNTR represents the number of target group members and the column TASK_CNTR the number of the follow up objects (here tasks).

View Model:

The problem as described above could be solved by an redesign probably (select all target group members and match each target group member with an follow-up object), but I do not know the impact regarding the performance. So I would like to leave the design as it is.

Regards, Ingo

[lbreddemann · Accepted solution]

If I get your requirement correctly, you want to do something like a "constant selection" in SAP BW Bex queries, correct?

If so, then this can be achieved by either:

• change the model (the way you indicated seems reasonable)

or

• query the same view a second time in the SELECT statement you use to query the view. This will likely not be the best performance then and rather cumbersome modelling wise.

At least these are the options I see...

- Lars