Important and Useful Interview Quesions of “Data Ware Housing”

1. What is Business Intelligence?

Business intelligence is the term which is used to simplify the process of data analysis and data determination. In an organization, it enable the decision makers (employees) at all the level, to do effective decision making, easy access, analyze, collaborate and act on information or data. To measure, manage and improve the performance of individuals, processes, teams and business units, many BI apps has been evolved nowadays with intuitive characteristics such as easy data access and flexibility.

2.Describe the BI Flow?

BI flow / functions include three stages. They are;

• Monitoring: To provide the quality data
• Analyzing: To gain deeper data insights and to facilitate better decision making
• Planning: To align and link the business executive’s (decision maker’s) decision with the corporate or company’s strategies and goals.

3. What is the Difference between BI and ETL?

ETL is the term which stands for (Extract-Transform-Load). These three are the data processing phases in data warehousing.

• Extract: Read and copy the data from the source system to the staging area.
• Transform: The data in the staging area is validated, cleansed and then transformed under business rules.
• Load: The transformed data is then moved to its presenting area (end target).

Whereas, BI is the term which primarily focused on simplifying the process of data analysis, data determination and effective decision making via easy access, analyze, collaborate and act on data.

4. What is Data warehousing?

Data warehousing is the technology that,

• Collects and aggregates structures data from multiple sources.
• Then compares and analyzes data with each other to negotiate the differences in storage schema and to facilitate better business intelligence.

5. What is OLAP and OLTP System?

OLAP (Online Analytical Processing) is the system which is used to access aggregated and organized data in multi dimensional structures from different business data sources.

Whereas OLTP (Online Transaction Processing) system can be used as the transaction process monitor under mainframe environment. CICS (Customer Information Control System) and IMS (Information Management System) are the subsets of OLAP.

6. What are SSIS, SSAS and SSRS?

• SSIS (SQL Server integration services): It is used to collect data (i.e.) it does ETL (Extract-Transform-Load) Operation.
• SSAS (SQL Server analysis services): It is used to analyze the data and creates cubes, facts, dimensions and measures.
• SSRS (SQL Server reporting services): It is used to view the helps us to viewthe analyzed data in different formats like graphical, tabular etc.

7. What are the major limitations of view?

• Temporary table’s creation and Parameter Passage is not possible in Views.
• Rules and defaults can’t be associated in Views.
• Usage of ROLLUP, HAVING and CUBE in Group by clause is not possible.
• AGV, MAX, Min is not allowed.
• Count (*) in a view creation query is not allowed.

8. Difference between Control Flow and Data Flow ?

Control flow

When the package runs, it executes Tasks, Containers and Precedence Constraints.

• Containers provide structures in packages.
• Tasks provide functionality.
• Precedence Constraints that connect the executables, containers, and tasks into an ordered control flow.

Data flow
The Data Flow will execute within the SSIS package and used to manipulate (creates, orders, Moves and runs) the data. It consists of the,

• Sources: Extract the Data.
• Destinations: To Load the Data.
• Transformations: To Modify the Data.
• Paths: To Link sources, transformations, and destinations.