What is the best practive for VDP virtual dataport database management?

Database Management

A Virtual DataPort database is equivalent to a virtual schema, and therefore the unit of project work in Denodo Platform. The following suggestions apply regarding database management:

Development
● Create a new database for each new application/project using the Platform.
● Create new databases for each user in a project that might need to perform frequent modifications on the project’s database. Create these databases as replicas of the project’s trunk and merge changes when considered stable.
● Use VQL export/import tools for replicating existing databases and performing version control of your schemas.

Production
● Do not mix development and production schemas in the same server.
● Test new modifications in a different schema in the same server before applying these modifications to the production schema. If a dedicated pre-production server is available, use it for these purposes.
● Do not mind replicating views among diverse virtual schemas if this makes sense for your application needs. Give applications/clients what they need and no more.
● Always apply source control tags to the production versions of your virtual schemas’ metadata.

Denodo adding SAP HANA fail over server as JDBC data sources

Fill the form with the following parameters:
● Database adapter: Generic
● Driver class: com.sap.db.jdbc.Driver.
● Database URI: use a connection string in the form of:

jdbc:sap://<server>:<port>[/?<option1>[&<option2>]…]

For example: jdbc:sap://host:30015/?autocommit=false. The port should follow the syntaz 3<instance number>15. For example: 30015, if the instance is 00.

You can specify one or more failover servers by adding additional hosts, as in the following example:

jdbc:sap://host:30015,host1:30015,host2:30015/?autocommit=false

Denodo Data Virutalization how to configure idle connections in a pool?

In order to modify the number of idle connections in the connection pool of a JDBC data source, you can run the following command from a VQL command line:

ALTER DATASOURCE JDBC <data source name>
VALIDATIONQUERY = ‘SELECT COUNT(*) FROM SYS.DUAL’
INITIALSIZE = 15
MAXIDLE = 25
MINIDLE = 5
MAXACTIVE = 20
EXHAUSTEDACTION = 1
TESTONBORROW = false
TESTONRETURN = false
TESTWHILEIDLE = false
TIMEBETWEENEVICTION =  60000
NUMTESTPEREVICTION = 3
MINEVIDECTABLETIME = 120000;

In addition to the configuration options that are available from the graphical interface, more options can be set to the desired value using VQL. You can check the meaning of the different properties in the documentation of Apache Commons Pool.

To configure the number of idle connections you can modify the MAXIDLE and MINIDLE properties.

Denodo how to connect to SAP data sources

Denodo provides multiple integration options for obtaining data from the SAP ecosystem modules.  The following data sources can be used to connect to SAP:  BAPI data source, web service data source, multidimensional data source and custom data source.

SAP’s recommended approaches for SAP integration are BAPIs and Web Services.

SAP BAPI Integration:

Denodo provides a built-in connector for the different components of the SAP Business Suite (ERP, CRM, SRM, SCM, PLM, etc) using BAPI and other RFM calls. After selecting the BAPI or RFM, VDP will introspect the schema of the BAPI. The schema will include the input parameters and hierarchical fields in their native format, which is a unique feature of Denodo’s Extended Relational Model. Some advantages of the BAPI approach:
a. Direct integration with BAPI does not require additional Web Service layer which could have a potential performance impact.
b. Eliminates the requirement for an SAP Application Server license and deployment.
SAP Enterprise Web Services:

The second option is to use SAP’s Web Service SOAP interface using Denodo’s built-in web service connector. This approach is commonly used where an SAP Web Service includes business logic that goes across multiple applications and there is no BAPI available that includes this logic. Some advantages of this option are:
a. Supports the future direction of SAP so some functionality may be available only through this interface.
b. Supports Web Services Security which may be needed in some use cases.
These methods are used by many applications in the SAP ecosystem, like SAP NetWeaver Gateway. These approaches enable access to SAP tables in cases where direct connection to the underlying database is not possible.

Additionally, through a Multidimensional data source, Denodo provides an OLAP adapter for SAP Business Warehouse (SAP BW) that offers full graphical introspection of the InfoCubes, facts and characteristics. Here, Denodo can automatically generate MDX queries to retrieve the requested information that are delegated to SAP BW for high speed performance. In this case, it is recommended to use the BAPI option over the XMLA option. This option uses BAPIs through the JCo library and provides faster connectivity with additional optimization options.

Other methods to access SAP data include direct connection to SAP’s underlying database through the creation of a custom connector leveraging SAP’s Java SDK.

Denodo Data Virtualization how to detect change in data sources

Denodo provides a stored procedure call “SOURCE_CHANGES” that detects the differences between the current schema of a base view and its underlying data source. The syntax is:
SOURCE_CHANGES ( base view name : text )

This stored procedure returns a row for each field of the view and also a row for each field that is present in the data source, but not in the base view.  If a field is an array or a register, there will also be a row for each one of its subfields.

 

Sometimes, if the virtual data base has a lot of base views and the data source schemas are changing quite frequently, the manual execution of this stored procedure for every base view can be costly.
It is possible to combine the SOURCE_CHANGES and CATALOG_VIEWS stored procedures to detect which views have changed

 

  1. Create a derived view called ‘base_views‘ using the CATALOG_VIEWS stored procedure which returns the list of base views. This is the VQL code to create the view:

    CREATE OR REPLACE VIEW base_views AS SELECT * FROM CATALOG_VIEWS() WHERE viewtype = 0;

    The viewtype = 0 condition guarantees that the CATALOG_VIEWS stored procedure only returns views that are base views.

 

  1. Create a derived view ‘source_changes‘ from the SOURCE_CHANGES stored procedure.  This is the VQL code to create the view:

    CREATE OR REPLACE VIEW source_changes AS SELECT * FROM SOURCE_CHANGES();

    Notice that to execute the view an input parameter is required, which is the name of the base view.

 

  1. Finally, create a nested join between the ‘base_view‘ derived view and the ‘source_changes‘ derived view.  The join condition will be:

 

base_views.resultname = source_changes.tablename ;

        

In the join, the “base_views” view provides the base view names to execute the “source_changes” view.