Data Warehousing Basics Interview Preparation Guide
Refine your Basics Data Warehouse interview skills with our 37 critical questions. Each question is designed to test and expand your Basics Data Warehouse expertise. Suitable for all experience levels, these questions will help you prepare thoroughly. Dont miss out on our free PDF download, containing all 37 questions to help you succeed in your Basics Data Warehouse interview. Its an invaluable tool for reinforcing your knowledge and building confidence.37 Basics Data Warehouse Questions and Answers:
1 :: Explain Differences between star and snowflake schemas?
star schema is a logical structure that can be arranged with fact and dimension tables
in a star formation.it looks like a star with fact table at the core of the star and the dimension tables along the spikes of the star.the dimension model is therefore called a STAR SCHEMA.
SNOWFLAKING is a method of normalizing the dimension tables in a star schema.
Star Schema: can have less number of joins.
Snow flake: can have more number of joins.
Star Schema : has data redundancy, so the query performace is good.
Snow flake: is normalized, so does not has data redundancey. can have perfomance issues.
in a star formation.it looks like a star with fact table at the core of the star and the dimension tables along the spikes of the star.the dimension model is therefore called a STAR SCHEMA.
SNOWFLAKING is a method of normalizing the dimension tables in a star schema.
Star Schema: can have less number of joins.
Snow flake: can have more number of joins.
Star Schema : has data redundancy, so the query performace is good.
Snow flake: is normalized, so does not has data redundancey. can have perfomance issues.
2 :: Explain the data type of the surrogate key?
Data type of the surrogate key is either integer or numeric or number
There is no data type for a Surrogate Key.
Requirement of a surrogate Key:UNIQUE
Recommended data type of a Surrogate key is NUMERIC.
There is no data type for a Surrogate Key.
Requirement of a surrogate Key:UNIQUE
Recommended data type of a Surrogate key is NUMERIC.
3 :: What is a CUBE in datawarehousing concept?
Cubes are logical representation of multidimensional data.The edge of the cube contains dimension members and the body of the cube contains data values.
4 :: Explain Why fact table is in normal form?
Basically the fact table consists of the Index keys of the dimension/ook up tables and the measures.
so when ever we have the keys in a table .that itself implies that the table is in the normal form.
so when ever we have the keys in a table .that itself implies that the table is in the normal form.
5 :: Explain What are the possible data marts in Retail sales?
Product information,sales information
Retailor_site_Dim, product_dim, Time_dim, Sales_staff_dim,Return_method_dim,
Retailor_site_Dim, product_dim, Time_dim, Sales_staff_dim,Return_method_dim,
6 :: Explain degenerate dimension table?
Degenerate Dimensions : If a table contains the values, which r neither dimesion nor measures is called degenerate dimensions.Ex : invoice id,empno
Degenerate dimensions are those without any attributes or measures
and they are included inside the fact table
Eg orderno
Degenerate dimensions are those without any attributes or measures
and they are included inside the fact table
Eg orderno
7 :: Explain hybrid slowly changing dimension?
Hybrid SCDs are combination of both SCD 1 and SCD 2.
It may happen that in a table, some columns are important and we need to track changes for them i.e capture the historical data for them whereas in some columns even if the data changes, we don't care.
For such tables we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2.
It may happen that in a table, some columns are important and we need to track changes for them i.e capture the historical data for them whereas in some columns even if the data changes, we don't care.
For such tables we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2.
8 :: Explain conformed fact?
Conformed dimensions are the dimensions which can be used across multiple Data Marts in combination with multiple facts tables accordingly
IF A DIMENSION IS EQUALLY SHARED BETWEEN DIFFERENT DATAMARTS HAVING COOMON DATA VALUES IS CALLED CONFORMED DIMENSION.
IF A DIMENSION IS EQUALLY SHARED BETWEEN DIFFERENT DATAMARTS HAVING COOMON DATA VALUES IS CALLED CONFORMED DIMENSION.
9 :: Explain BUS Schema?
BUS Schema is composed of a master suite of confirmed dimension and standardized definition if facts.
In a BUS schema we would eventually have conformed dimensions and facts defined to be shared across all enterprise data marts. This way all Data Marts can use the conformed dimensions and facts without having them locally. This is the first step towards building an enterprise Data Warehouse from Kimball's perspective. For (e.g) we may have different data marts for Sales, Inventory and Marketing and we need common entities like Customer, Product etc to be seen across these data marts and hence would be ideal to have these as Conformed objects. The challenge here is that some times each line of business may have different definitions for these conformed objects and hence choosing conformed objects have to be designed with some extra care.
In a BUS schema we would eventually have conformed dimensions and facts defined to be shared across all enterprise data marts. This way all Data Marts can use the conformed dimensions and facts without having them locally. This is the first step towards building an enterprise Data Warehouse from Kimball's perspective. For (e.g) we may have different data marts for Sales, Inventory and Marketing and we need common entities like Customer, Product etc to be seen across these data marts and hence would be ideal to have these as Conformed objects. The challenge here is that some times each line of business may have different definitions for these conformed objects and hence choosing conformed objects have to be designed with some extra care.
10 :: Explain What are the advantages data mining over traditional approaches?
Data Mining is used for the estimation of future. For example, if we take a company/business organization, by using the concept of Data Mining, we can predict the future of business interms of Revenue (or) Employees (or) Cutomers (or) Orders etc.
Traditional approches use simple algorithms for estimating the future. But, it does not give accurate results when compared to Data Mining.
Traditional approach is also algorithmic prespective and its advantage is convert larger algorithms into smaller ones.In this approach entire software is procedure
Traditional approches use simple algorithms for estimating the future. But, it does not give accurate results when compared to Data Mining.
Traditional approach is also algorithmic prespective and its advantage is convert larger algorithms into smaller ones.In this approach entire software is procedure