03 DWH Part 2

= Relational OLAP Online Analytical Processing

= Multi-dimensional OLAP

Answer: Relational databases are a type of database that stores data in a structured format using tables and relationships. Explanation:

• Store data in tables with rows and columns

• Tables can be related to each other through foreign keys

• Use SQL for querying and modifying data

• Offer transactional support and ACID properties (atomicity, consistency, isolation, durability)

• Suitable for storing structured data

• Examples include MySQL, Oracle, and SQL Server

R-OLAP

Star- and Snowflake-Schemas in classic, relational DBMSs

M-OLAP = Multi-dimensional OLAP

Proprietary DBMSs optimized for storage of multidimensional data

+ Compatibility → can be accessed by any SQL reporting tool

+ Flexibility, not limited to cube structure

(BUT: don’t reuse OLTP tables!)

+ No pre-computation of cubes, aggregations calculated upon request

+ More scalable for very large data volumes

+ Shorter load times

+ Costs may be lower

+ Better scalability for high cardinality dimensions

+ Response time (runtime efficiency)

+ Smaller storage footprint

(due to better compression)

+ Query performance may be better

+ More powerful specialized techniques for multi-dimensional data than general purpose DBMSs

+ More suitable for complex calculations that don’t translate well into SQL

Answer: There are several techniques that can be used to optimize ROLAP performance, including indexing, materialized views, and partitioning. Explanation:

• Materialized views: Pre-computing and storing the results of frequently used queries can improve query performance.

• Bitmap indexes: These indexes can be used to improve the performance of queries that use filters, especially when working with large datasets.

• Vertical partitioning/DSM/Column stores: Storing data in columns rather than rows can improve query performance, especially for queries that only access a few columns.

• Compression: Compressing data can reduce the amount of disk space required and improve query performance.

• Optimizing for CPU caches: Techniques such as CSS-/CSB-trees and collaborative scans can help to improve query performance by making better use of CPU caches.

  
  

Answer: Horizontal partitioning involves dividing a large table into smaller chunks called shards, which can improve query performance and make maintenance easier. Explanation:

• Each shard contains a subset of the rows from the original table

• Shards are stored on different servers or storage devices

• Queries can be run on a smaller subset of data, improving performance

• Different shards can be managed and backed up independently, making maintenance easier

• Pruning: Queries can be optimized by only accessing the relevant shards, rather than all of them.

• Parallelize certain query operations: Hash joins can be parallelized across multiple shards to improve performance.

• Add/remove huge data sets: Large data sets can be added or removed in a single operation by modifying the partitioning scheme.

• To use horizontal partitioning with ROLAP, the schema must be designed with partitioning in mind and queries must be written to take advantage of the partitioning.

• Range-based partitioning: Data is partitioned based on a range of values for a particular column, such as dates or numeric values.

• Hash-based partitioning: Data is partitioned based on the result of a hash function applied to a particular column.

• List-based partitioning: Data is partitioned based on a specific list of values for a particular column.

• Composite partitioning: A combination of multiple partitioning criteria is used, such as range-based partitioning on one column and hash-based partitioning on another column.

Answer: Indexing is a technique for improving the performance of database queries by creating a separate data structure that maps the values in a particular column to the rows that contain those values.

Answer: In OLAP and star schemas, indexing can be used to improve the performance of queries that filter on dimensions or measures.

 Indices should be optimized for reads (not updates)

• Star schemas consist of a central fact table surrounded by dimension tables

• Queries often involve filtering on the dimensions or aggregating measures

• Data is typically loaded in bulk into the star schema and then accessed read-mostly, with multidimensional value-based access rather than key-based access

Bitmap indexes are a type of index that stores a bit-vector for each distinct value of a single attribute, allowing efficient support for multidimensional queries.

Explanation:

• Bitmap indexes are used to improve the performance of queries that filter on a particular attribute

• The size of the bit-vector is equal to the number of tuples in the table

• A bit is set to 1 if the tuple with the corresponding RID has the value represented by the bit-vector

• Bitmap indices can be combined using boolean operations (AND, OR, NOT) efficiently

• Bitmap indices can be compressed if they contain many 0s, using techniques such as run length encoding

• Bitmap indices need to be decompressed at query time.

Idea: Divide a table into multiple tables that contain fewer columns

(each stored on disk sequentially)

• Unique key column is replicated in all tables for joining

• When scanning a table, only relevant data must be read from disk into main memory and then into CPU caches

Answer: Storing each column of a table in a separate table, also known as columnar storage or a column-oriented DBMS.

• Columnar storage can be more space-efficient and faster for queries that only access a few columns

• May be less efficient for queries that access many columns or for updates

