SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

SAS/DI report on all jobs and tables

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

SAS/DI report on all jobs and tables

Data Integration has a report on all DI objects that produces several HTML files, but it's not easy to consume the information.  We have version 4.2 and are upgrading to 5.3, but I have heard that the DI report job is not improved from 4.2.  The DI interface is written in Java, so I don't think this job and table information is available in the SAS server metadata.  I looked and did not find DI job and table information there.

So I'm considering processing the DI report HTML files for all jobs and tables into a form that can be used for analysis.  The results will be five or six SAS datasets that can be joined to find anything you want to know about DI jobs and structures.  This would show all jobs that read from or write to any table, as well as names in DI with physical names on the server, dates, etc.

Has anyone ever created a report or datasets like this for SAS/DI?  Or is the DI report improved in later versions?


Accepted Solutions
Solution
‎08-22-2014 09:21 PM
Respected Advisor
Posts: 3,894

Re: SAS/DI report on all jobs and tables

In regards of DIS out-of-the-box reporting:

I just created such a report using DIS 4.9 and then looked-up on Internet how this used to look like in DIS 4.2. Apparently not that much changed here. On the other hand: What else do you want from such a report? It gives you all the jobs with all the tables used in these jobs, and it gives you then the details for all the tables. The biggest issue I'm having with these reports is that you can't sub-set but it reports on the whole SAS Metadata repostitory which is a problem if you're having more than one project/application.

"He also said that the Java interface had all the information about the metadata and dependencies. "

Just imagine what the consequences were if the SAS metadata would be stored on client side. You would write a DI job but as metadata would be stored on your client no one else would have access to this job. DI Studio is the client (the "Java GUI"). Data is accessed via the Server as it's true for all Client/Server environments.

DI Studio let's you communicate with the SAS Metadata Server. 100% of the metadata is stored in metadata repositories under the SAS Metadata Server you're connecting to. That's where the DIS reporting features pull their data from. But you can also connect to a SAS Metadata repository via SAS code (of via Java if you want to) query the data and generate whatever report you need.

What the DIS client does: It generates the SAS code based on SAS Metadata it retrieves from the server (so the DIS version matters if you execute or deploy/schedule a job).

View solution in original post


All Replies
Respected Advisor
Posts: 3,894

Re: SAS/DI report on all jobs and tables

The latest SAS DI version is 4.9 (not 5.3).

Yes, the reports have become better and there is Job and Table info in the report.

If you're open to put in the effort then you can also query SAS Metadata. It's not that easy to understand the SAS metadata model - but it's fully documented and possible to extract whatever information you're after.

Depending on what sort of reporting you're after there is also a third party plug-in available which I can recommend http://www.metacoda.com/en/metacoda-security-plug-ins/

Contributor
Posts: 22

Re: SAS/DI report on all jobs and tables

Sorry about the 5.3 -- that's our new CSB version.  We will be getting DI 4.7 soon.  Do you know if the DI reports are greatly improved from version 4.21?

Can you confirm that the DI jobs and structures are in the metadata?  I expect that some table metadata would be there, but would the metadata server have the same information as presented in the DI tables report?

A co-worker said the new DI reports were like the old ones.  He also said that the Java interface had all the information about the metadata and dependencies.  If so, where does the Java GUI gets its information?  I will open up a ticket to see whether the DI jobs and tables metadata are in the SAS Metadata server or in the Java GUI.

Anyway, the jobs report will be done today and the tables report should be done by Monday.  We still need my DI information for next month's DI and CSB upgrades, so we can't wait for better reports in DI version 4.7.  The SAS programming effort? -- I love the challenge of a difficult project.  The input file is a single column of data that is processed by pattern matching and exception processing. 

Solution
‎08-22-2014 09:21 PM
Respected Advisor
Posts: 3,894

Re: SAS/DI report on all jobs and tables

In regards of DIS out-of-the-box reporting:

