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.
