Denodo VQL Templates for PPDM - Quick Guide
You can translate the document:
Overview
The PPDM Association is the not for profit, global society that enables the development of data professionals, engages them in community, and endorses a collective body of knowledge for data capability across the energy and natural resources industry. Its main pillars are providing Data Community (to connect data experts globally), Data Resources (such as a common Data Model) and developing Data Professionals.
The PPDM 3.9 data model was developed collaboratively by the petroleum industry and for the petroleum industry – is the de-facto choice for an industry that increasingly recognizes the value of standards-based solutions and effective data management.
This PPDM 3.9 data model is a robust relational data model ideal for Master Data Management strategies and for business focused application development. The data model is designed using a process known as the PPDM Way by subject matter experts, data management professionals, developers, regulators, data vendors, application vendors and more. It describes over 60 subject areas using relational data definition language.
The PPDM Association offers the necessary DDL statements to create the PPDM 3.9 data model which includes 2688 tables and 19847 constraints in three formats (SQL Server, Oracle and Postgres).
Note: "PPDM" is a registered trademark of https://ppdm.org/ppdm
Denodo VQL Templates for PPDM Data Model
Denodo VQL Templates for PPDM Data Model will help you in simplifying the Denodo implementation associated with the PPDM Data model. After the actual installation of the Denodo Platform, you could use these Denodo VQL templates to make the PPDM version of Denodo elements up and running right away.
All the tables and constraints in the PPDM data model have been created as a virtual layer in Denodo Virtual DataPort as base views and associations respectively. It is a one to one mapping between PPDM and Denodo by using the defined names. An interface layer and the related associations are defined as well. The VQLs also bring in all the table level and field level descriptions found in the PPDM Data model.
This component (Denodo VQL Templates for PPDM data model) contains a set of VQL files and a properties file for all three databases (Oracle, SQL Server and Postgres) to create the virtual layer of PPDM 3.9 model in the Denodo Platform.
In addition to the data model, the PPDM Association is maintaining a Data Rules Library containing statements that support data quality in technical systems and the knowledge needed by data professionals to understand and apply them. A sample Data Quality view is available which implements 150 Data Rules out of the 6351 rules. By running the Data Quality view, it will check for the quality of the data based on each rule and report the status along with the failed rows count in the output. A link within each row is provided by the Data Quality view to see all the failed rows. A sample view VQL can be found in the Data Quality > PostgreSQL directory of the PPDM VQL Template distribution.
PPDM vs Denodo Objects Mapping
Prerequisites
The Denodo VQL Templates require the following prerequisites:
- The current version of the PPDM VQL templates assume that all the 2,688 tables and constraints should have been created in the source database.
- In case, if only a limited number of tables are created in the database, this import operation will be successful. However, the execution of corresponding views will fail if the table is not present in the source database.
- The Denodo update 9.0.4 update of Denodo Platform has been used to produce this export. However, this import will work for all updates of Denodo Platform 9.0 and Denodo Platform 8.0 (after 20250514.1).
- There should be a new virtual database available in the Denodo Virtual DataPort server as a prerequisite. You have to choose the virtual database name while performing the import operation.
Distribution Elements
This Denodo VQL templates for PPDM 3.9 distribution consist of the following:
- PPDM_39_DENODO_VQL_ORA: VQL files to create data source, base and interface views for Oracle based PPDM data model.
- PPDM_39_DENODO_VQL_PG: VQL files to create data source, base and interface views for Postgres based PPDM data model.
- PPDM_39_DENODO_VQL_SQLSERVER: VQL files data source, to create base and interface views for SQL Server based PPDM data model.
- PPDM_39_DENODO_DATAQUALITY_VQL: VQL files to create data quality views for 150 data rules (common to all data sources) provided if the corresponding tables are present in the source database.
Importing VQL Templates
In order to Import, please follow the below steps at high level,
- Download the distribution zip file for the corresponding data source.
- Unzip the content into the desired folder.
- Replacing the database URI, username and password specific to your data source configuration in the base_views_vql.properties file.
For example, please refer to the below changes in the properties files for the Postgres distribution:
- Open the Web Design Studio.
- Create a new virtual database in the Denodo Virtual DataPort with the name (e.g. “ppdm”) to import the elements.
For importing the views to the Denodo Virtual Dataport server, go to File > Import.
Select the base views VQL (base_views_export_database.vql) and properties file (base_views_export_database.properties).
Click OK to import all the base views and associations into your environment. This process may take a while to complete therefore we recommend executing at a time of little to no server activity.
Once the base views and all associations were created, navigate again to File > Import, select Interface VQL (interface_views_export_database.vql), and click OK to import all interface views and the related associations into your environment. This process may take up a considerable amount of time.
Refresh
After the import process finishes, refresh the Design Studio Explorer by selecting File > Refresh.
Now, you will be able to see the data source, base views, interface views and associations related to the PPDM Data Model.
Querying Elements
Now, you can execute the base views and interface views that have been created either in the Design Studio or in the Denodo Data Catalog. In order to execute the queries in Denodo Data Catalog, please ensure that the Synchronization with the Virtual DataPort server is completed.
You can also execute the Data Quality view in the Design Studio or in the Denodo Data Catalog. Please note that, in the Data Catalog, you could click on the URL in the failure_rows field to see the number of rows which does not satisfy the data rule. In the Design Studio, you have to copy the URL manually and paste in a browser tab to see the failure rows.
Similarly, starting with Denodo 9 you could use Denodo AI SDK to interact with the “ppdm” database to get answers related to data and also related to metadata such as description etc.