Subject: Solving the Updated Transaction Problem


Hello and Welcome! 

This is Peter Nolan again from Instant Business Intelligence!

Thank YOU for being on my email list!

YOU are MUCH appreciated!

Today I just wanted to follow up on my email on Tuesday about creating a way to manage multi-level summary fact tables inside SQL. 

You can read that blog post on this link:

>>> Multi-Level Fact Tables Maintained in SQL<<<


One of the real bug bears of many ERPs (and other operational systems) is that they update transaction tables for data other than the amounts.

Sometimes, even when they update the amounts, they do not do a reversal transaction and then send in a new transaction.

This "updated transaction problem" has been a bug bear since I first starting building transaction fact tables way back in 1995.

We had to do all this complicated coding to determine if we had seen this transaction before and generate the reversal to send it in to the ETL subsystem to make sure our summaries stayed balanced.

A while ago I was looking at this problem again in the light of how #SeETL now generates delta detection SQL.

Much to my pleasure I discovered that if I just add one statement, which can be copied and edited in a matter of a minute, I could capture the state of the transaction record that is about to be updated and send that in to the ETL fact table processing process.

Combined with this weeks invention of being able to maintain summary fact tables using purely SQL?

We now have a way of easily making sure that when a transaction record is updated and it flows in to the ETL system again, that we reverse the prior transaction and the prior transaction amount and we feed the ETL subsystem the "new" record.

This "new record" will perform an update against the "old record" in the detailed level fact table. 

But BOTH records will be sent in to the summarisation processing. The "old record" reversing the existing contribution of the record for the existing summaries and the "new record" making its new contribution.

In this way the summaries over the detailed level fact table will remain synchronized and in balance with the detailed fact table without redundant processing for summarisation.

And all this will be done inside sql, inside the database, with no need to take the data out of the database in to any ETL tool and then put it back again. Ok? 

In case you are wondering? 

Yes, I am still amazed how little attention and effort is put in to maintaining summaries of fact tables.

I consistently talk to companies where they have issues with ETL reliability and performance and have issues with the cost of maintenance of their ETL subsystems in the face of changes to fact tables and new fact tables needing to be added.

Until very recently I still had people tell me that it is "impossible" to maintain the back end data warehouse via excel workbooks and a vb.net tool that reads the workbooks and generates what is necessary!! LOL! 

Now that #SeETL is open source and free and the code can be downloaded and inspected? None of those people who said #SeETL is impossible have come back to say "sorry"! LOL! 

So yes. We actually maintain entire data warehouses and ETL subsystems via Excel Workbooks.

As new requirements come along via our customers we will add more workbooks.

If you would like to read about the one simple and easy statement that needs to be added to solve the updated transaction problem perfectly well? 

Click on the button below. 

If you would like to ask any questions about this blog post? 

Just hit reply and ask me questions on my email, or if you want to video conference with me I am on skype at peternolan9.

If you want me to save your company time and money in your BI project efforts?

My working from my home office rate is EUR50/USD60 per hour.

Ok?

One last point. Since I am not longer welcome on linkedin?

Please share this post with all your colleagues who build data warehouses for a living. They will want to know!

Thanks for reading! 

I wish you a great day!

Best Regards

Peter

PS. You can download our open source and free #SeETL Software from here. This is the software I wrote to be able to deliver data warehouses faster, cheaper, better than anyone else. 

Yours free for being on my email list!

Instant Business Intelligence, Ludwig-Erhard-Allee 10, City Park, 76131, Karlsruhe, Germany
You may unsubscribe or change your contact details at any time.