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).

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!

Share this post

Related Posts