SSAS - контекстные SQL запросы к реляционным источникам, часть 2

OLAP, SSAS  SSAS Категория:  OLAP, SSAS
Опубликовал:         30.06.2013               print

... Не всегда и не все отчеты можно сделать на базе OLAP-кубов. Это обусловлено следующими причинами:

  • * требуются выборки сотен тысяч, нескольких миллионов записей, например, для передачи списков внешним контрагентам;
  • * требуются массивные вычисления на уровне гранулярности;
  • * очень сложная логика вычислений, онлайн преобразований данных;
  • * в OLAP-кубе отсутствуют необходимые данные / развитие модели куба, процессинг куба требует времени, а бизнесу нужен результат прямо сейчас (as soon as possible);
  • * исходные данные для отчета(ов) весьма низкого качества, невнятна перспектива их улучшения;
  • * не все пользователи готовы / хотят работать с современными технологиями - многомерными базами данных.


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

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

--------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE queries
( id_query                       NUMBER(8,0)       NOT NULL
 ,query_name                     VARCHAR2(400)     NOT NULL
 ,query_body                     VARCHAR2(2000)    NOT NULL
 ,query_end                      VARCHAR2(2000)
 ,query_param1_prefix            VARCHAR2(500) 
 ,query_param1_suffix            VARCHAR2(500) 
 ,olap_attribute1_name           VARCHAR2(500)
 ,query_param2_prefix            VARCHAR2(500) 
 ,query_param2_suffix            VARCHAR2(500) 
 ,olap_attribute2_name           VARCHAR2(500)
 ,query_param3_prefix            VARCHAR2(500) 
 ,query_param3_suffix            VARCHAR2(500) 
 ,olap_attribute3_name           VARCHAR2(500)
 ,query_param4_prefix            VARCHAR2(500) 
 ,query_param4_suffix            VARCHAR2(500) 
 ,olap_attribute4_name           VARCHAR2(500)
 ,query_description              VARCHAR2(3000)
 ,queries_group                  VARCHAR2(200)     NOT NULL
 ,
  CONSTRAINT pk_id_query
  PRIMARY KEY (id_query)
  USING INDEX
  TABLESPACE  dwh
)
  TABLESPACE  dwh
/

COMMENT ON TABLE queries IS 'Список запросов к внешним источникам данных, вызываемых из OLAP'
/
COMMENT ON COLUMN queries.id_query IS 'Уникальный идентификатор запроса'
/
COMMENT ON COLUMN queries.query_name IS 'Наименование запроса'
/
COMMENT ON COLUMN queries.query_body IS 'Тело запроса'
/
COMMENT ON COLUMN queries.query_end IS 'Окончание запроса'
/
COMMENT ON COLUMN queries.query_description IS 'Описание запроса'
/
COMMENT ON COLUMN queries.queries_group IS 'Группа запросов (для группирования запросов в иерархию)'
/
COMMENT ON COLUMN queries.query_param1_prefix IS 'Префиксная часть параметра 1 для подстановки в запрос'
/
COMMENT ON COLUMN queries.query_param1_suffix  IS 'Суффиксная часть параметра 1 для подстановки в запрос'
/
COMMENT ON COLUMN queries.olap_attribute1_name IS 'Полное наименование OLAP атрибута 1, значение которого должно передаваться параметру 1 запроса'
/
COMMENT ON COLUMN queries.query_param2_prefix IS 'Префиксная часть параметра 2 для подстановки в запрос'
/
COMMENT ON COLUMN queries.query_param2_suffix  IS 'Суффиксная часть параметра 2 для подстановки в запрос'
/
COMMENT ON COLUMN queries.olap_attribute2_name IS 'Полное наименование OLAP атрибута 2, значение которого должно передаваться параметру 2 запроса'
/
COMMENT ON COLUMN queries.query_param3_prefix IS 'Префиксная часть параметра 3 для подстановки в запрос'
/
COMMENT ON COLUMN queries.query_param3_suffix  IS 'Суффиксная часть параметра 3 для подстановки в запрос'
/
COMMENT ON COLUMN queries.olap_attribute3_name IS 'Полное наименование OLAP атрибута 3, значение которого должно передаваться параметру 3 запроса'
/
COMMENT ON COLUMN queries.query_param4_prefix IS 'Префиксная часть параметра 4 для подстановки в запрос'
/
COMMENT ON COLUMN queries.query_param4_suffix  IS 'Суффиксная часть параметра 4 для подстановки в запрос'
/
COMMENT ON COLUMN queries.olap_attribute4_name IS 'Полное наименование OLAP атрибута 4, значение которого должно передаваться параметру 4 запроса'
/
--------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE query_access
(   id_query                       NUMBER(8,0) NOT NULL,
    id_user                        NUMBER(8,0) NOT NULL,
    access_start                   DATE        NOT NULL,
    access_expiry                  DATE        NOT NULL,
  CONSTRAINT pk_query_access
  PRIMARY KEY (id_query, id_user)
  USING INDEX
  TABLESPACE  dwh
)
  TABLESPACE  dwh
