Warehouse Concepts Interview Preparation Guide
Prepare comprehensively for your Data Warehouse Concepts interview with our extensive list of 16 questions. Our questions cover a wide range of topics in Data Warehouse Concepts to ensure youre well-prepared. Whether youre new to the field or have years of experience, these questions are designed to help you succeed. Secure the free PDF to access all 16 questions and guarantee your preparation for your Data Warehouse Concepts interview. This guide is crucial for enhancing your readiness and self-assurance.16 Data Warehouse Concepts Questions and Answers:
1 :: Explain What is the difference between OLAP and datawarehosue?
Datawarehouse is the place where the data is stored for analyzing
where as OLAP is the process of analyzing the data,managing aggregations,
partitioning information into cubes for indepth visualization.
ODS:- A collection of tables created in the Data warehouse that maintains only current data.
OLTP:- Maintains the data only for transactions, these are designed for recording daily operations and transactions of a business.
where as OLAP is the process of analyzing the data,managing aggregations,
partitioning information into cubes for indepth visualization.
ODS:- A collection of tables created in the Data warehouse that maintains only current data.
OLTP:- Maintains the data only for transactions, these are designed for recording daily operations and transactions of a business.
2 :: Explain What are non-additive facts in detail?
A fact may be measure, metric or a dollar value. Measure and metric are non additive facts.
Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount.
A non additive fact, for eg measure height(s) for 'citizens by geographical location' , when we rollup 'city' data to 'state' level data we should not add heights of the citizens rather we may want to use it to derive 'count'
which cant be summed up with any columns in the table(all dimension keys)
EX: ratio columns,profit margin
Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount.
A non additive fact, for eg measure height(s) for 'citizens by geographical location' , when we rollup 'city' data to 'state' level data we should not add heights of the citizens rather we may want to use it to derive 'count'
which cant be summed up with any columns in the table(all dimension keys)
EX: ratio columns,profit margin
3 :: What is cubes?
Cube is used in DWH foar representing multidimensional data logically.
4 :: Explain Why Denormalization is promoted in Universe Designing?
In a relational data model, for normalization purposes, some lookup tables are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called DIMENSION table for performance and slicing data.Due to this merging of tables into one large Dimension table, it comes out of complex intermediate joins. Dimension tables are directly joined to Fact tables.Though, redundancy of data occurs in DIMENSION table, size of DIMENSION table is 15% only when compared to FACT table. So only Denormalization is promoted in Universe Desinging.
5 :: Hyperion is the one of the tool in data ware house. Its an olap tool. Why you cant display that tool?
Explain yourself
6 :: Explain What is fact less fact table? where you have used it in your project?
Fact less table means only the key available in the Fact there is no measures available.
Fact less fact table means it does not contain any facts(measures).It is used when we are integrating fact tables.
Fact less fact table means it does not contain any facts(measures).It is used when we are integrating fact tables.
7 :: Explain what is aggregate table and aggregate fact table ... any examples of both?
Aggregate table contains summarized data. The materialized view are aggregated tables.
for ex in sales we have only date transaction. if we want to create a report like sales by product per year. in such cases we aggregate the date?vales into week_agg, month_agg, quarter_agg, year_agg. to retrive date from this tables we use @aggrtegate function.
aggregate table is one of the data transaction function and some time it is create a protect per year.this aggregate value is week agg ,month agg quarter agg function
for ex in sales we have only date transaction. if we want to create a report like sales by product per year. in such cases we aggregate the date?vales into week_agg, month_agg, quarter_agg, year_agg. to retrive date from this tables we use @aggrtegate function.
aggregate table is one of the data transaction function and some time it is create a protect per year.this aggregate value is week agg ,month agg quarter agg function
8 :: Please explain in detail about
type 1,
type 2(SCD),
type 3?
Type-1
Most Recent Value
Type-2(full History)
i) Version Number
ii) Flag
iii) Date
Type-3
Current and one Perivies value
SCD'S slow change dimension
there are three types
scd1, scd2, scd3
scd1:- suppose it the data got updated in the table then there r 2 methods one is to drop the table and upload the new one. but its along process.
here by using table compression we can update the particular data that has been modified. that is scd1
scd2:- by using key generation we r going to generate the new rownum column if there r any update the next row will be updated one and row numbers will be increamented automatically by 1
scd3:- i this one a extra column is added and updated infromation is stored in that column. if again table is update another column is added to it again...
in this one scd2 is mostly used..
Most Recent Value
Type-2(full History)
i) Version Number
ii) Flag
iii) Date
Type-3
Current and one Perivies value
SCD'S slow change dimension
there are three types
scd1, scd2, scd3
scd1:- suppose it the data got updated in the table then there r 2 methods one is to drop the table and upload the new one. but its along process.
here by using table compression we can update the particular data that has been modified. that is scd1
scd2:- by using key generation we r going to generate the new rownum column if there r any update the next row will be updated one and row numbers will be increamented automatically by 1
scd3:- i this one a extra column is added and updated infromation is stored in that column. if again table is update another column is added to it again...
in this one scd2 is mostly used..
9 :: Where the applications and where
ware house management system is used?
Data warehousing system is used in OLAP systems. Systems in which mainly the analysis of the data is needed. High level and Top executives use this system for analysis purpose so that they can make correct decisions that can boost the productivity of the org.
10 :: What is snapshot?
You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data. However, you must reconnect to the catalog if you want to refresh the data.