Why do I see data in the facts table, but not in the Data Replicator view?


When I query an Invantive Data Replicator view like eol_transactionlinesbulk_r, I see no rows for my company with division 123456.

However, I see rows in the facts table dcd_abcde that stores the actual data for division 123456.

Why don’t I see those rows in the view?


This behavior is intentional and necessary to allow 24x7 availability of complete facts through the views. The facts table stores the facts in three phases of their life:

  • loading phase: in this phase Data Replicator is loading by batches the facts into a new version.
  • available phase: in this phase the facts are available for new queries through the view.
  • dying phase: in this phase the facts are available for queries that started running before a new version was brought online.

The phases of a combination of table and partition are visible in the view dc_table_partition_versions_r on your database.

Loading Phase

During the loading phase, the facts are not yet complete but already being loaded as far as possible. This reduces memory consumption and reduces the load on your database server. It also reduces loading times, since retrieval and storage are executed in parallel.

The user community however may not see the data in the view since the data is incomplete.

In dc_table_partition_versions_r the loading phase is signaled by the use of one of the following states:

  • I: Initializing
  • V: View creation
  • P: Prepared
  • S: Seeding
  • L: Loading

Each of these states can be present for a short time. However, a version can remain at initializing or seeding state when for instance loading fails due to database issues. Failed versions after removed automatically after a prolonged period of time to ensure that no Data Replicator instances are still accessing or loading the data.

Available Phase

During the available phase, the facts can be found when you query the view. On most database platforms, queries once started return a consistent result even when new changes are made in the database.

In dc_table_partition_versions_r the available phase is signaled by the use of one of the following states:

  • R: Ready.

The Ready state is typically combined for most of the time with the active flag set to ‘Y’. During short time periods at the start and end of the available phase, the active flag can be ‘Y’ will the state is not 'R’ (Ready) and vice versa. This is algorithmetically necessary.

Dying Phase

During the dying phase, the version of the previously available facts have been obsoleted and new queries started will use a new version. However, as long as queries are running, the facts remain available. After some time (often 24 hours), Invantive Data Replicator will start removing the facts of the version.

Once all facts have been removed from the table, the repository tables will still show the version for some (typically 7 days). After this period, the version will be permanently removed from the repository.

In dc_table_partition_versions_r the dying phase is signaled by the use of one of the following states:

  • O: Obsoleted; the facts are still (partially or completely) presents in the facts table.
  • D: Dropped; the facts have been removed, but the version is still visible in the repository.