Blog Archive

Theme images by Storman. Powered by Blogger.

Saturday, July 23, 2016

Latest Data WareHouse Interview Questions Fully Solved.


Latest Dataware House Interview Questions Fully Solved. Data warehouse Fully Solved Interview Questions For Freshers. DW Interview Questions and Answers.

1. What does the subject oriented data warehouse signifies?

A: Subject oriented signifies that the data warehouse stores the information around a particular subject such as product, customer, sales etc.

2. List any five applications of Data Warehouse?

Some applications include Financial services, Banking Services, Customer goods, Retail Sectors, Controlled Manufacturing.

3.What does OLAP and OLTP stand for?

OLAP is acronym of Online Analytical Processing and OLTP is acronym of Online Transactional Processing

4.What is the very basic difference between data warehouse and Operational Databases?

Data warehouse contains the historical information that is made available for analysis of the business whereas the Operational database contains the current information that is required to run the business.

5. List the Schema that Data Warehouse System implements ?

Data Warehouse can implement Star Schema, Snowflake Schema or the Fact Constellation Schema

6. What is Data Warehousing?

Data Warehousing is the process of constructing and using the data warehouse.

7. List the process that are involved in Data Warehousing?

Data Warehousing involves data cleaning, data integration and data consolidations.

8. List the functions of data warehouse tools and utilities?

The functions performed by Data warehouse tool and utilities are Data Extraction, Data Cleaning, Data Transformation, Data Loading and Refreshing

9. What do you mean by Data Extraction?

Data Extraction means gathering the data from multiple heterogeneous sources.

10. What is ODS?

ODS is abbreviated as Operational Data Store and it is a repository of real time operational data rather than long term trend data.

11. What is the difference between View and Materialized View?

A view is nothing but a virtual table which takes the output of the query and it can be used in place of tables.

A materialized view is nothing but an indirect access to the table data by storing the results of a query in a separate schema.

12. What is ETL?

ETL is abbreviated as Extract, Transform and Load. ETL is a software which is used to reads the data from the specified data source and extracts a desired subset of data. Next, it transform the data using rules and lookup tables and convert it to a desired state.

Then, load function is used to load the resulting data to the target database.

13. What is VLDB?

VLDB is abbreviated as Very Large Database and its size is set to be more than one terabyte database. These are decision support systems which is used to server large number of users.

14. What is real-time datawarehousing?

Real-time datawarehousing captures the business data whenever it occurs. When there is business activity gets completed, that data will be available in the flow and become available for use instantly.

15. What are Aggregate tables?

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.

This table reduces the load in the database server and increases the performance of the query.

16. What is factless fact tables?

A factless fact tables are the fact table which doesn’t contain numeric fact column in the fact table.

17. How can we load the time dimension?

Time dimensions are usually loaded through all possible dates in a year and it can be done through a program. Here, 100 years can be represented with one row per day.

18. What are Non-additive facts?

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.

19. What is conformed fact?

Conformed fact is a table which can be used  across multiple data marts in combined with the multiple fact tables.

20. What is Datamart?

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.

21. What is Active Datawarehousing?

An active datawarehouse is a datawarehouse that enables decision makers within a company or organization to manage customer relationships effectively and efficiently.

22. What is the difference between Datawarehouse and OLAP?

Data ware house 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.

23. What is ER Diagram?

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.

24. What are the key columns in Fact and dimension 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.

25. What is SCD?

SCD is defined as slowly changing dimensions, and it applies to the cases where record changes over time.

26. What are the types of SCD?

There are three types of SCD and they are as follows:

SCD 1 – The new record replaces the original record

SCD 2 – A new record is added to the existing customer dimension table

SCD 3 – A original data is modified to include new data

27. What is BUS Schema?

BUS schema consists of suite of confirmed dimension and standardized definition if there is a fact tables.

28. What is Star Schema?

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.

29. What is Snowflake Schema?

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.

30. What is a core dimension?

Core dimension is nothing but a Dimension table which is used as dedicated for single fact table or datamart.

31. What is called data cleaning?

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.

32. What is Metadata?

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.

33. What are loops in Datawarehousing?

In data ware housing, 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.

34. Whether Dimension table can have numeric value?

Yes, dimension table can have numeric value as they are the descriptive elements of our business.

35. What is the definition of Cube in Dataware housing?

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.

36. What is called Dimensional Modelling?

Dimensional Modeling is a concept which can be used by dataware house designers to build their own dataware house. This model can be stored in two types of tables – Facts and Dimension table.

Fact table has facts and measurements of the business and dimension table contains the context of measurements.

37. What are the types of Dimensional Modeling?

There are three types of Dimensional Modeling and they are as follows:

    Conceptual Modeling
    Logical Modeling
    Physical Modeling

38. What is surrogate key?

Surrogate key is nothing but a substitute for the natural primary key. It is set to be a unique identifier for each row that can be used for the primary key to a table.

39. What is the difference between ER Modeling and Dimensional Modeling?

ER modeling will have logical and physical model but Dimensional modeling will have only Physical model.

ER Modeling is used for normalizing the OLTP database design whereas Dimensional Modeling is used for de-normalizing the ROLAP and MOLAP design.

40. What are the steps to build the data warehouse?

Following are the steps to be followed to build the datawaerhouse:

    Gathering business requirements
    Identifying the necessary sources
    Identifying the facts
    Defining the dimensions
    Defining the attributes
    Redefine the dimensions and attributes if required
    Organize the Attribute hierarchy
    Define Relationships
    Assign unique Identifiers

41. What does MetaData Respiratory contains?

Metadata respiratory contains Definition of data warehouse, Business Metadata, Operational Metadata, Data for mapping from operational environment to data warehouse and the Alorithms for summarization

42. How does a Data Cube help?

Data cube help us to represent the data in multiple dimensions. The data cube is defined by dimensions and facts.

43. Define Dimension?

The dimensions are the entities with respect to which an enterprise keep the records.

44. Explain Data mart?

Data mart contains the subset of organisation-wide data. This subset of data is valuable to specific group of an organisation. in other words we can say that data mart contains only that data which is specific to a particular group.

45. What is Virtual Warehouse?

The view over a operational data warehouse is known as virtual warehouse.

46. List the phases involved in Data warehouse delivery Process?The stages are 

IT strategy, Education, Business Case Analysis, technical Blueprint, Build the version, History Load, Ad hoc query,Requirement Evolution, Automation, Extending Scope.

47. Explain Load Manager?

This Component performs the operations required to extract and load process. The size and complexity of load manager varies between specific solutions from data warehouse to data warehouse.

48. Define the function of Load Manager?

Extract the data from source system.Fast Load the extracted data into temporary data store.Perform simple transformations into structure similar to the one in the data warehouse.

49. Explain Warehouse Manager?

Warehouse manager is responsible for the warehouse management process.The warehouse manager consist of third party system software, C programs and shell scripts.The size and complexity of warehouse manager varies between specific solutions.

50. Define functions of Warehouse Manager?

The Warehouse Manager performs consistency and referential integrity checks, Creates the indexes, business views, partition views against the base data, transforms and merge the source data into the temporary store into the published data warehouse, Backup the data in the data warehouse and archives the data that has reached the end of its captured life.

0 on: "Latest Data WareHouse Interview Questions Fully Solved."