How to design a Data Warehouse?

What is Data Warehouse?

A  data warehouse is a relational database which is used for reporting and data analysis. Data warehouse is a main component of BI.

Data warehouse can be defined under two main concept-

According to Bill Inmon-

A data warehouse is subject-oriented,integrated,time-variant and non-volatile collection of data in support of management's decision-making process.

According to Ralph Kimball-

A data warehouse is a copy of transaction data specifically structured for query and analysis.

Difference between detail filter and summary filter

Filters are used to emphasize the important information and filter out what is unnecessary. You can apply a filter to a report to change the focus of the report.

Detail filter:-

• Detail filter is used to filter detail-level data within a report. A detail filter applies one or more specified conditions to each row of data. If conditions are true for a row, data in the row is retrieved and appears in the report. If a condition is not true, then row is not retrieved and it is not retrieved in the report.
• Detail filters can apply to each and every record in the database.
• Detail filter comes under the where clause.
• In detail filter you have two options while using it:

Before auto aggregation or after auto aggregation i.e. before the data is brought into the cache or after the data has been brought into the cache.

If you add set the property as before auto aggregation filter will be applied to where clause otherwise filter is applied in having

Summary filter:

• A summary filter is used to filter summary-level data in a report. Before you create a summary filter,  you must calculate the summary level data items that you want to use in the filter.
• A summary filter can apply to summary level data.
• Summary filter comes under having clause.
• Summary filters don't have the option before auto-aggregation or after aggregation.

Cognos Transformer

IBM Cognos Transformer is a multi-dimensional data modelling tool. This tool is used for to create multi-dimensonal cube for business presentation of data.

Cognos transformer model can contain data from one or more than one data source. Model is used to support OLAP reporting and faster analysis of data.

A cube contains calculated and summarized data organized into dimensions and measures and can be viewed and analysed in IBM Cognos Analysis Studio.

IBM Cognos Transformer creates metadata file with .mdl extension, which generates a physical cube with .mdc extension.

Cognos Framework Manager

Framework Manager is a metadata modeling tool for IBM Cognos BI. Metadata is data about data. Metadata model represent the data to business users in a very simple way, which remove the complexity of data structure.

Framework Manager is a Windows-based developer tool used to accomplish the metadata modeling task. A Framework Manager model can derived data from more than one data sources.

A model structure manages data such a way which is useful for business users.
Example- a model defines business rules, data descriptions, data relationships, business dimensions and hierarchy, and administrative tasks.

The team involve in Framework Manager modeling should have knowledge of database structure and requirements of business users.

When metadata modeling is completed, package is created and then package is published.

Published package is available in IBM Cognos Connection which  makes metadata accessible to report authors. Each package must contain all the information that a specific user or group of users need to create reports.

Example- One package have human resource data and another package have sale data. User of each department can use specific package related to their departments.