Dimensional Modelling in DWH



Data Warehouse Dimensional Modelling (Types of Schemas)
Four types of schemas

Star Schema:

A star schema is the one in which a central fact table is sourrounded by denormalized dimensional tables.
A star schema can be simple or complex.
A simple star schema consists of one fact table where as a complex star schema have more than one fact table.

Snow Flake Schema:

A snow flake schema is an enhancement of star schema by adding additional dimensions.
Snow flake schema are useful when there are low cardinality attributes in the dimensions.


Galaxy Schema:
Galaxy schema contains many fact tables with some common dimensions (conformed dimensions). 
This schema is a combination of many data marts.

Fact Constellation Schema:

The dimensions in this schema are segregated into independent dimensions based on the levels of hierarchy.

For example, if geography has five levels of hierarchy like teritary, region, country, state and city;
constellation schema would have five dimensions instead of one.

Comments

Post a Comment

Popular posts from this blog

Data Migration Testing Startegy

Image
1.   Purpose of the Article The purpose of this Aricle is to describe the different test stages that will be performed; how the testing will be prepared and executed for each stage, and the criteria that need to be met before transition from one stage to the next can occu r. 2.   Phases involved in Data Migration Testing Below are the phases involved in testing data migration. ·         Fusion ·         Release 1.       Design 2.       Implementation a)       Ear b)       Iterations 3.       SIT (System Integration Testing) 4.       UAT (User Acceptance Testing) 2.1.         Fusion (Discover and Define) In this Phase the requirements for the Data Migration project is gathered and identified from the Testing perspective. QA is responsible for defining the scope of testing; high level estimation and Requirement Specification docs. This phase we will perform pre-migration testing, these tests occur
Read more

Informatica DVO installation steps

1      About This Article The purpose of this article is to explain the steps involved in installing and configuring Data Validation Option for usage with Informatica PowerCenter installed on a local machine. Informatica PowerCenter Server can be installed on a remote machine other than the local machine. But installation of Data Validation Option should be done on the same machine as the Informatica PowerCenter Client. 2      Functional Overview Informatica Data Validation Option enables you to test and validate data. It requires the installation of, and does much of its work through, Informatica PowerCenter. Data Validation Option reads table definitions from PowerCenter metadata repositories, and checks the data at either end of the process. It does not check the correctness of transformations or mappings. Data Validation Option identifies problems or inconsistencies. It does not attempt to identify the source of the problem in the ETL process. 3     
Read more

ETL Testing

Image
Data ware house is relational database which is subject oriented, integrated, time-variant and nonvolatile collection of data used to support strategic decision making process Subject Oriented Data warehouses are designed to help you analyze data. For example, to learn more about your company's sales data, you can build a warehouse that concentrates on sales. Using this warehouse, you can answer questions like "Who was our best customer for this item last year?" This ability to define a data warehouse by subject matter, sales in this case, makes the data warehouse subject oriented. Integrated Integration is closely related to subject orientation. Data warehouses must put data from disparate sources into a consistent format. They must resolve such problems as naming conflicts and inconsistencies among units of measure. When they achieve this, they are said to be integrated. Nonvolatile Nonvolatile means that, once entered into the warehouse, data should not cha
Read more