0

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?

João Bragança
  • 1,353
  • 1
  • 13
  • 29
  • Wait your fact tables are linked to your OLTP system and not in the the snowflake? – Zane Jan 06 '14 at 22:48
  • No, the dimension tables are linked to the OLTP system. The fact tables don't appear to have relations to anything - pretty sure that's wrong too. – João Bragança Jan 06 '14 at 23:36

2 Answers2

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?

FrankPl
  • 13,205
  • 2
  • 14
  • 40
  • I was told 'maintenance' - it's easier to maintain a single database for everything. – João Bragança Jan 10 '14 at 17:40
  • @JoãoBragança Well, if there are proper ETL processes, then there is no manual maintenance on the OLAP part necessary, assuming there is more or less a single OLTP source, and its data quality is good enough for OLAP analysis as well. – FrankPl Jan 10 '14 at 19:40
0

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.

TomFH
  • 55
  • 3