The Newcomers Guide to Data Warehouse Software

“Data warehouse” is a term that’s been used in business intelligence since 1990. It refers to a collection of data that’s kept separate from day-to-day operational data, such as transaction records, in order to provide a consistent historical dataset. For that reason, the information in a data warehouse must be unchanging and integrated as a cohesive structure that can be used for different analytic goals, on different types of business intelligence software.

Why use a data warehouse?

A data warehouse provides the basis for executives to understand collected business data across variants such as time or location. Suppose a marketing director is looking for a history of seasonal differences in the sale of “EZ Cleaning Product” in the Midwest. He/she could extract such results from the data warehouse over the past three years to look for buyer patterns that could potentially improve future sales.

This is a very common business need; 89% of American businesses invest in data and data analytics. This includes retailers, manufacturers, financial institutions, and any business large or small hoping to gain productive insights from review of their past business data.

A data warehouse provides companies a multidimensional view of this consolidated information. This can include any relevant information in addition to time and place; it may include promotional and product information, buyer demographics, catalog or website identifiers, payment types, and a wide cross-section of data to meet various business goals for analysis or prediction. The data is structured and subject-oriented to provide the fastest and most accurate results.

Business intelligence software allows analysts reporting features to aggregate this data by totals, averages, etc., at various levels of granularity, or detail. With BI software, yearly total sales of a specific coffee maker could be automatically broken down and referenced by month, week, and day, a concept known as “drill down”.

ETL

Data warehouses do need to be frequently updated in order for the information to remain relevant. This is done through a process known as “Extract, Transform, and Load”. This is normally automated to upload information from various sources, such as websites or points-of-sale (POS) at physical stores. This may be scheduled for evening hours when network use is at a minimum.

The data is saved to files, delivered to IT servers via the internet, then subjected to a transformation process to remove any useless or redundant data. This “scrubbed” data can then be organized and formatted to be loaded directly into data warehouse tables. Most ETL procedures will also include functionality for backing up files or delivering alerts if the ETL process encounters an error.

BI software can automatically “refresh” datasets using newly loaded information if required.

OLAP

BI tools use several methods to process data, typically based on Online Analytical Processing (OLAP) architecture. OLAP allows for the multidimensional “data cube” concept, where, at its simplest, data can be seen from two dimensions, usually time and a logical concept such as store or product, and a useful numerical measure. There are three common implementations of OLAP:

1. ROLAP

Relational OLAP operates between back-end data servers and front-end client applications. To manage the data, ROLAP uses the basic ideas behind a standard DBMS (database management system). Related data is stored so that it can be linked by identifiers known as keys and foreign keys in a one-to-many relationship; for instance, all purchases by a certain customer. ROLAP includes navigation logic for more efficient aggregation, optimization for existing DBMS data stores, and any additional services the BI application provides.

2. MOLAP

Multidimensional OLAP employs array-based storage engines; that is, every item of data is stored in a space of memory with it’s own ID. This allows the use of data sets where information may be missing. MOLAP may use two levels of algorithm to locate requested information, one for sparse and one for dense data.

3. HOLAP

Hybrid OLAP combines both ROLAP and MOLAP. This dual approach provides faster aggregation of MOLAP datasets and better scalability of ROLAP data. HOLAP allows the data engine to handle larger ROLAP volumes, while preserving the aggregated data in MOLAP stores for faster retrieval.

OLAP advantages

BI applications can quickly load information into pre-defined data cubes to allow for reporting with the following capabilities:

* Roll-up

This allows for aggregation of data as a user climbs up through the data hierarchy established in the cube. For instance, total revenue per sales people at a particular store could be rolled up to total revenue per store, then revenue by city, by state, and so forth.

* Drill-down

As mentioned above, drill-down is the opposite of roll-up as it allows access to increasingly detailed levels of the hierarchy. Uses could discover how many prescriptions were filled at a certain pharmacy chain last year, then drill down to find out how many were filled per store, and ultimately drill down to count prescriptions filled by a certain pharmacist on a specific date.

* Slice and Dice

Slicing data means selecting one dimension from a cube to create a sub-cube. For instance, yearly sales figures could be split off into just sales figures for the third quarter.

To “dice” is selecting multiple dimensions to create a new sub-cube. For example, a user in the above example might select “Q3” but also sales for only “Chicago” stores and “XYZ” products.

* Pivot

Pivot operations are a rotation of dimensions, or changing perspective to a different data axis. Pivot reports are usually shown in a spreadsheet-type format. A pivot report showing percentage of sales by store (column) by month (row) could be easily pivoted to show sales by store (row) by month (column). While this doesn’t change the sales figure, it allows users to organize the same information in different ways.

This level of interactive data warehouse reporting is useful to employees across the enterprise. Professional analysts can use BI software to generate sophisticated data mining models by associations, clusters, classifications, and hierarchies in order to explore or predict more complex concepts.