Запросы с индивидуальными счетчиками

Рас Уитни, Windows 2000 Magazine

Тонкости анализа покупательской корзины средствами OLAP

Какой товар покупают лучше всего? Сколько покупателей приобрели указанные два товара вместе? Какую долю от общего числа покупателей (в процентах) составляют те покупатели, которые за один поход в магазин приобретает меньше 10 различных наименований товаров? Такие вопросы служат примерами запросов с индивидуальными счетчиками. На них очень сложно ответить с помощью OLAP, поскольку средства OLAP, как правило, хранят лишь суммарную информацию.

Запросы с индивидуальными счетчиками отличаются от запросов с обычным суммированием только тем, что для определения результата, относящегося к отдельным пунктам в используемой иерархии размерности, обязательно надо обращаться к информации, расположенной на самом нижнем уровне иерархии. К примеру, если подсчитывается количество различных товаров, закупленных в первом квартале, нельзя просто сложить товары, закупленные в январе, феврале и марте. Чтобы подсчитать суммарное значение для первого квартала, необходимо знать все товары, закупленные в каждый из этих месяцев, потому что необходимо исключить все дубликаты товаров, образовавшиеся при слиянии этих трех множеств. В этой статье я рассмотрю подходы с применением MDX, которыми можно воспользоваться при работе с SQL Server 7.0 OLAP Services или с SQL Server 2000 Analysis Services для решения задач, подобных описанным выше. Помимо этого я расскажу о новом свойстве Analysis Services, которое может существенно упростить решение таких задач. В основу этого материала легла техническая статья Амира Нетца (архитектора Microsoft OLAP Services) из серии "белых книг", которая посвящена анализу покупательской корзины товаров с применением OLAP Services. Анализ покупательской корзины обычно относится к видам анализа розничной торговли, где покупательской корзиной принято называть группу товаров, приобретаемых посетителем магазина за один раз (то есть за одну транзакцию). В своей статье Нетц рассматривает тот же самый материал, который разбирается в моей статье, но более глубоко. Статью Амира Нетца, которая в оригинале называется "OLAP Services: DISTINCT COUNT and Basket Analysis", можно найти на сайте Microsoft для разработчиков, Microsoft Developer Network (MSDN), по адресу http://msdn.microsoft.com/library/techart/distinct2.htm.

Анализ покупательской корзины

Мне доводилось встречать разные толкования термина анализ рыночной корзины или анализ покупательской корзины. Чаще всего он используется, когда речь идет об исследовании данных с целью извлечения информации(data-mining), когда идет поиск зависимости между товарами с точки зрения спроса на них. Классическим примером стала история о выявлении связи между пеленками и пивом. Кто-то когда-то при проведении анализа покупательской корзины с целью изучения пристрастий покупателей мужского пола обнаружил сильную корреляцию между покупками пеленок и покупками пива. Объясняют этот феномен так: мужчины используют поход в магазин за пеленками для своего малыша как повод заодно пополнить запасы пива. У меня у самого двое маленьких детей, но я не возьму на себя смелость ни подтвердить, ни опровергнуть этот вывод. Тем не менее, вооружившись этой рекомендацией, менеджер магазина может реорганизовать распределение товаров по полкам таким образом, чтобы поощрить такой стереотип поведения, потенциально увеличивая объем продаж пива.

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

Запросы с индивидуальными счетчиками можно с успехом применять в различных приложениях, а не только для анализа розничной торговли или исследования рынка. Фактически запросы с индивидуальными счетчиками полезны в любых ситуациях, если между сущностями действуют отношения многие-ко-многим (M:N). К примеру, в аналитическом приложении для центра обработки вызовов при помощи таких запросов можно выяснить, сколько звонков клиентов содержали одновременно просьбу сменить адрес и заказ на приобретение нового продукта. В приложении для промышленности подобные запросы смогут помочь установить, например, взаимозависимость между характером брака в производимых товарах и используемыми комплектующими этих товаров.

Какие товары больше всего покупают?

