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