Главная › [:. Блог .:] ›

Как получить данные из многомерных кубов в реляционном (табличном) виде

Опубликовано d.mokshin в птн, 13/03/2015 - 21:42.

Иногда возникает потребность импортировать/загрузить данные из кубов Microsoft SQL Server Analysis Services (MS SSAS) в обычную реляционную БД. Ну или просто работать с данными как с обычными табличными.

Сделать это в принципе не сложно, но потребуется несколько хитрых манипуляций.
Итак, по шагам:



   0. Предположим у нас есть:
MS SQL Server (называется SQLSERVER) содержит БД DEST_DB, где мы и хотим обрабатывать данные из куба с сервера SSAS.
MS SSAS (называется SSAS) содержит OLAP БД (называется OLAP_DB). В ней есть куб SALES_Cube с двумя мерами Sales и Profit (которые относятся к группе мер Sales_Results) и с двумя измерениями Departments и Fact_Dates. При этом измерение Fact_Dates имеет иерархию YMD с уровнями Year, Month, Day.
Мы хотим получить выборку сумм продаж и прибыли по всем отделам и всем датам.
MDX запрос, который вернет нам это:

select
    {
       [Measures].[Sales],
       [Measures].[Profit]
    } on columns,
    ([Departments].AllMembers, [Fact_Dates].[Day].AllMembers) on rows
from [SALES_Cube]

Если хотим ограничить выборку только теми комбинациями данных, для которых точно есть рассчитанные агрегаты, (а это имеет смысл сделать для выборки, если у вас много значений измерений, которые в комбинациях друг с другом дадум геометрически огромное количество вариантов - и зачем нам "переваривать" варианты, для которых точно нет агрегатов?) можно использовать функцию Exists:

select
    {
       [Measures].[Sales],
       [Measures].[Profit]
    } on columns,
    Exists(([Departments].AllMembers, [Fact_Dates].[Day].AllMembers), ,"Sales_Results") on rows
from [SALES_Cube]

Если нужно ограничить выборку какими-то критериями - считаем, что с MDX все знакомы. ;-)



   1. В SQLSERVER создать прилинкованный сервер, ссылающийся на источник данных OLAP - как это сделать описано здесь. Пусть прилинкованный сервер называется LOCAL_SSAS



   2. Чтобы получить данные на сервере SQLSERVER с сервера SSAS нужно использовать OPENQUERY. Соответственно, это можно оформить в хранимой процедуре. Но чтобы к данным можно было обращаться как к таблице, можно это оформить в виде функции. Например так:

create function [dbo].[OLAP_SALES_Cube]()
RETURNS @tRes TABLE
   (
   Department varchar(255),
   FDY varchar(255),
   FDM varchar(255),
   FDD varchar(255),
   Sales float,
   Profit float
   )
begin

   insert into @tRes
   SELECT *
   FROM
      OPENQUERY(
            [LOCAL_SSAS],
            '
            select
            {
               [Measures].[Sales],
               [Measures].[Profit]
            } on columns,
            Exists(([Departments].AllMembers, [Fact_Dates].[Day].AllMembers), ,"Sales_Results") on rows
            from [SALES_Cube]
            '
            )

   update @tRes set Department = '# TOTAL #' where Department is null

   return
end

На что тут стоит обратить внимание:

  • Несмотря на то, что мы вроде бы обращаемся к элементам уровня Day измерения Fact_Dates - преобразованный OPENQUERY запрос вернет значения и для Year и Month - поэтому используются 3 поля: FDY, FDM, FDD (но это, собственно, то, что и нужно).
  • Если у измерений настроено использование группирующих значений (All), то они в результирующей выборке будут проходить как строки со значениями NULL. Для этого применяется строка update @tRes set Department = '# TOTAL #' where Department is null (для наглядности). Можно это обрабатывать и как-то по-другому, конечно.
  • Рекомендую выполнить запрос SELECT * FROM OPENQUERY(.....) сам по себе отдельно - очень интересно и познавательно в нем выглядят названия полей.



   3. Вот и всё - функцию можно использовать как удобно на свое усмотрение: select * from [dbo].[OLAP_SALES_Cube]()
Нравится