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 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 Data 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 forsetCurrentDWH()
by default and throws theUnsupportedDwhPlatformException
with the corresponding error message if the check fails;- If
OPTION $NOFAIL
is added when thesetCurrentDWH()
system procedure is called, the above check will not happen andsetCurrentDWH()
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:
nameInDV | nameInSource |
---|---|
ms | ms |
The Data Virtuality Studio exporter adds OPTION $NOFAIL
when the setCurrentDWH()
system procedure is called.