Real Time Data Warehouse ETL

Part 2 – Implementing Service Broker as a Real Time ETL Tool – The Code

To implement my Service Broker ETL API, I started with the basic code/framework provided by Eitan Blumin in this blog post: http://www.madeiradata.com/service-broker-asynchronous-triggers/. He provides a good basic framework for the setup of Service Broker and an explanation of the installation scripts. To learn more on the basic framework and scripts to implement Service Broker please refer to his post.

In this post, I’m going to provide his scripts for the basic installation with some modifications to work with Dynamics GP database and Solver’s BI360 data warehouse plus my modified stored procedure, usp_AT_iGLETL, that provides the logic of the ETL process from Dynamics GP’s general ledger table, GL20000, to Solver’s BI360 staging table.

General Workflow

Eitan’s general Service Broker workflow looks like this:

1. The user performs an UPDATE operation on a table. In my case with the modifications in place, the posting of a General Ledger batch within Dynamics GP will be the UPDATE operation on the GL20000 table.

2. An AFTER INSERT trigger on the table is fired. This trigger compiles the contents of the INSERTED and DELETED tables into XML parameters, creates a Service Broker message and sends it to a queue. The original transaction immediately returns. The modifications to the AFTER INSERT trigger pass the usp_AT_iGLETL stored procedure into the SB_AT_Fire_Trigger stored procedure.

3. The Service Broker service fires up and processes the messages in the queue independently of the original transaction. It opens up a transaction that will pull the message out of the queue, execute a relevant stored procedure that will use the XML data previously taken from the INSERTED and DELETED tables, and implement the relevant logic within the usp_AT_iGLETL stored procedure.

Service Broker Framework Installation and Usage

The installation script provided below implement a generic Service Broker framework which will allow you to use it for any table without any changes to the installed objects. The framework will work like this:

1. First, run the “Step1_SB_AT_Installation” script on the Dynamics GP company database where you want the asynchronous triggers.

2. Run the “Step2_GP_GLTable_Trigger” script on the Dynamics GP company database to create the AFTER INSERT trigger on the GL20000 table.

3. Run the “Step3_GP_ETL_Logging” script to create the basic framework for logging the Service Broker ETL process and error logs.

4. Run the “Step4_BI360_f_Trans_GL_Staging_Table” script to create the staging table for in BI360DW database.

5. Run the “Step5_ETL_StoredProcedure” script on the Dynamics GP company database to create the stored procedure, usp_AT_iGLETL, with the ETL logic.

Download Code

You should now have in place a “Near Real Time ETL” process in place to handle the loading of data from Dynamics GP to staging table within Solver’s BI360DW data warehouse database. You could then use the same logic and scripts above to handle the loading of the data from the staging table to the f_Trans_GL table within the BI360DW database. In my tests of the implementation outlined above, I was able to post a 350 line batch within Dynamics GP and load the staging table within five seconds.

Conclusion

Hopes this helps you with the basic understanding and installation of Service Broker as an ETL tool in loading your data warehouse..

Using BI360 and SSIS to Automate a Daily Flash Report

Have you ever had to link multiple spreadsheets together to generate a “readable” or “useable” report? Back in a previous life/career, I was a Director of Finance at a casino, where I provided Daily Flash Reports to management. The reports were quite detailed with slot, table games and restaurant sales data. It would generate KPIs and a summary income statement for the day, month to date and year to date figures for current year, previous year and budget (screen shot below).

clip_image002[1]

This was a huge undertaking that linked three detailed Excel spreadsheets for current year actuals, previous year actuals and current year budget to one summary Excel spreadsheet. Each day’s revenue and expense numbers needed to be input into the current year actuals Excel spreadsheet. The entering of the detailed transactions would take hours to complete from a manual entry standpoint. Then more time was needed to review the final output, make sure links were not broken, formulas were totaling correctly and there were no data entry errors in the linked spreadsheets. In total, I would say we spent 3 to 5 man hours every day to produce the report, before it was sent to management. At the end of the year, we would spend another 30 to 40 additional hours setting up the spreadsheets for the next year. It involved updating the previous year actuals with the year’s data that was about ready to become the previous year, adding in the next year’s budgeted numbers and clearing out the actual year spreadsheet. That’s a whopping 1,865 hours per year spent on producing one report!

Now I jump into the DeLorean with Marty McFly to get back to the present and I find BI360 with its Data Warehouse capabilities. If I still worked at the casino, I would set up dimensions in the BI360 Data Warehouse for each of the slot machine data, table game data and restaurant data. Then I would populate the daily detail from the slot accounting system, restaurant POS system and other systems into a fact table with SSIS (SQL Server Integration Services), providing the same end result without the huge manual process of the past. This same slot machine data could also be used to meet Gaming Regulations requirements to analyze slot machine payment out volatility or PAR percentage. (For those of you not from the casino industry, volatility or PAR percentage analysis is a way to statistically analyze the performance of the slot machine’s programmed customer win percentage to make sure that it’s performing within authorized manufactured specs based on slot machine’s current play life cycle.)  After the initial process of creating the SSIS package and designing the Daily Operating Report (DOR), I estimate that I would probably spend one hour per day on the DOR, for a savings of 1,500 hours per year. What a time saver that would have been, which would have more than made up for the cost of the software!