And now here is the important question: Which model will be used? Star Schema or Snowflake Schema. The answer depends on the requirements. The frequency that the end users access the data will be the important reason which will effect the design. If the columns that are stored in the dimension tables, will be accessed so often, then you better choose Star Schema model. But if the columns accessed very rarely, then snowflake schema will be the best solution for this requirement. As you see, a solution depends on the requirements.
So as a result, here is a description of a datawarehouse:
- Subject oriented: It gives information about a particular subject of a company.
- Integrated: Data stored in different sources integrated into one database.
- Time variant: Data is identified with a particular time period.
- Non volatile: Data is persistent, stable. New data is always appended to the datawarehouse.
This description was made around 15 years ago. So by the time passes, the requirements change, too. Now we called subject oriented data as Data Marts and when it comes to collection of Data Marts: They are datawarehouses. In addition data can be volatile. Because of the large sized databases, it’s not easy to store the last 20 years data. In general, a period of data is stored in the datawarehouses. What about the historical data? Of course, they are stored in historical archives.
After designing the datawarehouse, next step will be about how to load data into the tables. Of course, a free solution will be best. Writing the code for ETL. But this solution doesn’t work always so we better try to find a solution. Because let’s suppose that there are 2 OLTP sources. And 1000 tables on each server. What are we going to do? Create a script of all tables and then build a routine for loading the data into DW everyday? It will definitely take a long time. You won’t be able to finish the project cause maintenance will start to take developer’s time. That’s why we’ll need a customizable and functional tool. It’s Oracle Data Integrator (ODI). Next article will be about ODI.
