Производительность плоских столов в сравнении с размерами и фактами
Я пытаюсь создать размерную модель на плоских таблицах OLTP (не в 3NF).
Есть люди, которые думают, что размерная модель таблицы не требуется, потому что большая часть данных для отчета представлена одной таблицей. Но эта таблица содержит более 300 столбцов. Должен ли я разделить плоский стол на измерения и факты или просто использовать плоские таблицы непосредственно в отчетах.
2 ответа
Этот вопрос не по теме для Stackru. Скорее всего, вы получите лучшие ответы на сайте, посвященном хранилищу данных.
Тем не менее, так как вы спросили...
При создании таблиц исключительно для целей отчетности (как это обычно происходит в хранилище данных) принято создавать широкие плоские таблицы с ненормализованными данными, потому что:
- Проще запросить
- Это позволяет избежать соединений, которые могут быть запутанными и подверженными ошибкам для причинных пользователей.
- Запросы выполняются быстрее (особенно для систем хранилищ данных, которые используют столбцовое хранилище данных)
Этот формат данных отлично подходит для отчетов, но не подходит для обычного хранения данных для приложений - база данных, используемая для OLTP, должна использовать нормализованные таблицы.
Не беспокойтесь о большом количестве столбцов - это вполне нормально для хранилища данных. Тем не менее, 300 столбцов звучат довольно большими и предполагают, что они не обязательно используются разумно. Итак, вы можете проверить, требуются ли они.
Отличным примером многих столбцов является наличие флагов, облегчающих написание предложений WHERE, таких как WHERE customer_is_active вместо того, чтобы присоединиться к другому столу и выяснить, пользовались ли они услугой в последние 30 дней. Эти столбцы нужно будет ежедневно пересчитывать, но они очень удобны для запроса данных.
Итог: при использовании хранилищ данных вы должны поставить простоту использования выше производительности. Затем выясните, как оптимизировать доступ с помощью системы хранилищ данных, такой как Amazon Redshift, которая предназначена для очень эффективной обработки данных этого типа.
Вы задали общий вопрос о моделировании базы данных для хранилищ данных, который даст вам общие ответы, которые могут не относиться к платформе базы данных, с которой вы работаете - если вы хотите получить ответы, которые вы сможете использовать тогда я бы предложил быть более конкретным.
Теги вопроса указывают, что вы используете Amazon Redshift, и ответ для этой базы данных отличается от традиционных реляционных баз данных, таких как SQL Server и Oracle.
Во-первых, вам нужно понять, чем Redshift отличается от обычных реляционных баз данных:
1) Это система массовой параллельной обработки (MPP), которая состоит из одного или нескольких узлов, по которым распределяются данные, и каждый узел обычно выполняет часть работы, необходимой для ответа на каждый запрос. В связи с тем, что способ, которым данные распределяются по узлам, становится важным, цель обычно состоит в том, чтобы данные распределялись достаточно равномерно, чтобы каждый узел выполнял примерно одинаковое количество работы для каждого запроса.
2) Данные хранятся в столбчатом формате. Это полностью отличается от строкового формата SQL Server или Oracle. В столбчатой базе данных данные хранятся таким образом, что делает запросы с большими типами агрегации намного более эффективными. Этот тип хранения частично сводит на нет причину использования таблиц измерений, поскольку хранение повторяющихся данных (атрибутов) в строках является относительно эффективным.
Таблицы красного смещения обычно распределяются по узлам с использованием значений одного столбца (ключ распределения). В качестве альтернативы они могут быть распределены случайным образом, но равномерно, или Redshift может сделать полную копию данных на каждом узле (обычно это делается только с очень маленькими таблицами).
Поэтому, решая, создавать ли измерения, вы должны подумать о том, принесет ли это на самом деле много пользы. Если в данных есть столбцы, которые регулярно обновляются, то лучше поместить их в другую таблицу меньшего размера, а не обновлять одну большую таблицу. Однако, если данные в основном только для добавления (не меняются), тогда нет никакой пользы в создании измерений. Запросы, объединяющие и объединяющие данные, будут эффективными для одной таблицы.
Соединения могут стать очень дорогими в Redshift, если обе таблицы не распределены по одному и тому же значению (например, идентификатору пользователя) - если они не Redshift, придется физически копировать данные вокруг узлов, чтобы выполнить запрос. Таким образом, если у вас должны быть измерения, то вы захотите распределить таблицу наибольшего размера по тому же ключу, что и таблица фактов (помня, что каждая таблица может быть распределена только по одному столбцу), тогда может потребоваться распределение любых других измерений. как ВСЕ (копируется в каждый узел).
Мой совет - придерживаться одной таблицы, если у вас нет острой необходимости создавать измерения (например, если часто обновляются столбцы).