Connecting to Analytical Storage

You are looking at an older version of the documentation. The latest version is found here.

Analytical storage is a special database where Data Virtuality can place the materializations and automatically optimize them by creating indexes on them if needed and allowed by the type of underlying database. Utilizing analytical storage can drastically improve query performance.

Creating a Data Source

With the Data Virtuality Server, you can use different DBMS as analytical storage (DWH): Oracle, Microsoft SQL Server, PostgreSQL, MySQL, EXASOL, SingleStore (formerly MemSQL), Snowflake, Teradata, and Vertica.

An analytical storage data source is created, in general, like every other JDBC data source. For detailed information on this, please refer to this section: JDBC Connectors. Note that it is important to set "importer.importIndexes" to true for the analytical storage data source.

Using Microsoft SQL Server and MySQL as Analytical Storage is not recommended due to numerous limitations.

If you plan to use MySQL as Analytical Storage, please consider MySQL as Analytical Storage first and MS SQL Server as Analytical Storage in the case of MS SQL Server.

Configuring a Data Source as Analytical Storage

This step can be skipped if both alias and schema/database have DWH as a name. If not, Data Virtuality Server needs to be told which alias is used for the analytical storage data source and which schema (Oracle, PostgreSQL, MSSQL) for which database (MySQL) is set as analytical storage:

SQL

SYSADMIN.setCurrentDWH(<alias_used_for_dwh>, <schema or database>)

<schema or database> should be provided using the same case as in the original DBMS.

To check how the DWH is configured, you can use the following procedure

SQL

SYSADMIN.getCurrentDWH()

Examples

SQL

CALL SYSADMIN.createConnection('dwh_alias','oracle','db=XE,host=127.0.0.1,user-name=johndoe,password=secret');;
CALL SYSADMIN.createDatasource('dwh_alias','oracle','importer.useFullSchemaName=false, importer.schemaPattern="DWH_SCHEMA", importer.tableTypes="TABLE,VIEW", importer.importIndexes=true','');;
CALL SYSADMIN.setCurrentDWH('dwh_alias', 'DWH_SCHEMA');;

SQL

CALL SYSADMIN.createConnection('dwh_alias','mysql','db=dwh_db,host=127.0.0.1,user-name=johndoe,password=secret');;
CALL SYSADMIN.createDatasource('dwh_alias','mysql5','importer.useFullSchemaName=false, importer.tableTypes="TABLE,VIEW", importer.importIndexes=true','');;
CALL SYSADMIN.setCurrentDWH('dwh_alias', 'dwh_db');;

SQL

CALL SYSADMIN.createConnection('dwh_alias','exasol','host=<host>,user-name=<username>,password=<pwd>');;
CALL SYSADMIN.createDatasource('dwh_alias','exasol','importer.useFullSchemaName=false,importer.schemaPattern=<username>', 'ForceQuotedIdentifiers=true,SupportsOrderBy=true');;
CALL SYSADMIN.setCurrentDWH('dwh_alias','<username>');;

SQL

 SELECT * FROM SYSADMIN.getCurrentDWH();;

The setCurrentDWH()procedure can be used with OPTION $NOFAIL:

setCurrentDWH() performs a check for the unsupported DWH platforms or non-existing schema provided for setCurrentDWH() by default and throws the UnsupportedDwhPlatformException with the corresponding error message if the check fails;
If OPTION $NOFAIL is added when setCurrentDWH() is called, the above check will not happen and setCurrentDWH() will not throw any exceptions;
If setCurrentDWH() is called for the unsupported DWH platform (with OPTION $NOFAIL), getCurrentDWH() will return the real data (empty set, if DWH was not configured or current supported set DWH).

SQL

CALL SYSADMIN.setCurrentDWH(nameInDv => 'salesforce2', nameInSource => 'salesforce2') OPTION $NOFAIL;;

SQL

-- MySQL data source
CALL SYSADMIN.createConnection('ms','mysql','host=localhost,db=adventureworks,user-name=root,password=root');;
CALL SYSADMIN.createDatasource('ms','mysql5','importer.defaultSchema=adventureworks,importer.useFullSchemaName=False,importer.tableTypes="TABLE,VIEW",importer.importIndexes=false','');;
 
--set MySQL data source as DWH
CALL SYSADMIN.setCurrentDWH(nameInDv => 'ms', nameInSource => 'ms');;
CALL SYSADMIN.setCurrentDWH(nameInDv => 'salesforce2', nameInSource => 'salesforce2') OPTION $NOFAIL;;
SELECT * FROM SYSADMIN.getCurrentDWH();;

Executing the latest block will display the result:

nameInDV	nameInSource
ms	ms

The Data Virtuality Studio exporter adds an OPTION $NOFAILsetCurrentDWH() when the system procedure is called.