Where to write MetaData information SQL Queries in SAP BODS

[kirankumar440]

Hello Team,

Can you please help , I wanted to access the BODS SAP given Meta data information tables/views.

But, I am not clear on the execution part of it , where exactly we need to write the SQL to get the required data.

I tried to put the code in side SQL Transformation ( Platform Transformation > SQL) but there we need to pass DATASTORE & the query can only be called on the tables in that particular DS, In this case I dont know if these system given meta data tables reside in any Specific Datastore.

Ex: View - AL_LANG

SELECT OBJECT_KEY, OBJECT_TYPE, NAME, GUID, NORMNAME, VERSION, TYPE, OWNER, DATASTORE, STATE, LANGSIZE, CHECKOUT_DT, CHECKOUT_REPO, CHECKIN_DT, CHECKIN_REPO, LABEL_DT, LABEL, COMMENTS, SEC_USER, SEC_USER_COUT FROM AL_LANG

Thanks,

Kiran

[Nawfal · Accepted solution]

Hi Kiran,

You can get the connection details for the registered local repositories from the CMC (http://hostname:8080/BOE/CMC) HOME>Data Services>Repositories. However for the password, please contact your dba. For the datastore creation please check the details in the designer guide

Thanks

Nawfal

[kirankumar440 · Accepted solution]

Ok Thank you...!!!!

[RichS]

As has already been suggested, you can get the database connection details for local repositories in the Central Management Console. Contact your BODS administrator if you are not familiar. You may also need to contact your database administrator for a password.

Once you have that info, you can create a datastore in Designer to reference the tables in your repository. Or, you can just query the database outside of Designer using the workbench/studio software associated with the repository database type (e.g. MS SQL Mgmt Studio, SAP HANA studio, etc.) - it's a database like any other.

[kirankumar440]

Thank you nawfal.tazi1 for the clarification.

I am new to SAP BODS , Can you please help me where can I get the connection details of the Local Repository DB in the SAP DS Designer Tool ? For creating the DataStore on it ?

Thank you

Kiran K

[kirankumar440]

Hi joseph_muiruri Thank you for the reply.

I do not want to consume the meta data into Job.. I just want to know the steps how? where I can write SQL code on top these SAP given meta data tables. Is there any place in Management console ?

Any steps will really help...!!!!

Ex:Table names

AL_ATTR - Attribute information about native objects
AL_AUDIT - Audit information about each data flow execution
AL_AUDIT_INFO - Information about audit statisticsAL_CMS_BVComponents of a Business

[Nawfal]

Hi

I think Joseph is suggesting that you create a new datastore in Designer pointing to the local repository (db) for which you want to retrieve the metadata. In this datastore you can import tables and views that you are interested in and use them as source in your dataflow for data extract. You can also use SQL transform or sql () function in a script to achieve the same results, alternatively you can run the metadata sql queries directly from your db SQL client tools.

Note: You can't run queries in the Management Console

Thanks

Nawfal

[jmuiruri]

Greetings kalivarapu.kiran ,

I don't understand why you would want to consume the metadata from the SAP Data Services repository in a job.

The only way to proceed is to include the repository as a datastore as well in designer.

Best Regards,

Joseph