Let me unleash your data

Organisations of all sizes are sitting on mountains of data, but often it is inaccessible or suffering poor performance.

Data, and more importantly, turning data into actionable insights and meaningful information, is one of my great passions, and I can help you with this.

Some examples

  1. Helping an organisation uncover its SQL Server disk-related performance issues.
  2. Implementing quality open-source tools for SQL Server performance monitoring.
  3. Migrating vast amounts of data into a single database and database schema for Bluestone Global Limited, a company which had grown large via the aggressive acquisition and merger of numerous businesses. When I entered the company one line-of-business application was in use but all historic data remained in the original applications creating enormous efficiency burdens and requiring the costs and efforts of licensing and platforms to hang around. I eliminated all the legacy applications bringing historic data into a single repository.
  4. Consulting to a range of recruitment and labour-hire organisations to migrate from AXiOM by Dialog Information Technology to FastTrack 360, extracting and re-organising data and documents to suit the FastTrack structures.
  5. Creating a data warehouse and unlocking the vast amount of data for Pacific Smiles Group, a public ASX-listed national health organisation had. My work gave it access to new ways of measuring utilisation, the ability to change future performance, accelerating financial reporting, enhancing marketing effectiveness, and much, much more.

Unlocking access to data

In the latter case when I entered Pacific Smiles there were 55 separate instances of the core Dental 4 Windows (D4W) application across 55 locations, with 55 individual Sybase databases. The Finance team spent four days each month remotely logging into 55 centres, running 55 copies of their main application, generating reports and extracting information and aggregating it at head office. This was similarly repeated by the Operations team for performance metrics, by Marketing for customer information and so on. In fact, the Chief Financial Officer and I calculated 490 hours – or 3.3 FTEs – were being spent on manually gathering data for routine and standard reporting. If the CEO wished to zoom in on something specific, such as future utilisation, he had to make the decision to pull someone out of the business for a day simply to gather this data.

I established a good relationship with the software vendor but even so it was their policy – not uncommonly – not to provide any assistance or detail on the database structure.

This did not deter me; I identified the dba user account credentials, made an ODBC connection and explored the tables, fields and data. I produced a series of utilities and reports:

  1. a general-purpose utility to execute any arbitrary SQL query across the entire fleet of databases, returning the results in a single Excel spreadsheet, formatting fields as text, date/time, integer and decimal appropriately. This by itself dramatically cut down reporting time and freeing people to genuinely dig into their data, not simply spend all their time collecting it
  2. a mechanism to automatically identify visits by patients who belonged to a specific member loyalty scheme, and to calculate their reward entitlements according to compliance requirements
  3. a daily report to all centre managers detailing their patient re-bookings, along with month-to-date and year-to-date stats. This was a vital KPI for the business at that time, but was previously not measurable. My work helped make it a focus, along with supporting the centre managers by continually displaying their personal results, and those of the group as a whole.

There were more, but these items had a huge impact by themselves. The company ultimately implemented a data warehouse, the work of an internal team and an external supplier, Empired, but it was my work that gave access to the databases, identified the fields and business logic, and gave full access into the organisation’s wealth of data for the very first time.

And much, much more …

I have many other stories, but the upshot is I come with both a deep love for data and a proven track record in bringing success to organisations large and small.

I’d be pleased to speak with you about your data challenges, whatever they may be.

  • Do you have delays in getting information out of your applications?
  • Do you have a difficult relationship with your application vendor?
  • Do you have older databases/applications you are locked out of?

I can help. I enjoy it, and I’ve done it before. Let me know.

When 2x RAID 5 = RAID 10

An occasion I will never, ever forget is the time I set foot in a small business and the developers and IT Manager alike told me how their database server was in RAID 10. They spoke at length about the research they put in to determine the most performant disk configuration and came to the determination RAID 10 was best for their requirements.

The old adage, “what gets measured gets improved” is equally true in reverse; what doesn’t get measured doesn’t get improved. Noting the database was a key component of the company’s business, and that no reporting or analysis was occurring, I recommended several tools such as the free open-source dbWarden script. I speak more about this and other good, even essential, tools for keeping your database healthy over on iTWire.

The results were not good! In fact, there was significant lag. The database server was spending most of its time waiting to write – adding up to minutes every day, and many queries were taking over 100ms.

I asked the IT Manager to show me the disk setup. He took me into the server room and entered the server’s RAID configuration. Two RAID 5 volumes. We looked at each other, and he immediately said, “This was RAID 10! The systems administrator must have changed it!”

He asked that person if he changed it, he said no, that’s how it has always been. The IT Manager, never short for an answer, then confidently explained the server had been ordered from Dell as a database server so Dell “must” have pre-configured it according to theirĀ best practice.

It got worse.

Within Windows, one of the RAID 5 volumes was used exclusively for drive E:, which contained nothing but the Windows pagefile.

The other RAID 5 volume was partitioned within Windows as C: and D:. The operating system, the application, the database and the log files were all on the one RAID 5 volume albeit two Windows partitions.

Oh, and there were also a bunch of file shares, used by the most prolific file-heavy section of the business sitting on D: also.

You can be sure I made a series of recommendations, not only relating to infrastructure configuration but about staffing too.