/

ALTER TABLE query_access ADD CONSTRAINT fk_id_user2 FOREIGN KEY (id_user) REFERENCES users (id_user)
/
ALTER TABLE query_access ADD CONSTRAINT fk_id_query2 FOREIGN KEY (id_query) REFERENCES queries (id_query)
/
ALTER TABLE query_access  ADD CONSTRAINT ck_access_expiry2 CHECK (ACCESS_EXPIRY>=ACCESS_START)
/

COMMENT ON TABLE query_access IS 'Для разграничения доступов пользователей к запросам к внешним источникам данных'
/
COMMENT ON COLUMN query_access.id_query IS 'ID запроса'
/
COMMENT ON COLUMN query_access.id_user IS 'ID пользователя'
/
COMMENT ON COLUMN query_access.access_start IS 'Дата начала права доступа пользователя к запросу'
/
COMMENT ON COLUMN query_access.access_expiry IS 'Дата окончания права доступа пользователя к запросу'
/
--------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER tr_queries_insert AFTER INSERT ON queries
FOR EACH ROW
BEGIN
  INSERT INTO query_access ( id_query
                            ,id_user
                            ,access_start
                            ,access_expiry
                           )
    SELECT :NEW.id_query
           ,u.id_user
           ,TO_DATE('01.01.2000', 'dd.mm.yyyy')
           ,CASE WHEN u.user_role='individual' THEN TO_DATE('01.01.2000', 'dd.mm.yyyy') ELSE TO_DATE('01.01.2100', 'dd.mm.yyyy') END
      FROM users u ;
END ;
--------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------------------------------------------------------------------
CREATE TABLE queries_log
( id_query                       NUMBER(8,0)     NOT NULL
 ,query                          VARCHAR2(4000)  NOT NULL
 ,login                          VARCHAR2(50)    NOT NULL
 ,query_execute_start            DATE            NOT NULL
 ,access_result                  VARCHAR2(50)
)
  TABLESPACE  dwh
/

COMMENT ON TABLE queries_log IS 'Журналирование выполнения запросов пользователей к внешним источникам данных'
/
COMMENT ON COLUMN queries_log.id_query IS 'Идентификатор запроса'
/
COMMENT ON COLUMN queries_log.query IS 'Полный текст запроса'
/
COMMENT ON COLUMN queries_log.login IS 'Учетная запись пользователя, который выполнял запрос'
/
COMMENT ON COLUMN queries_log.query_execute_start IS 'Дата-время, когда был вызов запроса'
/
COMMENT ON COLUMN queries_log.access_result IS 'Результат разрешения пользователю исполнить запрос'
/
--------------------------------------------------------------------------------------------------------------------------------------------------

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




