Connecting to Analytical Storage

Analytical storage is a special database where CData 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 CData 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, among others. For a full list of supported DBMS, see our website.

An analytical storage data source is created, in general, like every other JDBC data source. For detailed information on this, please refer to the JDBC Connectors section. 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, the  CData Virtuality Server needs to be told which alias is used for the analytical storage data source and which schema (Oracle, PostgreSQL, MS SQL) for which database (MySQL) is set as analytical storage:

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:

SYSADMIN.getCurrentDWH()

Examples

Configuring an Oracle Analytical Storage

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');;

Configuring a MySQL Analytical Storage

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');;

Configuring an Exasol Analytical Storage

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>');;

Checking Analytical Storage Settings

 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 the setCurrentDWH() system procedure is called, the above check will not happen and setCurrentDWH() will not throw any exceptions;
• If setCurrentDWH() is called for an unsupported DWH platform (with OPTION $NOFAIL), getCurrentDWH() will return the real data (empty set, if DWH was not configured, or current supported set DWH).

 Here is an example:

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

And here is an example with the result:

-- 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 last block will display the following result:

ms

ms

The CData Virtuality Studio exporter adds OPTION $NOFAIL when the setCurrentDWH() system procedure is called.