Uncategorized

Data Analysis with MS Excel and MS Access

Posted

Data Analysis with MS Excel and MS Access

From Data Wrangling to Data Clarity!


Excel and MS Access are not only not dead, but thriving. In fact, these tools form the foundation for almost all projects and are also well used in many small to medium enterprises as mission critical platforms for knowledge work. For this reason, Triatin has always focused on ensuring our consultants have solid MS Access and MS Excel skills.

From complex spreadsheets with heaps of VBA and Macro code to Ms Access being used as datamart and full blown knowledge applications, and even cases where Excel is imbedded into MS Access as additional third party database tables we have seen it all. But there is one common thread running through these experiences – finding value in data! Some examples that come to mind: –

  • A midrange Australian bank, using MS Access and Sybase as a data warehouse strategy. We helped them transition to a Microsoft SQL Server platform, while keeping the value MS Access and Excel bring to them.
  • A Sydney based super fund, with the most complex Excel Spreadsheets you could imagine, with over 50 spreadsheets some containing over 100 tabs and interconnected with complex formula for financial and tax calculations. We cleaned and remodelled the installation, moving some data functionality to SQL Server while streamlining the MS Excel application
  • Department of Health and Human Services Victoria. Working with their in-house Data Scientist we developed a case management reporting technology designed for Analytics tools like Tableau. Loading data from MS Excel and using MS Access as a control interface, the SQL server database is loaded via SSIS and results in a platform that is both analytics ready and can generate clear stakeholder reports for government ministers and public officials.
  • A Government Education Department. We analysed, documented and repaired complex Ms Excel based applications to manage school data. Significant data wrangling, working with Oracle and SharePoint bases systems, we recovered models for the data team to work with, and supported their services

These a but a few of the war stories in the data analysis space that Triatin team members have worked with. In business intelligence, we find so many varieties of data management. In fact, our biggest data warehouse project makes use of CSV files for most of the data loads, as the data is augmented data from external sources.

Data analytics and data wrangling are often the same thing – and like snake wrangling, it helps to have a healthy respect for the complex nature of the data, but not to be too afraid of it to get in subdue it.

Once the data is cleaned and the storage understood, it’s time to look for the nuggets of value in the analytics tools at your disposal, and that’s what we find the most rewarding part of our job. Finding that “pregnant man” or “lost” 12 million, catching a price drop from happening too early, or even worse, too late.

Triatin has a philosophy of “The customer business is our business”. Our consultants are driven to be what the customer wants – a banker when we work for a bank, a child care worker when we work for a child care NGO or government child safety or education department, a movie distributer when we work for, well, a major movie distributer. We just use our IT skills to do that, but we always align with your mission!

Leave a Reply

Your email address will not be published. Required fields are marked *