BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

Sat Jun 16, 2018 12:06 pm: The BOB server was upgraded (O/S and database) today. If you encounter any errors, please post on the board. Posting has been tested and seems to be working. Thanks.
General Notice: Upcoming Events: BI & HANA 2018 Prague: Jun 29.

To find the total information of jobs using metadata tables


 
Search this topic... | Search DI: Metadata Reports... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Metadata Reports  Previous TopicPrint TopicNext Topic
Author Message
ursfriend77
Principal Member
Principal Member



Joined: 02 Mar 2011

Posts: 157



PostPosted: Sun May 10, 2015 4:03 am 
Post subject: To find the total information of jobs using metadata tables

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
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4277
Location: Denver Area


flag
PostPosted: Mon May 11, 2015 3:30 pm 
Post subject: Re: To find the total information of jobs using metadata tab

You'll need to look at the AL_PARENT_CHILD table. It isn't a lot of fun to query against.
_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
ursfriend77
Principal Member
Principal Member



Joined: 02 Mar 2011

Posts: 157



PostPosted: Tue May 12, 2015 12:26 am 
Post subject: Re: To find the total information of jobs using metadata tab

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
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4277
Location: Denver Area


flag
PostPosted: Tue May 12, 2015 8:22 am 
Post subject: Re: To find the total information of jobs using metadata tab

This is a fairly involved query - enough that it would require time that would take away from my billable hours.
_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
TurningPointHolland
Principal Member
Principal Member



Joined: 06 Sep 2006

Posts: 338
Location: Gorinchem, Holland


flag
PostPosted: Mon Nov 28, 2016 3:59 am 
Post subject: Re: To find the total information of jobs using metadata tab

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...

_________________
Ton Vermeij
Senior Business Objects, Lumira, Birst, Tableau and Alteryx Consultant
Gorinchem, Holland, Europe
Owner of www.theturningpoint.nl and co-founder of
www.bi-united.nl (dutch BI-freelancers network)
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4277
Location: Denver Area


flag
PostPosted: Mon Nov 28, 2016 9:21 am 
Post subject: Re: To find the total information of jobs using metadata tab

I rarely use the views. Instead, I go straight to the base tables such as AL_PARENT_CHILD.
_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
TurningPointHolland
Principal Member
Principal Member



Joined: 06 Sep 2006

Posts: 338
Location: Gorinchem, Holland


flag
PostPosted: Mon Nov 28, 2016 9:28 am 
Post subject: Re: To find the total information of jobs using metadata tab

no problem for me to use the tables in stead of the views... the question stays the same... icon_wink.gif
_________________
Ton Vermeij
Senior Business Objects, Lumira, Birst, Tableau and Alteryx Consultant
Gorinchem, Holland, Europe
Owner of www.theturningpoint.nl and co-founder of
www.bi-united.nl (dutch BI-freelancers network)
Back to top
eganjp
Forum Addict
Forum Addict



Joined: 12 Sep 2007
ASUG Icon
Posts: 4277
Location: Denver Area


flag
PostPosted: Mon Nov 28, 2016 9:49 am 
Post subject: Re: To find the total information of jobs using metadata tab

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.

_________________
Jim Egan
ProKarma-an SAP Consulting Partner
Speaker at RMOUG Training Days 2013
Speaker at Sapphire Now 2013
Speaker at Business Objects User Conference 2013
-My ETL is faster than your database! - Advanced ETL performance tuning for Data Services
-Kill ETL bugs before they kill you!
My ETL blog

Latest Blog Posts
• 2014-04-17 Can Your ETL Make Your Reporting Faster?
• 2014-04-04 ProKarma Acquires Mantis Technology Group
• 2014-04-02 How to Create Charts in Webi (Part 2 of 2)
Back to top
TurningPointHolland
Principal Member
Principal Member



Joined: 06 Sep 2006

Posts: 338
Location: Gorinchem, Holland


flag
PostPosted: Mon Nov 28, 2016 10:08 am 
Post subject: Re: To find the total information of jobs using metadata tab

icon_wink.gif Nice! Whenever I have some code I will post it here! Thanks!
_________________
Ton Vermeij
Senior Business Objects, Lumira, Birst, Tableau and Alteryx Consultant
Gorinchem, Holland, Europe
Owner of www.theturningpoint.nl and co-founder of
www.bi-united.nl (dutch BI-freelancers network)
Back to top
tvanbreukelen
Senior Member
Senior Member



Joined: 16 Oct 2008

Posts: 75
Location: Arizona


flag
PostPosted: Tue Mar 13, 2018 8:10 pm 
Post subject: Re: To find the total information of jobs using metadata tab

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:
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Metadata Reports  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0138 seconds using 17 queries. (SQL 0.0033 Parse 0.0003 Other 0.0101)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo