You can translate the question and the replies:

Import Materialized View Data Lineage in Denodo

We would like to import an Oracle MV dependencies into Denodo, do we have an design option here Below will give all the underlying references of the MV, and we wanted to import those underlying tables/views and associate to the final view, so that we have a tree view. Please advise a optimal auotmated design for this? ``` SELECT * FROM dba_dependencies WHERE owner = 'dts' AND name = 'CAT_MV'; ```
user
09-05-2024 18:41:46 -0400
code

1 Answer

Hi, I managed to create a base view from Oracle's materialized view using the Virtual DataPort Administration Tool. When creating a base view, the tool displays a tree with the database schemas. To inspect a schema's tables and fields, click on it. The materialized view will appear under the 'tables' section of the selected schema, which you can expand by clicking on the plus sign. Alternatively, you can check the permissions and follow these steps. This will allow you to create a joined view by passing the materialized table name and the reference type to find the dependent views. Step 1: Create a base view by selecting the "Create from query" option over the Oracle data source, using the following query: `select * from all_dependencies where name='@name' and referenced_type='@reference_type'` The all_dependencies is used to list dependencies on Oracle views. Step 2: Create a JDBC data source and select 'Denodo Virtual DataPort 8.0' as your database adapter. Next, provide your Denodo server configuration details and test the connection. Afterward, navigate to 'Create base view' and select the 'Create from query' option. In the Query tab, input the required query. `select * from GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW ('<datasource_name>',null,'@schema_name','@table_name','@bv_name',null,null,'mv');` Replace <datasource_name> with the name of the Oracle data source you created in step 1. The GENERATE_VQL_TO_CREATE_JDBC_BASE_VIEW returns the VQL statements needed to create a JDBC base view for a specific table/view from the underlying database of a JDBC data source. It's important to note that this function doesn't create the view itself, but only provides the necessary VQL statements to do so. Step 3: Finally, create a join between the two base views(bv_1 and bv_2). Add the join between the following columns: `bv_2.schema_name = bv_1.owner AND bv_2.bv_name = bv_1.referenced_name AND bv_2.table_name = bv_1.referenced_name` When executing the join view, pass the name of the materialized view and the reference type. This will display all the dependencies of the materialized view. Once you execute the view, you'll see a column named 'creation_vql.' This column returns the VQL statement required to create a JDBC base view. Execute this VQL statement in the VQL shell of the Virtual DataPort Administration Tool to successfully create the view. Hope this helps!
Denodo Team
16-05-2024 17:53:26 -0400
code
You must sign in to add an answer. If you do not have an account, you can register here