Trying to find a transformation tasks where a particular table is used, right now going through each and every task its very time consuming effort.
Solved
Is there a way to find a table where it is used in data jobs?
+1Best answer by dennis.pflug
Hi,
something like a simple search bar would be great. I am also missing this kind of feature.
You can try the following:
- Open Data Integration
- Within the data pool section click on the three dots of your data pool and select export
- You will download a .JSON file of your data pool containing all information about the data pool setup with all data jobs, extractions, transformations.
- There you can search for a specific table and can see where it is being used.
Hi,
something like a simple search bar would be great. I am also missing this kind of feature.
You can try the following:
- Open Data Integration
- Within the data pool section click on the three dots of your data pool and select export
- You will download a .JSON file of your data pool containing all information about the data pool setup with all data jobs, extractions, transformations.
- There you can search for a specific table and can see where it is being used.
+1Hi,
something like a simple search bar would be great. I am also missing this kind of feature.
You can try the following:
- Open Data Integration
- Within the data pool section click on the three dots of your data pool and select export
- You will download a .JSON file of your data pool containing all information about the data pool setup with all data jobs, extractions, transformations.
- There you can search for a specific table and can see where it is being used.
great suggestion, will try that thank you Dennis
I'm working on such solution in a form of a python script.
I probably won't be able to provide ready solution, but probably I will able to give some insights, advice on how to overcome general challenges :)
I'm working on such solution in a form of a python script.
I probably won't be able to provide ready solution, but probably I will able to give some insights, advice on how to overcome general challenges :)
Would be a great to provide a script where you just need to insert a table name and the script is running through the whole data pools, data jobs etc. (so the whole setup you are getting when using the export feature I mentioned above) and checks for the table input and give details about where this table is used as output.
Would be a great to provide a script where you just need to insert a table name and the script is running through the whole data pools, data jobs etc. (so the whole setup you are getting when using the export feature I mentioned above) and checks for the table input and give details about where this table is used as output.
I'm planning to do such things for all Celonis components starting from PQL code in Components, KM KPI's, loading scripts up to backend transformations.
I think the hardest part is to prepare good enough REGEX statements. I've got something for PQL working fine, but making that work fine for SQL, and f.e. ignoring table names in comments, or ignoring hardcoded numbers like 11.22 is really hard.
Unfortunately if you will follow solution givie by Dennis, I can see that transformations still contain raw SQL statement. If you really want know which extracted tables and columns are in use, good REGEX is still needed to get those.
I'm still not sure which approach would be better:
A) Get tables / columns based on their location (positive/negative look-ahead + postivie/negative look-behind)
B) take values between TABLE.COLUMN with 4 cases:
case 1: TABLE.COLUMN
case 2: "TABLE".COLUMN
case 3: "TABLE"."COLUMN"
case 4: TABLE."COLUMN"
For both of those approaches first regex for getting ALIASES in SQL code probably will be needed.
Best Regards,
Mateusz Dudek
Would be a great to provide a script where you just need to insert a table name and the script is running through the whole data pools, data jobs etc. (so the whole setup you are getting when using the export feature I mentioned above) and checks for the table input and give details about where this table is used as output.
Hello Mateusz, I am starting to look into this type of solution. Have you had any progress that you would like to share/exchange ideas?
Thank you in advance!
Best,
Salma
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.