Многомерное представление данных. Общая схема организации хранилища данных. Характеристики, типы и основные отличия технологий OLAP и OLTP. Схемы звезда и снежинка. Агрегирование

Типы OLAP. Преимущества и недостатки

Выбор способа хранения данных зависит от объема и структуры детальных данных, требований к скорости выполнения запросов и частоты обновления OLAP-кубов. В настоящее время применяются три способа хранения данных:

MOLAP (Multidimensional OLAP)

Детальные и агрегированные данные хранятся в многомерной базе данных. Хранение данных в многомерных структурах позволяет манипулировать данными как многомерным массивом, благодаря чему скорость вычисления агрегатных значений одинакова для любого из измерений. Однако в этом случае многомерная база данных оказывается избыточной, так как многомерные данные полностью содержат детальные реляционные данные.

Преимущества MOLAP.

• Высокая производительность. Поиск и выборка данных осуществляется значительно быстрее, чем при многомерном концептуальном взгляде на реляционную базу данных.
• Структура и интерфейсы наилучшим образом соответствуют структуре аналитических запросов.
• Многомерные СУБД легко справляются с задачами включения в информационную модель разнообразных встроенных функций.

Недостатки MOLAP.

• MOLAP могут работать только со своими собственными многомерными БД и основываются на патентованных технологиях для многомерных СУБД, поэтому являются наиболее дорогими. Эти системы обеспечивают полный цикл OLAP-обработки и либо включают в себя, помимо серверного компонента, собственный интегрированный клиентский интерфейс, либо используют для связи с пользователем внешние программы работы с электронными таблицами.
• По сравнению с реляционными, очень неэффективно используют внешнюю память, обладают худшими по сравнению с реляционными БД механизмами транзакций.
• Отсутствуют единые стандарты на интерфейс, языки описания и манипулирования данными.
• Не поддерживают репликацию данных, часто используемую в качестве механизма загрузки.

ROLAP (Relational OLAP)

ROLAP-системы позволяют представлять данные, хранимые в классической реляционной базе, в многомерной форме или в плоских локальных таблицах на файл-сервере, обеспечивая преобразование информации в многомерную модель через промежуточный слой метаданных. Агрегаты хранятся в той же БД в специально созданных служебных таблицах. В этом случае гиперкуб эмулируется СУБД на логическом уровне.

Преимущества ROLAP.

• Реляционные СУБД имеют реальный опыт работы с очень большими БД и развитые средства администрирования. При использовании ROLAP размер хранилища не является таким критичным параметром, как в случае MOLAP.
• При оперативной аналитической обработке содержимого хранилища данных инструменты ROLAP позволяют производить анализ непосредственно над хранилищем (потому что в подавляющем большинстве случаев корпоративные хранилища данных реализуются средствами реляционных СУБД).
• В случае переменной размерности задачи, когда изменения в структуру измерений приходится вносить достаточно часто, ROLAP системы с динамическим представлением размерности являются оптимальным решением, так как в них такие модификации не требуют физической реорганизации БД, как в случае MOLAP.
• Системы ROLAP могут функционировать на гораздо менее мощных клиентских станциях, чем системы MOLAP, поскольку основная вычислительная нагрузка в них ложится на сервер, где выполняются сложные аналитические SQL-запросы, формируемые системой.
• Реляционные СУБД обеспечивают значительно более высокий уровень защиты данных и хорошие возможности разграничения прав доступа.

Недостатки ROLAP.

• Ограниченные возможности с точки зрения расчета значений функционального типа.
• Меньшая производительность, чем у MOLAP. Для обеспечения сравнимой с MOLAP производительности реляционные системы требуют тщательной проработки схемы БД и специальной настройки индексов. Но в результате этих операций производительность хорошо настроенных реляционных систем при использовании схемы "звезда" сравнима с производительностью систем на основе многомерных БД.

HOLAP (Hybrid OLAP)

Детальные данные остаются в той же реляционной базе данных, где они изначально находились, а агрегатные данные хранятся в многомерной базе данных.

Моделирование многомерных кубов на реляционной модели данных

Схема звезда. Преимущества и недостатки

Схема типа звезды (Star Schema) - схема реляционной базы данных, служащая для поддержки многомерного представления содержащихся в ней данных.

*Особенности ROLAP-схемы типа "звезда"*

