Questions? Call 888-404-2542

From data to a data warehouse

What if I told you that if you build a Data Warehouse you will increase the efficiency of your company saving you money and time?

You heard it right. You can increase your efficiency, or rather, decrease mundane and worthless tasks that your team is doing in order to do reporting. What kind of reporting? Well, pretty much anything they do should be measured or reported otherwise how can you improve? How do you know you are doing better now than before? If you don’t measure it, it may not be worth doing.

But reporting comes with its own challenges. Mainly the days of copying and pasting are over. I mean, it used to be easy to walk down the hall and ask “IT” for the report in Excel. You would then receive the email with these week’s numbers and go at it with all the pivoting and conditional formatting you could muster.

You pick a few charts and you save it on your desktop and you send it to your team via email. You would then get a few email replies stating that the data for a specific product is incorrect, that our chart is measuring the wrong thing and you forgot to select this other column that is why the SUM is not matching the total. Some of your colleagues would even be nice enough to correct the formulas or complement your spreadsheet and send it back to you thinking that you would magically know what changes they did. Anyway they changed the entire color scheme of the conditional formatting because it was cooler in red and blue and not orange and green.

That is when you turn to IT to save the day and ask them to standardize reporting for you. Let’s setup a project to build a data warehouse and standardize all of our systems and after some hires and purchase of thousands of dollars in software licenses and servers we can get you a state of the art data warehouse that you – the person requesting it in the first place – will not have access to change or modify unless you go through IT, or rather the consultant currently writing reports.

So you go back to doing our own cooking in Excel because the new system is really not what you wanted, it is slow, just as wrong at times as you were with your own Excel calculations and you end up exporting the data anyway.
This is, unfortunately, the truth in many companies that have the need for more than a single sheet Excel.

Let’s take on a simple example of a small company that has 1 person responsible for consolidating data and building a spreadsheet (Excel or some other tool) every week on their laptop. This person gathers data from an invoice system, a simple eCommerce/Web site sales system and another spreadsheet that contains expense data. Three data sources that are not connected. The process of gathering the data from the 3 different systems involves some type of report that you set parameters and export to – you guessed it – a spreadsheet.

This person has 4 or 5 spreadsheets and starts doing some pivoting around, some filtering and cleaning up on each and puts it all together on a “Master” spreadsheet that combines specific data from each. This Master spreadsheet becomes your central focus, and you continue to improve it and make it a serious focus of your weekly reporting process. It’s this multi-tab, color coded spreadsheet filled with hidden references, copying and pasting and SUM and VLOOKUP formulas that your team will have to go through each time you send it via email.

How much time over the course of a year would you say you spend on doing all of the above in a weekly or monthly basis? 2 hours a week or maybe more like 10 hours a month?

Communicate better with a data wasrehouseNow imagine that your company is a little larger and there is not one person doing the above but 3 people (financial, sales/marketing and the CEO), how much time do you think the entire company is spending and how much time to align all key metrics across the company and the 3 people?

If you have regional sales people for example, wouldn’t you prefer to have them spend 10% more on sales or with customers rather than putting numbers together for you or reading your spreadsheet that shows their performance (and that you had to painstakingly create multiple versions for so that each region gets their own numbers).

This is not the way to run a business.

A Different Approach

Here’s the thing, automating data into a centralized database is really simple if you have the right tools. Once the data is in one place it makes it easier to build reports (even if you use Excel). But if the data is all over the place; a cloud based CRM, a few Excels, and an internal finance system, then it makes it very hard to automate since you need to export and copy and paste data from all over the place to get a single view of how your company is doing.

This is the basis of a data warehouse. A Data Warehouse (DW) is the core of any business intelligence (BI) platform and its job is to integrate data from different data sources regardless of where they are located. Software such as Tableau and QlikView are not BI tools, they are data visualization tools, much like Excel. They are not databases, they don’t connect data and merge your data sets, you need additional tools for that.

A Data Warehouse is basically your “Master” spreadsheet but on a proper database capable of easily expand and join additional data sources, that can easily filter data per region or per person or per product, and that has all available dimensions and metrics that are important to you ready to visualize in a consistent and global manner.

You then “plug in” your visualization tools, Excel into them to extract the data and visualize it, but by now the hard part of the job is done for you. And more importantly, if you are using the right DW it is done automatically.

Many look at this as a reporting problem that needs to be addressed but in fact this is a data problem first and foremost. Solve this issue with a Data Warehouse and you will reap the benefits of fast data analysis, lowered costs, less time spent on non-critical tasks and a better centralized view of your business.

Finally, there is one benefit of building a Data Warehouse that is not easily measured: that by building one, you will understand more about your own company and how you are really – really – progressing. You see, it will give you a global vision of all metrics in one shot, the process of building it will give you the knowledge of how things operate at a lower level in your company. This knowledge is priceless.

Happy Data Warehousing!