TOP Data Warehouse Interview Questions and Answers

Read Latest Data Warehouse Interview Questions and Answers for Freshers, 2+ Years Experienced Q and A with Explanation PDF.

What is data warehouse architecture?

Data warehousing is the repository of integrated information data will be extracted from the heterogeneous sources. Data warehousing architecture contains the different; sources like oracle, flat files and ERP then after it have the staging area and Data warehousing, after that it has the different Data marts then it have the reports and it also have the ODS – Operation Data Store. This complete architecture is called the Data warehousing Architecture.

What do you mean by static and local variable?

Static variable is not created on function stack but is created in the initialized data segment and hence the variable can be shared across the multiple call of the same function. Usage of static variables within a function is not thread safe.On the other hand, local variable or auto variable is created on function stack and valid only in the context of the function call and is not shared across function calls.

What is Virtual Data Warehousing?

A virtual or point-to-point data warehousing strategy means that end-users are allowed to get at operational databases directly using whatever tools are enabled to the “data access network”

What is Data warehousing Hierarchy?

Hierarchies are logical structures that use ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level. A hierarchy can also be used to define a navigational drill path and to establish a family structure.Within a hierarchy, each level is logically connected to the levels above and below it. Data values at lower levels aggregate

into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies–one for product categories and one for product suppliers.Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. This is one of the key benefits of a data warehouse.When designing hierarchies, you must consider

the relationships in business structures. Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly.

What is a View?

A view is a virtual table. Every view has a Query attached to it. (The Query is a SELECT statement that identifies the columns and rows of the table(s) the view uses.)

Can objects of the same Schema reside in different table spaces?


What are the steps involved in Database Startup?

Start an instance, Mount the Database and Open the Database.

What is data analysis? Where it will be used?

Data analysis: consider that you are running a business and you store the data of that; in some form say in register or in a comp and at the year end you want know the profit or loss then it called data analysis .Data analysis use: then you want to know which product was sold the highest and if the business is running in a loss then finding, where we went wrong we do analysis.

What is a source qualifier?

When you add a relational or a flat file source definition to a mapping, you need to connect it to a Source Qualifier transformation. The Source Qualifier represents the rows that the Informatica Server reads when it executes a session.

What is the difference between metadata and data dictionary?

Meta data is nothing but information about data. It contains the information (i.e. data) about the graphs, its related files, abinitio commands, server information etc

i.e. all kinds of information about project related information etc.

What are data validation strategies for data mart validation after loading process?

Data validation is to make sure that the loaded data is accurate and meets the business requirements. Strategies are different methods followed to meet the validation requirements.

What is an Extent?

An Extent is a specific number of contiguous data blocks, obtained in a single allocation, and used to store a specific type of information.

Can a Table space hold objects from different Schemes?


Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the table space?

All the default storage parameters defined for the table space can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS

values cannot be changed.

What are data modeling and data mining? Where it will be used?

Data modeling is the process of designing a data base model. In this data model data will be stored in two types of table fact table and dimension table Fact table contains the transaction data and dimension table contains the master data. Data mining is process of finding the hidden trends is called the data mining.

What is the data type of the surrogate key?

Data type of the surrogate key is integer, numeric, or number.

What is the difference between mapping parameter & mapping variable in data warehousing?

Mapping Parameter defines the constant value and it cannot change the value throughout the session.Mapping Variables defines the value and it can be change throughout the session

What are the data types present in BO? What happens if we implement view in the designer n report?

Three different data types: Dimensions, Measure, and DetailView is nothing but an alias and it can be used to resolve the loops in the universe.

What is an Index?

An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

Can a View based on another View?


What is On-line Redo Log?

The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to the database. Whenever a transaction is committed, the corresponding redo entries temporarily stores in redo log buffers of the SGA are written to an on-line redo log file by the background process LGWR. The on-line redo log files are used in cyclical fashion.

What is “method/1??

Method 1 is system develop lifecycle create by Arthur Anderson a while back.

What are the steps to build the data warehouse?

Gathering business requirements>>Identifying Sources>>Identifying Facts>>Defining Dimensions>>Define Attributes>>Redefine Dimensions / Attributes>>Organize Attribute

Hierarchy>>Define Relationship>>Assign Unique Identifiers

Explain the advantages of RAID 1, 1/0, and 5. what type of RAID setup would you put your TX logs.

The basic advantage of RAID is to speed up the data reading from permanent storage device (hard disk).

What is surrogate key? Where we use it? Explain with examples.

Surrogate key is a substitution for the natural primary key.It is just a unique identifier or number for each row that can be used for the primary key to the table.

The only requirement for a surrogate primary key is that it is unique for each row in the table.

Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Info sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.

It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.

Some tables have columns such as AIRPORT_NAME OR CITY_NAME which are stated as the primary keys (according to the business users) but ,not only can these change, indexing on a numerical value is probably better and you could consider creating a surrogate key called, say, AIRPORT_ID. This would be internal to the system and as far as the client is concerned, you may display only the AIRPORT_NAME.

What is an Integrity Constrains?

An integrity constraint is a declarative way to define a business rule for a column of a table.

What is a full backup?

A full backup is an operating system backup of all data files, on- line redo log files and control file that constitute ORACLE database and the parameter.

What is Log Switch?

The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.

After the generation of a report to whom we have to deploy or what we do after the completion of a report?

The generated report will be sent to the concerned business users through web or LAN.

What is the advantages data mining over traditional approaches?

Data Mining is used for the estimation of future. For example, if we take a company/business organization, by using the concept of Data Mining, we can predict the future of business in terms of Revenue (or) Employees (or) Customers (or) Orders etc.Traditional approaches use simple algorithms for estimating the future. However, it does not give accurate results when compared to Data Mining.


Popular posts from this blog

TOP Agile Testing Interview Questions and Answers

Latest Agile Testing Interview Questions and Answers

Most Asked ADO.NET Interview Questions and Answers