Open Source Columnar Databases

Columnar databases are actually SQL databases with a different storage format where data is organised according to columns rather than rows. This is far more optimal for fact table queries.

If cloud hosting is a the only option, then the best solution is probably Amazon Redshift. However, if you need to install and operate your own the open source options (which have not been evaluated) are:

• CitusDB: http://citusdata.com/blog/76-postgresql-columnar-store-for-analytics
• MonetDB: https://www.monetdb.org/

If these are not suitable, there is also a commercial option in Infobright: https://www.infobright.com/

Data Warehouse Basic Concepts:Part 2

Dimensions

Dimensions are used to provide context to facts. They can also be drilled down upon. Dimensions can either be orthogonal to each other or related via parent-child relationships.

When there are dimensions related via parent-child relationships, Dimension Hierarchies are formed.

Dimension Hierarchies

There are many aspects of dimension hierarchies.

Level-Based vs Value-Based.

1. Level-based: clearly defined parent-child levels.
2. Value-based: parent-child relationships within values at the same level (e.g. people relationships).

Balanced vs Unbalanced

1. Balanced: all branches descend to the same depth.
2. Unbalanced: some branches stop having children at certain depth where other branches continue having children.

Ragged

1. Null values can appear in the middle of a hierarchy, with child values continuing beyond. i.e. it is possible to skip one level.

Simple vs Multiple Path vs Parallel Path

1. Simple: dimension hierarchies do not cross (i.e. levels & values overlap).
2. Multiple Path: dimension hierarchies cross, but criteria for analysis is same.
3. Parallel Path: dimension hierarchies cross, and criteria for analysis can be different (e.g. city in business unit vs location hierarchies). 

Other Notes

1. Each child dimension value can only have one parent.
2. Dimension hierarchy data are usually normalized in source data and staging area, but flattened into a single dimension table in the data warehouse.
3. Create a root node at the top that represents "ALL".

References

1. www.informationweek.com/news/software/bi/showArticle.jhtml
2. www.executionmih.com/olap/data-warehouse-olap-hierarchy.php
3. andyblg.wordpress.com/2010/01/29/oracle-olap-hierarchies-and-levels/

Data Warehouse Basic Concepts:Part 1

Dimensional Database

A Data Warehouse system is used for OLAP (Online Analytical Processing) as opposed to OLTP. Because of the requirements & nature of OLAP, a Data Warehouse typically uses a relational database for storage but the database design is non-relational. Instead the design follows the Dimensional Database principle, which is very different from Relational Database principles. For example, the following are direct opposites from what is typically done in a relational database design.

1. Maximum data redundancy
2. Minimum joins
3. Minimum normalization
4. Maximum indexing
5. Maximum derived data

Components

A Data Warehouse system typically consists of the following components:

1. Reporting system: used by the end-users to query & view the information in the data warehouse.
2. ETL pipeline: used to extract raw data, transform them into the dimensional database format, and load them into the data warehouse. 
3. Data Warehouse: storage and organization of data to be queried.

Raw Data

The raw data is usually extracted from operational systems, sometimes from various sources (e.g. ERP, Accounting, CRM systems etc.).

In many cases, raw data is also consolidated in an "operational data store" (ODS) before being put through an ETL pipeline, and during this stage various operations may be performed (e.g. calculations, aggregation, reconciling & matching data from different sources etc.).

ETL

ETL stands for Extract, Transform, Load.

From the ODS to the ETL pipeline, the first place the data goes is usually the Staging area.

The raw data in the staging area is then transformed. Many operations are done here. They are listed in this article.

After transformation, they are loaded into the data warehouse according to how the fact tables are to be organized within the data warehouse.

Data Warehouse

The data warehouse is responsible for storage of transformed data ready to be queried.

The data warehouse has the following data:

1. Fact Tables: de-normalized tables containing the facts to be queried. Also contains foreign keys to dimension values.
2. Data Dictionary: contains information about where to lookup information (e.g. fact tables, fact table partitioning schemes, measures & dimensions etc.)
3. Master Data: contains dimension values.

References

1. en.wikipedia.org/wiki/Dimensional_database
2. en.wikipedia.org/wiki/Operational_data_store
3. Difference between ODS & Staging

Review of Open Source Data Mining Software

Review: www.junauza.com/2010/11/free-data-mining-software.html
Featured Software:

1. Orange
2. RapidMiner
3. Weka
4. JHepWork
5. KNIME