... Не всегда и не все отчеты можно сделать на базе 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 запросов можно систематизировать посредством иерархий, дополнительных атрибутов вспомогательного измерения [_Запросы к источникам данных], в свойстве "Описание запроса" - хранить краткое бизнес предназначение запроса, описание входных параметров. Одним словом, есть над чем подумать еще :)