itgendch033: Backing databases require Invantive Data Replicator to restrict the number of columns to 1,000 for 'ExactOnlineXML.XML.SubscriptionLines'

Question

When executing the batch script to replicate my data from Exact Online, I get the following error:

Error itgencun016: Exclamation itgendch033: Backing databases require Invantive Data Replicator to restrict the number of columns to 1,000 for 'ExactOnlineXML.XML.SubscriptionLines'.

It occurs for the following query:

select /*+ ods(true, interval '20 hours') */ count(*) 
from   ExactOnlineXML.XML.SubscriptionLines

Same goes for ExactOnlineXML.XML.InvoiceLines.

How can I replicate these tables without maxing out the columns.

Answer 1

Invantive SQL places no restrictions on the length of column names nor on the number of columns for a table or view.

However, traditional databases were designed in another time and typically are restricted to for instance 30..128 characters column names and 1.000, 1.024 or few thousand columns. Remember that Oracle ran on 64 KB (32 K code, 32 K data); that is approximately the size of this question and answer :-)

When replicating data from Exact Online into a traditional database like Oracle, SQL Server or PostgreSQL, Invantive Data Hub will use Invantive SQL to retrieve the data from in this case Exact Online and then bulk load it into the database.

However, the data must fit both in column names as well as column number.

That is the main reason that the column names are so weird; they are fit into a limited number of characters independent of the original column names. The column names of the generated views are also shortened by removing center pieces with a unique MD5 hash.

For number of columns, Data Hub just rigorously checks that your source doesn't have more than the limit of 1.000 columns. The Exact Online XML APIs have no documentation that describes what columns can be filled with a value; just a XSD that describes all theoretical possibilities, leading to millions of columns.

Most Exact Online XML-based tables have been tuned to exclude column name paths that do not have values, but still they often don't fit within 1.000 columns.

The possible solutions are:

• Use the Exact Online REST API variant, which is often present and sometimes also similar in functionality and performance (not always, the XML API is old, but in general better designed for usability). So check whether there is a ExactonlineREST..SubscriptionLines.
• Exhaustively describe which columns to replicate.

Describe Columns to Replicate

The last solution is a little complex. It also defies use of advanced strategies like trickle loading (with web hooks) or smart sampling; it is just a plain copy with or without versioning.

As a sample I've run a query on 200 Exact companies with subscriptions, when connected to a Data Replicator environment:

Note that the /*+ ods(true) */ is not present but assumed implicitly; it is default to replicate when also connected to Data Replicator.

By adding /*+ ods(false) */, you effectively tell the SQL engine to not send the data for replication into a database to the Data Replicator provider.

When I run it, there is another error itgenugs026 (requested number of columns exceeds the maximum number supported for display in the results grid):

This is actually a rendering error; the grid used in the Query Tool restricts itself to 1.000 columns. Larger volumes of columns cause very slow UI response time.

By clicking the button 'Hide empty columns' or using Invantive Data Hub as user interface, you can get the actual results:

Note the tooltip: the heading display somewhat natural labels, but the actual column name is displayed in the tooltip.

Write down the column names that you need and fill an in-memory table with only the columns you need, such as:

create or replace table my_subscriptions@inmemorystorage
as
select /*+ ods(false) */ 
       subscription_number_attr 
,      subscription_description
from   exactonlinexml..subscriptionlines

Now replicate this table the normal way:

select /*+ ods(true, interval '1 second') */ 
       count(*) some_unneeded_data_to_force_replication
from   my_subscriptions@inmemorystorage

Note that the ODS hint must be present. In-memory tables are never replicated by default.

For refresh you can use alter persistent cache [force] refresh, but the in-memory table must have been filled in advance.

The resulting entry in the repository will be:

The facts table (see dcs_.... for the Data Vault with time travel) is:

And the default named view is imy_my_subscriptions_r (imy is abbreviation of 'inmemorystorage' driver):