What is a data warehouse, and when do you need one?
There’s many uses for data warehouses but I’m gonna talk about when we need to use them for dashboards 📊.
Usually it’s when the software you want to connect to has a bad API, which stands for Application Programming Interface, and is a topic for another day!
Sometimes it’s bad because they want to limit how you use it 🤐
Sometimes it’s bad because the software is poorly coded 🤡
Similar to above, sometimes it’s bad because they made poor decisions when they were designing the software and now they’re stuck with it 😩 Xero admit this in their API documentation!
Sometimes it’s bad because they’re cheap skates and don’t want to accommodate other software querying it 😤
Sometimes it’s bad because the software is so old it’s amazing it even has an API 👴
Anyway, when we come across a piece of software like this, a method that we can use to get around it is use a data warehouse 🏢
This is a piece of software that sits in the middle and works with the bad API, coaxing and cajoling it to give up it’s data, and then stores it in another place, usually a SQL database or similar.
Now your data is arranged logically and neatly and we can then make simple and fast 🏎 SQL queries to get the data.
You can also use it for other purposes than a dashboard, for example updating your website 🕸 If we can’t connect to your software then your web designer probably can’t either.
By the way these are the bits of software that we sometimes need to use data warehouses for:
- Cin7
- HubSpot
- MYOB AccountRight
- Simpro
- Xero
Usually we will know up front if we need to use a data warehouse and will include it in the fixed price quote.
Sometimes it only becomes apparent after we’ve started your dashboard. Because it is an additional cost, we always give our customers the option to cancel the project and receive a full refund.
And just like a normal warehouse, you need to be careful how you store and move your data, otherwise you’ll end up with a mess!