Should Indexed Views be utilized/recommended as method to auto-generate DataMart Reporting tables?

We have a Customer timeline of tables which can be simply joined on CustomerId in each tables.

Indexed views definitely should not be created in high volume OLTP environment database, due to write latency in creating tables. See Brent Ozar Indexed View Data Modifications .

However, we want utilize transactional replication of all tables into a separate ReportingDB on another server to create indexed views. This way indexed view will not have any performance reduction on the actual OLTP.

Example OLTP Tables:

  1. Initial Sales tables,
  2. Purchase order tables,
  3. Shipping table, and
  4. Customer Product feedback/Returns table
  • But why? What’s wrong with a regular (5 minute) incremental of a table? Often data mart tables cannot be generated with views because for example you want to pre generate surrogate keys and you won’t be able to do this in a view. – Nick.McDermaid May 9 '18 at 13:19

Yes. It is generally safe, and often useful to create indexed views on subscriber tables in transactional replication.

You may need to drop and recreate the indexed views using custom scripts before and after initializing from a snapshot. See Execute Scripts Before and After the Snapshot Is Applied

You can also use a different indexing scheme on the subscriber (also by using post-snapshot scripts). In particular you can make the subscriber tables Clustered Columnstore tables, or add non-clustered Columnstore indexes on them for reporting.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy