Solution Hacker

Create 2 tables
Item(id) and Item_log(item_id, price)

Populate it
insert into item(id) values(1);
insert into item(id) values(2);
insert into item(id) values(3);
insert into item(id) values(4);

insert into item_log(item_id, price) values(1, 100);
insert into item_log(item_id, price) values(1, 100);
insert into item_log(item_id, price) values(1, 100);
insert into item_log(item_id, price) values(1, 200);
insert into item_log(item_id, price) values(1, 200);
insert into item_log(item_id, price) values(1, 200);
insert into item_log(item_id, price) values(1, 200);
insert into item_log(item_id, price) values(1, 200);
insert into item_log(item_id, price) values(1, 200);
insert into item_log(item_id, price) values(2, 200);
insert into item_log(item_id, price) values(2, 200);

Run SQL
SELECT COUNT(il.price), i.id AS item_id, il.price
FROM item i, item_log il
WHERE i.id = il.item_id GROUP BY il.price, i.id;

Result
count | item_id | price
——-+———+——-
3 | 1 | 100
6 | 1 | 200
2 | 2 | 200

Run SQL
SELECT COUNT(il.price), i.id AS item_id, il.price, array_accum(il.id) AS item_id_array
FROM item i, item_log il
WHERE i.id = il.item_id
GROUP BY il.price, i.id;

Result
count | item_id | price | item_id_array
——-+———+——-+—————
3 | 1 | 100 | {1,2,3}
6 | 1 | 200 | {4,5,6,7,8,9}
2 | 2 | 200 | {10,11}

To build data warehouse, you will use the techniques of dimensional modeling. Here are the guidelines you can follow:

1. Divide the world into measurements and context.
2. Numeric measurements place in Fact table whereas context are broken down into Dimensions. A fact table in a pure star schema consists of multiple foreign keys, each paired with a primary key in a dimension, together with the facts containing the measurements.
3. Build the FK-PK pairs as surrogate keys that are just sequentially assigned integers.
4. Use a special record in Dimension to represent unknown or no because we want to avoid putting null as FK.
5. Resist snowflake the dimensional tables and leave them in flat second normal form because the flat tables are much more efficient to query. Snowflaking a dimension into third normal form, while not incorrect, destroys the ability to use bitmap indexes and increases the user-perceived complexity of the design.
6. Semi-additive fact – Most fact tables are huge, with millions or even billions of rows, you almost never fetch a single record into your answer set. Rather, you fetch a very large number of records, which you compress into digestible form by adding, counting, averaging, or taking the min or max. Bank balance and inventory levels represent intensities that are awkward to express in an additive format. You sum over balance for 1 month is not really meaningful. Normally, we still treat these semiadditive facts as if they were additive but just before presenting the results to the end user, divide the answer y the number of time periods to get the right result. This technique is called averging over time.
7. Slowly changing dimension -
8. Hierarchical Dimension – There are 2 types of hierarchies. One is “Parent-Child” relationship and the other one is “Array of Level”. Array of level like Country -> State -> City -> Store. Parent-Child like product categories that can be nested in different ways.

For those who don’t want to go for licensing path. Open source is definitely a better solution. However, whether open source DBMS can be used to build your data warehouse? I am not a good person to answer this question. But I have seen more and more small and medium size companies launched their business intelligent platform powered by open source DBMS like PostgreSQL and MySQL. Before MySQL v5 released, I don’t recommend to use MySQL for data warehouse because it missed some of key features that others provide like trigger, stored procedure, partitioning. But now, I suggest to revisit it, especially I have heard MySQL become a golden partner with the great open source business intelligent platform “Pentaho”. Below is a rough comparison chart for DBMS I got from devx.com. Take a look at it first.

There are debates about whether we should choose PostgreSQL vs MySQL. Here is one case study that shows PostgreSQL is better in OLTP system. However, for select query, another study shows MySQL v5 is 2X faster the PostgreSQL v8. For data warehouse application, MySQL sounds like a better option as it is mostly read-only.

Here is the summary that I obtained from this article that compares PostgreSQL with MySQL:

1. MySQL uses traditional row-level locking. PostgreSQL uses something called Multi Version Concurrency Control (MVCC) by default. MVCC is a little different from row-level locking in that transactions on the database are performed on a snapshot of the data and then serialized.
2. MySQL supports the advanced feature of data partitioning within a database whereas PostgreSQL does not.
3. PostgreSQL has many of the database features that Oracle, DB2, or MS-SQL has, including triggers, views, inheritance, sequences, stored procedures, cursors, and user-defined data types. MySQL’s development version, version 5.0, supports views, stored procedures, and cursors. MySQL’s future version, version 5.1, will support triggers.
4. PostgreSQL supports user-defined data types, while MySQL does not.
5. Both MySQL and PostgreSQL have support for single-master, multi-slave replication scenarios. PostgreSQL offers additional support for multi-master, multi-slave replication from a third-party vendor, as well as additional replication methods.
6. MySQL uses a threaded model for server processes, wherein all of the users connect to a single database daemon for access. PostgreSQL uses a non-threaded model where every new connection to the database gets a new database process.
7. MySQL does not support for bitmap indexes. Bitmap indexes are ideal for the kind of low-cardinality data that is commonly used in data warehouses. PostgreSQL supports bitmap indexes as of version v8.1, as do a number of commercial database systems.