• Stores data in columns rather than rows.

Answer: The waterfall model is a linear software development process that involves moving sequentially through a series of phases.

The phases of the waterfall model are:

1. Specification: Define the requirements and constraints of the project.

2. Platform, software, and tools selection: Choose the hardware and software platform, as well as any tools or libraries that will be used.

3. Realization: Design the overall system and create a detailed specification.

4. Conceptualization: Create a high-level design of the system.

5. Implementation: Write the code and test the individual components.

6. Execution: Integrate and test the system as a whole.

7. Operation: Deploy and maintain the system.

Main:

– Assessment of the current state

– Elicit requirements of end-users and managers

Critical:

– Choice of interview partners

– Assignment of roles in the DW team

– Management support

– Shared vocabulary

Result:

– Project objective and scope definition

– Business and technical requirements

– Internal and external data sources

– Team members responsible for implementation

– Who is accountable for what

1. Analysis of potential alternatives

2. Evaluation of alternatives based on a list of criteria

3. Invite vendors to submit a proposal

4. Decision

1. Conceptualization

– Logical data modeling

– Physical data modeling

– Dialog design

– Modularization

2. Implementation

– Declarative Implementation (Data, Queries)

– Procedural Implementation

– Module and System test

3. Execution

– Installation

– Maintenance

Continuously optimize:

Loading time

Query response time

Storage requirements

Availability

Hardware upgrades

etc.

• Scalability

• OLAP capabilties: cube generator support, management of cubes, in-memory OLAP?

• Optimizations for analytical queries

  • 
    

• Cloud or on-premise

  
  

Top-down approach – Inmon model

Centrralizted DW then depertmental DMs to accommodate the requirements of various user groups

deductive

Generic -> Specific

Bottom-up approach – Kimball model

One DM per major business process then Enterprise-wide cohesion through data bus

Specific -> generic

Inductive

Answer: Departmental data marts are smaller, more focused data warehouses that serve the needs of a specific department or business unit.

Deductive reasoning starts with a general principle and applies it to a specific case, while inductive reasoning starts with specific observations and draws a general conclusion.

In-memory OLAP is a technique for performing online analytical processing (OLAP) by keeping the data and indexes in memory, providing faster access to data.

Advantages

+ Truly corporate effort, enterprise view of data

+ Inherently architectonic – not a union of disparate data marts

+ Single, central storage of data about the content

Disadvantages

– Takes longer to build even with an iterative method

– High exposure/risk to failure

– Needs high levels of cross-functional skills

Advantages

+ Faster and easier implementation of manageable pieces

+ Favorable return on investment and proof of concept

+ Lower risk of failure

Disadvantages

– Each data mart has its own narrow view of data

– Redundant data in every data mart

– Inconsistent and irreconcilable data

1. Focus on the business and its key performance indicators (KPIs)

2. Structure the data dimensionally to support ad hoc queries and reports

3. Use an iterative and incremental approach to development, rather than trying to implement everything at once.

1 Deployment

• Orchestration and extensive planning crucial to ensure that everything fits together

• Deployment should be deferred if any of the pieces (including training, documentation, validated date!) are not ready for prime time

• Education and support infrastructure [!!| (support need will typically not decline over time!)

2 Maintenance

3 Growth

- Requires an understanding of business requirements and project management

(bi-directional arrows, i.e. iterative planning)

• 
  

• Project = single iteration of the lifecycle

- Program = ongoing coordination of resources, infrastructure, timelines, and

communication across multiple projects

Ensures that the activities remain on track and in sync

Activities:

• Status monitoring

• Issue tracking

• Change control to preserve scope boundaries

• Development of a comprehensive communication plan that addresses both the business and IT constituencies

1. Technology track

2. Data track

3. BI applications track

Answer: Agile BI is a flexible and iterative approach to business intelligence (BI) that emphasizes collaboration, iterative process and feedback loops

Approach:

User stories (rather than technical specifications)

Adaptive planning

Time-boxed

iterativeevolutionary development approach

Obejctive:

Deliver value in days or hours rather than months or years

Rapid and flexible response to change

Deliver the right information to the right business users at the right time

Management across interdependent BI projects

(prioritization, coordination, synchronization..)?

Conflict with top-down, slow-moving data governance?

Deployment: can operations keep up the pace?

[DevOps not a well-established concept in the BI context]

Answer: JEDUF stands for "just enough, delivered up front." It is a practice used in Agile software development methodologies that involves delivering the minimum necessary functionality at the start of a project, with the intention of adding additional features and functionality iteratively as the project progresses.

DBaaS, or database-as-a-service, is a cloud-based service model in which a third-party provider hosts and manages a database on behalf of its customers.