You can translate the question and the replies:

Extract the origin of a data source in metadata for report view

Hi, In Denodo 6, i need to know the origin of data sources, is there a way to know the origin of a data sources in the metadata? I need extract for Excel many information about metadata: Data Source Name, Type, Schema, Table or view, Column, Data Type, etc. I extract all information using the Stored procedures (GET_ELEMENTS, CATALOG_METADATA_VIEWS, GET_SOURCE_COLUMNS, etc), but i can´t find any place where i extract with a Select for a Report_View information about the origin of data sources, i.e (Oracle, Sql Server, DB2, SAP, etc). Exists any Stored procedure or Table/View where i extract this information? Best regards
user
26-04-2019 10:10:05 -0400

3 Answers

Hi, To find the origin of the data sources, you can refer to the steps mentioned in the previously answered community question [here.](https://community.denodo.com/answers/question/details?questionId=9060g000000bmMEAAY&title=Origin+of+a+data+source+in+metadata) Additionally, to get only ‘Database Name’ in the output, you can create a derived (selection) view on top of the base view and apply regular expressions and text processing functions. For more information , please refer to the VDP VQL guide on [Text processing functions.](https://community.denodo.com/docs/html/browse/6.0/vdp/vql/appendix/syntax_of_condition_functions/text_processing_functions) Hope this helps!
Denodo Team
29-04-2019 19:01:25 -0400
Hi, Thanks for your answer, but VQL command - DESC VQL DATASOURCE JDBC <base/derived view>, don´t responds to my problem. I need a teble/view or Stored procedure that allows i extract the origin of a data source for a view and after export from excel file. With Stored procedures (GET_ELEMENTS, CATALOG_METADATA_VIEWS, GET_SOURCE_COLUMNS, etc), I select the information I need: Example: Select * from GET_ELEMENTS() database_name = vdp_xxxxx name= ds_xxxxx Type=datasource Sub_type =jdbc folder = /01 - connectivity/01.1 - data sources but I still need the origin of data source (DATABASENAME = 'oracle', DATABASENAME = 'sqlserver', etc) Any ideia where i find this? Thanks
user
30-04-2019 08:16:37 -0400
Hi, To get the database name and of your virtual database in Denodo, you can follow these steps: 1. Create a self-referential data-source (JDBC) to the virtual database in which the target datasource exists. 2. Create base view using the ‘create from query’ option and run the command ‘ DESC VQL DATASOURCE JDBC <datasource name>. 3. Create a selected view on the base view and add a new column in the output tab. Put the command, *** regexp(field ,'DATABASENAME\s+\=\s+''([^'']+).', '$1')*** in the field expression. This will remove the string ‘DATABASENAME =’ from the output of DESC command when you query the view. 4. Create a new view on top of the view created in step 3. Add a new field and apply SPLIT() function to the output generated in the previous step to divide the string in two different parts and convert it into an array. 5. Create a new view and store the output of the array index which contains the database name part in another variable. (f(0).string) 6. Again apply the SPLIT() function on the output of step 5 7. Store the output of array index in another variable which contains the database name. Hope this helps!
Denodo Team
07-05-2019 19:21:05 -0400
You must sign in to add an answer. If you do not have an account, you can register here