Latest Data Warehouse Interview Questions and Answers

Read Top Data Warehouse Interview Questions and Answers Experienced, Freshers for Job Interview Quiz with Solutions.

What are the Characteristics of Data Files?

A data file can be associated with only one database. Once created a data file can’t change size. One or more data files form a logical unit of database storage called a table space.

What is a linked cube?

Linked cube in which a sub-set of the data can be analyzed into detail. The linking ensures that the data in the cubes remain consistent.

What are Clusters?

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

What is Mirrored on-line Redo Log?

A mirrored on-line redo log consists of copies of on-line redo log files physically located on separate disks; changes made to one member of the group are made to all members.

How can we run the graph? What is the procedure for that? How can we schedule the graph in UNIX?

If you want to run the graph through GDE then after save the graph just press F5 button of your keyboard, it will run automatically. If you want to run through the shell script then you have to fire the command at your UNIX box.

What is Dimensional Modelling?

Dimensional Modelling is a design concept used by many data warehouse designers to build their 
data warehouse. In this design model all the data is stored in two types of tables – Facts table and Dimension table. Fact table contains the facts/measurements of the business and the dimension table contains the context of measurements i.e., the dimensions on which the facts are calculated.

After the complete generation of a report who will test the report and who will analyze it?

After the completion of reporting, reports will be sent to business analysts. They will analyze the data from different points of view so that they can make a proper business decisions.

What is the difference between view and materialized view?

View – store the SQL statement in the database and let you use it as a table. Every time you access the view, the SQL statement executes. Materialized view – stores the results of the SQL in table form in the database. SQL statement only executes once and after that every time you run the query, the stored result set is used. Pros include quick query results.

What is Rollback Segment?

A Database contains one or more Rollback Segments to temporarily store “undo” information.

What are the different types of Segments?

Data Segment,

Index Segment,

Rollback Segment


Temporary Segment

What is Partial Backup?

A Partial Backup is any operating system backup short of a full backup, taken while the database is open or shut down.

What is a real-time data warehouse? How is it different from near to real-time data warehouse?

As the term suggests, a real-time data warehouse is a system, which reflects all changes to its sources in real time. As simple as it sounds, this is still an area of active research in the field. In traditional DWH, the operational system(s) are kept separate from the DWH for a good reason. The Operational systems are designed to accept inputs or changes to data regularly, hence have a good chance of being regularly queried. On the other hand, a DWH is supposed to do just the opposite – it is

used to query data for reports only. No changes to data, through user actions is expected (or designed). The only inputs could come from the ETL feed at stipulated times. The ETL would source its data from the Operational systems just explained above.

To create a real-time DWH we would have to merge both systems (several ways are being explored), a concept that is against the reason of creating a DWH. Bigger challenges occur in terms of updating aggregated data in facts at real time, still maintaining the surrogate keys. Besides, we would need lightening fast hardware to try this.Near Real time DWH is a trade-off between the conventional design and the dream of all clients today. The frequency of ETL updates in higher in this case for

e.g. once in 2 hours. We can also analyze and use selective refreshes at shorter time intervals, while complete refreshes may still be kept further apart. Selective refreshes would look at only those tables that get updated regularly.

What are the difference between Snow flake and Star Schema? What are situations where Snow flake Schema is better than Star Schema to use and when the opposite is true?

Star schema contains the dimension tables mapped around one or more fact tables. It is a renormalized model and no need to use complicated joins. Also queries results fast.Snowflake schema: It is the normalized form of Star schema. It contains in-depth joins, because the tables are split in to many pieces. We can easily do modification directly in the tables. We have to use complicated joins, since we have more tables.There will be some delay in processing the query.

Can you pass sql queries in filter transformation?

We cannot use sql queries in filter transformation. It will not allow you to override default sql query like other transformations (Source Qualifier, lookup)

What is the main difference between Inmon and Kimball philosophies of data warehousing?

Both differed in the concept of building the data warehouse.According to Kimball, Kimball views data warehousing as a constituency of data marts. Data marts are focused on delivering business objectives for departments in the organization. And the data warehouse is a conformed dimension of the data marts. Hence, a unified view of the enterprise can be obtained from the dimension modeling on a local departmental level.Inmon beliefs in creating a data warehouse on a subject-by-subject area

basis. Hence, the development of the data warehouse can start with data from the online store. Other subject areas can be added to the data warehouse as their needs arise. Point-of-sale (POS) data can be added later if management decides it is necessary.

What is a Table space?

A database is divided into Logical Storage Unit called table spaces. A table space is used to grouped related logical structures together.

Explain the relationship among Database, Table space and Data file?

Each databases logically divided into one or more table spaces one or more data files are explicitly created for each table space.

What is Restricted Mode of Instance Startup?

An instance can be started in (or later altered to be in) restricted mode so that when the database is open connections are limited only to those whose user accounts have been granted the RESTRICTED SESSION system privilege.

What is difference between drill & scope of analysis?

Drilling can be done in drill down, up, through, and across; scope is the overall view of the drill exercise.

What is a cube in data warehousing concept?

Cubes are logical representation of multidimensional data. The edge of the cube contains dimension members and the body of the cube contains data values.

Where the Data cube technology is used?

A multi-dimensional structure called the data cube. A data abstraction allows one to view aggregated data from a number of perspectives. Conceptually, the cube consists of a core or base cuboids, surrounded by a collection of sub-cubes/cuboids that represent the aggregation of the base cuboids along one or more dimensions. We refer to the dimension to be aggregated as the measure attribute, while the remaining dimensions are known as the feature attributes.

What is junk dimension? What is the difference between junk dimension and degenerated dimension?

Junk dimension: Grouping of Random flags and text attributes in a dimension and moving them to a separate sub dimension. Degenerate Dimension: Keeping the control information on Fact table ex: Consider a Dimension table with fields like order number and order line number and have 1:1 relationship with Fact table, In this case this dimension is removed and the order information will be directly store

What is Database Link?

A database link is a named object that describes a “path” from one database to another.

What is an Index Segment?

Each Index has an Index segment that stores all of its data.

What is Archived Redo Log?

Archived Redo Log consists of Redo Log files that have archived before being reused.

I have two Universes created by two difference database can we join them in Designer & Report level? How

We can link one universe to other universe in Universe parameters.

What are the differences between star and snowflake schema?

Star schema: A single fact table with N number of DimensionSnowflake schema: Any dimensions with extended dimensions are known as snowflake schema.

How can you implement many relations in star schema model?

Many-many relations can be implemented by using snowflake schema .With a max of n dimensions.

Why fact table is in normal form?

The fact table consists of the Index keys of the dimension/look up tables and the measures. So whenever we have the keys in a table. That it implies that the table is in the normal form.

What is a Private Synonyms?

A Private Synonyms can be accessed only by the owner.


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