Skip to content

SelectDistinct.io

A tech blog about data, warehousing and analytics.

Home » SAP » SAP | BODS Workflow to Dataflow to Target Metadata Repository

SAP | BODS Workflow to Dataflow to Target Metadata Repository

December 31, 2016 / Matt / Leave a comment

For my Business Objects (BOBJ) SAP Data Services (BODS) folks out there, this post will indeed be helpful at some point during your exposure to the ETL tool. You’ll eventually find yourself digging through the Data Services repository trying to locate details about your jobs, reports, or anything that makes your dynamic processing easier. There are loads of forum posts on the subject and even a few nice posts that attempt to make sense of what the schema has to offer. But, there’s one thing that I couldn’t find and am sharing with you today: How to find BODS Workflow to Dataflow to Target Metadata for your target tables within BODS.

BODS Workflow to Dataflow to Target Metadata Repository
BODS Workflow to Dataflow to Target Metadata Repository

In my case, I needed to retrieve the number of rows inserted/discarded/deleted/updated for all target tables within all dataflows within a particular workflow. Since my job routine is divided into workflows, I didn’t need to take the granularity any farther but is certainly possible. Still, in my case, I had the need to retrieve metadata about what my workflow just did with having many dataflows within it all running in parallel. This parallelism introduced the need to dynamically pull these statistics from the repository rather than after each dataflow execution.

Let’s take a look at the query:

Retrieve the BODS Workflow to Dataflow to Table metadata repository information
PgSQL
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
 
SELECT
*
FROM(
       SELECT
             PC_WF.PARENT_OBJ AS WORK_FLOW_NAME
             ,PC_DF.PARENT_OBJ AS DATA_FLOW_NAME
             ,TA.TABLE_NAME
             ,TA.DATASTORE
             ,TA.TABLE_ATTR
             ,TA.TABLE_ATTR_VALUE
       FROM ALVW_TABLEATTR AS TA
       INNER JOIN ALVW_PARENT_CHILD AS PC_DF
             ON  TA.TABLE_NAME = PC_DF.DESCEN_OBJ
             AND PC_DF.PARENT_OBJ_TYPE = 'DataFlow'
             AND PC_DF.DESCEN_OBJ_TYPE = 'Table'
             AND PC_DF.DESCEN_OBJ_USAGE = 'Target'
       INNER JOIN ALVW_PARENT_CHILD AS PC_WF
             ON  PC_DF.PARENT_OBJ = PC_WF.DESCEN_OBJ
             AND PC_WF.PARENT_OBJ_TYPE = 'WorkFlow'
             AND PC_WF.DESCEN_OBJ_TYPE = 'DataFlow'
)t
PIVOT
(MAX(TABLE_ATTR_VALUE)
FOR TABLE_ATTR IN ([Date_last_loaded],[Total_Number_Of_Rows_Processed],[Number_Of_Rows_Rejected],[Number_Of_Inserts],[Number_Of_Updates],[Number_Of_Deletes],[Elapsed_Time_For_Load])
)P
 

Notice the two views in use here: the first view, ALVW_TABLEATTR has all the attributes of your table objects within the datastores that I need to capture and the other view, ALVW_PARENT_CHILD houses all the relationships between the objects within the jobs.

The way the repository stores its metadata is in a highly unnormalized, transactional, and without any referential integrity! So, it’s not an easy schema to navigate. So hopefully you’re here because you wanted to know the BODS Workflow to Dataflow to Target Metadata information hidden inside your BOBJ repository.

Hope you find it helpful and Happy New Year!

Share this post on your page!
Share on Facebook Share
Share on TwitterTweet
Share on LinkedIn Share
Business Objects Data Services, SAP BOBJ, BODS, Metadata, Repository, SAP

Post navigation

Older post
Properly Implementing Dynamic Dimension Descriptions
Newer post
SQL Foo | Method to Find Data Patterns

Recent Posts

  • Plural Table Name(s)
  • SQL | Find Patterns In A Dataset
  • When To Use A CTE – And When Not To
  • ETL, DDL, & Self-Documenting Code Generator
  • It’s Performance Review Time!

Categories

  • Business Objects Data Services
  • DBMS
  • Dimensional Design
  • Interview Questions
  • Javascript
  • JSON
  • Python
  • Random Thoughts
  • Reporting
  • SAP
  • SQL Foo
  • SSRS
  • Window Functions

Archives

  • October 2020
  • July 2019
  • June 2019
  • May 2019
  • November 2018
  • September 2018
  • July 2018
  • June 2018
  • May 2018
  • March 2018
  • February 2018
  • December 2017
  • October 2017
  • September 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
Delivery by Matt Komyanek