Relational Integrity between OLAP and OLTP

Question

I've been reviewing a client's architecture, particularly their OLAP system, which is just a regular old snowflake schema on SQL Server. The facts and dimensions are ETL'd in from other transactional systems such as ERP.

One thing that jumped out at me was several additional tables, in the same database, for multiple additional OLTP applications. These tables have FK relationships to dimension tables in the snowflake schema.

There are a lot of joins into the dimension data from the OLTP system, so performance is not the best.

I am not an OLAP expert at all; but this just feels wrong. I've done some searching but can't find much about this on the internet either pro or con. What are the benefits of doing this? Are there any? What about potential problems?

Answer 1

I would try to avoid any explicit foreign keys between OLTP and OLAP data. Having foreign keys from OLTP to OLAP prevents you from adding new entities in the business and may require to define entities in OLAP first, while the standard is to run the ETL processes one-directional only - always from OLTP o OLAP. And having foreign keys from OLAP to OLTP prevents you from keeping historical data in the data warehouse that is not relevant for the current business, but may be interesting for analysis.

Of course, there are always situations where you break rules for a reason. Maybe there is one. Does someone at the client's side have an explanation why this was implemented the way you describe?

Answer 2

It is not common to share a dimension table between OLTP and OALP. There are at least 2 reasons: (1) the attributes interesting in OLTP and in OLAP may be quite different. (2) the contention and consequent performance problem.

On the other hand, it is not uncommon (but is somewhat advanced) for OLTP and an ODS to share exactly the same copy of a dimension. This is often called a "golden copy". I often call an ODS designed like this to be an active ODS. When there are multiple copies of the dimension, I call it a passive ODS. It may be that the OLAP you refer to is not true OLAP but just some form of tactical reporting, in which case sharing the same dimension table is not uncommon.