cancel
Showing results for 
Search instead for 
Did you mean: 

Merging 2 Analytic Views on Calculation View

Former Member
0 Kudos

I'm a little stuck on something which looks simple but can't model it correctly.

I have 2 analytic views that I need to merge where all the records from both views show, like a Union.

For reference, I'm getting 7 records in table AFRU and 5 in AFVV, I want to merge these records getting relevant corresponding data.

Here is an example.

My 2 Analytic Views

Analytic View 1

Order       CompanyCode    Activity    Location     Quantity     Price

10                 100                    10               CHI               4              10

10                 100                    10               CHI               5              13

10                 100                    20               NYC             4               22

10                 100                    30               SFO             1                5

10                 100                    30               SFO             5              33

Analytic View 2

Order       CompanyCode    Activity        CostCenter        Quantity     Price

10                 100                    40               FSDF                      4               10

10                 100                    50               FSFD5                   13               44

10                 100                    60               GGG4                     22               66

10                 100                    70               FFF                         1                5

10                 100                    70               FFF                          5              33

I want to merge and want all the records from both views, the non-measure fileds Location and Cost Center should  get filled also because logic is synchronized.

Output

Order       CompanyCode    Activity    Location           CostCenter       Quantity     Price

10                 100                    10               CHI                  DFS                     4              10

10                 100                    10               CHI                  FDES                   5              13

10                 100                    20               NYC                SSA                     4               22

10                 100                    30               SFO                 RRR                    1                5

10                 100                    30               SFO                 RRR                    5               33

10                 100                    40               AFR                 FSDF                  4               10

10                 100                    50               CAL                  FSFD5                5              13

10                 100                    60               NOWt               GGG4                 4               22

10                 100                    70               FTD                   FFF                   1                5

10                 100                    70               FTD                   FFF                   5               33

How can I merge the 2 Analytic Views? What join type? What cardinality? What Node? What table should be on left and what on right?

Note: I can't use a Union node right? (because not all non-measure fields match (Location doesn't exist in analytic 2 & Cost Center doesn't exist in analytic 1) or maybe I'm wrong in thinking that.

Accepted Solutions (1)

Accepted Solutions (1)

henrique_pinto
Active Contributor
0 Kudos

Hi Mic (I suggest you use your real name in SCN):

Your final desired output has some information that isn't included in these two attributes. For example, how do you know that the cost centers for the activities in Chile are DFS and FDES?? Where does this information come from?? Similarly, from where comes the information that the Location of the entries for Cost center FFF is FTD?

The way I see it, you need to join the sources of this information within each AN, i.e. add cost center to AN1 and add location to AN2, so that you can properly Union them.

Another thing, notice that from an AN perspective, you will never get the output for AN1 and AN2 as stated above. ANs will always aggregate, and in your case, without further attribute definition, the first two entries of AN1 will be aggregated (i.e. you will get one entry of quantity 9 and price 23 (you might want to use price with aggregation type average instead)*. For AN2 it's even worse, since even if you add Location, the attributes are still all the same, it will get aggregated nevertheless. If you don't want them to be aggregated, you might want to look into adding further attributes that separate them logically.

henrique_pinto
Active Contributor
0 Kudos

* as for the price measure, ideal case would be to have the total price and quantity, aggregate them by SUM, and calculate the average price as a calculated measure in the calc view you use for the union.

Answers (5)

Answers (5)

Former Member
0 Kudos

Thanks Henrique.

Well, this is my dilemma.

I have 2 fact tables AFRU and AFVV. I need non-measure fields from both tables that don't match. Hence the only route was to do a join at calculation level using AUFPL and APLZL fields. I understand that Join is not encouraged because of performance reasons, but I don't know how I can do a Union when certain fields don't exist in each other's table (ex. FSAVD, ISM01, etc). What's the best approach to bring these 2 tables together at calculation level??

henrique_pinto
Active Contributor
0 Kudos

Hi Mic,

I think you still don't have a clear understanding of what you want.

If you look at your initial request, you were indeed looking for a union. There was some information missing that you had "filled" in your desired output (e.g. "Cost center for activity 10 is DFS" or "Location for activity 40 is AFR"). I then asked "where do you get this information from?".

Now, you're saying you don't have that information anywhere, is that so? If yes, how do you want to achieve that desired output, if there is unknown data there?? If you don't have the cost center & location per activity anywhere, your desired output is not achievable, with your exiting model.

Former Member
0 Kudos

Henrique - Got it, thanks. I got little confused. Basically all the data must be present before Union. Thanks, sorry got little confused.

Ravindra - Your last paragraph made me think. Are you saying to add a 'dummy' field to the analytic views to have a union?

In this example:

For Analytic View 1 I'd create a calculated attribute "Cost Center"

For Analytic View 2 I'd create a calculated attribute "Location"

And Union the dummy "Cost Center" to the real Cost Center.

And Union the dummy "Location" to the real Location.

And this will eliminate the ? and apply the data (costcenter and location) accordingly??

henrique_pinto
Active Contributor
0 Kudos

Mic xyz wrote:

Henrique - Got it, thanks. I got little confused. Basically all the data must be present before Union. Thanks, sorry got little confused.

Exactly.

Notice that the data you wanted to replace the null values with (represented by question marks in HANA) was not included in your two original Analytic views. You probably would need to join the fact tables with additional attribute views to add the Cost Center attribute to AN1 and the Location attribute to AN2.

former_member184768
Active Contributor
0 Kudos

Hi Mic,

Looking at the data, it is quite evident that there is some logic beyond the simple UNION which derives the values like FTD, NOWT etc. These values are currently not mentioned in your data set hence I don't think can be derived by simple UNION.

Secondly JOIN operation in Calc view is known to be more expensive. So even if you have these values in either of the tables and want to perform the JOIN to get Location and Cost center attributes in both the data sets (vertical or columns data set combination) and then perform the UNION (horizontal or rows data set combination), it may not be good from performance perspective.

Advisable option would be to add these two columns in each of the underlying tables and populate them during the data load itself (based on business logic) and perform a simple UNION in the Calc View.

Regards,

Ravi

Former Member
0 Kudos

This message was moderated.

Former Member
0 Kudos

Unfortunately, this is the output I'm getting with a UNION:

Order       CompanyCode    Activity    Location           CostCenter       Quantity     Price

10                 100                    10               CHI                           ?                       4              10

10                 100                    10               CHI                           ?                       5              13

10                 100                    20               NYC                          ?                      4               22

10                 100                    30               SFO                          ?                      1                5

10                 100                    30               SFO                          ?                      5               33

10                 100                    40                  ?                          FSDF                 4               10

10                 100                    50                  ?                         FSFD5                5              13

10                 100                    60                  ?                          GGG4                 4               22

10                 100                    70                  ?                            FFF                   1                5

10                 100                    70                  ?                            FFF                   5               33

Varada - What exactly will constant column do? Will it be able to get data instead of ?

Henrique - How exactly should I join them to make sure I don't get question marks? when you say 'join', I'm assuming you mean Join Node, can you tell me what join type and cardinality?

henrique_pinto
Active Contributor
0 Kudos

Did you read my previous message? You're getting Null exactly on the information you don't have in the original ANs, i.e. cost center for AN1 and location for AN2. You need to fill the missing information prior to the union.

Former Member
0 Kudos

Hi Mic

   As your Attribute structures are different still I believe you can use Union using Constant column,you can achieve this using graphical calculation view.

Thanks

Santosh Varada