Top 50 Data Warehouse Interview Questions & Answers. last updated March There are four stages of Datawarehousing: . Download PDF. All Rights Reserved. nbafinals.info INDEX. Data Warehousing Interview Questions and Answers. 1. Data Warehousing Interview Q and Answers . 2. This is a top data warehouse interview questions and answers that can help you crack your data warehousing job interview. You will learn about difference.
|Language:||English, Spanish, Hindi|
|Genre:||Politics & Laws|
|Distribution:||Free* [*Sign up for free]|
These Data Warehousing interview questions and answers on data warehousing concepts will get you your dream Data Warehousing job in. If you're looking for Data Warehouse Interview Questions & Answers for Experienced or Freshers, you are at right place. There are lot of opportunities from many. Click here to get free chapters (PDF) in the mailbox What are slowly changing SQL SERVER – Data Warehousing Interview Questions and.
When there is business activity gets completed, that data will be available in the flow and become available for use instantly. Aggregate tables are the tables which contain the existing warehouse data which has been grouped to certain level of dimensions. It is easy to retrieve data from the aggregated tables than the original table which has more number of records. Time dimensions are usually loaded through all possible dates in a year and it can be done through a program.
Here, years can be represented with one row per day. Non-Addictive facts are said to be facts that cannot be summed up for any of the dimensions present in the fact table.
If there are changes in the dimensions, same facts can be useful. A Datamart is a specialized version of Datawarehousing and it contains a snapshot of operational data that helps the business people to decide with the analysis of past trends and experiences. A data mart helps to emphasizes on easy access to relevant information. An active datawarehouse is a datawarehouse that enables decision makers within a company or organization to manage customer relationships effectively and efficiently.
Datawarehouse is a place where the whole data is stored for analyzing, but OLAP is used for analyzing the data, managing aggregations, information partitioning into minor level information.
ER diagram is abbreviated as Entity-Relationship diagram which illustrates the interrelationships between the entities in the database. This diagram shows the structure of each tables and the links between the tables.
Foreign keys of dimension tables are primary keys of entity tables. Foreign keys of fact tables are the primary keys of the dimension tables. SCD is defined as slowly changing dimensions, and it applies to the cases where record changes over time.
BUS schema consists of suite of confirmed dimension and standardized definition if there is a fact tables. Star schema is nothing but a type of organizing the tables in such a way that result can be retrieved from the database quickly in the data warehouse environment. Snowflake schema which has primary dimension table to which one or more dimensions can be joined. The primary dimension table is the only table that can be joined with the fact table.
Core dimension is nothing but a Dimension table which is used as dedicated for single fact table or datamart.
Name itself implies that it is a self explanatory term. Cleaning of Orphan records, Data breaching business rules, Inconsistent data and missing information in a database. Metadata is defined as data about the data.
The metadata contains information like number of columns used, fix width and limited width, ordering of fields and data types of the fields. In datawarehousing, loops are existing between the tables. If there is a loop between the tables, then the query generation will take more time and it creates ambiguity.
It is advised to avoid loop between the tables. Yes, dimension table can have numeric value as they are the descriptive elements of our business. Cubes are logical representation of multidimensional data. The edge of the cube has the dimension members,and the body of the cube contains the data values. Dimensional Modeling is a concept which can be used by dataware house designers to build their own datawarehouse. That is why we design a separate system that will have a subject oriented OLAP system Interview questions and answers — free pdf download Page 6 of 31 7.
What are Aggregate tables? Aggregate table contains the summary of existing warehouse data which is grouped to certain levels of dimensions. Retrieving the required data from the actual table, which have millions of records will take more time and also affects the server performance.
To avoid this we can aggregate the table to certain required level and can use it. This tables reduces the load in the database server and increases the performance of the query and can retrieve the result very fastly. Interview questions and answers — free pdf download Page 7 of 31 8. Why is Data Modeling Important? Data modeling is probably the most labor intensive and time consuming part of the development process.
Why bother especially if you are pressed for time? A common response by practitioners who write on the subject is that you should no more build a database without a model than you should build a house without blueprints.
The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language , it can be reviewed and verified as correct by the end-users.
The data model is also detailed enough to be used by the database developers to use as a "blueprint" for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning you may create a database that omits data required to create critical reports, Interview questions and answers — free pdf download Page 8 of 31 9.
Interview questions and answers — free pdf download Page 9 of 31 What is Dimensional Modelling? Why is it important? Dimensional Modelling is a design concept used by many data warehouse desginers to build thier datawarehouse. In this design model all the data is stored in two types of tables - Facts table and Dimension table.
Interview questions and answers — free pdf download Page 10 of 31 What is ETL? ETL stands for extraction, transformation and loading. ETL provide developers with an interface for designing source-to-target mappings, ransformation and job control parameter. Interview questions and answers — free pdf download Page 11 of 31 What does level of Granularity of a fact table signify? Granularity The first step in designing a fact table is to determine the granularity of the fact table.
By granularity, we mean the lowest level of information that will be stored in the fact table. This constitutes two steps: Determine which dimensions will be included.
Determine where along the hierarchy of each dimension the information will be kept. The determining factors usually goes back to the requirements Interview questions and answers — free pdf download Page 12 of 31 Normalization is process for assigning attributes to entities—Reducesdata redundancies—Helps eliminate data anomalies— Produces controlledredundancies to link tables 2.
Repeating groups must beeliminated, Dependencies can be identified, All key attributesdefined,No repeating groups in table 2NF: The Table is already in1NF,Includes no partial dependencies—No attribute dependent on a portionof primary key, Still possible to exhibit transitivedependency,Attributes may be functionally dependent on non-keyattributes 3NF: The Table is already in 2NF, Contains no transitivedependencies Interview questions and answers — free pdf download Page 13 of 31 What are the Different methods of loading Dimension tables?
Conventional Load: Before loading the data, all the Table constraints will be checked against the data. Direct load: Faster Loading All the Constraints will be disabled.
Data will be loaded directly.
Later the data will be checked against the table constraints and the bad data won't be indexed. Interview questions and answers — free pdf download Page 14 of 31 What are Data Marts? Data Marts are designed to help manager make strategic decisions about their business. Data Marts are subset of the corporate-wide data that is of value to a specific group of users. There are two types of Data Marts: Independent data marts — sources from data captured form OLTP system, external providers or from data generated locally within a particular department or geographic area.
Dependent data mart — sources directly form enterprise data warehouses. Interview questions and answers — free pdf download Page 15 of 31 What is a lookup table? A lookUp table is the one which is used when updating a warehouse.
Interview questions and answers — free pdf download Page 16 of 31 What is Fact table? Fact Table contains the measurements or metrics or facts of business process. If your business process is "Sales" , then a measurement of this business process such as "monthly sales number" is captured in the Fact table.
Fact table also contains the foriegn keys for the dimension tables. Interview questions and answers — free pdf download Page 17 of 31 What is a level of Granularity of a fact table? Level of granularity means level of detail that you put into the fact table in a data warehouse. For example: Based on design you can decide to put the sales data in each transaction.
Now, level of granularity would mean what detail are you willing to put for each transactional fact. Product sales with respect to each minute or you want to aggregate it upto minute and put that data.
Interview questions and answers — free pdf download Page 18 of 31 How are the Dimension tables designed? Most dimension tables are designed using Normalization principles upto 2NF. In some instances they are further normalized to 3NF.
Find where data for this dimension are located. Figure out how to extract this data.
Determine how to maintain changes to this dimension see more on this in the next section. Interview questions and answers — free pdf download Page 19 of 31 What type of Indexing mechanism do we need to use for a typical datawarehouse? On the fact table it is best to use bitmap indexes.
To my knowledge, SQLServer does not support bitmap indexes. Only Oracle supports bitmaps. Interview questions and answers — free pdf download Page 20 of 31 What are conformed dimensions? Conformed dimentions are dimensions which are common to the cubes. What Snow Flake Schema? Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.
Interview questions and answers — free pdf download Page 22 of 31 Useful job interview materials: If you need top free ebooks below for your job interview, please visit: Top 6 tips for job interview Interview questions and answers — free pdf download Page 24 of 31 Tip 1: Do your homework You'll likely be asked difficult questions during the interview. Spend time researching the company.