Data Warehouse Design Strategies

To build an effective data warehouse, it is important for you to understand data warehouse design principles. If your data warehouse is not built correctly, you can run into a number of different problems. The proper methods for building a powerful data warehouse are based on information technology tactics. First off, it is important that you and your organization understand the importance of having a data warehouse. If workers feel that a data warehouse is unnecessary, they may not use it, and this could cause conflicts. Everyone in your organization should understand the importance of using the system. After you have got...

Data Warehousing Methods

Most organizations agree that data warehouses are a useful tool. They benefit from the ability to store and analyze data, and this can allow them to make sound business decisions. It is also important for them to make sure the correct information is published, and it should be easy to access by the people who are responsible for making decisions. There are two elements that make up the data warehouse environment, and these are presentation and staging. The staging could also be known as the acquisition area. It is composed of ETL operations, and once the data has been prepared, it will be sent to the presentation area. When...

Data Warehouse Tools

There are a number of important tools which are connected to data warehouses, and one of these is data aggregation. A data warehouse can be designed to store information based on a certain level of detail. For example, you can store data based on each transaction, or you can store it based on a summary. These are examples of data aggregation. When data is summarized, the queries will move at a much faster rate. However, some of the information may be lost during a query, and this information may be important for solving a certain problem. Before you decide which one you will use, it is important to weigh your options carefully....

For every rule there is an exception; for each exception there are more exceptions…

To implement an ETL process there are many steps that are followed. One such step is creating a mapping document. This mapping document describes the data mapping between the source systems and the target and the rules of data transformation. Ex. Table / column map between source and target, rules to identify unique rows, not null attributes, unique values, and range of a attributes, transformations rules, etc. Without going into further details of the document, lets analyze the very next step. It seems obvious and natural to start development of the of the ETL process. The ETL developer is all fired up and comes up with...

Difference between Reference Data and Master Data

It is not unusual for people to use ‘Reference Data’ and ‘Master Data’ interchangeably without understanding the differences. Lets try to understand the differences with an example of sales transaction. A sales transaction contains information like…. Store, Products Sold, Sales Person, Store Name, Sales Date, Customer, Price, Quantity, etc. Attributes from the above example can be separated into two types: Factual (transactional) and Dimensional information Price and Quantity are measurable attributes of a transaction. Store, Products Sold, Sales Person, Store Name, Sales Date, and Customer are dimensional attributes of...

ETL delta logic & de-normalization of data model.

It is a normal practice in data warehouse to de normalizes (Or once auto corrected as demoralize) as the data model for performance. I am not going to discuss the benefits vs. issues with de-normalization.  As by the time it comes to the ETL guy the fate of the model is already decided. Let’s look at the model in the source side, which is perfectly normalized.     Now let’s look at the de normalized model on the target side.       Next lets think of delta logic...