Automatically set logical view on Data Replicator table on Exact Online

Question

I am using Invantive Data Hub with the Data Replicator option to replicate Exact Online in our Azure database. The tables with names such as: dcd_ganw2_wpbz0_djmwe are nicely created automatically and go away automatically. And column names like d63bb0afa6fe178c81ad30dc3a34b3.

However, I would like also that views are maintained which user friendly names and column names so I don't have to create them myself.

Is there a way to automate this process other than doing it manually and mapping the column names back to normal user-friendly names?

Answer 1

You can have views automatically created on SQL Server in the same database as the Data Cache tables.

Please note that Data Cache by intent have such totally weird names. Some cloud platforms have data sets with names exceeding 100 characters in length and column names with over 1.000 characters after being made unique and logical.

Release 2

For release 2 things are a lot more simple than for release 1.

The settings can be found using:

select * from dc_settings@datacache

Or using:

local data cache

Or using a button in the ribbon.

And the options can be changed using:

alter persistent cache set overall view maintain true; /* New tables automatically get an overall view created. */
alter persistent cache set overall view prefix '{sourceabbreviation}_';
alter persistent cache set overall view postfix '_v';

And using forms in the User Interface.

As place holders in prefix and postfix you can use:

• sourceabbreviation: abbreviation of the source platform, such as eol for Exact Online and at for AutoTask.
• tablename: name of the table when defined, such as projects.
• shortname: short name (alias) of the table when defined, such as pjt for projects.

Or per table:

alter persistent cache table FULLNAME logical overall view maintain true;
alter persistent cache table FULLNAME logical overall view name 'NAME_V';

Massive updates can be triggered using:

alter persistent cache refresh

To force obsoletion on all cache table partition versions in READY state:

alter persisten cache purge ready tables

Massive initial load for all data sets in scope can be triggered using:

alter persistent cache load

For a platform such as Salesforce or Dynamics CRM such an initial load can take hours!

Release 1

For release 1 you can specify the mapping as follows:

• First of all make sure you have hit the data set of the cloud application; this ensures that a table like dcd_ganw2... is created in Data Cache.
• Then open your favorite SQL editor on the database platform used, such as SQL Server Management Studio for SQL Server.
• Query the table dc_tables and look for rows where the column lv_overall_active_flag is Y. These are the tables for which no logical views are created.

• Update these as follows:

  update dc_tables set lv_overall_active_flag = 'Y' , lv_overall_view_name = 'eol_SOME_NAME_r' where id = UNIQUE-ID

• The _r is just a standard postfix, but can be something else. But make sure the view name is a valid identifier on the platform.

• Also make sure that all already cached data is considered obsolete, for instance using the hint /*+ ods(true, interval '1 minutes') */ in your SQL or simply massive by:

  update dc_table_partition_versions set date_becomes_obsolete = getdate() where tbe_id = UNIQUE-ID

• Now log off from the Invantive SQL engine again. Do not forget!

• Log on to the Invantive SQL engine again.
• Hit the data set another time.
• The data cache of the cloud data will be rebuilt.
• A view will be created which contains data across all partitions selected. On Exact Online that might involves hundreds of companies, on Salesforce only one, etc.

• You can now run the following query in SQL Server Management Studio, PGAdmin or whatever:

  select * from eol_SOME_NAME_r

The performance will be a lot better than native querying on Exact Online or Salesforce, typically in the range 50-2.500x faster. Please note that depending on the data volume, the tables with the weird names will have indexes auto-created based upon volume, statistics, etc. You do not have to create them yourselve in general.

When you need a view per company/partition, please use the lv_ptn_active_flag and lv_ptn... columns.