DWH Material - III

OLAPon-line Analytical Processing (OLAP) is a category of software technology that enables analysts, managers and executives to gain insight into data through fast, consistent, interactive access to a wide variety of possible views of information that has been transformed from raw data to reflect the real dimensionality of the enterprise as understood by the user.

OLAP functionality is characterized by dynamic multi-dimensional analysis of consolidated enterprise data supporting end user analytical and navigational activities including:

- calculations and modeling applied across dimensions, through hierarchies and/or across members

-        trend analysis over sequential time periods

-         slicing subsets for on-screen viewing

-         drill-down to deeper levels of consolidation

-         reach-through to underlying detail data

-         rotation to new dimensional comparisons in the viewing area

OLAP is implemented in a multi-user client/server mode and offers consistently rapid response to queries, regardless of database size and complexity. OLAP helps the user synthesize enterprise information through comparative, personalized viewing, as well as through analysis of historical and projected data in various "what-if" data model scenarios. This is achieved through use of an OLAP Server.

OLAP Server

An OLAP server is a high-capacity, multi-user data manipulation engine specifically designed to support and operate on multi-dimensional data structures. A multi- dimensional structure is arranged so that every data item is located and accessed based on the intersection of the dimension members which define that item. The design of the server and the structure of the data are optimized for rapid ad-hoc information retrieval in any orientation, as well as for fast, flexible calculation and transformation of raw data based on formulaic relationships. The OLAP Server may either physically stage the processed multi-dimensional information to deliver consistent and rapid response times to end users, or it may populate its data structures in real-time from relational or other databases, or offer a choice of both. Given the current state of technology and the end user requirement for consistent and rapid response times, staging the multi-dimensional data in the OLAP Server is often the preferred method.

OLAP Client

End user applications that can request slices from OLAP servers and provide two- dimensional or multi-dimensional displays, user modifications, selections, ranking, calculations, etc., for visualization and navigation purposes. OLAP clients may be as simple as a spreadsheet program retrieving a slice for further work by a spreadsheet- literate user or as high-functioned as a financial modeling or sales analysis application.

ROLAP - Relational OLAP (ROLAP) application provides the dimensional interface to a relational database.

MOLAP - Multidimensional OLAP (MOLAP) engines provide highly specialized support for analysis. Because facts are prestored at all valid combinations of the dimensions, the query performance is very high where as the amount of data to be stored is voluminous.


Drill Down/Up

Drilling down or up is a specific analytical technique whereby the user navigates among levels of data ranging from the most summarized (up) to the most detailed (down). The drilling paths may be defined by the hierarchies within dimensions or other relationships that may be dynamic within or between dimensions. For example, when viewing sales data for North America, a drill-down operation in the Region dimension would then display Canada, the eastern United States and the Western United States. A further drill- down on Canada might display Toronto, Vancouver, Montreal, etc.

DWH Material - II

ODSThe Operational Data Store (ODS) is a subject oriented, integrated, volatile & current data store containing detailed corporate data. ODS stores only a specific data (Ex. credit data) from different systems. Because it is always current, it requires very frequent data loads from source systems to the ODS.

Hierarchical Relationships

Any dimension's members may be organized based on parent-child relationships, typically where a parent member represents the consolidation of the members which are its children. The result is a hierarchy, and the parent/child relationships are hierarchical relationships. For example, the Time dimension would contain the following hierarchy
Day – Week – Month – Quarter – Year.Based on the business queries, such hierarchies would be formed in all dimensions.

ExtractionRefers to the process of extracting data from OLTP systems by means of scripts/tools for use in the data warehouse.

TransformationRefers to a set of procedures that are used to ensure the uniformity of data in the warehouse. Multiple OLTP systems may store the same data in different formats. For example, gender may be stores as Male/Female, 1/0, M/F. The process of standardizing this storage and applying the standard for all extracted data is called transformation.
CleaningRefers to the process of validating the extracted data. The business queries would specify some business rules that the extracted data has to undergo. The process of applying these rules is called cleansing.

ETLExtraction, Transformation & Loading process loads the data from heterogeneous source systems to the data warehouse. The data is also cleansed by eliminating duplicate rows, standardizing on acronyms (For ex, co., comp., company etc) in the staging area. The ETL process is triggered at a frequency based on the business needs.

Multi-dimensional/HyperCube/CubeA group of data cells arranged by the dimensions of the data. For example, a spreadsheet exemplifies a two-dimensional array with the data cells arranged in rows and columns, each being a dimension. A three-dimensional array can be visualized as a cube with each dimension forming a side of the cube, including any slice parallel with that side. Higher dimensional arrays have no physical metaphor, but they organize the data in the way users think of their enterprise. Typical enterprise dimensions are time, measures, products, geographical regions, sales channels, etc.

Member/AttributeA dimension member is a discrete name or identifier used to identify a data item's position and description within a dimension. For example, January 1989 or 1Qtr93 are typical examples of members of a Time dimension. Wholesale, Retail, etc., are typical examples of members of a Distribution Channel dimension.

Consolidation/AggregationMulti-dimensional databases generally have hierarchies or formula-based relationships of data within each dimension. Consolidation involves computing all of these data relationships for one or more dimensions, for example, adding up all Departments to get Total Division data. While such relationships are normally summations, any type of computational relationship or formula might be defined.

DWH Material

Business Query

This refers to the set of queries that the warehouse would be expected to answer. Business queries define the structure and the usage of the warehouse. This is the reason that warehouse models follow a business model rather than the operational model used in transactional systems. A sample business query would be: ‘List all stores that showed a profit increase of over 10% for the northwest region for the last quarter’.

OLTP

On-line Transaction Processing (OLTP) systems are the operational systems that record and process the day to day transactions. Hence they are always current. OLTP systems do not support adhoc queries because they store data mostly in Entity Relation model. (Ex. NM Spot TV)

Dimensional Model

Dimensional Model is the logical design technique often used for data warehouses. It contains the same information as an Entity Relation model but packages the data in a symmetric format whose design goals are user understandability, query performance and resilience to change.

FactFacts are the numerical measurements of the business. They are continuously valued and additive. The term ‘FACT’ refers to the subject areas that are to be analyzed. Consider an example in which a retail company wishes to employ a data warehouse. The ‘Facts’ for this warehouse would be the subject areas that the managers of the company would like to analyze (ex. Sales, Inventory, Profit etc). Examples for fact are cost price, sold price, units sold.

DimensionDimensions represent those data element that would be used for analyzing the Facts. For example, the above listed facts would be analyzed using the dimensions of Time (to provide time-based analysis), Geography (To provide performance analysis across regions), Product (To provide analysis across products). Dimensions will store the textual descriptions of the business. Examples are product definition attributes like color, size etc.