Очевидно, что в ходе создания этих правил могут быть допущены ошибки. Либо в процессе доработки репозитория могут быть допущены неточности, приводящие к некорректной работе трансформаций данных. И как результат - несмотря на корректные данные в физических источниках, пользователи будут получать ошибочные данные в отчетах BI.
Данная статья описывает настройки автоматизированного тестирования репозитория метаданных BI с помощью DBFit.
Основная идея решения по тестированию заключается в том, что по каждой предметной области репозитория BI создается пара SQL запросов:
один к BIServer (тот логический SQL запрос, который отправляет BIPresServer к BIServer; его можно увидеть на вкладке "Дополнительно" в режиме редактирования анализа BI),
другой к DWH (запрос-эталон).
Результаты выполнения запросов сравниваются и возвращается дельта. Если дельта не пустая, то тест считается проваленным.
В этой конструкции основным элементом является Java Stored Procedure, созданная в БД Oracle (DWH) и осуществляющая доступ к BIServer по протоколу JDBC. Полученные из BIServer данные процедура записывает в global temporary table.
В дальнейшем вспомогательная PL/SQL процедура генерирует курсор, сравнивающий данные из gtt-таблицы и результаты запроса-эталона к DWH.
DBFit же является удобным инструментом по быстрому созданию и запуску таких тестов.
Доступ к BIServer из БД Oracle
Предполагается, что DWH построено на базе Oracle Database.
Для осуществления доступа из БД Oracle к БД BIServer (nqsserver) необходимо предварительно загрузить во внутреннюю JVM Oracle набор необходимых классов.
Загрузка jdbc классов BIServer в БД
Необходимы классы, содержащиеся в следующих jar-файлах: dms.jar и bijdbc.jar.
Файлы должны соответствовать той версии OBIEE, с которой планируется работать. Потому и брать файлы нужно из развернутой инсталляции OBIEE:
$MIDDLEWARE_HOME/oracle_common/modules/oracle.dms_11.1.1/dms.jar
$MIDDLEWARE_HOME/Oracle_BI1/bifoundation/jdbc/bijdbc.jar
Для загрузки классов из указанных jar-файлов следует:
- Скопировать jar-файлы по sftp на сервер БД
- Перейти в каталог с jar-файлами
- Выполнить команды в том же порядке, что указано (следует указать корректный пароль sys и при необходимости изменить tns-имя БД):
loadjava -u sys/
loadjava -u sys/
Возможные ошибки можно увидеть в системном представлении all_errors
select *
from all_errors t
where owner = 'SYS' and type = 'JAVA CLASS'
Java Stored Procedure для доступа к BIServer
В БД, содержащей загруженные и скомпилированные классы, следует создать java source с рабочим классом.
Создавать java source как и все последующие объекты БД я буду в рабочей схеме DWH.
create or replace and compile java source named exec_nqsserver_query as
import java.sql.*;
import java.text.*;
import oracle.bi.jdbc.AnaJdbcDriver;
public class execNQSServerQuery {
private static String convertDataToString(Object dataObj, int dataType) {
if (dataObj == null)
return null;
if (dataType==java.sql.Types.BIGINT) {
return Integer.toString((Integer) dataObj);
}
else if (dataType==java.sql.Types.BOOLEAN) {
return Boolean.toString((Boolean) dataObj);
}
else if (dataType==java.sql.Types.DOUBLE) {
DecimalFormat df = new DecimalFormat("#.######");
DecimalFormatSymbols dfs = df.getDecimalFormatSymbols();
dfs.setDecimalSeparator('.');
df.setDecimalFormatSymbols(dfs);
return df.format((Double) dataObj);
}
else if (dataType==java.sql.Types.FLOAT) {
DecimalFormat df = new DecimalFormat("#.######");
DecimalFormatSymbols dfs = df.getDecimalFormatSymbols();
dfs.setDecimalSeparator('.');
df.setDecimalFormatSymbols(dfs);
return df.format((Float) dataObj);
}
else if (dataType==java.sql.Types.INTEGER) {
return Integer.toString((Integer) dataObj);
}
else if (dataType==java.sql.Types.NVARCHAR) {
return (String) dataObj;
}
else if (dataType==java.sql.Types.VARCHAR) {
return (String) dataObj;
}
else if (dataType==java.sql.Types.TINYINT) {
return Integer.toString((Integer) dataObj);
}
else if (dataType==java.sql.Types.SMALLINT) {
return Integer.toString((Integer) dataObj);
}
else if (dataType==java.sql.Types.DATE) {
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return df.format((Date) dataObj);
}
else if (dataType==java.sql.Types.TIMESTAMP) {
DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
return df.format((Timestamp) dataObj);
}
else {
return (String) dataObj;
}
}
public static String doQuery(String nqSql, String nqHost, String nqPort, String nqUser, String nqPwd, String sessId) {
StringBuilder xml = new StringBuilder("<?xml version=\"1.0\" encoding=\"UTF-8\" ?><RESULT>");
Connection nqsConn = null;
Statement nqsStmt = null;
ResultSet nqsRs = null;
Connection localConn = null;
PreparedStatement localStmt = null;
try {
Class.forName("oracle.bi.jdbc.AnaJdbcDriver");
nqsConn = DriverManager.getConnection("jdbc:oraclebi://" + nqHost + ":" + nqPort + "/", nqUser, nqPwd);
nqsStmt = nqsConn.createStatement();
nqsRs = nqsStmt.executeQuery(nqSql);
ResultSetMetaData nqsRsmd = nqsRs.getMetaData();
int colCount = nqsRsmd.getColumnCount();
xml.append("<COL_COUNT>").append(colCount).append("</COL_COUNT><DTYPES>");
StringBuilder localColumnList = new StringBuilder();
StringBuilder localValueList = new StringBuilder();
StringBuilder localSql = new StringBuilder();
for (int i = 1; i <= colCount; i++) {
xml.append("<DT>").append(nqsRsmd.getColumnType(i)).append("</DT>");
localColumnList.append("C").append(i).append(",");
localValueList.append("?,");
}
localColumnList.append("SESS_ID");
localValueList.append("?");
xml.append("</DTYPES>");
localSql.append("insert into NQS_TEMP_TABLE(").
append(localColumnList).
append(") values (").
append(localValueList).
append(")");
localConn = DriverManager.getConnection("jdbc:default:connection:");
localConn.setAutoCommit(false);
localStmt = localConn.prepareStatement(localSql.toString());
while (nqsRs.next()) {
for (int i = 1; i <= colCount; i++) {
localStmt.setString(i, convertDataToString(nqsRs.getObject(i), nqsRsmd.getColumnType(i)));
}
localStmt.setString(colCount+1, sessId);
localStmt.addBatch();
}
localStmt.executeBatch();
/*localConn.commit();*/
return xml.append("<RESULT_CODE>SUCCESS</RESULT_CODE><ERROR_MESSAGE></ERROR_MESSAGE></RESULT>").toString();
}
catch (Exception e) {
e.printStackTrace();
return "<?xml version=\"1.0\" encoding=\"UTF-8\" ?><RESULT><RESULT_CODE>ERROR</RESULT_CODE><ERROR_MESSAGE>" + e.toString() + "</ERROR_MESSAGE></RESULT>";
}
finally {
if(nqsRs != null){
try {
nqsRs.close();
} catch (Exception e) {}
}
if(nqsStmt != null){
try {
nqsStmt.close();
} catch (Exception e) {}
}
if(nqsConn != null){
try {
nqsConn.close();
} catch (Exception e) {}
}
if(localStmt != null){
try {
localStmt.close();
} catch (Exception e) {}
}
if(localConn != null){
try {
localConn.close();
} catch (Exception e) {}
}
}
}
}
В качестве "шлюза" для данных, полученных по JDBC, используется GTT-таблица NQS_TEMP_TABLE
create global temporary table NQS_TEMP_TABLE ( sess_id number, c1 varchar2(255), c2 varchar2(255), c3 varchar2(255), c4 varchar2(255), c5 varchar2(255), c6 varchar2(255), c7 varchar2(255), c8 varchar2(255), c9 varchar2(255), c10 varchar2(255), c11 varchar2(255), c12 varchar2(255), c13 varchar2(255), c14 varchar2(255), c15 varchar2(255), c16 varchar2(255), c17 varchar2(255), c18 varchar2(255), c19 varchar2(255), c20 varchar2(255) ) on commit delete rows
Код PL/SQL-обертки для java-процедуры:
create or replace package PKG_NQSSERVER_QUERY as
...
function EXEC_NQSSERVER_QUERY(p_nqSql in varchar2,
p_nqHost in varchar2,
p_nqPort in varchar2,
p_nqUser in varchar2,
p_nqPwd in varchar2,
p_sessionId in varchar2) return varchar2;
...
end PKG_NQSSERVER_QUERY;
/
create or replace package body PKG_NQSSERVER_QUERY as
/*Параметры обращения к BIServer*/
G_nqHost varchar2(150) := '192.168.10.1';
G_nqPort varchar2(150) := '9703';
G_nqUser varchar2(150) := 'weblogic';
G_nqPwd varchar2(150) := 'Admin123';
/*Java Stored Procedure, запускающая логический SQL запрос на стороне BIServer*/
function EXEC_NQSSERVER_QUERY(p_nqSql in varchar2,
p_nqHost in varchar2,
p_nqPort in varchar2,
p_nqUser in varchar2,
p_nqPwd in varchar2,
p_sessionId in varchar2) return varchar2 is
language java name 'execNQSServerQuery.doQuery(java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String,java.lang.String) returns java.lang.String';
...
end PKG_NQSSERVER_QUERY;
/
Как видно из кода - общие параметры вызова (хост, порт, логин и пароль для доступа к рабочей среде BIServer) указаны непосредственно в теле пакета.
Необходимые права для работы JSP
Java Stored Procedure обращается по сети к BIServer, а значит на нее дейстуют ограничения по доступу в Oracle.
Необходимо явно указать на какой хост, какой порт и какой схеме БД можно осуществлять сетевые обращения.
Для этого под поономочиями sys запускаем процедуру:
begin
dbms_java.grant_permission('DWH',
'SYS:java.net.SocketPermission',
'192.168.10.1:9703',
'connect,resolve');
end;
/
Процедура сравнения результатов запросов к DWH и к BIServer
Теперь, когда имеется процедура, обращающаяся по JDBC к удаленному BIServer,
возвращающая данные во временную таблицу NQS_TEMP_TABLE, необходимо создать процедуру,
сравнивающую результирующие наборы данных запросов к BI и к DWH.
PROCEDURE GET_DWH_BI_DIFF(p_dwh_query in varchar2,
p_bi_query in varchar2,
x_cursor out SYS_REFCURSOR) as
l_cursor int;
l_column_cnt int;
l_columns dbms_sql.desc_tab;
l_nqs_result varchar2(1024);
l_metadata_xml xmltype;
l_columns_list varchar2(1024);
l_columns_order varchar2(255);
l_column_data_type integer;
l_stmnt varchar2(32000);
BEGIN
/*dbms_java.set_output(10000);*/
l_cursor := dbms_sql.open_cursor;
dbms_sql.parse(l_cursor, p_dwh_query, dbms_sql.native);
dbms_sql.describe_columns(l_cursor, l_column_cnt, l_columns);
dbms_sql.close_cursor(l_cursor);
l_nqs_result := pkg_nqsserver_query.exec_nqsserver_query(p_nqSql => p_bi_query,
p_nqHost => G_nqHost,
p_nqPort => G_nqPort,
p_nqUser => G_nqUser,
p_nqPwd => G_nqPwd,
p_sessionId => 1);
dbms_output.put_line(l_nqs_result);
l_metadata_xml := xmltype(l_nqs_result);
for i in 1 .. l_column_cnt loop
l_column_data_type := l_metadata_xml.extract('//DT[' || i ||']/text()')
.getStringVal();
if (l_column_data_type in
(G_DATATYPE_DATE, G_DATATYPE_TIME, G_DATATYPE_TIMESTAMP)) then
l_columns_list := l_columns_list || ',to_date(C' || i ||
',''YYYY-MM-DD HH24:MI:SS'')';
elsif (l_column_data_type in
(G_DATATYPE_TINYINT,
G_DATATYPE_SMALLINT,
G_DATATYPE_INTEGER,
G_DATATYPE_BIGINT,
G_DATATYPE_FLOAT,
G_DATATYPE_REAL,
G_DATATYPE_DOUBLE,
G_DATATYPE_NUMERIC,
G_DATATYPE_DECIMAL)) then
l_columns_list := l_columns_list || ',to_number(C' || i || ')';
else
l_columns_list := l_columns_list || ',C' || i;
end if;
end loop;
l_columns_list := ltrim(l_columns_list, ',');
for i in 1 .. l_column_cnt - 1 loop
l_columns_order := l_columns_order || ',' || (i + 1);
end loop;
if (l_columns_order is not null) then
l_columns_order := 'ORDER BY ' || ltrim(l_columns_order, ',') ||
', 1';
end if;
execute immediate 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS=''.,''';
l_stmnt := 'SELECT ' || l_columns_list || ' from NQS_TEMP_TABLE t';
l_stmnt := 'SELECT * FROM (SELECT ''DWH'' as DELTA, q1.* FROM ((' ||
p_dwh_query || ') MINUS (' || l_stmnt || ')) q1 UNION ALL ' ||
'SELECT ''BI'' as DELTA, q2.* FROM ((' || l_stmnt ||
') MINUS (' || p_dwh_query || ')) q2) ' || l_columns_order;
OPEN x_cursor FOR l_stmnt;
END GET_DWH_BI_DIFF;
Полный код пакета PKG_NQSSERVER_QUERY доступен здесь.
Пример вызова
Протестировать работу процедуры можно следующим PL/SQL блоком:
begin
pkg_nqsserver_query.get_dwh_bi_diff(p_dwh_query => 'select count(f.id)+1 as query_cnt
from PRD_BIPLATFORM.S_NQ_ACCT f
where extract(year from f.start_dt) = 2014',
p_bi_query => 'SELECT "U - Usage Tracking"."Показатели"."Кол-во запросов" s_1
FROM "U - Usage Tracking"
WHERE (("Календарь"."Год" = 2014) AND ("Календарь"."Месяц года" = "Календарь"."Месяц года"))',
x_cursor => :x_cursor);
end;
Как видно из примера, процедура принимает на вход 2 параметра:
- Текст запроса к DWH, этот запрос должен выполняться средствами самой БД Oracle (но ничего не мешает использовать dblink'и к другим БД).
- Текст логического запроса к BIServer (то, что можно увидеть в мониторе сеансов BI, либо на вкладке "Дополнительно" тестируемого анализа BI).
Возвращает процедура результат в виде курсора, содержащий взаимные дельты двух набором данных.
В примере специально искажены данные в первом запросе для иллюстрации получения дельты.
Также в примере добавлен предикат "Календарь"."Месяц года" = "Календарь"."Месяц года" в логический запрос к BIServer как иллюстрация
предлагаемого варианта проверки: сравнивать итоговое значение в запросе-эталоне к DWH с агрегатными значениями в различных разрезах BI.
Ограничения
- Количество столбцов в курсоре с дельтой определяется кол-вом столбцов в запросе к DWH.
- Типы данных обоих запросов должны совпадать.
- Точность NUMBER полей из BI ограничена 5 знаками после запятой. Поэтому нужно при сравнении использовать round(.., 5) в запросе к DWH.
- Общее кол-во столбцов курсора не должно превышать 20 (либо следует пересоздать таблицу NQS_TEMP_TABLE с бОльшим кол-вом столбцов).
- VARCHAR значения из BI не должны быть длиннее 255 символов (либо следует пересоздать таблицу NQS_TEMP_TABLE).
Трассировка выполнения процедуры
Для выявления причин возможных проблем следует
- раскомментировать строку /*dbms_java.set_output(10000);*/ в начале процедуры GET_DWH_BI_DIFF.
- перекомпилировать java source exec_nqsserver_query, вставляя в местах отладки вызовы System.out.println(....);
- анализировать трассировочные сообщения в стеке dbms_output.
Установка и базовая настройка DBFit
Для создания юнит-тестов через веб-интерфейс, а также для удобства их комплексного запуска мы будем использовать opensource ПО DbFit.
Скачиваем дистрибутив с ресурса https://github.com/dbfit/dbfit/releases
Операционная система и битность не важны. Так как ПО реализовано на Java.
Но следует учитывать, что версии дистрибутива могут быть скомпилированы под версией JRE, отличной от установленной на сервере. Так для инсталляции DBFit на том же сервере, где установлен OBIEE 11.1.1.7, следует скачивать не последнюю версию DBFit, а версию v2.2.0 - так как она скомпилирована в JRE 1.6, использующемся и в OBIEE 11.1.1.7
После загрузки дистрибутива необходимо распаковать его в какую-либо директорию сервера.
Я запускаю DbFit как сервис на Linux-системе.
Код сервиса:
#!/bin/bash
#
# File: /etc/init.d/dbfit
# Purpose: Start and stop DBFit.
#
# chkconfig: 2345 99 10
# description: Manage DBFit service.
#
# These values must be adapted to your environment.
# Local Unix user running DBFit
DBFIT_OWNR=oracle
# Path to DBFit root directory
DBFIT_PATH=/u01/app/dbfit
# Path to java 1.6
# JAVA_EXEC=/home/u00/app/oracle/product/ofmodi/Oracle_BI1/jdk/jre/bin/java
JAVA_EXEC=java
# Path to DBFit exec script
# DBFIT_EXEC="$JAVA_EXEC -cp '$DBFIT_PATH/lib/dbfit-docs-3.1.0.jar:$DBFIT_PATH/lib/fitnesse-standalone-20140903.jar' fitnesseMain.FitNesseMain"
DBFIT_EXEC="$JAVA_EXEC -cp '$DBFIT_PATH/lib/dbfit-docs.jar:$DBFIT_PATH/lib/*' fitnesseMain.FitNesseMain -p 8085 -e 0"
# Log file
DBFIT_LOG=/var/log/dbfit.log
# Pid file
DBFIT_PID=/var/run/dbfit.pid
case $1 in
start)
echo "Starting DBFit ..."
if [ ! -f $DBFIT_PID ]; then
cd $DBFIT_PATH
su $DBFIT_OWNR -c "$DBFIT_EXEC" > $DBFIT_LOG 2>&1 &
echo $! > $DBFIT_PID
echo "DBFit started ..."
else
echo "DBFit is already running ..."
fi
;;
stop)
if [ -f $DBFIT_PID ]; then
PID=$(cat $DBFIT_PID);
echo "DBFit stoping ..."
kill $PID;
echo "DBFit stopped ..."
rm $DBFIT_PID
else
echo "DBFit is not running ..."
fi
;;
restart)
if [ -f $DBFIT_PID ]; then
PID=$(cat $DBFIT_PID);
echo "DBFit stopping ...";
kill $PID;
echo "DBFit stopped ...";
rm $DBFIT_PID
echo "DBFit starting ..."
cd $DBFIT_PATH
su $DBFIT_OWNR -c "$DBFIT_EXEC" > $DBFIT_LOG 2>&1 &
echo $! > $DBFIT_PID
echo "DBFit started ..."
else
echo "DBFit is not running ..."
fi
;;
*)
echo "Usage: $(basename $0) start|stop|restart"
exit 1
esac
Дополнительные настройки DbFit
Так как в качестве основной рабочей среды для тестов будет использоваться БД Oracle - следует скопировать в каталог dbfit/lib jar-файл с JDBC-драйверами к Oracle.
Пример теста DbFit
Пользовательский доступ к DBFit осуществляется через веб-интерфейс по адресу http://hostname:8085
Тесты могут объединяться в TestSuite - наборы тестов, выполняющихся в рамках одного контекста.
Создадим новый TestSuite с именем NqsTest для хранения тестов репозитория BI.
При этом внутри каждого TestSuite (вложенного в другие, либо базового) можно создать статичную страницу с именем SetUp. На которой задаются настройки доступа, контекст, параметры и т.д.
В нашем случае эта страница будет общей для всего TestSuite первого уровня. И будет содержать настройки JDBC-доступа к рабочей БД Oracle.
Теперь откроем сам тест:
Он содержит вызов пакетной процедуры, в которую передаются значения параметров - тексты запросов к DWH и к BIServer.
А также вывод результатов выходного курсора.
Запускается тест нажатием на кнопку Test вверху.
И если выходной курсор содержит данные (есть дельта между наборами данных) - тест считается проваленным.
Код страницы DbFit с тестом:
!3 Тест, проверяющий соответствие результатов SQL запросов к DWH и к BIServer
Установка параметров пакетной процедуры (тексты запросов к DWH и BIServer)
|Set Parameter|P_DWH_QUERY|!-
select count(f.id)+1 as query_cnt
from PRD_BIPLATFORM.S_NQ_ACCT f
where extract(year from f.start_dt) = 2014
-!|
|Set Parameter|P_BI_QUERY|!-
SELECT "U - Usage Tracking"."Показатели"."Кол-во запросов" s_1
FROM "U - Usage Tracking"
WHERE (("Календарь"."Год" = 2014) AND ("Календарь"."Месяц года" = "Календарь"."Месяц года"))
-!|
Сравнение результирующих наборов данных
!|Execute procedure|pkg_nqsserver_query.get_dwh_bi_diff|
|P_DWH_QUERY|P_BI_QUERY|X_CURSOR?|
|<<P_DWH_QUERY|<<P_BI_QUERY|>>cursor|
!|Query|<<cursor|
|DELTA?|query_cnt?|
Комментариев нет:
Отправить комментарий