1) What is Data warehouse?
Data warehouse is relational database used for query analysis and reporting. By definition data warehouse is Subject-oriented, Integrated, Non-volatile, Time variant.
Subject oriented: Data warehouse is maintained particular subject.
Integrated: Data collected from multiple sources integrated into a user readable unique format.
Non volatile : Maintain Historical date.
Time variant : data display the weekly, monthly, yearly.
2) What is Data mart?
A subset of data warehouse is called Data mart.
3) Difference between Data warehouse and Data mart?
Data warehouse is maintaining the total organization of data. Multiple data marts used in data warehouse. where as data mart is maintained only particular subject.
4) Difference between OLTP and OLAP?
OLTP is Online Transaction Processing. This is maintained current transactional data. That means insert, update and delete must be fast.
OLAP is Online Analytical Processing.This is Used to Read the data and is more useful for the analysis.
5) Explain ODS?
Operational data store is a part of data warehouse. This is maintained only current transactional data. ODS is subject oriented, integrated, volatile, current data.
6) Difference between Power Center and Power Mart?
Power center receive all product functionality including ability to multiple register servers and Metadata across the repository and partition data.
One repository multiple informatica servers. Power mart received all features except multiple register servers and partition data.
7) What is a staging area?
Staging area is a temporary storage area used for transaction, integrated and rather than transaction processing.
When ever your data put in data warehouse you need to clean and process your data.
8) Explain Additive, Semi-additive, Non-additive facts?
Additive fact: Additive Fact can be aggregated by simple arithmetical additions.
Semi-Additive fact: semi additive fact can be aggregated simple arithmetical additions along with some other dimensions.
Non-additive fact: Non-additive fact can’t be added at all.
9) What is a Fact less Fact and example?
Fact table which has no measures or A table with out Facts is said to be as Fact less Fact Table.
10) Explain Surrogate Key?
Surrogate Key is a series of sequential numbers assigned to be a primary key for the table.
A surrogate key is an arbitrary value (GUID and IDENTITY types are frequently used) that is used in place of a natural or intelligent key. The choice may be one of performance or one of convenience. A degenerate key is usually a surrogate key and is used to replace primary key values from the source OLTP system since these values are not likely unique across multiple systems in an enterprise.
A GUID is a "globally unique identifier" -- a big huge 16 bit random number that's fairly certain to be unique. An IDENTITY is a seeded, sequential number that's unique because it's always one bigger than the previous one.
A surrogate key is a substitution for the natural primary key.
It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.
Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.
How are Surrogate Keys useful in DataWare House:
1.It is useful because the natural primary key (eg.,Customer Number in Customer table) can change and this makes updates more difficult.
2. Another usage is to track the Slowly changing dimensions.
0 comments:
Post a Comment