Aiding the Analyst - Tips for the Hardware Guys

Information Technology is the heart of almost every business - even though they are often viewed as a necessary evil.  At some point the IT staff is going to be called upon to aid another employee in getting data from their transactional systems for reporting purposes.  Having come from an IT environment that was dominated a by hardware-centric staff aspiring to be network administrators, I have a few tips that will make aiding analysts much easier.

*Note - I'm not going to reinvent the wheel and write lengthy tutorials.  These are just techniques I've learned over the years that have helped me and my staff.

Learn Basic SQL

In order to troubleshoot most transactional systems, you need to have at least a rudimentary understanding of relational databases.  Learning the basics of writing SQL is not difficult and there are plenty of resources to help you out.  IT professionals should have a basic understanding of tables, unique rows, keys, and relationships as a foundation for the basic SQL statement.  Knowing how to construct a basic SELECT statement is the logical progression from there.  Minimum basics - SELECT statement structure, FROM clause and joining 1 or more tables, join types, and WHERE clause.  I would say that the minimum is just selecting rows from one table but when working with analysts you will almost always be using multiple tables.

Utilize Microsoft Office Tools

Excel and/or Access can be good alternatives until you are comfortable writing SQL statements.  Both can connect to your database via ODBC connections and offer graphical interfaces for query creation.  If the request is a fairly simple data-dump, Excel is going to be easier.  If you have a need for more complex data manipulations, Access is the way to go.  In Access you can set up your ODBC connection then link directly to the tables in your database and use the built-in wizards to help you build the queries you need.  The resulting datasets can then be exported to Excel.  The best part of utilizing MS Office is that you can train most analysts to use these tools to pull the data for themselves.  (If you're feeling adventurous look into PowerPivot for Excel)

Employ User-Defined Fields to Facilitate Reporting over Multiple Systems

If you don't have a data warehouse that consolidates your data combining data from multiple systems can be challenging.  I worked in the gaming industry for over 10 years and no matter the size of the property there are at least two transactional systems in use and it can often be advantageous or even required to combine data from both into a single view.  Save the analyst (or you!) from having to manually enter data from reports into a spreadsheet and find out if there are any user-defined fields available that you can configure to contain foreign keys.  (See Step 1 )  I think this will be best understood with an example.

I was employed at a property that had a casino floor/CRM system, a point of sale system for food and beverage and retail, a hotel management system and an accounting/payroll/HR system.  What do they all have in common?  Employee IDs  and each one unique.  IT was responsible for security in all these systems I needed to find a way to make processing terminations seamless.  I was provided with a list of terminated employees on a regular basis but had to manually review each system to revoke their access so when we changed vendors for the casino floor system, I took that opportunity to institute a process that would make things much easier.  In the new system there was an additional field on the employee set-up screen that we used to hold the employee's "master" employee number from the HR system.  NOT their SSN but their unique identifier.  I created a report that joined the HR employee table to the casino system employee user table which returned all the information needed to revoke the access of any terminated employee.  It also allowed for additional reporting, like system access by title, that helped make our department more efficient as well as compliant.

Make the Case for a Data Warehouse

OK - I’m more than a little biased here but if you have multiple, disparate systems that are reported on daily you probably need a data warehouse or data mart.  Research the options and tools and formulate the ROI.  If it is favorable, start making the case to management and solicit help in this regard from the analysts.