Data availability during data mart load.

Question

Hello,

I need some help or best practices for client reporting datamart. Our problem is that when the datamart is getting loaded and clients use the database at the same time, they sometimes see inconsistent data or no data at all.

What approach should we use to make sure clients are not affected when we are loading or building datamarts?

Answer 1

All is depending on your load process. If your load process of your datamart is incrementally adding/updating data, all should be fine. You can first add/update your dimensions without any issues. Then add new data to your fact tables.

Since you are experiencing issues, I expect your load process is not fully incremental. Sometimes calculations in dimensions and facts prevent a feasible incremental load strategy.

The next best thing in my opinion is do a schema swapping. Assumption: all your tables for the datamart are in one schema called DM1.

Approach:

• we use an intermediate schema to load the data.
• Once the data is fully loaded we move the 'real life' data into a temp schema
• we move the newly loaded data into the 'real life' schema
• then we clean up the temp schema, so we are ready for the new load batch.

Step-by-step:

• create 2 new schemas: loadcopy and swaptemp (one time action)
• in the loadcopy, create a replica of all your tables (one time action)
• start the load process to load into your loadcopy tables
• once the load process is done:
  • do a schema swap where you transfer all tables from the DM1 schema to the swaptemp schema
  • do a schema swap where you transfer all tables from the loadcopy schema to the DM1 schema
  • do a schema swap where you transfer all tables from the swaptemp schema to the loadcopy schema

to move a table from one schema to another, execute the following code:

-- from DM1 to swaptemp
ALTER SCHEMA swaptemp TRANSFER DM1.dimension_table1;
ALTER SCHEMA swaptemp TRANSFER DM1.dimension_table2;
ALTER SCHEMA swaptemp TRANSFER DM1.fact_table;

-- from loadcopy to DM1
ALTER SCHEMA DM1 TRANSFER loadcopy.dimension_table1;
ALTER SCHEMA DM1 TRANSFER loadcopy.dimension_table2;
ALTER SCHEMA DM1 TRANSFER loadcopy.fact_table;

-- from swaptemp to loadcopy
ALTER SCHEMA loadcopy TRANSFER swaptemp.dimension_table1;
ALTER SCHEMA loadcopy TRANSFER swaptemp.dimension_table2;
ALTER SCHEMA loadcopy TRANSFER swaptemp.fact_table;

Answer 2

One way is to use staging area/tables or a complete staging database, but that's a lot of work.

https://en.wikipedia.org/wiki/Staging_(data)