Data Warehouse in Simple English

If you need ''the right information in the right place at the right time with the right cost in order to support the right decision''[1] from , the data warehouse comes into play. So what does right information mean? The knowledge workers, like accountants, project managers and data analysts, need a support to make decisions and data warehouse provide such a support for all types of knowledge workers on the same data.

How do the developers manage to use same data and support different tasks? I know two classical approaches to design a data warehouse: top-down and bottom-up approaches. In the top-down approach [2], all the data and its statistics are stored and parts of it are copied in data marts (pieces of data) for the further use. The approach is time-consuming and costly as by designing a data warehouse model one have to reconcile structure of sources. The bottom-up approach [3] is less costly and focus on creating rapid solutions with less redundant data and small data marts. But the approach consider just one or a few tables that are fact tables and include integrated data from data sources while all other data land in data marts. The flaw of the bottom-up approach lies in its inflexibility according to the changes of requirements of data warehouse users.

In the top-down approach you extract the data, cleaned it, transformed it according to your schemata and uploaded into the global warehouse. You can use some pieces of the data for some statistics (in Operation Data Store for example) but generally all data you need to use for the applications (Data mining, Data visualization, Planning, Prediction) you copied in additional data marts. A good example of this design is taken from http://datacommanders.com/

In the bottom-up approach after the extraction and transformation of the data, you store it in global warehouse as following: one or several fact tables and its dimensions - so global warehouse information model looks like a star.
A good example of it taken from the www.databaseanswers.org

[1] Matthias Jarke, Maurizio Lenzerini, Yannis Vassiliou, Panos Vassiliadis. Fundamentals of Data Warehouses. Springer, Dec 1, 2010

[2] William H. Inmon(1996). Building the Data Warehouse. John Wiley & Sons, ISBN

[3] Ralph Kimball (1996). The Data Warehouse Toolkit. Wiley.

Deleting duplicates in the DB2 with the IBM Control Center

Sometimes you have to do just a small task on the database and if you don't have/want to use a command line, you can use an interface like the Control Center.

If you have tables with duplicate values in the DB2 and the tables have primary keys, let say ID, it is easy to avoid the duplicates.

First copy the table. Use the

create table message_table_copy ...

query and check that all fields you want to copy have the same types. 

Than you can load the data into the message_table_copy. The table from which you are coping and the table to which you are coping, in this case  message_table and message_table_copy should belong to the same schema. Choose Load (right-click menu) -> Add data -> from cursor. Than you can choose the data you want to copy (Select * from message_table if you copy the whole table) and the folder where the status information have to be stored (usually on the server, so choose the home directory of your db2 instance on the server). All other tabs are default except of the tab where you have to choose to perform the task immediately or to add in the task manager.

Now you can check if all values were copied. You can just count the number of entries in both tables:

select count(*) from message_table 

If everything is ok, look for duplicates. Let say, you have message_id which should be used to define duplicates. Here is the SQL query to delete the duplicates from the message_table

delete from message_table where id in 
          (SELECT MIN(ID) FROM message_table
                          GROUP BY message_id 
                          HAVING COUNT(message_id) >1) )

The query searches for entries with same message_id (INT, SMALLINT, BIGINT) and chooses the id ( you can choose between MAX(id)/MIN(id)) that is used for deleting.