We’re kicking off a series of videos to answer “What is a data warehouse?” and “Data Warehouse Automation” with our first video: Introduction to Data Warehousing. This video is ideal for people with a business background who would like to learn more about data warehousing, specifically:
- What a data warehouse is
- How a data warehouse delivers business value
- How a data warehouse is structured
- How a data warehouse is used
We hope you find the video below informative.
Notes from the video are provided below. The slides can also be found on SlideShare.
What is a data warehousing?
In order to correctly understand and make use of data warehousing, you need to understand the problem you are solving.
It may be a decision that needs to be made, or a question that needs answering. For example:
- A marketing manager might want to understand where to invest their online advertising dollars
- A call centre manager might want to know the optimum number of staff to hire for their call centre, or
- A sales manager might want to identify the customers that deliver the most profit – so they can find more of these customers.
We want to make an informed, and objective, data driven decision, and we need some data to answer these questions.
At an organisation, you may have data being collected in a variety of places. This may be:
- From internal systems, such as a finance system or ERP, customer systems such as your CRM, HR / payroll, or operations systems such as a manufacturing system
- From web apps, such as Salesforce and Xero, Google Analytics, or social media such as Twitter and Facebook
- From spreadsheets and flat files
- From other cloud sources
- Or from any other data sources within the organisation
However this data is likely to be:
- Not structured for reporting
- Hard to access
- Captured in a silo and not integrated with all the other data to give a complete picture
This is where data warehousing comes in. Data warehousing allows you to:
- Extract data from your organisational systems
- Load it into a centralised location
- Transform and integrate the data into a format optimised for analytics
The data warehouse can be used as a source for your data visualisation tool to provide reports & dashboards, for advanced analytics, and for a variety of other purposes.
So how does this create value for an organisation?
- A data warehouse creates a single source of data that is consistent in format, structured in a uniform way, contains complete and accurate data that can be relied upon, and is up to date
- It is structured and designed specifically to allow data to be accessed quickly
- It provides a single integrated view of an organisation by combining data from multiple sources
- Provides a complete data set, allowing you to analyse data from the past to predict the future
All of this delivers value to the business by:
- Giving managers access to the information they need more quickly and easily, with significantly less ongoing effort to prepare the data
- Providing information about the business environment more quickly which means that managers can respond to changes rapidly
- Providing information more frequently
And leads to more informed managers, making data driven, objective decisions – for which you’ll see the results in your organisation’s bottom line.
How is a data warehouse structured?
At its most basic, a data warehouse is a collection of tables containing data structured in a way that is optimised for reporting and analytics.
There are different ways that the data can be modeled. The best way to model data to support decision making is a dimensional model, sometimes known as a ‘star schema’ or a Kimball model.
At a high level, the tables in a dimensional model can be categorised into two different types:
- Fact tables (also called event tables) contain individual business events (e.g. sales transactions), or aggregated, summarised business events (e.g. sales by month). They consist of measures and calculations such as sales amount and discount rate.
- Dimensions contain descriptive attributes – fields that describe the measures in the fact table. They allow you to slice and dice your data. For example, a given sales transaction relates to a customer, a product, a salesperson, and a date.
So you could look at, for example
- The transactions by salesperson for a given product
- The top customers by revenue
- The overall trend on revenue over time
- If there is a relationship between discounts given and sales
Data warehouses usually contain multiple star schemas for analysing organisational data.
How are data warehouses used?
A data warehouse contains data that is used to support business decisions. It can be used in a variety of ways:
- With a Data visualisation tool such as Yellowfin, Power BI, Tableau or Qlik, to provide reports and dashboards to managers to allow them to monitor and manage organisational performance, share information around the business, drive processes or send out tasks.
- With Advanced Analytics such as network and cluster analysis, forecasting, data mining, sentiment analysis, simulation modeling, etc. There could be a two-way feed between the data warehouse and advanced analytics.
- As a source of data to feed into other organisational systems, for example, into CRM for future marketing after customer segmentation analysis.
Stay tuned for the next video in our data warehousing automation series: Introduction to Data Warehouse Automation!
Do you have people in your organisation who spend most of their time using Excel to manually “glue” together data from for different systems? Do you only get monthly reports because weekly reports take too long? Do you have to wait too long for ad hoc data questions to be answered? If you answered yes to one or more of these questions then your organisation may gain a lot of value from a data warehouse. Minerra’s consultants have a lot of experience working with many type of organisation to help them quickly build a cost effective data warehouse that significantly reduces the manual effort required to prepare the data for regular reports and ad hos analysis. Contact us for a casual chat to see how we can help.