topic Re: Using the Releationship Postgres database to report lineage in Administration and Deployment

Using the Releationship Postgres database to report lineage

DavidGannon — Fri, 14 Jul 2017 16:19:46 GMT

We are looking for advice on using the relationship data that is stored in the postgres database.

This information is populated via the Relationship Loader (sas-relationship-loader). We have used the Relationship Reporter (sas-relationship-reporter) successfully.

We have some specific reports we would like to develop that accessing the Postgres database would facilitate.

Has anyone used this metadata database? How did you connect to it? And are there example queries available?

Re: Using the Releationship Postgres database to report lineage

JuanS_OCS — Sun, 16 Jul 2017 11:02:58 GMT

Hello @DavidGannon,

what you are asking for - to access the WIP database to query the relationships, I believe it is not documented. For this purpose, I would rather ask the question to SAS Technical Support 9 (and/or wait here, perhaps a SAS employee can asnwer you the question). But please beware the SAS WIP database it is not intended to be queried for non-internal-SAS purposes, so also perhaps you won't get your answer. If you get this question answered, please let us know it, we feel curious!

I think it would be more interesting for you to use sas-relationship-reporter by exporting the data to a CSV file and create your own little datamart. (with the parameter -format CSV )

Something else it might help you: if you have SAS Visual Analytics, there is a process you can enable to get the reports out of the box: http://blogs.sas.com/content/sgf/2015/03/04/relationships-are-easy-for-sas-objects/ (and a SAS dataset that will be loaded into VA, and you can copy to a different location for your own purposes).

Re: Using the Releationship Postgres database to report lineage

kellington — Mon, 17 Jul 2017 13:51:40 GMT

The challenge with the sas-relationship-reporter is that it does not have all the same exports available - without having to run "lots" of different exports/queries and then parsing and stitching the info back together. The goal is to be able to have queries that simplify impact lineage reporting (initial sources and final targets - without all the intermediary steps), as well as do quality/standards reporting on tables and jobs that are being built in SAS DI.

The PostgreSQL tables *should* make this easier.

Re: Using the Releationship Postgres database to report lineage

JuanS_OCS — Mon, 17 Jul 2017 13:55:28 GMT

Indeed.

FYI, if it takes too long to get the answer you need, you can always create the relationship tables by yourself with the BI Lineage plugin from the SAS Management Console.

http://support.sas.com/documentation/cdl/en/bisag/68240/HTML/default/viewer.htm#p14w0edwch53wdn1fior9hlxo39x.htm

Re: Using the Releationship Postgres database to report lineage

ToddVlk — Tue, 19 Mar 2019 21:26:49 GMT

Has anyone been able to find / query against Postgre data base for relationships?

Thanks,

Todd

Re: Using the Releationship Postgres database to report lineage

ToddVlk — Thu, 21 Mar 2019 19:36:41 GMT

Were you able to query relationships directly?

Thanks,

Todd