1. Одна таблица фактов (fact table), которая сильно денормализована. Является центральной в схеме, может состоять из миллионов строк и содержит суммируемые или фактические данные, с помощью которых можно ответить на различные вопросы.
2. Несколько денормализованных таблиц измерений (dimensional table). Имеют меньшее количество строк, чем таблицы фактов, и содержат описательную информацию. Эти таблицы позволяют пользователю быстро переходить от таблицы фактов к дополнительной информации.
3. Таблица фактов и таблицы размерности связаны идентифицирующими связями, при этом первичные ключи таблицы размерности мигрируют в таблицу фактов в качестве внешних ключей. Первичный ключ таблицы факта целиком состоит из первичных ключей всех таблиц размерности.
4. Агрегированные данные хранятся совместно с исходными.

Преимущества

Благодаря денормализации таблиц измерений упрощается восприятие структуры данных пользователем и формулировка запросов, уменьшается количество операций соединения таблиц при обработке запросов. Некоторые промышленные СУБД и инструменты класса OLAP / Reporting умеют использовать преимущества схемы "звезда" для сокращения времени выполнения запросов.

Недостатки

Денормализация таблиц измерений вносит избыточность данных, возрастает требуемый для их хранения объем памяти. Если агрегаты хранятся совместно с исходными данными, то в измерениях необходимо использовать дополнительный параметр - уровень иерархии.

Схема снежинка. Преимущества и недостатки

Схема типа снежинки (Snowflake Schema) - схема реляционной базы данных, служащая для поддержки многомерного представления содержащихся в ней данных, является разновидностью схемы типа "звезда" (Star Schema).

*Особенности ROLAP-схемы типа "снежинка"*

1. Одна таблица фактов (fact table), которая сильно денормализована. Является центральной в схеме, может состоять из миллионов строк и содержать суммируемые или фактические данные, с помощью которых можно ответить на различные вопросы.
2. Несколько таблиц измерений (dimensional table), которые нормализованы в отличие от схемы "звезда". Имеют меньшее количество строк, чем таблицы фактов, и содержат описательную информацию. Эти таблицы позволяют пользователю быстро переходить от таблицы фактов к дополнительной информации. Первичные ключи в них состоят из единственного атрибута (соответствуют единственному элементу измерения).
3. Таблица фактов и таблицы размерности связаны идентифицирующими связями, при этом первичные ключи таблицы размерности мигрируют в таблицу фактов в качестве внешних ключей. Первичный ключ таблицы факта целиком состоит из первичных ключей всех таблиц размерности.
4. В схеме "снежинка" агрегированные данные могут храниться отдельно от исходных.

Преимущества

Нормализация таблиц измерений в отличие от схемы "звезда" позволяет минимизировать избыточность данных и более эффективно выполнять запросы, связанные со структурой значений измерений.

Недостатки

За нормализацию таблиц измерений иногда приходится платить временем выполнения запросов.

Практические занятия

Постановка задачи. Проектирование данных на концептуальном и логическом уровнях. Нормализация отношений.

Презентация по ER-моделированию

ER нотации

Пример модели в ERwin

Подготовка SQL скриптов генерации схемы отношений БД в ERwin. Разработка скрипта для ввода тестовой информации.

Видео-презентация (Для проигрывания требуется Windows Media Player)

Архитектура MS SQL Server 2005. Настройка и использование основных компонент среды. Создание учебной базы данных.

Видео-презентация (Для проигрывания требуется Windows Media Player)

Работа с СУБД MS SQL Server 2005, ORACLE 10g. Примеры соединений с БД, технологии разработки клиенского приложения

Использование технологии Java Database Connectivity (JDBC) для работы с базами данных

Презентация

Примеры к презентации

SQL-скрипты, проект и исходные коды

package org.mai806.jdbcsample;

import java.sql.*;

public class QuerySample {
    
