Why would you need a Data Warehouse?

The simple answer to this question is that most companies, irrelevant to the size of the company, have dispersed data stores.
All companies grow during their existence, and as they grow new data becomes available, as do new technologies, and this drives the outcome of multiple data stores. Either different technologies or different data types being stored together.

If you don’t have a warehouse where data is stored, you struggle with reporting across the entire business.
Its like a large retail outlet, imagine they had to order stock from suppliers directly, and not from a centralised store where they kept the minimum stock available to move quickly as and when needed.
They will constantly be out of stock on fast moving items. In the same way your data warehouse will assist you in always having available what you need to make fast decisions.

Why a Data Warehouse and not a Data base?

Is there a difference between a Data Base and a Data Warehouse? Well let us look at the differences.

A database is a collection of information organized to be easily accessed, managed and updated. While there are many types of databases available today, the most common is an RDBMS. But when most people use the word “database”, they mean a traditional RDBMS that handles Online Transaction Processing (OLTP).

So, what are some of the defining characteristics of an OLTP database?

  • Designed for rapid storage and retrieval of small sets of current data records in support of transactions and interactions within an enterprise.
  • Data is organized in tables and columns, allowing users access via structured query language (SQL).
  • Handles quick, real-time activity such as entering a customer name, recording a transaction and recording all activity for that transaction.
  • It works well for basic operational reporting of a limited number of records. Analytic reporting is forced to be simple, static reports often driven by IT.

And what is a Data Warehouse then?

So, what are some of the defining characteristics of an OLTP database?

  • A database designed to store, and process large volumes of current and historical data collected from multiple sources inside and outside the enterprise for deep analysis.
  • Normally stores subsets and metadata of larger detailed data in other data sources.
  • Organizes data into tables and columns and allows users access via SQL.
  • Optimized for loading, integrating and analysing very large amounts of data.
  • Designed to support descriptive, diagnostic, predictive and prescriptive analytic workloads.

What do we put in the Data Warehouse?

A Data Warehouse does not contain all your data from all your data sources This would simply be duplicating your data and slowing down your processing. There needs to be a analysis on the businesses current data sources, and a clear plan defined that take the valuable data from these different locations and uses key data to load to the Data Warehouse.

This is an extremely important step in any data warehouse design, you need to think of the future, what is needed, how do we add new data sources to enrich the reporting, and how to store it.

There are quite a few important design models that can be applied to a Data Warehouse, based on the needs, you need to apply the correct design and principles to achieve the correct design for your Data Warehouse.

What if I don’t have a Data Warehouse or other data storage solution:

DataSimplified has skilled architects that can assist in designing, and implementing a Enterprise grade data warehouse for your organisation, based on best practice guidelines, and your requirements. (See below section that refer to Technologies)

What technologies would be available to use:

In a BI reporting solution there are normally a few layers involved in the building of the reporting solution, these layers each perform an important role in allowing the report to be generated:

  • Reporting Layer
  • Analysis Layer
  • Business Layer
  • Data Storage Layer

For each of these layers DataSimplified can offer experienced skills to deliver your reporting solution:

Reporting layer (Presentation Layer and Report Generation)

  • Microsoft SQL Server Reporting Services (MS SSRS) 2008 – 2017
  • DunDas BI reporting tool
  • Tableau BI reporting solution
  • Open source reporting platforms like Spark

Analysis Layer (Metric Calculations and Analytics)

  • Microsoft SQL Server Analysis Services (MS SSAS) 2008 – 2017
  • Cube Analytics
  • Ab Initio DataLineage

Business Layer (Integration, Transforming of Data, Business Rules and Data Preparation)

  • Clover ETL
  • Ab Initio
  • Microsoft SQL Server Integration Services (MS SSIS) 2008 - 2017
  • SAP PI
  • Talend ETL Platform
  • Informatica
  • DataStage

Data Storage Layer (Data Bases that contain the data)

  • Microsoft SQL Server (MS SQL) 2008 – 2017
  • MySQL
  • PostgressSQL
  • Teradata
  • Oracle
  • MongoDB
  • Big Data - Hadoop

If you need assistance to get your Data Warehouse and BI reporting solution in place as soon as possible, you can contact us today to setup a meeting and plan how we can assist you.

Process of implementing a BI Reporting solution:

The process that DataSimplified uses is a simple one, that delivers a fast and trusted outcome for the customer.

  • Engagement with Business role players (Report users)
  • Document the Business requirements in detail
  • Engage with Data owners in IT to understand the data landscape at the customers
  • Document this in a High-Level solution overview document
  • Get sign off from business and IT based on the understanding of the requirements
  • Analyse Data and reporting requirements while involving the customers
  • Create high level design of the overall solution, including data sources and technology that is proposed to client (Taking into consideration what is available at the client.)
  • Sign off High Level Design
  • Detail design follows that includes the Enterprise Data warehouse if needed, as well as Reporting requirements.
  • Sign off Detail design specifications
  • Meet with customer to setup delivery plan, this might include customer resources availability as well.
  • Deliver the solution as per plan, with regular milestone meetings and reporting to the Business owners.
  • Testing of delivery with Customer
  • Sign off solution after testing is completed.
  • Support customer for any additional requirements or training after solution delivery.