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:
- Initial Sales tables,
- Purchase order tables,
- Shipping table, and
- Customer Product feedback/Returns table