    public static void main(String[] args) throws Exception {
        
        /* ======== Подключение к MS SQL Server ===== */
        // Загрузка драйвера
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        // Соединение с базой данных
        Connection connection = DriverManager.getConnection(
                "jdbc:sqlserver://localhost:1433;databaseName=o01;", 
                // localhost - сервер СУБД, o01 - имя базы данных
                "sa", "123"); // пользователь, пароль
        
        /* ======== Подключение к Oracle ============
        // Загрузка драйвера
        Class.forName("oracle.jdbc.OracleDriver");
        // Соединение с базой данных
        Connection connection = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:orcl", 
                        // localhost - сервер СУБД, orcl - SID базы оракла
                        "o01", "o01"); // пользователь, пароль         
        
        // Создание Statement
        PreparedStatement stmt = connection.prepareStatement
        ("select ID, NAME from PERSON where NAME like ?");
        stmt.setString(1, "%S%");
        // Выполнение запроса
        ResultSet rs = stmt.executeQuery();
        // Перебор результата выполнения запроса
        while(rs.next()) {
            // Пример выбора параметра по номеру или по имени
            System.out.println("ID: " +
                    rs.getLong(1) + "; NAME="+
                    rs.getString("NAME"));
        }
        
        // закрытие использованных ресурсов БД
        rs.close();
        stmt.close();
        connection.close();
    }
}

package org.mai806.jdbcsample;

import java.sql.*;
import java.util.ResourceBundle;

public class StoredProcedureSample {
    
    private static Connection connection = null;
    public static void main(String[] args) throws Exception {
        // Получение соединения из значений параметров в файле properties
        ResourceBundle properties = ResourceBundle.getBundle("database");
        Class.forName(properties.getString("driver"));
        connection = DriverManager.getConnection(
                properties.getString("url"),
                properties.getString("user"),
                properties.getString("password"));
        
        transferAmount(1,2,100.0);
        connection.close();
    }
    
    /**
     * Переводит указанную сумму с одного счета на другой
     * @param from счет плательщика
     * @param to счет получателя
     * @param amount сумма
     */
    public static void transferAmount(long from, long to, double amount)
    throws Exception {
        // Создание Statement
        CallableStatement stmt
                = connection.prepareCall("{call TransferAmount(?,?,?)}");
        // Установка параметров
        stmt.setLong(1, from);
        stmt.setLong(2, to);
        stmt.setDouble(3, amount);
        // Выполнение процедуры
        stmt.execute(); 
    }
}

package org.mai806.jdbcsample;

import java.sql.*;
import java.util.ResourceBundle;

public class TransactionalSample {
    
    private static Connection connection = null;
    public static void main(String[] args) throws Exception {
        
        // Получение соединения из значений параметров в файле properties
        ResourceBundle properties = ResourceBundle.getBundle("database");
        Class.forName(properties.getString("driver"));
        connection = DriverManager.getConnection(
                properties.getString("url"),
                properties.getString("user"),
                properties.getString("password"));
        
        // Ручное управление транзакциями
        connection.setAutoCommit(false);
        
        try {
            transferAmount(2, 1, 10.0);
        } finally {
            connection.close();
        }
    }
    
    /**
     * Переводит указанную сумму с одного счета на другой
     * @param from счет плательщика
     * @param to счет получателя
     * @param amount сумма
     */
    public static void transferAmount(long from, long to,
            double amount) throws Exception {
        PreparedStatement stmt = null;
        Statement query = null;
        try {
            stmt = connection.prepareStatement
            ("update ACCOUNT set AMOUNT=AMOUNT+? where ID=?");
            // Забираем сумму со счета плательщика
            stmt.setDouble(1, -amount);
            stmt.setLong(2, from);
            stmt.execute();
            
            // Добавляем сумму на счет получателя
            stmt.setDouble(1, amount);
            stmt.setLong(2, to);
            stmt.execute();
            
            // Пост-проверка: отрицательность счета плательщика
            query = connection.createStatement();
            ResultSet rs = query.executeQuery(
                    "select AMOUNT from ACCOUNT where ID="+from+" and AMOUNT<0");
            if (rs.next()) {
                throw new Exception("На счете №"+from+" 
                недосточно средств ["+(amount+rs.getDouble(1))+"] 
                для снятия суммы ["+amount+"]");
            }
            connection.commit();
            System.out.println("Перечисление средств успешно выполнено");
        } catch(Exception e) {
            e.printStackTrace();
            connection.rollback();
        } finally {
            if (stmt!=null)
                stmt.close();
            if (query!=null)
                query.close();
        }
    }
    
}

Работа с базами данных из J2EE Web-контейнера

Презентация

Объектно-реляционное отображения для работы с базами данных

Презентация

Использование препроцессора для работы с API СУБД

Презентация