Каждый хранимый запрос состоит из следующих частей: тело запроса (обязательная часть), 4 необязательных частей - входных параметров, довершающая часть запроса (необязательная часть). Четыре входных параметра вполне достаточно для многих запросов, хотя при необходимости можно сделать больше.

На базе этой таблицы создадим вспомогательное измерение, которое в кубе не будет привязано ни к какой группе мер:




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

Далее нам не обойтись без расширенной CLR процедуры. Скачаем известный проект Analysis Services Stored Procedure Project, откроем проект в среде разработки Microsoft Visual Studio 2010 и там, где находится функция CurrentCellAttributes, рядом добавим нижеследующие функции:

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public static string CurrentCellAttributeValue(string AttributeName)
// функция возвращает заголовок текущего элемента атрибута; 
// полное имя атрибута измерения указывается в AttributeName
{
  String attrVal = "";
  string CubeName = AMOHelpers.GetCurrentCubeName();
  foreach (Dimension d in Context.Cubes[CubeName].Dimensions)
  {
    foreach (Hierarchy h in d.AttributeHierarchies)
    {
      // пропускаем пользовательские и parent-child иерархии
      if (h.HierarchyOrigin == HierarchyOrigin.UserHierarchy)
        continue;

      // пропускаем меры и формулы
      if (d.DimensionType == DimensionTypeEnum.Measure || h.CurrentMember.Type == MemberTypeEnum.Formula)  
        continue;

      if (h.CurrentMember.UniqueName.StartsWith(AttributeName))
        {
          if (h.CurrentMember.Type == MemberTypeEnum.All)
            attrVal = "|||";  // так будем обозначать заголовок уровня AllMember
          else
            attrVal = h.CurrentMember.Caption;
         break;
        }
    }
  }
  return attrVal;          
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public static DataTable ControlledExecuteQuery(string ID_query, string OriginalQuery, string DoQueryLog)
// функция выполняет sql-запрос (OriginalQuery) c индентификатором ID_query; 
// при необходимости (DoQueryLog="logging" ) в отдельную таблицу логгируется вызов исполнения запроса
{
  string query;
  string User_Name = Context.CurrentConnection.User.Name;
  string access_result="";
  string connectionString = "Provider=OraOLEDB.Oracle.1;Data Source=DWH;User ID=OLAP_user;Password=XXXXX;";
  System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(connectionString);
  if (QueryCheckPermission(User_Name, ID_query, connectionString) < 1)
    {// если пользователю запрещено выполнение запрашиваемого query, то выполнится фейковый запрос
     query = "SELECT 'У Вас нет прав для выполнения данного запроса!' AS access_denied FROM dual";
     access_result = "доступ запрещен";
    }
  else
    query = OriginalQuery;
  if (DoQueryLog == "logging") // Если требуется, то в лог-таблицу записывается кто какой запрос запрашивал
    WriteQueryLog(User_Name, ID_query, OriginalQuery, access_result, connectionString);

  System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
  cmd.CommandText = query;
  cmd.CommandTimeout = 3000; // на тот случай, если sql-инструкция будет долгоисполняющейся
  cmd.Connection = conn;
          
  System.Data.OleDb.OleDbDataAdapter da = new System.Data.OleDb.OleDbDataAdapter();
  da.SelectCommand= cmd;

  DataTable dt = new DataTable("Result");
  da.Fill(dt);
  return dt;
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public static int QueryCheckPermission(string UserName_, string ID_query_, string connString_)
// функция проверяет - разрешено ли пользователю выполнить указанный запрос ID_query_;
// если разрешено, то данная функция должна вернуть 1
{
  int retVal = -1;
  string queryString = "SELECT u.*, r.*, a.access_start, a.access_expiry " 
                     + "  FROM users u "
                     + "  INNER JOIN query_access  a ON u.id_user=a.id_user "
                     + "  INNER JOIN queries       r ON a.id_query=r.id_query "
                     + "    WHERE LOWER(u.user_activity)='активный' AND u.user_role='individual' AND LOWER(u.login)=LOWER('" + UserName_ + "')"
                     + "          AND r.id_query=" + ID_query_
                     + "          AND TRUNC(SYSDATE) BETWEEN a.access_start AND a.access_expiry" 
                     + " UNION " 
                     + "SELECT u.*, r.*, TO_DATE('01.01.2000', 'dd.mm.yyyy') AS access_start, TO_DATE('01.01.2100', 'dd.mm.yyyy') AS access_expiry "
                     + "  FROM users u "
                     + "  INNER JOIN query_access  a ON u.id_user=a.id_user "
                     + "  INNER JOIN queries       r ON a.id_query=r.id_query "
                     + "    WHERE LOWER(u.user_activity)='активный' AND u.user_role<>'individual' AND LOWER(u.login)=LOWER('" + UserName_ + "')"
                     + "          AND r.id_query=" + ID_query_ ;

  System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
  conn.ConnectionString = connString_;
  System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryString, conn);
  try
  { conn.Open(); }
  catch (Exception ex)
  {
    retVal = -2;
    return retVal;
  }

  try
  { System.Data.OleDb.OleDbDataReader rdr = cmd.ExecuteReader();
    if (rdr.HasRows) // если есть запись в выборке, значит пользователю разрешен доступ к ресурсу
      retVal = 1;
    else
      retVal = 0;
    rdr.Close();
  }
  catch (Exception ex)
    { retVal = -5; }

  if (conn.State == ConnectionState.Open)
    conn.Close();

  return retVal;
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
public static int WriteQueryLog(string UserName_, string ID_query_, string query_, string access_result_, string connString_)
// функция пишет в лог-таблицу-  кто какой запрос к внешним источникам данных запрашивал
{
  int retVal = -1;
  System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection();
  conn.ConnectionString = connString_;
  try
  { conn.Open(); }
  catch (Exception ex)
  {
    retVal = -2;
    return retVal;
  }

  try
  {
  ////System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(
  ////  "INSERT INTO dbo.queries_log(id_query, query, login, query_execute_start, access_result) VALUES(:id_query, :query, :login, :query_execute_start, :access_result)", conn);
  ////cmd.Parameters.AddWithValue("id_query", ID_query_);
  ////cmd.Parameters.AddWithValue("query", query_);
  ////cmd.Parameters.AddWithValue("login", UserName_);
  ////cmd.Parameters.AddWithValue("query_execute_start", DateTime.Now);
  ////cmd.Parameters.AddWithValue("access_result", access_result_);
  ////cmd.ExecuteNonQuery();
  string query_2 = query_.Replace("'", "''"); // обертка для одиночных кавычек, если вдруг такие встретятся
  string queryString = "INSERT INTO dbo.queries_log(id_query, query, login, access_result) VALUES('" + ID_query_ + "', '" + query_2 + "', '" + UserName_ + "', '" + access_result_ +"')";
  System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(queryString, conn);
  cmd.ExecuteNonQuery();
  retVal = 1;
  }
  catch (Exception ex)
  { retVal = -5; }

  if (conn.State == ConnectionState.Open)
    conn.Close();

  return retVal;
}
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

Скомпилируем проект, подключим обновленную DLL к Analysis Services. Теперь имеем право в OLAP-проекте добавить дополнительное действие (Action):




Данное действие собирает финальный текст запроса на основании значений свойств выбранного элемента вспомогательного измерения [_Запросы к источникам данных] и контекста текущей ячейки куба. Вспомогательное измерение (как и прочие вспомогательные измерения из других ранее рассмотренных задач) подлежит размещению в области фильтров сводной таблицы. Если для текущей ячейки по атрибуту измерения выбрано несколько элементов или выбран элемент уровня All, то в тексте запроса соответствующий входной параметр будет проигнорирован (не будет включена подстрока).

--------------------------------------------------------------------------------------------------------------------------------------------------
-- Данное дополнительное действие (Action) отправляет запрос к внешнему источнику данных;
-- текст запроса собирается из значений свойств текущего элемента вспомогательного измерения [_Запросы к источникам данных]
-- и контекста текущей ячейки куба

'call ASSP.ControlledExecuteQuery("'
 + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Member_key + '", "'
 + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Тело запроса", typed)

 + IIF([_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Префикс параметра 1", typed)="", "",  
     +IIF( ASSP.CurrentCellAttributeValue(  [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("OLAP атрибут 1", typed) )="|||", "",
          " " 
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Префикс параметра 1", typed)
        + ASSP.CurrentCellAttributeValue(  [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("OLAP атрибут 1", typed) )
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Суффикс параметра 1", typed)
      ))
 + IIF([_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Префикс параметра 2", typed)="", "",  
     +IIF( ASSP.CurrentCellAttributeValue(  [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("OLAP атрибут 2", typed) )="|||", "",
          " " 
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Префикс параметра 2", typed)
        + ASSP.CurrentCellAttributeValue(  [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("OLAP атрибут 2", typed) )
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Суффикс параметра 2", typed)
      ))
 + IIF([_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Префикс параметра 3", typed)="", "",  
     +IIF( ASSP.CurrentCellAttributeValue(  [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("OLAP атрибут 3", typed) )="|||", "",
          " " 
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Префикс параметра 3", typed)
        + ASSP.CurrentCellAttributeValue(  [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("OLAP атрибут 3", typed) )
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Суффикс параметра 3", typed)
      ))
 + IIF([_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Префикс параметра 4", typed)="", "",  
     +IIF( ASSP.CurrentCellAttributeValue(  [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("OLAP атрибут 4", typed) )="|||", "",
          " " 
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Префикс параметра 4", typed)
        + ASSP.CurrentCellAttributeValue(  [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("OLAP атрибут 4", typed) )
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Суффикс параметра 4", typed)
      ))

 + IIF([_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Окончание запроса", typed)="", "",  
          " " 
        + [_Запросы к источникам данных].[Запрос].CURRENTMEMBER.Properties("Окончание запроса", typed)
      )
 + '", "logging")'
--------------------------------------------------------------------------------------------------------------------------------------------------

Проверить, убедиться, как все работает, можете самостоятельно. Главное, чтобы OLAP-клиент поддерживал Actions. К счастью менеджеров, MS Excel поддерживает дополнительные действия (впрочем как и ProClarity Desktop, как и другие приложения).


Стоит заметить, как будет выглядеть строка подключения в MS Excel (см. пункты меню Данные -- Подключения), после того как исполнится действие - запрос к источнику данных. Строка подключения к внешнему источнику отсутствует, поскольку вшита в DLL, а значит безопасность соблюдается. Проблему SQL injection также можно решить, усилив код CRL функции дополнительными проверками.




Данное решение позволят быстро встроить Ad hoc запросы к внешним источникам в многомерные кубы, при этом пользоваться всей мощью других платформ. Бизнес-пользователи могут работать с кубом, оставаясь в привычной среде MS Excel (возможно, даже нет особой необходимости развертывать Reporting Services) и получать дополнительные выборки, которыми, кстати, могут быть данные из оперативных фронт, OLTP систем.


Множество подобных Ad hoc запросов можно систематизировать посредством иерархий, дополнительных атрибутов вспомогательного измерения [_Запросы к источникам данных], в свойстве "Описание запроса" - хранить краткое бизнес предназначение запроса, описание входных параметров. Одним словом, есть над чем подумать еще :)


Энергия идеи   dvbi.ru                    Последнее изменение: 2017-10-15 16:16:19Z         Возрастная аудитория: 14-70         Комментариев:  0
Теги:  DLL для SSAS Примеры
Связанные статьи:

Пожалуйста, проголосуйте и ниже поставьте лайк:   rating
3 3 0


  Comments


Следующая статья:    SSAS - анализ и работа над ошибками
Предыдущая статья:  Стратегические риски быстро растущих банков