BusinessObjects Board

To find the total information of jobs using metadata tables

dataservices metadata reports
#1

Hi

In my project I am having around 45 jobs and inside jobs lot of workflows and dataflows and used several datastores, but My client want to see all the job information inculding workflow,dataflow,source_ds,source table,source column,target_ds,target table,target column in one table using metadata tables.

please suggest me anyone how to find out. I am also working on this

Here i attached sample table what my client expecting from me,In this I want to add job_name and WF_name.

Thanks
Venki
metadata table information.jpg

ursfriend77 (BOB member since 2011-03-02)

#2

You’ll need to look at the AL_PARENT_CHILD table. It isn’t a lot of fun to query against.

eganjp :us: (BOB member since 2007-09-12)

#3

Hi Jim,

Thanks for your reply

I am very poor in writing query,so can you please provide the query to get the information about job and dataflow using AL_Parent_Child table

I want Job and Dataflows in seperate columns

please i am struggling here

Thanks&Regards
Venki

ursfriend77 (BOB member since 2011-03-02)

#4

This is a fairly involved query - enough that it would require time that would take away from my billable hours.

eganjp :us: (BOB member since 2007-09-12)

#5

Hi everybody,

I am trying to write the SQL query that gives me the functionality as we have this in BODS itself the so called “View where used” functionality.

Example: we start from the source application ; we have 1 job per source application to fill the Staging layer with the well known job hierarchy (job/workflows/Dataflows) where the Dataflow does the technical filling part of the Staging table.

After this we have the ODS layer. After that we have the Datavault layer and after that we end up with the Datamart layer.

What I want is (the SQL behind) a collection of all Dataflows that show me the “View where used"functionality” from Source to Datamart.

So a report that shows this 4 columns:

Dataflow_name Source Table Target_table Layer

What I already found out is BODS metatable ALVW_Parent_child does not provide unique keys that can be used, only names…

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

#6

I rarely use the views. Instead, I go straight to the base tables such as AL_PARENT_CHILD.

eganjp :us: (BOB member since 2007-09-12)

#7

no problem for me to use the tables in stead of the views… the question stays the same… :wink:

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

#8

I’m not completely sure what you’re trying to achieve but I’ll tell you what I did. The existing built-in functionality wasn’t good enough for my research. I needed to be able to roll up to the job level which tables were being used. I wanted a list of jobs that a table (or any other object) was used in.

I ended up writing a stored procedure to flatten the data found in AL_PARENT_CHILD. It is a recursive search of that table. One of these days I’ll write a blog post that includes the source code.

eganjp :us: (BOB member since 2007-09-12)

#9

:wink: Nice! Whenever I have some code I will post it here! Thanks!

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

#10

Did you ever get this working? I have to do something similar and just started looking in this forum to get some ideas on how and where and what etc.

Bedankt voor de moeite … :lol:

tvanbreukelen :netherlands: (BOB member since 2008-10-16)