Important Short Questions and Answers: Data Warehousing Business Analysis

Business Analysis

1.What are the five categories of decision support tool

Reporting

Managaed query

Executive information system

On_line analytical processing

Data mining

2.What are the rwo types of reporting tools?

Production reporting tools

desktop report writers

3.List the guide lines of OLAP

Multidimensional conceptual vie

Transparency

Accessibility

Consistent reporting performance

Client/server architecture

Generic dimensionality

Dynamic sparse matrix handling

Multi-user support

Unrestricted cross-dimensional operations

Intuitive data manipulation

Flexible reporting

12)Unlimited dimensions and aggregation levels

4.What is impromptu?

Impromptu is an interactive database reporting tool. It allows Power Users to query data without programming knowledge. When using the Impromptu tool, no data is written or changed in the database. It is only capable of reading the data.

5.List the Different types of frames and its purpose for creating frame based reporting

Form frame: An empty form frame appears.

 List frame: An empty list frame appears.

Text frame: The flashing I-beam appears where you can begin inserting text.

Picture frame: The Source tab (Picture Properties dialog box) appears. You can use this tab to select the image to include in the frame.

 Chart frame: The Data tab (Chart Properties dialog box) appears. You can use this tab to select the data item to include in the chart.

 OLE Object: The Insert Object dialog box appears where you can locate and select the file you want to insert, or you can create a new object using the software listed in the Object Type box.

6. Define OLAP

OLAP stands for Online Analytical Processing. It uses database tables (fact and dimension tables) to enable multidimensional viewing, analysis and querying of large amounts of data. E.g. OLAP technology could provide management with fast answers to complex queries on their operational data or enable them to analyze their company’s historical data for trends and patterns.

7.What are the Categories of OLAP Tools?

MOLAP

ROLAP

HOLAP (MQE: Managed Query Environment)

8.What is OLTP vs OLAP?

OLTP stands for On Line Transaction Processing and is a data modeling approach typically used to facilitate and manage usual business applications. Most of applications you see and use are OLTP based. OLTP technology used to perform updates on operational or transactional systems (e.g., point of sale systems)

OLAP stands for On Line Analytic Processing and is an approach to answer multi-dimensional queries. OLAP was conceived for Management Information Systems and Decision Support Systems. OLAP technology used to perform complex analysis of the data in a data warehouse.

9.Write short notes on multidimensional data model?

Data warehouses and OLTP tools are based on a multidimensional data model. This model is used for the design of corporate data warehouses and department data marts. This model contains a Star schema, Snowflake schema and Fact constellation schemas. The core of the multidimensional model is the data cube.

10. Define data cube?

It consists of a large set of facts (or) measures and a number of dimensions.

11. What are facts?

Facts are numerical measures. Facts can also be considered as quantities by which we can analyze the relationship between dimensions.

12. What are dimensions?

Dimensions are the entities (or) perspectives with respect to an organization for keeping records and are hierarchical in nature.

13. Define dimension table?

A dimension table is used for describing the dimension. (e.g.) A dimension table for item may contain the attributes item_ name, brand and type.

14. Define fact table?

Fact table contains the name of facts (or) measures as well as keys to each of the related dimensional tables.

15. What are lattice of cuboids?

In data warehousing research literature, a cube can also be called as cuboids. For different (or) set of dimensions, we can construct a lattice of cuboids, each showing the data at different level. The lattice of cuboids is also referred to as data cube.

16. What is apex cuboid?

The 0-D cuboid which holds the highest level of summarization is called the apex cuboid. The apex cuboid is typically denoted by all.

17. Define OLTP?

If an on-line operational database systems is used for efficient retrieval, efficient storage and management of large amounts of data, then the system is said to be on-line transaction processing.

18. Define OLAP?

Data warehouse systems serves users (or) knowledge workers in the role of data analysis and decision-making. Such systems can organize and present data in various formats. These systems are known as on-line analytical processing systems.

19.  How a database design is represented in OLTP systems?

Entity-relation model

20. How a database design is represented in OLAP systems?

Star schema

Snowflake schema

Fact constellation schema

21. What is star schema?

The star schema architecture is the simplest data warehouse schema. It is called a star schema because the diagram resembles a star, with points radiating from a center. The center of the star consists of fact table and the points of the star are the dimension tables.

The main characteristics of star schema:

Simple structure-> easy to understand schema   Great query effectives-> small number of tables to join

22. What is Snowflake schema?

It is the result of decomposing one or more of the dimensions. The many-to-one relationships among sets of attributes of a dimension can separate new dimension tables, forming a hierarchy. The decomposed snowflake structure visualizes the hierarchical structure of dimensions very well.

23. What is Fact constellation schema:

For each star schema it is possible to construct fact constellation schema (for example by splitting the original star schema into more star schemes each of them describes facts on another level of dimension hierarchies). The fact constellation architecture contains multiple fact tables that share many dimension tables.

24. List out the OLAP operations in multidimensional data model?

Roll-up

Drill-down

Slice and dice

Pivot (or) rotate

25. What is roll-up operation?

The roll-up operation is also called drill-up operation which performs aggregation on a data cube either by climbing up a concept hierarchy for a dimension (or) by dimension reduction.

26. What is drill-down operation?

Drill-down is the reverse of roll-up operation. It navigates from less detailed data to more detailed data. Drill-down operation can be taken place by stepping down a concept hierarchy for a dimension.

27. What is slice operation?

The slice operation performs a selection on one dimension of the cube resulting in a sub cube.

28. What is dice operation?

The dice operation defines a sub cube by performing a selection on two (or) more dimensions.

29. What is pivot operation?

This is a visualization operation that rotates the data axes in an alternative presentation of the data.

30. List the distinct features of OLTP with OLAP. Distinct features (OLTP vs. OLAP):

User and system orientation: customer vs. market

Data contents: current, detailed vs. historical, consolidated

Database design: ER + application vs. star + subject

View: current, local vs. evolutionary, integrated

Access patterns: update vs. read-only but complex queries