Главная страница | Контакты | Случайная страница Автомобили Астрономия Биология География Дом и сад Другие языки Другое Информатика История Культура Литература Логика Математика Медицина Металлургия Механика Образование Охрана труда Педагогика Политика Право Психология Религия Риторика Социология Спорт Строительство Технология Туризм Физика Философия Финансы Химия Черчение Экология Экономика Электроника

Проблемы отдельных источников данных

Контекст запроса. Чтобы создать именованное множество, которое определено как часть запроса, с областью, ограниченной этим запросом, используется ключевое слово WITH. Затем именованное множество можно использовать внутри оператора MDX SELECT. В таком случае именованное множество, созданное с использованием ключевого слова WITH, может быть изменено без изменений в инструкции SELECT.

Контекст сеанса. Чтобы создать именованное множество, область которого шире контекста запроса, то есть множество, действующее в течение сеанса многомерных выражений, следует использовать инструкцию CREATE SET. Именованный набор, определенный с использованием инструкции CREATE SET, доступен для всех запросов многомерных выражений в этом сеансе. Например, инструкция CREATE SET полезна в клиентском приложении, в котором набор многократно применяется в различных запросах.

Создание именованных множеств с областью действия запроса

Выражение - допустимое многомерное выражение, возвращающее множество.

Набор кортежей не обязательно заключать в одинарные кавычки (''). Одинарные кавычки используются для версии Analysis Services 2000.

Пример. Пусть нужно извлечь сведения о продажах вин Шардоне и Каберне. Этот запрос возвращает достаточно простой результирующий набор, однако является очень длинным и громоздким с точки зрения его обслуживания.

Чтобы упростить запрос, можно создать именованное множество [ШардонеКаберне] с помощью ключевого слова WITH:

Синтаксис оператора создания именованного множества с областью сеанса для текущего куба.

Для обращения к текущему кубу вместо указания имени куба рекомендуется использовать переменную CURRENTCUBE.

Ключевое слово HIDDEN помечает вычисляемые элементы как скрытые. Такие вычисляемые элементы не видны пользователям, обращающимся к кубу с запросом.

Именованное множество, созданное с помощью оператора CREATE SET, удаляется только при закрытии сеанса многомерных выражений.

Область запроса имеет приоритет по сравнению с областью сеанса.

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

При определении вычисляемого члена для него можно задать один из следующих контекстов.

· Область запроса. Для создания вычисляемого члена, определяемого как часть многомерного запроса, применяется ключевое слово WITH. После создания вычисляемый член можно использовать в операторе SELECT.

· Область сеанса. Для создания вычисляемого члена, область которого шире контекста запроса и распространяется на весь сеанс многомерных выражений, применяется оператор CREATE MEMBER. Такой вычисляемый член доступен для всех запросов текущего сеанса.

Создание вычисляемых членов для области запроса

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

Пример. Определить вычисляемый член [Measures].[Специальная скидка], который рассчитывает особую скидку, исходя из начальной суммы скидки.

Вычисляемые члены можно создавать в любой точке иерархии. Например, в следующем запросе определяется вычисляемый член [Хороший продавец], с помощью которого определяется, продал ли заданный магазин более 100 бутылок пива и вина. Однако в запросе вычисляемый член [Хороший продавец] создается не как потомок измерения [Товар], а как потомок члена [Пиво и вино].

Для измерения Measures также можно создавать вычисляемые члены. Фактически большинство вычисляемых членов, применяемых в реальных бизнес-процессах, обычно создается именно для измерения Measures. Вычисляемые члены, созданные для измерения Measures, принято называть вычисляемыми мерами.

Вычисляемые члены также могут создаваться на основе других вычисляемых членов, определяемых в том же многомерном выражении. Например, в следующем запросе значение, созданное в первом вычисляемом члене [Measures].[Специальная скидка], используется для формирования значения второго вычисляемого члена [Measures].[Сумма специальной скидки].

Параметр SOLVE_ORDER определяет порядок, в котором должны вычисляться члены, если задано несколько вычисляемых членов.

