Companies use Data Warehouses in order to manage and analyze their data and find insights and opportunities.
Data Warehouses enable data analysts and executives to spend more time understanding what the data is saying instead of collecting, cleaning and organizing the data. It may be especially useful if you are running your company on multiple systems, such as a Microsoft Dynamics ERP system, or Sage or QuickBooks, Epicor and also using other systems such as Kronos, ADP, Salesforce CRM, Google Analytics, NetSuite etc.
Example of When a Data Warehouse Might Be Needed
A Data Warehouse is a collection of data that has been extracted for one or more systems, and then organized for easy analysis. Let’s see an example. Suppose you are interested in analyzing the overhead costs and direct labor costs so that you can price your products more accurately. Your company has an accounting system and another system that tracks time worked by your employees, and you maintain the payroll details for all employees in spreadsheets.
In order to analyze your direct and overhead costs on a regular basis, you would have to combine the data from all three sources (the accounting system, time tracking system and the various spreadsheets). You would have to create a new spreadsheet, and paste the required fields from each source into the spreadsheet. Since the data is typically at different levels of detail (maybe the accounting system contains one lump sum entry for overhead payroll, and not broken out by individual employees, as the spreadsheet does), and the names don’t always match (the department names and employee names in the time tracking system aren’t written exactly like they are in the spreadsheets, so a VLOOKUP won’t work), there is considerable manual effort involved.
Building a Data Warehouse can resolve such recurring manual work. A well-designed Data Warehouse can automate the extraction and loading of the data from each of the systems. Any transformations required (such as changing the spelling of a name, or filling in blank values, or standardizing time periods etc.) can be done automatically. New ratios and metrics can be computed, and “High/Medium/Low” buckets can also be created based on your business rules.
Once the Data Warehouse has been built, you are then able to concentrate on analyzing the data and finding opportunities for improving your operations. Automated alerts can also be set up so that if a metric is above or below a threshold, you are alerted proactively instead of having to discover the discrepancy.
Justifying the Need for a Data Warehouse
Data Warehouses are best built gradually in order to satisfy the lack of analytical capability. A return on the investment is fairly easy to compute by taking into account cost avoidance and revenue increases. In our consulting practice, we regularly come across customers whose senior employees spend many hours in Microsoft Excel cutting and pasting data and computing new columns and performing lookups. Those tasks can be automated, and the team is better off spending their valuable time understanding what the data is saying.
For our customers (midmarket and smaller companies in Michigan), we build data warehouses with data at the most granular level of detail, for example, general ledger transactions or invoice line items. This enables our customers to perform rich data analysis and also monitor their operations without having to spend time in Excel exporting/importing and copying/pasting.