Grouping problem n-n

[ErikLund_Jensen]

Dear community

In our data warehouses we have a lot of tables that we want to rename because they don't follow our naming conventions. As a consequence, any job that writes or reads these jobs should be redeployed, because they would fail in the next batch otherwise.

I have a program that locates the relevant tables, generates new metadata table names and/or physical data set names and locates all affected jobs as well, and I have another program that processes the list and performs the renaming of metadata names and physical names. So this part is not a problem.

The problem is that there is 1-n jobs for each table, each job uses 1-n tables. The list has about 600 table/job combinations with 285 different jobs, so I want to split the task over several days. So the list should be split in groups where any table or job is in one group only, so we can rename all tables in a group and then redeploy the affected jobs without having to redeploy the same job more than once.

It seems to be a simple grouping problem, but I haven't figured out how to do it. Here is a very simple test data set. TableID and Job is what I have, and Group is what I want. Any help would be greatly appreciated.

data have;
	input TableID Job$ Grp;
	cards;
1 E 1
2 A 1
3 B 2
4 C 3
5 D 3
6 C 3
6 D 3
7 A 1
7 E 1
8 A 1
8 E 1
;
run;

[Tom]

Are you just asking how to break down an acyclic graph into connected sub graphs?

https://communities.sas.com/t5/SAS-Communities-Library/How-to-find-all-connected-components-in-a-gra...

What do you get it if you try that method?

Problem is you might need to modify the same job more than once to keep the number of changes done per day down to a reasonable number.  Say job B uses table 2 and 6 and your schedule of when to modify the table names looks like your GRP variable.  So you have to fix job B on day 1 and then fix it a second time on day 3.  This result is compounded if there are some key tables that are used by most of the jobs.  It might help to identify those first and remove them from the task of partitioning the rest of the table*job pairs.

[ErikLund_Jensen]

@Tom 

Thanks for your ideas. 

I couldn't get the break down to work. It treated both tables and jobs as nodes and mixed them in output, and didn't find time to do a line by line analysis of the code to fix the problem. 

It still annoys me that I can't find a simple algoritmic solution, but I choose another approach to solve the problem. Instead of just writing a list of all jobs to deploy manually, the progran writes a script with calls to the batch deployment tool, so it is not necessary to split it over several days.

[ErikLund_Jensen · Accepted solution]

The batch deployment tool worked. The metadata extract became a little more tricky, because I needed the full metadata path for the job.

I have previously experienced some problems with redeployment of a long list of jobs in a single call, so I made a call per job instead. It is not fast, took about 15 minutes for 300 jobs, but with 100% succes.