Пусть нужно сравнить качество работы компаний между двумя годами и увидеть изменения. Можно построить запрос, который использует {[2010], [2011]}в качестве осей и просматривать пары чисел для каждой меры. А можно определить вычисляемый член на уровне Год, параллель между 2010 и 2011, который будет определять разность между ними:

WITH MEMBER Время.[11 к 10] AS 'Время.[2011] –Время.[2010]'

WITH MEMBER Время.[2010].[Дек к Окт] AS 'Время.[2010].[12] – Время.[2010].[10]'

Создание вычисляемых элементов с областью действия сеанса

Аргумент <Свойство> может относиться к стандартным или дополнительным свойствам вычисляемого элемента.

У каждого вычисляемого элемента есть набор стандартных свойств. При подключении клиентского приложения к Analysis Services стандартные свойства либо поддерживаются, либо доступны для поддержки по выбору администратора.

Дополнительные свойства могут быть доступны в зависимости от определения куба.

Следующие свойства отражают сведения, относящиеся к уровню измерения в данном кубе.

Функции языка многомерных выражений MDX

Рассматриваются следующие вопросы:

· категории функций;

· функции для навигации в иерархиях;

· фильтрация данных;

· сортировка данных.

С синтаксической точки зрения функции МDХ можно разделить на методы и свойства.

Методы имеют следующий синтаксис:

<function_name> ([<parameter> [, <parameter>...])

Например:

CROSSJOIN({[2010],[2011]},{[Украина],[РФ], [Молдова]}), [Время])

А свойства имеют следующий синтаксис:

