1. What is the difference between ODS and OLTP?

ODS:- It is nothing but a collection of tables created in the Data warehouse that maintains only current data.

Where as OLTP maintains the data only for transactions,these are designed for recording daily operations and transactions of a business.

2. What is the difference between Datawarehousing and BusinessIntelligence?

Data warehousing deals with all aspects of managing the development, implementation and operation of a data warehouse or data mart including meta data management, data acquisition, data cleansing, data transformation, storage management, data distribution, data archiving, operational reporting, analytical reporting, security management, backup/recovery planning, etc. Business intelligence, on the other hand, is a set of software tools that enable an organization to analyze measurable aspects of their business such as sales performance, profitability, operational efficiency, effectiveness of marketing campaigns, market penetration among certain customer groups, cost trends, anomalies and exceptions, etc. Typically, the term ?business intelligence? is used to encompass OLAP, data visualization, data mining and query/reporting tools.Think of the data warehouse as the back office and business intelligence as the entire business including the back office. The business needs the back office on which to function, but the back office without a business to support, makes no sense.

As explained , Data warehouse contains the data or data mart which final product of all the process like meta data management , acquire , data cleansing , transformation and load other process as mentioned above. Business intelligence are set of software which connects to data mart to do various reporting useful for the business or good running of the company. All the business decision are taken based on the data warehousing reporting. Hope this make sense.

3. Explain What is the difference between datawarehouse and BI?

Simply speaking, BI is the capability of analyzing the data of a datawarehouse in advantage of that business. A BI tool analyzes the data of a datawarehouse and to come into some business decision depending on the result of the analysis.

Ware House Mangement is So Important in the Large number of Data's Handling Part.For Ex.In Ms.Access ,we can store 100 Thosand data Consistently and can be retrived.Same like Each and Every DB having them Own Speciality.From various DB number Columns like more than 200 Column ,Data Retrive is not so easy through SQL & PL&SQL.Lots lines should write.In this case to maintain the data the DWH is So important.Especialy for Banking,Insurance,Telecom,Business etc.

4. Explain Is OLAP databases are called decision support system true/false?


OLAP (online analytical processing) works by analysing aggregated data to give final reports to top management to take action/decisions on business which is same as DSS.(Decision support system)

5. What is the difference between datawarehouse and BI?

Simply speaking, BI is the capability of analyzing the data of a datawarehouse in advantage of that business. A BI tool analyzes the data of a datawarehouse and to come into some business decision depending on the result of the analysis.

Business Intelligence is a collection of broad category of application programs and techniques used to querying,retrieving,reporting and analyzing the business informations multidimentionally.

Business Intelligence is a collection of application specifications which allow the client applications to retrieve business informations from the DataWare House in order to make some business decissions.

6. What is active data warehousing?

An active data warehouse provides information that enables decision-makers within an organization to manage customer relationships nimbly, efficiently and proactively. Active data warehousing is all about integrating advanced decision support with day-to-day-even minute-to-minute-decision making in a way that increases quality of those customer touches which encourages customer loyalty and thus secure an organization's bottom line. The marketplace is coming of age as we progress from first-generation "passive" decision-support systems to current- and next-generation "active" data warehouse implementations

7. What is snapshot?

You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data. However, you must reconnect to the catalog if you want to refresh the data.

8. Where the applications and where
ware house management system is used?

Data warehousing system is used in OLAP systems. Systems in which mainly the analysis of the data is needed. High level and Top executives use this system for analysis purpose so that they can make correct decisions that can boost the productivity of the org.

9. Please explain in detail about
type 1,
type 2(SCD),
type 3?


Most Recent Value

Type-2(full History)

i) Version Number

ii) Flag

iii) Date


Current and one Perivies value

SCD'S slow change dimension

there are three types
scd1, scd2, scd3

scd1:- suppose it the data got updated in the table then there r 2 methods one is to drop the table and upload the new one. but its along process.
here by using table compression we can update the particular data that has been modified. that is scd1

scd2:- by using key generation we r going to generate the new rownum column if there r any update the next row will be updated one and row numbers will be increamented automatically by 1

scd3:- i this one a extra column is added and updated infromation is stored in that column. if again table is update another column is added to it again...

in this one scd2 is mostly used..

10. Explain what is aggregate table and aggregate fact table ... any examples of both?

Aggregate table contains summarized data. The materialized view are aggregated tables.

for ex in sales we have only date transaction. if we want to create a report like sales by product per year. in such cases we aggregate the date?vales into week_agg, month_agg, quarter_agg, year_agg. to retrive date from this tables we use @aggrtegate function.

aggregate table is one of the data transaction function and some time it is create a protect per year.this aggregate value is week agg ,month agg quarter agg function

Download Interview PDF