А теперь подробно рассмотрим следующий вопрос: какие товары больше всего покупают? В стандартном кубе для анализа продаж предусмотрены размерности заказчиков Customer, товаров Product и времени Time, а в фактографической таблице содержатся сведения о транзакциях Sales, каждая из которых относится к конкретной комбинации заказчика, товара и времени. Отдельная транзакция выглядит следующим образом: заказчик A купил товар B в момент времени C за некоторую сумму денег. Если создать измерение счетчиков для этого куба, то счетчик сможет для любой комбинации элементов заказчика, товара и времени подсчитать суммарное количество транзакций. Такой счетчик, безусловно, полезен, но мне нужно нечто иное. Мне не важно, покупал ли один и тот же заказчик определенный товар много раз. Меня интересует подсчет отдельных заказчиков, а не отдельных транзакций. Чтобы вычислить количество отдельных заказчиков, покупавших заданный товар, можно воспользоваться следующим выражением MDX:

  Count( Filter( Customers.Name.Members, Not

    IsEmpty( (Product.CurrentMember, 

    Customers.CurrentMember) ) )

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

  Count( {Unit Sales} *

   Customers.Name.Members, ExcludeEmpty )

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

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

  Count( {Unit Sales} * Descendants

  ( Customers.CurrentMember,

   Customers.Name ), ExcludeEmpty )

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

Выражение MDX для создания вычисляемого члена.

With Member [Measures].[Unique Customers]

AS `Count( {[Unit Sales]} * Descendants(

[Customers].CurrentMember, [Customers].[Name] ), ExcludeEmpty )`

SELECT { [Unique Customers] } ON COLUMNS,

  TopCount( Descendants([Product].[All Products].

[Drink].[Alcoholic Beverages].[Beer and Wine].[Beer],

[Product].[Product Name]), 5, [Unique Customers] ) ON ROWS

FROM Sales

WHERE ([Customers].[All Customers].[USA].[CA])

На моем портативном компьютере с процессором с тактовой частотой 600 МГц этот запрос выполнялся около 2,5 минут.

В Analysis Services индивидуальные счетчики оформлены в виде нового встроенного типа агрегированных данных. Поэтому при построении куба можно создать дополнительное измерение индивидуальных заказчиков Unique Customers. В качестве типа данных для этого измерения можно указать тип данных индивидуальных счетчиков и идентифицировать столбец в исходной фактографической таблице, содержимое которого должно суммироваться. В рассматриваемом примере в измерении индивидуальных заказчиков Unique Customers должно подсчитываться количество различных имен заказчиков.

На измерения, относящиеся к индивидуальным счетчикам, Analysis Services накладывает некоторые ограничения. Одно из таких ограничений состоит в том, что куб, в котором определено измерение индивидуальных счетчиков, не может содержать размерности, для которых будет определен специальный алгоритм свертки, заданный клиентом, или же какие-либо введенные пользователями формулы для подобных сверток. Другое ограничение оказывается еще более важным: в кубе может существовать только одно измерение, относящееся к индивидуальным счетчикам. К счастью, это ограничение не относится к виртуальным счетчикам. Фактически, по мнению разработчиков Microsoft, кубы, содержащие измерение индивидуальных счетчиков, должны быть как можно меньше. Затем эти кубы можно объединять с другими кубами в единый виртуальный куб. Наименьший возможный из подобных кубов не будет содержать других измерений, кроме измерения индивидуального счетчика, и только те размерности, которые будут использоваться в запросах с применением индивидуального счетчика.

Если изолировать измерение индивидуального счетчика, то Analysis Services затратит меньше памяти, и будет выполнять запросы с применением индивидуальных счетчиков намного быстрее. Такой подход позволит заметно повысить производительность, поскольку Analysis Services сможет определить, что запросам с использованием индивидуальных счетчиков потребуется информация только из небольшого куба, содержащего измерение индивидуального счетчика. В итоге Analysis Services потребуется обрабатывать существенно меньший объем информации. При использовании индивидуальных счетчиков особое значение приобретает оптимизация структуры куба. Это обусловлено тем, что для выполнения запросов с индивидуальными счетчиками требуется доступ к самой подробной информации о размерностях, тогда как обычно Analysis Services пользуется лишь обобщенной информацией при обработке запросов без использования индивидуальных счетчиков.

Какие товары покупают вместе?

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

Нетц также обращает внимание читателей на то, что важно иметь в виду возможность трактовать две транзакции, произошедшие в заданный интервал времени, как одну и ту же транзакцию. К примеру, посетитель магазина, состоящего из нескольких отделов, может купить что-то в одном отделе, а затем продолжить поход по магазину и сделать ряд покупок в других отделах. Транзакции этого покупателя будут зарегистрированы в один день, но в различных пунктах оплаты. Нетц считает, что для принятия решений желательно объединить в одну транзакцию все транзакции конкретного заказчика за определенный период времени (день или неделя). Этот подход прекрасно работает в среде OLAP, поскольку все многомерные базы данных имеют размерность времени Time, а язык запросов MDX хорошо приспособлен к работе с временными периодами. В кубе продаж Sales витрины данных FoodMart 2000 размерность времени Time не разбита на дни, поэтому в приведенных примерах запросов MDX я воспользовался наименьшим доступным в кубе Sales интервалом времени (месяц).

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

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

  Count(Filter(Time.Month.Members, Not

   IsEmpty((Unit Sales, Pizza)) and Not

   IsEmpty((Unit Sales, Product.CurrentMember))))

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

  Sum( {Tx per Customer} *

   Descendants(Customers.CurrentMember,

    Customers.Name))

В листинге 2 показано, как можно скомбинировать две приведенные выше формулы для формирования полного запроса MDX, который ответит на поставленный вопрос.

Полный запрос MDX, который отвечает на вопрос о пиве и пицце.

WITH MEMBER [Measures].[Tx per Customer] AS `Count(Filter([Time].

[Month].Members, Not IsEmpty(([Unit Sales], [Pizza]))

and Not IsEmpty(([Unit Sales], [Product].CurrentMember))))`

MEMBER [Measures].[Total Transactions] AS `Sum( {[Tx per Customer]}*

Descendants([Customers].CurrentMember, [Customers].[Name]))`

SELECT {[Total Transactions] } ON COLUMNS,

  Descendants([Beer], [Product].[Product Name]) ON ROWS

FROM Sales

WHERE ([Customers].[All Customers].[USA].[CA])

В реальных приложениях для проведения анализа покупательской корзины может потребоваться ограничить общее количество транзакций. Для этого следует наложить определенные условия, как я это и сделал в рассматриваемом примере, ограничив заказчиков только жителями штата Калифорния. Такой подход предпочтительнее, поскольку нам нужно не подсчитать количество транзакций, а выявить зависимости между товарами. Выявить зависимости проще, тестируя лишь часть данных. Поэтому пример, показанный в листинге 2, вполне корректен (конечно, если заказчики из штата Калифорния образуют представительную выборку). На рисунке 1 показан ответ, полученный на данный запрос.

Рисунок 1.

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

Предложенное решение можно легко расширить таким образом, чтобы выявлять покупаемые вместе комбинации товаров, состоящие более чем из двух компонентов. Для этого следует просто изменить формулу для Tx per Customer , чтобы она включала большее количество товаров. К сожалению, новый тип агрегированных данных Analysis Services, индивидуальные счетчики, не поможет в составлении такого рода запросов. Агрегирование при помощи индивидуальных счетчиков работает только при подсчете количества пунктов в столбце фактографической таблицы. Однако в приведенном примере для покупаемых вместе товаров подсчитывается количество различных вариантов определенных комбинаций в столбце.

Анализ куба

Те вопросы, на которые позволяют получить ответы запросы с индивидуальными счетчиками, оказываются крайне важными при анализе розничной продажи товаров. В качестве примера мы рассмотрели вопрос о том, какое пиво чаще всего покупают вместе с пиццей? Но подобный анализ полезно проводить в любом кубе. Чтобы продемонстрировать это положение, достаточно взглянуть на куб, описывающий персонал в базе данных FoodMart 2000. Измерение "Количество служащих", Number of Employees, представляет собой индивидуальный счетчик, который позволяет выяснить, сколько служащих работает в конкретном магазине или под руководством определенного менеджера. А поскольку соответствующая фактографическая таблица содержит сведения лишь о транзакциях оплаты, получить ответ на эти вопросы при помощи типового измерения счетчиков нельзя. Обратите внимание на то, что читатели смогут воспользоваться той методикой, которую я продемонстрировал в данной статье, для формирования запросов с индивидуальными счетчиками в приложениях. Свои пожелания и предложения присылайте по адресу: olapmasters@sqlmag.com.

Об авторе

Рас Уитни rwhitney@knosysinc.com возглавляет исследовательское отделение в компании Knosys, руководит разработкой клиентского инструментария OLAP. Входит в совет директоров компаний Knosys и Distributed Database Consulting (DDBC).