<object_name>.<property_name>[ (<parameter>[,<parameter>...]

Например:

[Время].DefaultMember

Обе эти разновидности функций возвращают значения одного из следующих типов: Dimension (Измерение), Hierarchy (Иерархия), Level (Уровень), Member (Член), Tuple (Кортеж), Set (Множество) и Scalar (Скалярное значение), которые, в свою очередь, могут быть переданы в ка­честве параметров в другие функции

Функции для навигации в иерархиях

Функция Members применяется к иерархиям или к уровням.

При применении к иерархии функция возвращает набор всех членов иерархии, независимо от уровня.

Пример:

[Дата].[Иерархия1].Members – возвращает полный перечень всех годов, месяцев и дней.

При применении к уровню функция возвращает набор всех членов измерения, находящихся на данном уровне.

Пример: [Место].[Иерархия_Место].[Города].Members – возвращает полный перечень всех городов.

Функция AllMembers работает аналогично функции Members, но Members возвращает все элементы иерархии, кроме вычисляемых, а AllMembers возвращает также и вычисляемые элементы.

Для перемещения в пределах одного уровня, используются функции PrevMember и NextMember:

[Дата].[2009].[Март].NextMember – возвращает апрель 2009 года,

[Дата].[2009].[Март].PrevMember – возвращает февраль 2009 года,

[Дата].[2009].[Март].PrevMember.PrevMember – возвращает январь 2009 года.

Для более компактной записи применяются функции Lag(.) и Lеad(.):

[Дата].[2009].[Март]. Lag(2) – возвращает январь 2009 года,

[Дата].[2009].[Март]. Lеad(5) – возвращает август 2009 года,

[Дата].[2009].[Март]. Lag(-1) – возвращает апрель 2009 года.

Рассмотрим примериерархии Магазины (Stores) измерения Магазин (Store).

В этой иерархии элемент ALL — родитель (parent) элементов следующего уровня иерархии: Украина, РФ и Молдова. Области Одесская, Киевская и Херсонская являются дочерними (children) для Украина, и т. д. Области Одесская, Киевская и Херсонская также являются потомками (descendants) элемента ALL, а ALL — это предок (ancestor) членов, представляющих области.

Для перемещения вверх и вниз по уровням используются функции Children и Parent:

[Дата].[2009].[Март].Children – возвращает все дни марта,

[Дата].[2009].[Март].Parent – возвращает [Дата].[2009],

[Дата].[2009].[Март].[25].Parent.Parent – возвращает [Дата].[2009].

Функция Children создает множество элементов, которые являются дочерними по отношению к заданному элементу

SELECT [Украина].Children ON COLUMNS FROM [MyCube]

Функция Descendants немного сложней, но она более гибкая, чем функция Children. Она используется для получения потомков члена.

Например, необходимо проанализировать продажи магазинов, распо­ложенных в различных городах Украины

SELECT DESCENDANTS([Украина],[Город магазина]) ON COLUMNS

FROM [MyCube]

Функция Descendants возвращает множество членов, которые являются потомками заданного члена на заданном уровне иерархии.

Чтобы увидеть листовые элементы (leaf memebers) — элементы, у которых нет потомков, являющиеся потомками элемента Украина, используется ключе­вое слово LEAVES.

SELECT DESCENDANTS([Магазин.[Страна магазина].[Украина],, LEAVES)

on COLUMNS

FROM [MyCube]

МDХ поддерживает целый ряд функций, которые подпадают под категорию функций для навигации по иерархии, например, FirstChild, LastChild, функции для работы с элементами одного уровня, и т. д.

Функция Filter имеет два параметра: множество и выражение МDХ, задающее критерий и возвращающее значение типа Boolean. Для вычисления результата функции Filter сервер проходит множество, которое было передано в качестве первого параметра функции. Затем для каждого кортежа во множестве вычисляет выражение, переданное в качестве второго параметра. Если это выражение принимает значение True, кортеж включается в результирующее множество.

Например, пусть требуется определить магазины, продажи в которых снизились в 2010 г., по сравнению с 2009 г.

SELECT Filter([Магазин].members,

([Количество продаж], [2010]) < ([Количество продаж],[2009])) ON COLUMNS,

{[2009], [2010]} ON ROWS

FROM [MyCube]

WHERE [Количество продаж]

Для выполнения функции Filter сервер вычисляет выражение фильтра ([Количество продаж],[2010]) < ([Количество продаж],[2009]). Это выражение содержит только члены измерений Measure и Время. Все остальные члены опре­деляются по шагам, описанным в определении контекста запроса: сервер сначала устанавлива­ет в текущую координату члены по умолчанию всех атрибутов, а затем перезаписывает их членами измерений, указанными в предложении WHERE. Затем сервер перезаписывает члены, указанные в вы­ражении критерия. И, наконец, перезаписываются члены, указанные в фильтруемом множестве.

Другой пример

SELECT Filter([Клиент]. [Страна].members,

[Measures].[Количество продаж].Value>1000) ON COLUMNS

FROM [MyCube]

WHERE ([Время].[Год].[2009])

В этом запросе фильтруются магазины по мере Количество продаж. Пусть сеть мага­зинов сначала была только в Украине; так что в 2009 г. все продажи были только в Украине. Но в 2010 г. товары продавались также в России и Молдове. Если бы выражение Filter вычислялось в контексте выражения, а не в контексте всего запроса, в результате были бы получены все три страны: Украина, РФ и Молдова. Для измерения Время используется член по умолчанию ALL. В запросе из примера будет получена только одна страна: Украина, потому что текущий контекст для выполнения выражения включает члены, указан­ные в предложении WHERE

То есть предложение WHERE влияет на вычисление выражения критерия и на результат функции Filter.

Чтобы лучше понять правила вычисления контекста в функции фильтрации, рассмотрим, что произойдет, если измерения, используемые в выражении критерия, были бы теми же самыми, что и в предложении WHERE. Допус­тим, что в запросе измерение Measure существует и в предложении WHERE, и в выражении критерия. При выполнении выражения критерия сервер будет использовать меру Количество продаж, но для вычисления значений ячеек будет использована мера Сумма продаж.

SELECT Filter([Клиент].[Страна].members,

[Measures].[Количество продаж].Value>1000) ON COLUMNS

FROM [MyCube]

WHERE ([Время].[Год].[2009], [Measures].[Сумма продаж])

Функция Order сортирует кортежи в множестве в соответствии со значением выражения, которое передается в качестве второго параметра.

Пример. Отсортировать магазины по значениям меры Сумма продаж.

SELECT Order([Магазин].members, [Measures].[Сумма продаж], BDESC)

ON COLUMNS

FROM [MyCube]

В этом операторе результирующее множество отсор­тировано в порядке убывания. Но при этом проигнорирована иерархич­ность множества.

Рассмотрим пример, который показывает сортировку с сохранением иерар­хичности. Например, надо проанализировать производительность магази­нов, но требуется сделать это в контексте страны, в которой расположен магазин. Поэтому необходимо не просто отсортировать магазины, сравни­вая их друг с другом. Сначала нужно отсортировать значения для стран, в кото­рых расположены магазины. Затем будут отсортированы области, города и только после всего этого — магазины. Теперь можно сравнивать значения про­даж в одном магазине со значениями продаж в других магазинах того же самого города. Ключевое слово DESC в функции Order указывает системе, что при упорядочивании множества в убывающем порядке надо сохранить иерархичность, определенную пользовательской иерархией.

SELECT Order(([Магазин].[Страна магазина].members,

[Магазин].[Область магазина].members),

[Measures].[Сумма продаж], DESC) ON COLUMNS

FROM [MyCube]

Результаты этого запроса показывают, что больше всего продаж было сдела­но в Украине, и среди областей в Украине больше всего продаж в Одесской области, затем идут магазины в Херсонской области и затем — в Киевской.

Неопределенные члены

Члены измерения, использованные в MDX-запросе, могут не существовать в кубе (например, они могут быть заданы неправильно). Ячейки многомерного пространства тоже могут оказаться пустыми.

Например, в MDX-выражении используется член, находящийся вне границ куба. Это может произойти, например, когда запрос выбирает родительский элемент элемента, находящегося на верхнем уровне иерархии. Для обработки таких случаев введена кон­цепция неопределенных членов (Null Members) и неопределенных кортежей (Null tuples):

- сервер использует неопределенный член для ссылки на ко­ординату, находящуюся вне пространства куба;

- если кортеж содержит хотя бы один неопределенный член, он назы­вается неопределенным кортежем.

В некоторых случаях использование неопределенных членов и кортежей разрешено, в других — приводит к сообщению об ошибке. Некоторые функции МDХ возвращают ошибку, если в качестве параметра передается неопределенный член или кортеж.

Если множество не содержит кортежей или содержит только неопределен­ные кортежи, оно называется пустым множеством(empty set). Если множе­ство содержит как обычные, так и неопределенные кортежи, пользователю возвращаются только обыч­ные кортежи. Например, следующий запрос вернет только один кортеж:

SELECT {[All], [All].Parent} ON COLUMNS

FROM [MyCube]

Режим отсутствия члена

В Analysis Services 2000 при ссылке на член по имени, которое не соответствует никакому элементу в кубе, вы­давалась ошибка, что приводило к проблемам в некоторых клиентских при­ложениях. Например, клиентские приложения сохраняют текст запросов МDХ, которые использовались для формирования отчетов. Со временем, если члены удалялись из системы, запросы, ссылающиеся на удаленные элементы, переставали работать, и отчеты переставали открываться. Analysis Services 2005 представлена новая возможность — Режим Отсутствия члена (Missing Member Mode).

Этот режим позволяет запросу или выражению МDХ ссылать­ся на члены, которые не существуют в кубе. Система преобразует такие члены в неопределенные.

Например, если нужно выбрать некоторых покупателей, можно написать следующий запрос SQL

SELECT Фамилия, Имя, Отчество, Должность

FROM Клиент

WHERE Фамилия = 'Иванов' or Фамилия ='Петров'

Если в базе данных не существует покупателя по фамилии Петров, запрос вернет только одну запись для клиента Иванов и не вызовет ошибку.

Аналогичный МDХ-запрос

SELECT {[Клиент].[Иванов],[ Клиент].[Петров]} ON COLUMNS

FROM [MyCube]

В Analysis Services 2000 отсутствие элемента Петров в базе данных привело бы к ошибке. Analysis Services 2005 в режиме отсутствия элемента выполнит запрос успешно и вернет результат co значением только для покупателя Иванов.

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

Для управления режимом отсутствия члена измерение имеет свойство Режим Отсутствия члена (MdxMissingMemberMode), которое может быть установлено в состояние Ошибки (Error) или Игнорировать Ошибку (IgnoreError). По умолчанию режим отсутствия члена установлен в Игно­рировать Ошибку (IgnoreError).

Режим отсутствия элемента не распространяется на имена измерений, и не­правильно указанное имя измерения приведет к ошибке.

Кортежи, автоматическая проверка существования

Известно, что пространство куба определяется элементами иерархий атрибутов. Но в действительности многомерное пространство ограничено. Существуют комбинации элементов из разных иерархий членов измерений, которые просто не существуют в таблице измере­ния или в кубе. Например, покупатель Ирина Петрова — Женщина, и в таблице измерения Покупатель существует запись Ирина Петрова, Ж. Это значит, что кортеж ([Клиент].[Пол].[Ж], [Ирина Петрова]) существует в измерении Клиент. Мужчины с именем Ирина Петрова в измерении не задано, поэтому кортеж ([Клиент].[Пол].[M], [Ирина Петрова]) не существует. Когда выражение МDХ ссылается на кортеж, который не существует в из­мерении, то система преобразует его к неопределенному кортежу. Напри­мер, следующий запрос вернет пустой результат:

SELECT {([Клиент].[Пол].[M], [Ирина Петрова]) } on COLUMNS

FROM [MyCube]

Результатом выполнения выражения МDХ не может быть несуществующий кортеж. Поэтому система удаляет несуществующие кортежи из результи­рующего множества специальной операцией, называемой автоматическая проверка существования (Auto-Exists). Результаты применения системой опе­рации Auto-Exists можно увидеть при выполнении следующей функции Crossjoin. Если множества, участвующие в функции Crossjoin, принадлежат одному и тому же измерению, несуществующие кортежи удаляются. Напри­мер, если взять множество из двух покупателей — Ирина Петрова (женщина) и Василий Иванов (мужчина) — и использовать функцию Crossjoin для объединения этого множества с множеством, состоящим из одного элемента, [Клиент].[Пол].[M], результирующее множество не будет содержать полное перекрестное соединение ([Ирина Петрова], [M]), ([Василий Иванов], [M]), а только один кортеж ([Василий Иванов, [M]).

SELECT { [Ирина Петрова], [Василий Иванов] } * [Клиент].[Пол].[M] on COLUMNS

FROM [MyCube]

WHERE [Measures].[Количество продаж]

Сервер не выполняет Auto-Exists между осями, но Auto-Exists выпол­няется для множеств, спроецированных на оси, с множеством, заданным в предложении WHERE. Например, если поместить множество {[Ирина Петрова], [Василий Иванов]} на ось, но элемент [Клиент].[Пол].[M] в предложение WHERE — кортеж [Ирина Петрова] будет удален из множества, спроецированного на ось COLUMNS.

SELECT { [Ирина Петрова], [Василий Иванов] } ON COLUMNS

FROM [MyCube]

WHERE ([Measures].[ Количество продаж],[Клиент].[Пол].[M])

Для того чтобы определить, какие элементы существуют с другими элемен­тами, в дополнение к операции Auto-Exists, можно использовать МDХ-функцию Exists, введенную в Analysis Services 2005. Функция Exists принима­ет в качестве параметров два множества и возвращает множество кортежей из первого множества, которые существуют хотя бы для кортежа из второго множества. Например,

SELECT Exists({[Ирина Петрова], [Василий Иванов]}, [Клиент].[Пол].[M]) ON COLUMNS

FROM [MyCube]

WHERE [Measures].[Количество продаж]

Пример. Получить сведения о продажах для покупателей, которые работают менеджерами.

SELECT [Measures].[ Количество продаж] on COLUMNS,

Exists([Клиент].members,

[Клиент].[Должность].[Менеджер]) ON ROWS

FROM [MyCube]

Неопределенные значения и пустые ячейки

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

Пусть организация начала работать в Украине, а затем расширило свое деятельность на РФ и Молдову. Поэтому в 2009 г. продажи были только в магазинах в Украине, так что в многомерном пространстве не существует данных о продажах в РФ и Молдове. Пусть есть запрос для получения информации о продажах в 2009 г. и о должностях покупателей, которые сделали покупки в магазинах различных стран:

SELECT [Клиент].[Должность].members ON COLUMNS,

[Магазин]. [Страна магазина].members ON ROWS

FROM [MyCube]

where ([Measures].[Сумма продаж],[Время].[Год].[2009])

Для удаления подобных координат из результирующего многомерного про­странства можно использовать оператор NON EMPTY.

SELECT [Клиент].[Должность].members ON COLUMNS,

NON EMPTY [Магазин].[Страна магазина].members ON ROWS

FROM [MyCube]

WHERE ([Measures].[Сумма продаж],[Время].[Год].[2009])

Однако даже если используется оператор NON EMPTY, результаты запроса все равно могут содержать пустые ячейки, потому что оператор NON EMPTY удаляет кортежи из множества, спроецированного на ось, для кото­рых все ячейки пустые.

Оператор NON EMPTY действует на верхнем уровне запроса. Это означает, что сначала генерируются множества, определяющие оси, а затем удаляются кортежи, приводящие к пустым ячейкам. Существует случаи, когда производительность приложения будет значительно выше, если пустые кортежи были бы удалены рано в логике выполнения запроса.

Если запрос МDХ использует функцию Filter для фильтрации очень боль­шого множества, на основании выражения, которое содержит значение кор­тежа, и пространство куба очень разрежено, было бы эффективнее удалить все кортежи, создающие пустые ячейки, перед тем, как выполнять фильтра­цию. МDХ предоставляет функцию NonEmpty, которая позволяет удалять такие кортежи из множества.

Например, необходимо получить всех покупателей со всеми мага­зинами, в которых покупатель купил больше 10 единиц товара. Для этого можно написать следующий запрос:

SELECT Filter([Клиент].members * [Магазин].[Страна магазина].members,

[Measures].[Количество продаж] >10) ON COLUMNS

FROM [MyCube]

WHERE [Measures].[ Количество продаж]

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

SELECT Filter(NonEmpty([Клиент]..members * [Магазин].[Страна Магазина],

[Measures].[Количество продаж]), [Measures].[Количество продаж]>10) ON COLUMNS

FROM [MyCube]

WHERE [Measures].[ Количество продаж]

На первый взгляд, функция NonEmpty и оператор NON EMPTY делают од­но и то же, но выполняются они в разных контекстах. Запросы, кото­рые выглядят похоже, могут возвращать разные результаты. Пусть есть два запроса, один с функцией NonEmpty, другой — с оператором NON EMPTY:

SELECT [Время].[Год].[2009]ON COLUMNS,

NONEMPTY ([Магазин].[Страна магазина].members)ON ROWS

FROM [MyCube]

и

SELECT [Время].[Год].[2009] ON COLUMNS,

NON EMPTY [Магазин].[Страна магазина].members ON ROWS

FROM [MyCube]

Эти запросы возвращают разные результаты.

Функ­ция NonEmpty вычисляется, когда вычисляется множество, помещенное на оси ROWS. Это вычисление выполняется независимо от вычисления мно­жества, помещенного на оси COLUMNS. В примере множество оси ROWS ссылается только на измерение Магазин, а не на измерение Время. Так что измерение Время представлено элементом по умолчанию All. Значение элемента All — непустое и для РФ, и для Молдовы. Поэтому кортежи для РФ и Молдовы не удаляются из множества после применения функции NonEmpty. Но когда вычисляются фактические зна­чения ячеек, они вычисляются для пересечения осей COLUMNS и ROWS. Таким образом, текущая координата измерения Время равна 2009 г. (где не существует данных для РФ и Молдовы), и поэтому получаются неоп­ределенные значения.

Оператор NON EMPTY принимает во внимание кортежи всех осей. Поэтому результаты вычисляются в контексте 2009 г., в котором нет данных для РФ и Молдовы. Поэтому NON EMPTY удаляет кортежи для РФ и Молдовы из результатов.

Извлечение, преобразование и загрузка данных

Рассматриваются следующие вопросы:

· стадии процесса загрузки;

· процесс извлечения данных и его подпроцессы;

· методы ускорения процесса перегрузки данных.

Извлечение, преобразование и загрузка, известные под аббревиатурой ETL (extraction, transformation, loading), – это основные этапы переноса информации из одного приложения в другое.

В общем случае ETL извлекают информацию из исходной базы данных, преобразуют ее в формат, поддерживаемый базой данных назначения, а затем загружают в нее преобразованную информацию. Чтобы извлечь данные из исходной базы данных, можно выбрать один из трех вариантов – создать собственные программы, обратиться к готовому специализированному инструментарию ETL или использовать сочетание и того и другого.

Программист ETL может представлять себе архитектуру ХД в виде совокупности трёх областей: источник данных (совокупность таблиц оперативной системы и дополнительных справочников (классификаторов, таблиц согласования), позволяющую создать многомерную модель данных с требуемыми измерениями), промежуточная область (совокупность таблиц, использующихся исключительно как промежуточные при загрузке ХД) и приёмник данных.

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

В свою очередь, процесс извлечения включает следующие подпроцессы:

- выгрузка;

- структурирование;

- обработка;

- пересылка;

- импорт.

В зависимости от характера источника данных могут использоваться такие методы организации выгрузки данных:

- из СУБД – обычно не вызывает затруднений, можно использовать собственные утилиты СУБД;

- из структурированного источника – для выгрузки используются соответствующие драйвера, например, ODBC; может потребоваться предварительная подготовка структурированного файла человеком или сторонней программой;

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

Должна быть также учтена глубина выборки данных по времени. Как правило, это 2 режима работы процедуры выгрузки: выгрузка всей информации, без учёта времени её поступления, и выгрузка за некоторый последний период (например, за последний закрытый день). Универсальным средством решения является возможность задания, в качестве параметра процедуры, даты, начиная с которой будут выбираться данные.

Структурированные данные могут потребовать дополнительной обработки (очистки, фильтрации, согласования, и т.д.) для повышения качества информации.

В обработке данных могут быть задействованы различные инструменты: от ручного исправления текстового файла аналитиком до утилит, применяющих продвинутые методы анализа данных (нейронные сети, тезаурус, кластеризация и др.).

В ходе проектирования процедур извлечения данных необходимо учесть условия обеспечения безопасности при пересылке и обработке данных. Данные источника не всегда могут быть обработаны на сервере поставщика данных. Тогда их необходимо переслать для обработки на сервер консолидации данных, что также требует наличия защищённых каналов связи и административных ресурсов. В этом случае, этап пересылки выполняется прежде структурирования данных.

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

Ошибки могут появляться в любом из подпроцессов стадии извлечения данных. Отследить их возникновение – задача слабо формализуемая. Кроме того, зачастую вся ответственность за обеспечение корректной работы процедур извлечения данных возлагается на программистов, что может быть оправдано только для жёстко структурированных источников данных (например, когда загрузка производится напрямую из СУБД).
В любом случае, обязательной является обработка фатальных для дальнейших стадий ошибок.

Примеры фатальных ошибок:

- отсутствие файлов источника данных;

- ошибка доступа к данным;

- возникновение системной ошибки ОС.

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

Категории критериев оценки качества данных можно свести к следующим классам:

1) по критичности:

- критичные ошибки в данных (данные, которые не соответствуют этому критерию, не могут быть загружены в ХД), например, числовое выражение, содержащее букву;

- некритичные ошибки в данных (данные, которые могут быть загружены в ХД, но не являются качественными), например, пустое (NULL) значение в поле имени;

- качественные данные.

2) по проверяемым объектам:

- корректность форматов и представлений данных;

- уникальность первичных и альтернативных ключей;

- полнота данных;

- полнота связей;

- соответствие данных аналитическим ограничениям.

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

Преобразование данных сводится к нескольким элементарным операциям:

- вычисление;

- агрегация;

- согласование ключей;

- генерация суррогатных ключей.

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

Согласование ключей – операция приведения идентификаторов набора данных источника к виду, соответствующему идентификаторам ХД.

Генерация суррогатных ключей– операция сопоставления естественному ключу (чаще всего – составному) уникального суррогатного ключа – идентификатора набора данных ХД. Чаще всего применяются следующие способы: последовательная нумерация или кодирование естественного ключа (суррогатный ключ вычисляется из естественного с помощью некоторой функции).

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

Процесс перегрузки данных источников в хранилище данных, с технической точки зрения, является последовательностью SQL-запросов к СУБД над довольно большими объёмами данных (от 1 до 100 мегабайт за один сеанс). Поэтому, выполнение неоптимизированных процессов перегрузки может на порядки увеличить время выполнения за счёт излишних или повторных обработок или пересылок данных.

В частности, можно учесть следующиеметоды ускоренияпроцесса:

- наличие у таблицы индексов или представлений может сильно увеличить время вставки данных в эту таблицу – перед заполнением необходимо, по возможности, удалить все индексы и представления и создать их заново после заполнения;

- скорость передачи данных по сети намного меньше скорости передачи данных внутри одного сервера СУБД – на этапе выгрузки данных из источника следует применить все возможные фильтры и процедуры агрегации, чтобы ускорить прохождение данных по сети;

- применение оператора distinct сильно замедляет выполнение запроса в некоторых СУЬБД – вместо оператора distinct использовать group by, например, вместо оператора SELECT DISTINCT Ид_Товара FROM Продажа использовать оператор SELECT Ид_Товара FROM Продажа group by Ид_Товара;

- в некоторых СУБД отсутствует отдельная нежурнализируемая операция очистки таблицы, а применение оператора DELETE FROM может выполняться медленно – можно применить парные операции удаления и последующего создания таблицы, при этом придется заново создать зависимые объекты, например, индексы.

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

Например, один из способов оптимизации очистки данных состоит в следующем. При очистке данных производится проверка каждой записи на соответствие ряду заранее выбранных критериев и правил. Так как проверка одних критериев может зависеть от результатов проверки других (например, проверка ограничения на значение числа, содержащегося в поле с типом данных char(10), зависит от проверки, является ли содержимое этого текстового поля числом), то рекомендуется по результатам проверки критериев с более высоким приоритетом (в приведённом примере проверка, является ли значение поля числом, имеет более высокий приоритет) формировать промежуточные (временные) таблицы, которые будут потом проверяться на соответствие другим критериям. В результате запрос на проверку каждого последующего критерия будет обрабатывать всё меньший объём данных. Этот способ имеет и недостатки: отсутствие выигрыша при поступлении качественных данных, и даже проигрыш по скорости за счёт потребности в очистке промежуточных таблиц; увеличение числа объектов промежуточной области; увеличение числа шагов.

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

Одна из самых медленных операций в СУБД – это операция обновления UPDATE. Один из методов обойти использование UPDATE – замена её на операции удаления и вставки.

Возможны два варианта замены:

1) стандартный

a) удаление из ХД строк, которые подлежат обновлению;

