1. What is a data warehouse?
A data warehouse is a collection of integrated data from one or more sources, used for data analysis and reporting. Several years of data is stored in data warehouse. Data is static and not a transactional.
2. What is a data mart?
A data mart is a subset of data warehouse. Data mart gives a clear understanding of the small portion of a data warehouse. For viewing, analyzing, reporting, and documentation, data mart will be better.
3. What is the difference between a data warehouse and data mart?
Data Warehouse comprises of all subject areas, where data mart is focused on a specific subject area.
4. What is a Dimensional Data Model?
Dimensional Data Model contains one or more dimension tables and fact tables and is used for calculating the summarized data. Dimensional Data Model is used in data warehouse and data marts.
5. What is a dimension?
Dimension table is also called as lookup or reference table. The data (foreign keys) in the fact table refers to the data (primary key) in the dimension and is used for validation and calculation purpose.
6. What is a slowly changing dimension (SCD)? What are types of SCD?
Dimensions that change over time are called slowly changing dimensions. Type1, Type2 and Type3 are three types of slowly changing dimensions.
7. What is a star schema?
Star Schema is a database schema, which contains one or more dimensions and fact table representing multidimensional data. It is called as star schema because the relationship between the fact tables and dimensions looks like a star.
8. What is OLAP data modeling?
OLAP stands for ONLINE ANALYTICAL PROCESSING. The approach by which data models are constructed for analyzing data is called as OLAP data modeling. Example: Data Warehouse and Data Marts.
9. What is ETL?
ETL acronym stands for Extraction, Transformation and Loading. ETL is a process by which data stored in various sources are extracted, transformed and loaded into the Target Database.
10. What is a Fact table?
The centralized table in a star schema is called as Fact table. Fact table contains many columns referenced to dimension tables and standalone measure/fact columns. These facts or measure columns give useful and meaningful data based on some calculation.
11. What are the types of measure columns in a fact table?
Additive, Semi Additive and Non-Additive columns are three types of measure columns.
Additive means: Measures or facts that can be added across all columns.
Semi Additive means: Measures or fact that can be added across few columns.
Non Additive means: Measures that cannot be added across all dimensions.
12. What are the steps to create a Data Warehouse?
The various steps are: Analyzing the data from different sources, Data Modeling, creating databases, designing etl process, extracting data from various sources, transforming the data, loading the data into target data warehouse database/target Data Mart databases. From database, reports are generated as per the needs.