on 10-07-2012 3:34 PM
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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??
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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??
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.