Dealing with a terrible OLTP system. Long, but I'd appreciate some expert guidance.

To recap, I am dealing with an OLTP with the following characteristics.
It doesn't have any Primary or Foreign Keys and lacks referential integrity. It does have clustered unique indexes created on identity columns in most tables.
A majority of the fields are CHAR fields, even those that should rightly be integer or other field types. It does however, correctly use Datetime fields.
Most fields are nullable.
The database size range I've run into is between 3GB and 100GB so we're not talking about large data sets.

I've read 3 books on data warehousing from The Kimball Group as well as the majority of two books which deal with SSIS ETL processes. Here is the problem which struck me today. Most of the tables which contain Facts or Measures in this database do not have any transactional component to them. This includes Quotes, Sales, Purchasing, Receiving, etc. Even the accounts receivable and payable modules are not transactional in nature. The only tables which are transactional deal with general ledger.

Deletes and updates are "hard" and are performed in place in the tables rather than keeping track of them as transactions. Further, there isn't a provided method to track these actions (even deletes) which makes auditing impossible. Also, even with deletion triggers on various tables (which I've implemented) I cannot retrieve the user name because the software package only accesses the database with a single database admin login. 95% of companies who use this actually use the SA login for the server for their ERP databases. Every change shows up as SA. Also, everyone who uses this product (to the best of my knowledge) reports directly from the OLTP. The data you receive is literally the current value of the data.

Please review my list of issues by Fact Table Type.

Transaction Fact Table

• Since the system doesn't log transactions I'm not sure how I could create a table of this type from anything but the General Ledger.

• Perhaps I could devise a way to incrementally export changes every 15 minutes and then consider those 15 minutes segments a "Transaction." Opinions?

Accumulating Snapshot Fact Table
The system allows VERY lax workflows. For example:

• With some exceptions records can be deleted at nearly any point in the process. Users can delete quotes, sales orders, purchase orders, etc unless there is a record from another module that is tied to them. For example, you can delete a purchase order in mid process as long as you haven't received against it.

• Sales Orders(and other modules) can be re-opened, added to, edited, and then the additional product shipped and invoiced. This makes an accumulating snapshot table very difficult to create and maintain.

• Set workflows for orders are very lax as well. This system is used by manufacturers but some companies such as my employer are mostly buy/resell. I could receive an order for a specific product (Widget in this example), tie a purchase order to it from a vendor, receive it, inspect it, ship it, and invoice it in that order. Each one of those would be a date in an accumulating snapshot fact table. However, they could just have easily created a Sales Order, purchased the widget for inventory, received it, shipped it as miscellaneous (which doesn't really log), and invoiced directly from the sales order. I just don't know how to design a accumulating snapshot for that.

Periodic Snapshot
The classic periodic snapshots for my system might be by fiscal period (typically month) for incoming sales, amount purchased, amount received, etc. However, when you consider how lax the workflow is, you cannot accurately calculate most of these. Once again, a brief example:
The government creates a blanket PO for widgets which lasts 5 years. Company creates a sales order with an order date of today 8/23/2010. Company adds 5 widgets a month for $10 each for the next five years. However, because the order date is only accepted in one place (the header table) then the entire amount shows up in August 2010 even if the majority of the sales are spread out for the next decade.

Without the transactional record, I don't think I can create a Periodic Snapshot on this. Opinions?

When one considers the three basic types of fact tables Transaction, Periodic Snapshot, and Accumulating Snapshot; what can I do with an OLTP system like this? Triggering all of those tables so that I could get actual in R/T transactions isn't really an option because it invalidates the user agreement.

Other than loading a fact table which represents exactly what is in OLTP at the current moment only in a different structure, how do I provide value with this?

Ugh... I take it the rows don't have insert or update timestamps either...

One possible approach, since the rows have identity PKs, is to keep track of the max values so you can identify rows added since the last extract. If you pull daily, you would at least be able to fix dates to 'new' sales like the blanket PO example.

But, to be honest, it doesn't sound like you can get around the fact that you will probably need to pull all active documents and compare then against the previous values stored in a staging area... or better yet, call it an ODS

But, if it is as bad as you describe, it just doesn't seem right. I would expect at least the accounting modules to have clear transactional information. It is possible that such data may only appear as journals in the GL, but there should be references back to the doucments that generated them in AR, AP and so on.

Thank you for the response. Actually almost all the tables have a timestamp column. I think I can engineer incremental loads by placing delete triggers on select tables and checking timestamps for inserts and updates.

The General Ledger is transactional, and the Accounts Receivable and Payables are transactional in a way. Once an invoice is confirmed, it cannot be deleted or edited, it must be voided and a new one created. Therefore, if one considers transactions at that grain, then I can create all three fact table types in those modules.

However, given the terrible way the system handles incoming sales orders, quotes, purchasing, and other modules; can one of you suggest an approach to create (or maybe approximate) those table types?

Is checking for changes every 15 minutes a feasible plan to approximate transactions or is that a waste of time?

Perhaps I can create accumulating fact tables that would require certain business flows to be followed in order for them to be accurate. Something like, once a sales order is closed, it needs to remain that way. If you need to sell more product, you must create a new order. What do you think?

If you are able to add triggers (in a lot of shops that is not an option) then you could, potentially, solve your problems. You can capture before and after (or just after) images into a simple flat table and use that as your source. Another alternative, would be to use the database logs, a bit more complicated depending on what tools you are using.

Also, using triggers (update, insert and delete) avoids the need for the 'check for changes' bit since every change would be captured automatically.

Finding new insert, delete or update records can be done, but you have to be creative.
Save all records from your system at the end of the business… I shall call it data file1. Get all records from OLTP system at end of the business… I shall call it data file2.

If record exists in file1 but not in file2, then those records are your delete.
If records exist in file1 and file2 and their timestamp is different, then they are your updates.
If records exist in file2, but not in file1, then they are your New inserts.


Different fact tables you want to have is based on the business user and what kind of report they want to see.

Transaction Fact Table

You can have transaction fact table which shows all the transactions (inserts and update) from previous day. Have real time transactions in a different fact table.

Accumulating Snapshot Fact Table

Ask you business users if they want to see deleted sale in the report or not. If the do not, then delete them out as I mentioned above. If the do want to see deleted item than have a flag on the fact table marking it ‘deleted’ and a date when it was deleted.

Ask your business user if a sales reopened, should you update pervious transaction or keep the previous transaction separate from reopened sales. You can use SCD type 3, if they want to keep previous transaction measures.


Periodic Snapshot

Work with your accounting or finance group to figure out how to spread out sale amount. They cannot show sales amount as revenue which has not received, so they know how to spread out sale amount.

I hope this help!!