The various OLAP Capabilities and OLAP Architectures are covered in the separate sections. This topic provides what & why of the same. A Data- Warehouse/Data-Mart is a repository having relational database structure. OLAP is an optional layer, which sits between the Data-Warehouse/Data-Mart and the end-user tools.
As data is moved from various transaction systems into the warehouse, it must be stored in a way that maximizes system flexibility, manageability and overall accessibility. Because the information stored in the warehouse is read-only, historic in nature and includes detailed transaction data, the best data warehousing technology is the relational database. Both Data Warehouses (e.g., comprehensive, enterprise-wide, etc.) and data marts (e.g., subject-OR application-specific) must be accessible to a wide variety of users to satisfy their information needs
Why OLAP is needed
OLAP layer/server provide the capability, which cannot be met by the productivity tools sitting directly on the relational database of warehouse. Data Warehouse first & foremost function is to provide a sanitized repository (system of record) of current and historical details for various purposes (which include analytics, data mining, Strategic planning , enterprise reporting and so on…) OR in other words, provider of the Data. OLAP sits over the Data Warehouse to enable the end-user tools translate the same into information.
In brief, various OLAP Capabilities are:
- The ability to scale to large volumes of data and large numbers of concurrent users.
- Consistent, fast query response times that allow for iterative analysis.
- A calculation engine that includes robust mathematical functions for computing derived data (aggregations, matrix calculations, cross-dimensional calculations, OLAP-aware formulas and procedural calculations).
- A multi-user read/write environment to support users what-if analysis, modeling and planning requirements.
- The ability to be deployed quickly, adopted easily and maintained cost-effectively.
- Robust data-access security and user management.
- Availability of a wide variety of viewing and analysis tools to support different user communities
Difference between Data Warehouse Vs. OLAP
| Comparison Factor
|
Data Warehouse
|
OLAP
|
| Purpose |
| Scope of Content
|
Across the enterprise, functions and processes.
|
Subject OR function linked
|
| Role |
System of record- Data Reference point for BI |
Analytics and end-user BI enablement |
| Data Positioning |
|
|
| Level of detail of data storage
|
Detailed transaction level data, with some aggregate tables
|
Summarized data
|
| Data Structure
|
Dimensional Model in relational database form
|
Dimensional Model with multi-dimensional form (while there are some OLAPs with RDBMS format)
|
Level of pre-calculated Data
|
Limited (As the information is at a transaction level pre-calculated data can lead to significant data-base growth)
|
High
|
Data Volumes
|
Gigabyte to Terabytes
|
Gigabytes
|
| Access |
| User Access Rights
|
Read-only
|
Read and write
|
| Access mode |
Data Retrieval, one-way |
Iterative, to and fro (if write backs are allowed in an OLAP) |
| Ease of Access |
Highly IT assisted |
Less IT assisted- More ease of use |
|
