Data Warehousing Interview Questions and Answer Part – 5
41) What is snapshot with reference to data warehouse?
You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data.
42) 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.
43) What is the difference between data warehousing and business intelligence?
Data warehouses 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.
44) What is data warehousing?
“In computing, a data warehouse (DW) is a database used for reporting and analysis. The data stored in the warehouse is uploaded from the operational systems. The data may pass through an operational data store for additional operations before it is used in the DW for reporting.
A data warehouse maintains its functions in three layers: staging, integration, and access.
Staging is used to store raw data for use by developers. The integration layer is used to integrate data and to have a level of abstraction from users. The access layer is for getting data out for users.
The term Data Warehouse was coined by Bill Inmon in 1990, which he defined in the following way:
“A warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management’s decision making process”.
He defined the terms in the sentence as follows:
Subject Oriented: Data that gives information about a particular subject instead of about a company’s ongoing operations.
Integrated: Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.
Time-variant: All data in the data warehouse is identified with a particular time period.
Non-volatile: Data is stable in a data warehouse. More data is added but data is never removed.
45) What are the different types of data warehousing?
Types of data warehousing are:
1. Enterprise Data warehousing
2. Operational data store (ODS)
3. Data Mart
46) 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
47) 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.
48) 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.
49) What is the data type of the surrogate key?
There is no data type for a Surrogate Key.
Requirement of a surrogate Key: UNIQUE
Recommended data type of a Surrogate key is NUMERIC.
50) What are the differences between star and snowflake schema?
Star schema: A single fact table with N number of Dimension
Snowflake schema: Any dimensions with extended dimensions are known as snowflake schema.
