What is a Data Warehouse ?
In computing, a data warehouse or enterprise data warehouse (DW, DWH, or EDW) is a database used for reporting and data analysis. It is a central repository of data which is created by integrating data from one or more disparate sources. Data warehouses store current as well as historical data and are used for creating trending reports for senior management reporting such as annual and quarterly comparisons
So why would you need a data warehouse? The answer to this question is fairly simple
a) It gives you increased analytical performance
b) It will record historical data on dynamic data
c) It can consolidate more than 1 data source
d) It can transform raw data into meaning words
Performance
One of the main reasons for the creation of a data-warehouse is instantaneous performance of calculating totals and slicing variables. Excel 2010 is good for analytics. Excel 2013 is better, but they still start to get slow when dealing with large volumes. The following report is an example of a performance issue that was resolved with a data-warehouse.
The customer wanted an inventory analysis which gave them the ability to compare inventory levels, week by week. Each day there would typically be 10,000 transactions, therefore each week 70,000, and they had been running the software for 3 years. The problem with most ERP systems is that they typically don’t keep summary historical records. Therefore in order to summarize the 2nd week it is not 70,000 but 140,000 and each subsequent week it would compound. Technically I was able to create a stored procedure that calculated this, but it took 7 hours to process and was terribly slow to analyze.
Hence the need to run the query every week and copy only the net changes to the data warehouse that stores the summary values and detailed transactions.
There is some performance gained from a data warehouse, but mainly it’s from the fact that you can add indexing to your tables and you don’t have an entire company (800 people in this instance) accessing the database all the time.
True performance comes from OLAP cubes https://en.wikipedia.org/wiki/OLAP_cube , which basically pre-summarizes your potential combinations of data elements.
Historical Data
This is a classical sales problem. Your sales manager asks you how many opportunities were open, won and lost by month in comparison to what they did last year
This might be an example of what he is looking for.
The problem is that there is typically only 1 record, 1 date field, 1 status field and if it was open a year ago, it might now be closed and therefore the report is impossible to build. This concept applies to sales orders, purchase orders, work orders, etc.
Consolidating Data
Another reason why you might need a data warehouse is to consolidate information from 2 or more data-sources. For instance your ERP system may or may not be able to handle the most common problem which is multiple companies. Or perhaps you have different ERP systems between 2 companies.
This is an interesting example, in which the owner of the company wanted to see immediate what his real-time valuation of inventory was based on the silver spot rate. Using data from the commodities index you can map it back historically to your inventory by date or project forward. This allowed him to see the high and low season of his product next to historical silver prices. Once again, this is not possible without a data-warehouse.
Transforming Data
Another simple example is simply a translation of information
For instance, in the database you might see values just as 1 or 0. The below example shows Manufacturing Policy as a 0, but this doesn’t mean anything to a user.
In this next illustration, I have transformed the 0 into the word Fabricated
This means that if the user is trying to use the slicers in excel or parameters they are no longer cryptic and self-explanatory.
High level Overview of a data warehouse
This is a high level overview of the schematics of a data warehouse
Vox ISM has pre-built data warehouses and the OLAP cubes for both CRM and NAV
If you think you need a data warehouse and want to learn more. Please come on one of our web sessions to learn more. Its on our coming events page on www.voxism.com