[PA_score_l1], [PA_score_l2] Join

[langloislu]

We are ingesting the PA_score tables in our data warehouse but my developer is not sure how to join both table to get the breakdown score for each indicators.

what column can be used for that ? 

Luc

[JP52]

It sounds like you might be looking for Database Views. Find the module in the navigation or enter sys_db_view.list in the "Filter navigator" field

[Adam Stout]

Generally, you would want to use the Performance Analytics API to extract data.  The raw tables do not include formula indicators and would need to be joined to several different tables to interpret the breakdown and the element correctly as these are references to other tables.

[langloislu]

unfortunately, the rest API will take some time before we can use it as our cyber security team have some concern with cloud to cloud, we have the PA_score tables in an interim server that synch with our data warehouse and we have a requirement to add some KPI data into power BI dashboard.

what I am looking for specifically is the field we can use to join both table and get the breakdown score related to each KPI in table1.

is there any reference or ID field we can use between both table to join them ?

also is there any documentation available for those tables ?

Luc

[Adam Stout · Accepted solution]

Generally, I see exports use the sys_updated_on to perform incremental updates.  Since there is no time stamp on the l1 and l2 tables, how will you export the data out?  You can use starts_on, but be careful since a historical collection won't be obvious (or possibly detectable from the pa_scores_l1 table alone).  You will most likely need to bring over all the supporting pa tables as well.  Essentially, you'll be recreating the Performance Analytics API and the widgets on your own to get what Performance Analytics produces in the platform.  Be ready for lots of questions from your users as you will have to put in a lot of work to ensure the data reflects what you think it should be. 

This is not a recommended approach.