I just created such a report using DIS 4.9 and then looked-up on Internet how this used to look like in DIS 4.2. Apparently not that much changed here. On the other hand: What else do you want from such a report? It gives you all the jobs with all the tables used in these jobs, and it gives you then the details for all the tables. The biggest issue I'm having with these reports is that you can't sub-set but it reports on the whole SAS Metadata repostitory which is a problem if you're having more than one project/application.

"He also said that the Java interface had all the information about the metadata and dependencies. "

Just imagine what the consequences were if the SAS metadata would be stored on client side. You would write a DI job but as metadata would be stored on your client no one else would have access to this job. DI Studio is the client (the "Java GUI"). Data is accessed via the Server as it's true for all Client/Server environments.

DI Studio let's you communicate with the SAS Metadata Server. 100% of the metadata is stored in metadata repositories under the SAS Metadata Server you're connecting to. That's where the DIS reporting features pull their data from. But you can also connect to a SAS Metadata repository via SAS code (of via Java if you want to) query the data and generate whatever report you need.

What the DIS client does: It generates the SAS code based on SAS Metadata it retrieves from the server (so the DIS version matters if you execute or deploy/schedule a job).

Contributor
Posts: 22

Re: SAS/DI report on all jobs and tables

I believe you are correct that the Metadata Server does have all of the information about DIS.  But I don't know whether I can get access to look at the Metadata like an admin.  However, I will ask SAS to confirm our hunches about where this information is stored, and make my plans to beg the admin for mercy.

Until that happens, I have my first report on Jobs, and the second one on Tables will be easy.  How would I use it?  I just sent out an analysis today using that data.  I answered a question about an obsolete program, including the data sources and downstream programs.  I subsetted my query on any job with a chosen string or any table sources or targets with the same chosen string.  The report included the job deployment time, so I could confirm where the table came from and that none of the jobs were recent.

I have another request where I will use the datasets.  I am planning a conversion from Oracle to Teradata ETL for our 70+ staging level datasets.  I have the list of SAS datasets, and the Oracle table sources, which I grep'd from the deployed programs folder and trimmed down to what we use for today.  Now I want to add the DI job, DI job location, DI program source file location, libname physical locations, and table physical names.  Then I will reconcile the tables in my DI report, to see if I missed any recent tables, since job deployment date and table modify times will be in the report.  This Excel report will help validate, plan, and track our conversion project.

Every field in those DIS reports will be in my analysis datasets.  Then you can do queries and joins to answer questions about any jobs and tables.  Imagine what you could do with SAS datasets like that.  It's not HTML linked, but who needs that anyway?  I will show you the structures and joins later, and I would be interested in your opinions.

Respected Advisor
Posts: 3,894

Re: SAS/DI report on all jobs and tables

"I believe you are correct that the Metadata Server does have all of the information about DIS."

The SAS Metadata Server has ALL the information about SAS Metadata - AND SAS DI Jobs are Metadata driven.

"But I don't know whether I can get access to look at the Metadata like an admin."

If you can see everything you need from within DIS when connecting to a Metadata Server then you can use exactly the same Metadata user to query metadata (as: if you can see it using DIS then this metadata user has all the permissions you need).

Contributor
Posts: 22

Re: SAS/DI report on all jobs and tables

I confirm that SAS Metadata has all of the information about DIS jobs and structures, because I found several papers about DIS, Proc Metadata, and making queries of the Metadata server to get info on SAS/DI.  However, I finished my program that processes the text files from the DIS report, so I am not rushed to learn the Proc Metadata structures with their 3-phased interpretation into English.

I will post my dataset structures later.

I will review the articles on DIS metadata that I got from Lex Jansen's repository.  There were not very many relevant papers, and I looked at all 10 pages from the search engine.  I will post my reviewed list later.

These postings have really helped with getting deeper into DIS.  I like the responsiveness and expertise of this forum.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 1638 views
  • 2 likes
  • 2 in conversation