Comparing corresponding KPIs of 2 tables

[shyam_uthaman]

Hi All,

I would like to have your suggestions on a requirement.

There are two tables -

1. Table1 which will be filled once with data for a certain set of KPIs- A,B,C,D in this example. It also has a key field.

2. The structure of table 2 would change every week. It would have at least one or all of the fields of table 1 in addition to the key field.

The requirement would be to have a final view/procedure output that compares the KPI values for the columns present in table 2 for each key entry and displays the deltas.

To make it clearer, let's take an example.

Table1 - Filled once with all data.

Key	A	B	C	D
101	1	5	4	8
102	2	6	3	7
103	3	7	2	6
104	4	8	1	5

Table2 - Structure and data changes each week. Let's say this week we have fields A and C in this table along with the key field.

              The list of fields here would be always a subset of the fields in Table 1. The data would be different at some rows which are marked as bold in this example.

Key	A	C
101	1	4
102	2	3
103	3	10
104	20	1

Output needed

The data should show only for columns in Table 2. For each column in Table2, I would need 3 fields, data from first table, data from second table and the delta between these two fields.

Key	A1	A2	A_DELTA	B1	B2	B_DELTA
101	1	1	0	4	4	0
102	2	2	0	3	3	0
103	3	3	0	2	10	-8
104	4	20	-16	1	1	0

If table 2 was a static structure, we could do this the good ol' way probably by a join and calculated columns for each field but due to the fact that structure of table 2 is dynamic, the number and structure of the output would also be dynamic.

Looking forward to your opinions on a solution to this.

Thanks,
Shyam

[michael_eaton3 · Accepted solution]

As a programming exercise this can be done by getting the metadata for both tables, determining the common columns, then dynamically constructing and executing a SQL statement.  However, this raises a few questions in my mind

• do the HANA metadata tables change structure?  Answer - no.
• does the structure of the HANA metadata tables allow for multiple combinations to be represented?  Answer - yes.

Relational databases, and tools that consume datasets from databases do not like dynamic structures, so any design where this happens is questionable.  Dropping and recreating (non-temporary) tables should be avoided - what if someone queries it during the recreation process?  You'll also have to re-apply any authorisations. 

Both of your tables could be designed with a static structure - key, kpi, kpi_value, then a simple inner join between the 2 with a calculated column would produce the data you require.  Pivoting it is best left to the consumption layer rather than HANA.

Is this a real requirement, and if so, how would the final dataset be consumed?

Michael

[shyam_uthaman · Accepted solution]

Hi Michael,

Thanks for the insightful reply.

The whole idea of the requirement is that there will be a central table with all data and fields of a particular application. Meanwhile, there are other tools in the landscape that also generate the same data but only for the subset of fields which each of them require which can be then extracted by excel.

These excels are loaded into a flat file table in HANA and the expectation is to find individual deviations in data for these tables when compared to the master table.

This would also then be used to derive the count of deviations by each KPI (where the difference between master table data and table2 is not zero).

The consumption later is not decided upon yet.

Regards,

Shyam

[SergioG_TX]

Shyam,

is this what you need? 

if so, then you need to join on your  Key column first, then perform your delta operation - hope this helps

[shyam_uthaman]

Hi Sergio,

Thanks for the reply.

No..this is not what I need.

This solution is Ok when the structure of table 2 is fixed but it's not the case.

Important points to note:

1. Table 2 structure can change every week and would have at least 1 or more fields from table1.

2. The number of columns in the output has to be dynamic. If Table2 has 3 columns, the output must have 9 columns(3 * 3) . If table2 next week has 2 columns, the output would then have 6 columns (3 *2)  which is also the example I made in the original question.

Thanks,

Shyam

[SergioG_TX]

if having the table 2 as dynamic - does that mean that table 1 is also dynamic?  if not, what would be the mapping of table 2 to table 1 .. would it always be first column of t2 - first column of t1, ... column on tableN - column on tableN ???

will it ever be a scenario where table 2 has more columns than table 1?  if so - how will this be handled?

[shyam_uthaman]

1. Table1 is fixed.. structure and data both. It is the master table.

2. Table 2 will be dropped and re-created and reloaded. It will always have equal or less fields than Table1.

3. The order of fields is not same. But the column names would always be same. So Table 1 could have fields A|B|C|D and Table2 may have D|A|C .

Hope this clarifies the requirement.

[shyam_uthaman]

To summarize, Table1 is kind of a master check table against which any data in Table 2 will be checked each time and deltas will be noted.