Data Warehouse Concepts Interview Questions and Answers
1. Define Data Warehouse.
Ans. A data warehouse is a relational
database that is designed for query and analysis rather than for transaction
processing. It usually contains historical data derived from transaction data,
but it can include data from other sources.
2. Which
are three layers in which data warehouse maintains it’s functions?
Ans. A data warehouse maintains its functions in staging, integration and access.
Ans. A data warehouse maintains its functions in staging, integration and access.
3. Can you
elaborate it?
Ans. Staging: It is used to store raw data for use by developers.
Ans. Staging: It is used to store raw data for use by developers.
Integration: It is used to integrate data and to
have a level of abstraction from users.
Access: It is for accessing data out of
data warehouse for it’s users.
4. What is
Data Mart?
Ans. A Data Mart is the access layer of the data warehouse environment that is used to make data accessible to it’s users. It is subset of Data Warehouse.
Ans. A Data Mart is the access layer of the data warehouse environment that is used to make data accessible to it’s users. It is subset of Data Warehouse.
5. What is
dimensional approach for storing data in data warehouse?
Ans. In dimensional approach transaction data are partitioned into either “facts”, which are generally numeric transaction data, or “dimensions”, which are the reference information that gives context to the facts.
Ans. In dimensional approach transaction data are partitioned into either “facts”, which are generally numeric transaction data, or “dimensions”, which are the reference information that gives context to the facts.
6. What is
normalization approach for storing data in data warehouse?
Ans. In normalization approach the data in the data warehouse are stored following, to a degree, database normalization rules. Tables are grouped together by subject areas that reflect general data categories (e.g., data on customers, products, finance, etc.).
Ans. In normalization approach the data in the data warehouse are stored following, to a degree, database normalization rules. Tables are grouped together by subject areas that reflect general data categories (e.g., data on customers, products, finance, etc.).
7. How we
organize sales transactions data into dimensional approach?
Ans. A sales transaction can be broken up into facts such as the number of products ordered and the price paid for the products, and into dimensions such as order date, customer name, product number, order ship-to and bill-to locations, and salesperson responsible for receiving the order.
Ans. A sales transaction can be broken up into facts such as the number of products ordered and the price paid for the products, and into dimensions such as order date, customer name, product number, order ship-to and bill-to locations, and salesperson responsible for receiving the order.
8. What is
the main advantage in using dimensional approach?
Ans. The main advantage in using dimensional approach is that the data warehouse is easier for the user to understand and to use. The retrieval of data from data warehouse tends to operate very quickly.
Ans. The main advantage in using dimensional approach is that the data warehouse is easier for the user to understand and to use. The retrieval of data from data warehouse tends to operate very quickly.
9. What
are the main disadvantages of using dimensional approach?
Ans. There are mainly two disadvantages of using dimensional approach to storing data in data warehouse.
Ans. There are mainly two disadvantages of using dimensional approach to storing data in data warehouse.
1. For maintaining integrity of
facts and dimensions, the process of loading the data from data warehouse from
different operational systems gets complicated.
2. If organization is adopting the
dimensional approach changes the way in which it does the business, it is
difficult to modify the data warehouse structure.
10. What
are the advantages and disadvantage of using normalization approach for storing
data in data warehouse?
Ans. Advantages 1. The main advantage is that it is easy to add information into the database.
Ans. Advantages 1. The main advantage is that it is easy to add information into the database.
Disadvantages
1. It is difficult to join data from
different sources into meaningful information
2. It is also difficult to access
the information without a precise understanding of the source of data and the
data structure of the data warehouse.
11. What
is dimension?
Ans. It is a data element. It categorizes each item in a data set into non-overlapping regions. It aids to “slice and dice” data in data warehouse.
Ans. It is a data element. It categorizes each item in a data set into non-overlapping regions. It aids to “slice and dice” data in data warehouse.
12. Can
you describe different types of dimensions?
Ans. There are three types of dimensions are available in dimensional approach.
Ans. There are three types of dimensions are available in dimensional approach.
Conformed
dimension: It is a set of data attributes
which is physically implemented in multiple database tables using same
structure, attributes, domain values, definitions and concepts in each
implementation. It cuts across many facts.
Dimensions are conformed when they
are either same(with keys) or one is perfect subset of other.
Junk
Dimension: It is grouping of low cardinality
flags and indicators. These flags and indicators are removed from the fact
table while placing them into a useful dimensional framework.
Role
Playing Dimension:
Often dimensions are recycled for multiple applications within the same
database. i.e. “date” dimension can be used for “sales date” or “hire date”.
This is referred as role playing dimension.
13. What
is cube?
Ans. Cube is a data structure that allows fast analysis of data. It is the capability of manipulating and analyzing data from different perspective.
Ans. Cube is a data structure that allows fast analysis of data. It is the capability of manipulating and analyzing data from different perspective.
14. What
data cleansing(or data scrubbing) will do?
Ans. It will remove errors, redundancies and inconsistencies in the data that is being imported into a data mart or data warehouse. (part of quality assurance process)
Ans. It will remove errors, redundancies and inconsistencies in the data that is being imported into a data mart or data warehouse. (part of quality assurance process)
15. What
do you know about data mining?
Ans. It is process of researching data marts and data warehouses to detect specific patterns in the data sets. It may be performed on databases and multi-dimensional data cubes with ad-hoc query tools and OLAP.
Ans. It is process of researching data marts and data warehouses to detect specific patterns in the data sets. It may be performed on databases and multi-dimensional data cubes with ad-hoc query tools and OLAP.
The queries and reports are designed
to answer specific questions to uncover trends or hidden relationships in the
data.
16. What
you know about drill-down and drill-up?
Ans. Drill-down is the ability of a data-mining tool to move down into increasing levels of detail in a data mart, data warehouse or multi-dimensional data cube.
Ans. Drill-down is the ability of a data-mining tool to move down into increasing levels of detail in a data mart, data warehouse or multi-dimensional data cube.
Drill-up is the ability of a data-mining
tool to move back up into higher levels of details in a data mart, data
warehouse or multi-dimensional data cube.
17. What
is a fact table?
Ans. It is primary table that contains measurements(mostly numeric data like grade, wages, etc).
Ans. It is primary table that contains measurements(mostly numeric data like grade, wages, etc).
18. What
is OLAP(On-Line Analytical Processing)?
Ans. Online retrieval and analysis of data to disclose business trends and statistics not directly visible in the data is known as OLAP.
Ans. Online retrieval and analysis of data to disclose business trends and statistics not directly visible in the data is known as OLAP.
19. What
is OLTP(On-Line Transaction Processing)?
Ans. It refers to a class of systems that facilitates and manage transaction-oriented applications, typically for data entry and retrieval transaction processing.
Ans. It refers to a class of systems that facilitates and manage transaction-oriented applications, typically for data entry and retrieval transaction processing.
20. What
are advantages and disadvantages of data warehouse?
Ans. Advantages of using data warehouse includes:
Ans. Advantages of using data warehouse includes:
1. Integrating data from multiple
sources.
2. Performing new types of analysis and reducing cost to access historical data.
3. Improving turnaround time for reporting and analysis.
4. Supporting ad-hoc query and reporting.
2. Performing new types of analysis and reducing cost to access historical data.
3. Improving turnaround time for reporting and analysis.
4. Supporting ad-hoc query and reporting.
Disadvantage
1. Long initial implementation time
and associated high cost.
2. Difficult to accommodate changes in data types and ranges, data source schema, indexes and queries.
3. Data owners lose control over their data, raising ownership, privacy and security issues.
4. Adding new data source takes time and associated high cost.
2. Difficult to accommodate changes in data types and ranges, data source schema, indexes and queries.
3. Data owners lose control over their data, raising ownership, privacy and security issues.
4. Adding new data source takes time and associated high cost.
Subscribe to:
Post Comments (Atom)
20 comments:
Hi
I read this post two times.
I like it so much, please try to keep posting.
Let me introduce other material that may be good for our community.
Source: Microsoft interview questions
Best regards
Henry
Data Warehousing Interview Questions and Answers
http://allinterviewquestionsandanswerspdf.blogspot.in/2016/06/top-100-data-warehousing-interview.html
Nice Collection
200 Informatica Interview Questions And Answers
Your nice work is very attractive and impressive.
Chicago Ambient Warehouse
This information really worth saying, i think you are master of the content and thank you so much sharing that valuable information and get new skills after refer that post.
Data Warehousing Training in Chennai
We are a group of volunteers and starting a new initiative in a community. Your blog provided us valuable information to work on.You have done a marvellous job!
python training in Bangalore
python training in pune
python online training
python training in chennai
I think you have a long story to share and i am glad after long time finally you cam and shared your experience.
java training in jayanagar | java training in electronic city
java training in chennai | java training in USA
This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me..
Data Science training in rajaji nagar | Data Science with Python training in chenni
Data Science training in electronic city | Data Science training in USA
Data science training in pune | Data science training in kalyan nagar
This looks absolutely perfect. All these tiny details are made with lot of background knowledge. I like it a lot.
angularjs Training in chennai
angularjs Training in chennai
angularjs-Training in tambaram
angularjs-Training in sholinganallur
angularjs-Training in velachery
Awesome post, you got the best interview questions and answers for ssis interview questions and answers. You’re doing a great job.
Angularjs Training in Chennai
Angular Training in Chennai
Angular 5 Training in Chennai
AWS Certification in Chennai
ccna Training in Chennai
RPA Training Institute in Chennai
Your blog is nice. I believe this will surely help the readers who are really in need of this vital piece of information. Thanks for sharing and kindly keep updating.
Best Spoken English Training Institute in Adyar
Spoken English Classes in Gandhi Nagar
Spoken English Classes in ECR
Spoken English Center in Porur
Spoken English in Ramapuram
Spoken English Classes in Moulivakkam
Spoken English Course near me
Nice post. I learned some new information. Thanks for sharing.
bloggydirectory
Technology
Innovative thinking of you in this blog makes me very useful to learn.i need more info to learn so kindly update it.
AWS Course in Bangalore
Aws Certification in Bangalore
AWS Training in Chennai Anna Nagar
AWS Certification Training in T nagar
Amazing Post. The content is very interesting. Waiting for your future updates.
Xamarin Training in Chennai
Xamarin Course in Chennai
Xamarin Training
Xamarin Course
Primavera Training in Chennai
Primavera Course in Chennai
IELTS coaching in Chennai
IELTS Training in Chennai
Awesome Post. Great way of sharing the thoughts. Waiting for your future updates.
Node JS Training in Chennai
Node JS Course in Chennai
Node JS Advanced Training
Node JS Training Institute in chennai
Node JS Training Institutes in chennai
Node JS Course
Node JS Training in Velachery
Node JS Training in Tambaram
Informative post indeed, I’ve being in and out reading posts regularly and I see alot of engaging people sharing things and majority of the shared information is very valuable and so, here’s my fine read.
click here apk
click here animated gif button
click here bit ly
click here button gif
click here book
Inspiring writings and I greatly admired what you have to say , I hope you continue to provide new ideas for us all and greetings success always for you..Keep update more information.. thnaks
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
Nice tutorial. Thanks for sharing the valuable information. it’s really helpful. Who want to learn this blog most helpful. Keep sharing on updated tutorials… nice page
Ai & Artificial Intelligence Course in Chennai
PHP Training in Chennai
Ethical Hacking Course in Chennai Blue Prism Training in Chennai
UiPath Training in Chennai
Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
Salesforce Training in Chennai | Certification | Online Course | Salesforce Training in Bangalore | Certification | Online Course | Salesforce Training in Hyderabad | Certification | Online Course | Salesforce Training in Pune | Certification | Online Course | Salesforce Online Training | Salesforce Training
Thank you for the nice stuff.
Cyber Security Training Course in Chennai | Certification | Cyber Security Online Training Course | Ethical Hacking Training Course in Chennai | Certification | Ethical Hacking Online Training Course |
CCNA Training Course in Chennai | Certification | CCNA Online Training Course | RPA Robotic Process Automation Training Course in Chennai | Certification | RPA Training Course Chennai | SEO Training in Chennai | Certification | SEO Online Training Course
Post a Comment