Intereting Posts
Невозможно преобразовать varchar в datetime в MySql Выбор нескольких строк в одной таблице в том же JOIN Ошибка арифметического переполнения, преобразующая числовые данные в числовой тип данных Построить выражение запроса OR постепенно массовая вставка из Java в Oracle SQL – выбор запроса для сложных динамических строк Запустить Вставить SQL-запросы Каков наилучший способ отладки хранимых процедур (и написать sprocs, которые легче отлаживать)? Поле SQL с несколькими идентификаторами другой таблицы Как работает параметр «LIMIT» в sql? Хранить данные SQLite 3 как переменную в Python Геолокационное расстояние SQL из таблицы городов Представление ограничения FOREIGN KEY может вызвать циклы или несколько каскадных путей? Почему последовательности не обновляются, когда COPY выполняется в PostgreSQL? Должен ли я удалить или отключить строку в реляционной базе данных?

Обработка нулей в Datawarehouse

Я хотел бы спросить вас о том, что лучше всего подходит для обработки нулевых или пустых значений данных, когда оно относится к хранилищу данных и SSIS / SSAS.

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

Особенности:

1) Каков наилучший способ обработки значений null date / times? Должен ли я сделать строку «по умолчанию» в моих измерениях времени или даты и указать SSIS в строку по умолчанию при обнаружении нулевого значения?

2) Каков наилучший способ обработки значений nulls / empty внутри данных измерения. Пример: у меня есть несколько строк в измерениях «Учетные записи», которые имеют пустые (не NULL) значения в столбце «Имя учетной записи». Должен ли я преобразовать эти пустые или нулевые значения внутри столбца в определенное значение по умолчанию?

3) Как и в пункте 1 выше – Что делать, если в итоге я получаю строку Facttable, которая не имеет записи в одном из столбцов измерения? Нужен ли размер записей измерений по умолчанию для каждого измерения в случае, если это произойдет?

4) Любое предложение или советы в отношении того, как обрабатывать эти операции в службах интеграции серверов Sql (SSIS)? Было бы полезно использовать лучшие конфигурации потока данных или лучшие объекты преобразования для использования.

Благодаря 🙂

Как сказано в предыдущем ответе, может быть много разных значений, привязанных к значениям Null для измерения, неизвестным, неприменимым, неизвестным и т. Д. Если полезно различать их в приложении, добавление «псевдо» элементов измерения может помочь.

В любом случае я бы избегал иметь либо внешние ключи или размерные поля Null, имеющие хотя бы одно значение неизвестного значения, которое поможет вашим пользователям определить запросы, которые включают группу catch-all, где качество данных не 100% (и это никогда не бывает).

Один очень простой трюк, который я использовал для этого, и не укусил меня, – это определить суррогатные ключи моих измерений, используя int IDENTITY (1,1) в T-sql (начало с 1 и увеличение на 1 в строке). Псевдо ключи («Недоступно», «Не назначено», «Не применимо») определяются как отрицательные int и заполняются хранимой процедурой, запущенной в начале процесса ETL.

Например, таблица, созданная как

CREATE TABLE [dbo].[Location] ( [LocationSK] [int] IDENTITY(1,1) NOT NULL, [Name] [varchar](50) NOT NULL, [Abbreviation] [varchar](4) NOT NULL, [LocationBK] [int] NOT NULL, [EffectiveFromDate] [datetime] NOT NULL, [EffectiveToDate] [datetime] NULL, [Type1Checksum] [int] NOT NULL, [Type2Checksum] [int] NOT NULL, ) ON [PRIMARY] 

И хранимая процедура, заполняющая таблицу

 Insert Into dbo.Location (LocationSK, Name, Abbreviation, LocationBK, EffectiveFromDate, Type1Checksum, Type2Checksum) Values (-1, 'Unknown location', 'Unk', -1, '1900-01-01', 0,0) 

Я сделал правило, чтобы иметь хотя бы одну такую ​​псевдо-строку для измерения, которая используется в тех случаях, когда поиск в измерениях терпит неудачу, и создавать отчеты об исключениях для отслеживания количества фактов, которые присваиваются таким строкам.

  1. Или NULL или зарезервированный идентификатор из вашего измерения даты с соответствующим значением. Помните, что NULL действительно может иметь много разных значений, он может быть неизвестным, неприменимым, недействительным и т. Д.

  2. Я бы предпочел пустую строку (а не NULLable), но в проекте, над которым я работаю, теперь преобразует пустую строку в NULL и разрешает их в базе данных. Потенциальная проблема, которая будет обсуждаться, заключается в том, что пустой средний начальный (без среднего имени, так что средний начальный, как известно, пуст) отличается от неизвестной средней начальной или подобной семантики. На деньги наша модель позволяет NULL – у меня есть большая проблема с этим в фактах, так как обычно они действительно должны быть 0, они всегда используются как 0, и их всегда нужно использовать в ISNULL (). Но из-за политики ETL преобразования пустой строки в NULL они были установлены в NULL, но это был всего лишь артефакт формата транспортного файла с фиксированной шириной, который имел пробелы вместо 0 из некоторых исходных систем.

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

  4. В SSIS я создал компонент отделки, который выравнивает пробелы с концов всех строк. Обычно нам приходилось выполнять многодокументацию и преобразование даты в SSIS, что было бы лучше всего в компоненте.

Спасибо за ввод,

Две вещи, которые я сделал в моем последнем проекте:

1) Используется предложение Стива об отрицательных идентификационных ключах для неизвестных / специальных значений измерения. Это отлично работает, и никаких проблем не возникало в процессе сборки куба SSAS.

2) Созданные преобразования, чтобы проверить, имеет ли значение значение NULL, и если это так, конвертировать в -1 (Неизвестная запись в измерении) ИЛИ, если это значение меры, преобразовать в 0. Выражения приведены ниже в качестве примеров (я использовал их в Производные преобразования столбцов):

 ISNULL(netWeight) ? 0 : netWeight // This is an example of a Measure column ISNULL(completeddateid) ? -1 : completeddateid // This is an example of a dimension key column 

Надеюсь, это поможет кому-то еще в будущем 😉

Еще одно решение, которое я могу предложить, заключается в том, что во время ETL-step определена таблица переноса, в которую временно хранятся импортированные записи ПОСЛЕ всех необходимых преобразований. Я бы добавил несколько дополнительных атрибутов в таблицу переноса, позволяющую кому-то; рядом с исходными атрибутами значений, которые могут быть NULL или другим нежелательным значением; вставить «закодированное» значение, идентифицирующее проблему, с одной стороны, и имя атрибута, в котором произошло ошибочное значение.

Сделав это, я все еще могу решить, как использовать денормализованные и переданные данные на более позднем этапе … возможно, отфильтровывать ошибочные значения или упоминать их в отдельном измерении ошибок для включения в отчеты, в которых указывается, какие значения были девиантными и как они могут / может повлиять на агрегированные значения.

например

 error-code attribute= -1 = NULL date -2 = NULL numerical value -3 = NULL PK -4 = NULL text value 

и другой атрибут = IdOrder , BirthDate , OrderAmount и т. д.

Конечно, у вас гораздо больше проблем, если записи могут иметь БОЛЕЕ 1 ошибочное (NULL) значение, но в этом случае можно либо расширить количество атрибутов «трассировки», либо «вернуться к источнику» и выяснить, где и почему возникла проблема (вместе с разработкой).

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

Может быть, это тоже поможет кому-то;)