Data Warehouse

An enterprise data warehouse (EDW), is a system used for reporting and data analysis, and is considered a core component of business intelligence(B1).

What is the difference between OLTP and OLAP?

OLTP is the transaction system that collects business data. Whereas OLAP is the reporting and analysis system on that data.

OLTP systems are optimized for INSERT, UPDATE operations and therefore highly normalized. On the other hand, OLAP systems are deliberately denormalized for fast data retrieval through SELECT operations.

List the types of OLAP servers.

• • Relational OLAP

  • Multidimensional OLAP

  • Hybrid OLAP

  • Specialized SQL Servers

What is a snow flake schema?

Just like the star schema, a single fact table references number of other dimension tables in snow flake scheme. Here however, these dimension tables are further normalized into multiple related tables. As these tables are further snow flaked into smaller tables, this schema is called a snow flake schema.

What are the different types of SCD's used in data warehousing?

SCD (Slowly changing dimensions), are the dimensions in which the data changes slowly, rather than changing regularly on a time basis.

Three types of SCDs are used in data warehousing, which are defined as: 

– SCD1: It is a record that is used to replace the original record even there is only one record existing in the database. The current data will be replaced and the new data will take its place.

– SCD2: It is the new record file that is added to the dimension table. This record exists in the database with the current data and previous data that is stored in the history.

– SCD3: This uses the original data that is modified to the new data. This consists of two records: one record that exist in the database and another record that will replace the old database record with the new information.

What is the CAP Theorem?

The CAP Theorem is a fundamental theorem in distributed systems that states any distributed system can have at most two of the following three properties.

• Consistency -->any read operation that begins after a write operation completes must return that value, or the result of a later write operation

• Availability -->every request received by a non-failing node in the system must result in a response

• Partition tolerance -->the network will be allowed to lose arbitrarily many messages sent from one node to another

What is a star schema?

Star schema is a schema used in data warehousing where a single fact table references a number of dimension tables. In a star schema, “keys” from all the dimension tables flow into the fact table. This entity-relationship diagram resembles a star, hence it is named a Star schema.

What is a snow flake schema?

Just like the star schema, a single fact table references number of other dimension tables in snow flake scheme. Here however, these dimension tables are further normalized into multiple related tables. As these tables are further snow flaked into smaller tables, this schema is called a snow flake schema.

Star Schema vs. Snowflake Schema

1.       Star schema dimension tables are not normalized, snowflake schemas dimension tables are normalized. 

2.       Snowflake schemas will use less space to store dimension tables but are more complex.

3.       Star schemas will only join the fact table with the dimension tables, leading to simpler, faster SQL queries.

4.       Snowflake schemas have no redundant data, so they're easier to maintain.

5.    Snowflake schemas are good for data warehouses, star schemas are better for datamarts with simple relationships.

Differentiate between “bteqexport” and “fastexport”?

“bteqexport” is used when the number of rows is less than half a million, while “fastexport” is used if the number of rows in more than half a million.