DBMS based systems are passe. Gone are the days when organizational data processing involved assimilation, storage, retrieval and processing. Today, data from various sources need to be processed concurrently and instant results need to be presented and worked upon, to ensure customer-centric business operations. Industry verticals like BFSI, healthcare, utilities, even government organizations are turning to Data Warehousing, powered by Business Intelligence, to beat competition. This critical business need has given rise to a whole new business dynamic, and jobs are mushrooming around it.
Data Warehousing and Business Intelligence (DWBI) is a lucrative career option if you are passionate about managing data. We are here to help you if you wish to attend DWBI interviews. We have created a list of probable Data Warehousing interview questions and answers. If you have attended DWBI interviews in the recent past, we encourage you to paste additional questions in the comments tab. All the best!
1. What is a data warehouse? List the types of Data warehouse architectures.
A data warehouse is the electronic storage of an organization’s historical data for the purpose of data analytics. In other words, a data warehouse contains a wide variety of data that supports the decision-making process in an organization.
There are mainly 3 types of data warehouse architectures:
| Single-tier architecture |
|
| Two-tier architecture |
|
| Three-tier architecture |
|
2. Define data analytics in the context of data warehousing.
- Data analytics is the science of examining raw data with the purpose of drawing business-driven conclusions about that data.
- The role of a data warehouse is to enable data analysis.
3. What is a subject-oriented data warehouse?
Subject-oriented data warehouses are those that store data around a particular “subject” such as customer, sales, product, among others.
4. What does OLAP stand for?
OLAP stands for On Line Analytical Processing. It is a system which collects, manages, and processes multi-dimensional data for analysis and management.
5. What does OLTP stand for?
OLTP stands for On Line Transaction Processing. It is a system which modifies the data whenever it received, to a large number of concurrent users.
6. List the types of OLAP servers.
- Relational OLAP
- Multidimensional OLAP
- Hybrid OLAP
- Specialized SQL Servers
7. List some of the functions performed by OLAP.
Some of the major functions performed by OLAP include “roll-up”, “drill-down”, “slice”, “dice”, and “pivot”.
8. 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.
9. 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.
10. What is the language that is used for schema definition?
Data Mining Query Language (DMQL) is used for schema definition.
11. What are the different types of “dimension”?
- Conformed dimension
- Junk dimension
- Degenerated dimension
- Role Playing dimension
12. What is a mini dimension?
Mini dimensions are dimensions that are used when a large number of rapidly changing attributes are separated into smaller tables.
13. Define fact-less fact.
Fact-less fact is a fact table that does not contain any value. Such a table only contains keys from different dimension tables.
14. What is ODS?
ODS stands for Operational Data Store. it is essentially a repository of real-time operational data.
15. What is a data cube?
A data cube helps represent data in multiple facets. Data cubes are defined by dimensions and facts.
16. What do you understand by the ER model?
ER model or entity-relationship model is a methodology for data modeling wherein the goal of modeling is to normalize the data by reducing redundancy.
17. What do you understand by dimensional modeling?
Dimensional model is a methodology that consists of “dimensions” and “fact tables”. Fact tables are used to store various transactional measurements from “dimension tables” that qualifies the data.
18. What is VLDB in the context of data warehousing?
VLDB stands for Very Large Database. The size of a VLDB is preset to more than one terabyte.
19. What is a data mart?
Data mart is a subset of organizational data. In other words, it is a collection of data specific to a particular group within an organization.
20. What is data aggregation?
Data aggregation is the broad definition for any process that enables information gathering expression in a summary form, for statistical analysis.
21. What is summary information?
Summary Information is the location within data warehouse where predefined aggregations are stored.
22. 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.
Got a question for us? Please mention it in the comments section and we will get back to you or join our data warehousing and business intelligence course online today.
Related Posts: