exact-onlineinvantive-sqlinvantive-data-hub

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


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.


Solution

  • 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:

    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:

    normale query on subscription lines

    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):

    Error 2

    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:

    Result on Exact Online XML subscriptions

    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:

    repository change

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

    raw facts backing table

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

    default current version view