b) Вставка всех строк с новыми значениями в ХД;

2) оптимизированный

a) перенос строк, которые не подлежат обновлению из ХД, во временную таблицу со строками, которые подлежат обновлению;

b) очистка всей таблицы ХД;

c) вставка в ХД всей временной таблицы.

Подобная замена будет эффективна при большом количестве обновляемых полей таблицы ХД (более 10). Однако, эта замена невозможна для случаев, когда ограничения ссылочной целостности созданы физически в базе данных. Для таких случаев UPDATE – единственный способ обновления данных.

ETL-процесс является узким местом концепции хранилищ данных для решения многих задач. При построении ХД наибольшие затраты, как правило, приходятся именно на этап ETL. Правильный подход в реализации процессов ETL позволят существенно оптимизировать затраты при построении современного аналитического информационного комплекса и повысить его эффективность.

Очистка данных

Рассматриваются следующие вопросы:

· проблемы отдельного источника данных на уровне схемы;

· проблемы отдельного источника данных на уровне элемента данных;

· проблемы очистки данных из множества источников;

· методы очистки данных;

· разрешение конфликтов;

· примеры возникновения «грязных данных».

Поскольку хранилища данных загружают и постоянно обновляют огромные объемы данных из различных источников, вероятность попадания в них «грязных данных» весьма высока. Более того, ХД используются для принятия решений, следовательно, чтобы некорректные данные не привели к некорректным выводам, просто жизненно необходимо проводить корректировки таких данных. Например, дублирующаяся или утраченная информация может стать причиной некорректной или неадекватной статистики.

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

Проблемы отдельных источников данных

Проблемы отдельного источника данных на уровне схемы

Проблемы отдельных источников данных на уровне элемента