Using the Dimensional Data Warehouse as source data for the OLTP process

[brianlieb]

I have two architectural questions about using a dimensional data warehouse in what seems an "non-traditional way".

First:

We have a process that imports data from disparate data sources and integrates it and uses already loaded dimensions to create a fact table. These data are measurements of real world occurrences.
The interesting part is that our process then takes that data and runs an estimation process to "fill the gaps" in the imported data. Essentially, this creates more of the same kind of facts.

Is this 'two-step' ETL described above problematic? Essentially creating facts that are then run through another analysis to create more facts of the same kind.

The Second Question:

Is it a bad practice to use the data warehouse as a clearing house for OLTP applications to reference a specific dimension at a specific time. Meaning is there a reason not to reference a dimension record from an OLTP procees?

[ngalemmo]

1. No, it is not that unusual. For example, predictive analysis does just that.

2. That not common and can be argued is a bad practice. OLTP systems are traditionally the system of record. A well designed OLTP system should be complete unto itself. It should have the most current and accurate information. Data warehouses, on the other hand, are not designed to handle the rigors of OLTP activity. Their data is not current... even in so-called 'real time' data warehouses, there is an inherent latency.

You do not mention if the OLTP system actually updates this table. That is definitely not good practice. All updates to a data warehouse should follow proper load/audit/transformation procedures that would be burdensome to an OLTP system.

[brianlieb]

So, 1 is essentially what we are doing! That is great news.

I need to be clear, we are not actually doing the second question. And if we considered doing it, then No, the OLTP system would absolutely not make changes to the Warehouse, that would only occur through some ETL.

Thank you for your response.

[ngalemmo]

I am not saying that #2 isn't done, but it needs to be done in a controlled manner.  I would not give an OLTP system direct access to the data warehouse.  What is typically done is data is extracted from the data warehouse and then consumed by another system (such as the OLTP system).  For example, an organization may use the data warehouse to perform a data quality audit and use the results to produce data to feed into their MDM system.

The key is each system has their own data that can be tailored to the specific need.  The physical structure of the table and performance can be optimized for the specific use.

The reason why data warehouses exist is because the data is modeled for a different purpose and to separate the query loads from the OLTP system. Allowing the OLTP system to place its own query load on the data warehouse may get a lot of people upset. The OLTP system may experience delays due to a large analytic query, those using BI tools may experience response slowdowns due to the query load from OLTP.

[brianlieb]

I think the latency is actually my main concern here. (Which is one of those, "Why didn't I already think of that?" things) I appreciate your answers, it has cleared up a couple of nagging questions I had.

[Sponsored content]