logo

11 дек. 2012 г.

BIEE 11g: список информационных панелей (PL/SQL)

Всем привет!
Сегодня хочу рассказать о том, как получить список информационных панелей Oracle BI из PL/SQL.

1) Обязательно будем учитывать, что для различных учетных записей могут быть доступны различные информационные панели.
Поэтому нам потребуется настроить имперсонацию - т.е. возможность "олицетворения" любых пользователей из-под некоторой системной учетной записи.
Нам это потребуется для того, чтобы при вызове pl/sql-функции, возвращающей список информационных панелей, не требовалось передавать пароль учетной записи, а только логин.
Итак, для начала следует в консоли EM настроить дополнительные разрешения для роли
BI Administrator




Сами разрешения:
oracle.bi.server.impersonateUser
oracle.bi.server.queryUserPopulation


Затем нам необходимо указать, что все пользовательские учетные записи позволяют себя олицетворять.
Так как, скорее всего, все интересующие нас пользователи будут входить в группу
BI Consumer, то



После произведенных манипуляций Oracle BI позволит нам войти в систему под учетной записью test, не указывая ее пароль.
http://localhost:7001/analytics/saw.dll?Dashboard&NQUser=weblogic&NQPassword=weblogic_1&Impersonate=test

2) Ну а дальше я приведу сам код PL/SQL пакета, реализующего логику получения списка информационных панелей, доступных конкретному пользователю.

Но сначала - SQL-типы, необходимые для вызова PL/SQL функции с pipelined-результатом из SQL.
create or replace type bi_dashboard as object
(
  dashboard_name       VARCHAR2(150),
  dashboard_path       VARCHAR2(512)
);

create or replace type bi_dashboard_array as table of bi_dashboard;

CREATE OR REPLACE PACKAGE BI_UTILS IS

  G_RET_STS_SUCCESS     constant varchar2(1) := 'S';
  G_RET_STS_ERROR       constant varchar2(1) := 'E';
  G_RET_STS_UNEXP_ERROR constant varchar2(1) := 'U';

  FUNCTION GET_DASHBOARDS(P_USER_NAME   IN VARCHAR2,
                          P_SHOW_HIDDEN IN VARCHAR DEFAULT 'F')
    RETURN bi_dashboard_array
    PIPELINED;

END BI_UTILS;
/
CREATE OR REPLACE PACKAGE BODY BI_UTILS IS

  G_BI_ADMIN_USER     varchar2(50) := 'weblogic';
  G_BI_ADMIN_PASSWORD varchar2(50) := 'weblogic_1';

  G_BI_BASE_URL   varchar2(512) := 'http://localhost:7001';
  G_BI_LOGIN_URL  varchar2(512) := G_BI_BASE_URL ||
                                   '/analytics/saw.dll?Dashboard&NQUser=#ADMINUSER#&NQPassword=#ADMINPASSWORD#&Impersonate=#IMPERSONATE#';
  G_BI_LOGOFF_URL varchar2(512) := G_BI_BASE_URL ||
                                   '/analytics/saw.dll?Logoff';

  G_BI_SEARCH_URL varchar2(512) := G_BI_BASE_URL ||
                                   '/analytics/saw.dll?CatalogTreeModel';

  G_ItemAttributes_NORMAL       integer := 0;
  G_ItemAttributes_READ_ONLY    integer := 1;
  G_ItemAttributes_ARCHIVE      integer := 2;
  G_ItemAttributes_HIDDEN       integer := 4;
  G_ItemAttributes_SYSTEM       integer := 8;
  G_ItemAttributes_DO_NOT_INDEX integer := 16;


  /*Procedure to print strings larger than 255 chr*/
  procedure output(p_string in varchar2) is
    l_string     long default p_string;
    l_str_length number;
    l_loop_count number := 0;
  begin
    l_str_length := length(l_string);
  
    while l_loop_count < l_str_length loop
      dbms_output.put_line(substr(l_string, l_loop_count + 1, 255));
      l_loop_count := l_loop_count + 255;
    end loop;
  end output;

  /*Procedure to submit HTTP requests*/
  procedure http_post(p_url_in        in varchar2,
                      p_data_in       in varchar2 default null,
                      p_data_type     in varchar2 default 'text/html',
                      p_username_in   in varchar2 default null,
                      p_password_in   in varchar2 default null,
                      x_status_code   out varchar2,
                      x_reason_phrase out varchar2,
                      x_response      out clob) is
    l_data_in   varchar2(32767);
    l_http_req  utl_http.req;
    l_http_resp utl_http.resp;
    l_clob      clob;
    l_text      varchar2(32767);
  begin
  
    DBMS_LOB.createtemporary(l_clob, FALSE);
    utl_http.set_response_error_check(false);
  
    l_http_req := utl_http.begin_request(p_url_in, 'POST', 'HTTP/1.1');
  
    utl_http.set_header(l_http_req,
                        'User-Agent',
                        ' Mozilla/5.0 (Windows NT 6.1; WOW64; rv:16.0) Gecko/20100101 Firefox/16.0');
    utl_http.set_header(l_http_req, 'content-type', p_data_type);
  
    l_data_in := convert(p_data_in, 'utf8');
    utl_http.set_header(l_http_req, 'content-length', length(l_data_in));
  
    if p_username_in is not null then
      utl_http.set_authentication(l_http_req, p_username_in, p_password_in);
    end if;
  
    utl_http.set_body_charset(l_http_req, null);
    utl_http.write_text(l_http_req, l_data_in);
  
    l_http_resp := utl_http.get_response(l_http_req);
    if (l_http_resp.status_code = utl_http.HTTP_OK) then
      x_status_code := G_RET_STS_SUCCESS;
    else
      x_status_code := G_RET_STS_ERROR;
    end if;
    x_reason_phrase := l_http_resp.reason_phrase;
  
    BEGIN
      LOOP
        UTL_HTTP.read_text(l_http_resp, l_text, 32766);
        DBMS_LOB.writeappend(l_clob, LENGTH(l_text), l_text);
      END LOOP;
    EXCEPTION
      WHEN utl_http.end_of_body THEN
        utl_http.end_response(l_http_resp);
    END;
  
    x_response := l_clob;
    DBMS_LOB.freetemporary(l_clob);
  
  EXCEPTION
    when others then
      utl_http.end_response(l_http_resp);
      DBMS_LOB.freetemporary(l_clob);
      x_status_code   := G_RET_STS_UNEXP_ERROR;
      x_reason_phrase := sqlerrm;
  END http_post;

  /*Main function*/
  FUNCTION GET_DASHBOARDS(P_USER_NAME   IN VARCHAR2,
                          P_SHOW_HIDDEN IN VARCHAR DEFAULT 'F')
    RETURN bi_dashboard_array
    PIPELINED IS
  
    l_status_code   varchar2(1);
    l_reason_phrase varchar2(1024);
    e_expected_err exception;
  
    l_url             varchar2(1024);
    l_post_data       varchar2(1024);
    l_receive_data    clob;
    l_db_details_json clob;
  
    l_dummy integer;
    l_scid  varchar2(20);
  
    l_db_details       json;
    l_itemsinfos_array json_list;
    l_tmp_obj          json;
    l_tmp_obj_caption  json_value;
    l_tmp_obj_path     json_value;
    l_tmp_obj_attr     json_value;
  
  BEGIN
    /*clear all cookies from previous runnings*/
    utl_http.clear_cookies;
  
    /*call login HTTP request*/
    l_url := replace(G_BI_LOGIN_URL, '#ADMINUSER#', G_BI_ADMIN_USER);
    l_url := replace(l_url, '#ADMINPASSWORD#', G_BI_ADMIN_PASSWORD);
    l_url := replace(l_url, '#IMPERSONATE#', P_USER_NAME);
  
    http_post(p_url_in        => l_url,
              x_status_code   => l_status_code,
              x_reason_phrase => l_reason_phrase,
              x_response      => l_receive_data);
    if (l_status_code != G_RET_STS_SUCCESS) then
      output('LOGIN: status_code = ' || l_status_code);
      output('LOGIN: reason_phrase = ' || l_reason_phrase);
      raise e_expected_err;
    end if;
  
    l_dummy := instr(l_receive_data, 'var obips_scid="') + 16;
    l_scid  := substr(l_receive_data, l_dummy, 20);
  
    /*call dashboards search HTTP request*/
    l_url       := G_BI_SEARCH_URL;
    l_post_data := 'action=search' || --
                   '&path=%2F' || --
                   '&mask=*' || --
                   '&recurse=t' || --
                   '&followLinks=f' || --
                   '&withperm=1' || --
                   '&withpermmask=1' || --
                   '&sig=' || --
                   '&compositeSig=dashboardfolder1' || --
                   '&icharset=utf-8' || --
                   '&_scid=' || l_scid || -- 
                   '&urlGenerator=qualified';
    http_post(p_url_in        => l_url,
              p_data_in       => l_post_data,
              p_data_type     => 'application/x-www-form-urlencoded',
              x_status_code   => l_status_code,
              x_reason_phrase => l_reason_phrase,
              x_response      => l_receive_data);
    if (l_status_code != G_RET_STS_SUCCESS) then
      output('SEARCH: status_code = ' || l_status_code);
      output('SEARCH: reason_phrase = ' || l_reason_phrase);
      raise e_expected_err;
    end if;
    --output(l_receive_data);
    l_dummy           := instr(l_receive_data, '{"itemsinfos"');
    l_db_details_json := substr(l_receive_data, l_dummy);
  
    /*call logoff HTTP request*/
    l_url := G_BI_LOGOFF_URL;
    http_post(p_url_in        => l_url,
              x_status_code   => l_status_code,
              x_reason_phrase => l_reason_phrase,
              x_response      => l_receive_data);
  
    /*parse received JSON data with dashboards details*/
    l_db_details       := json(l_db_details_json);
    l_itemsinfos_array := json_list(l_db_details.get('itemsinfos'));
  
    for i in 1 .. l_itemsinfos_array.count loop
      l_tmp_obj         := json(l_itemsinfos_array.get(i));
      l_tmp_obj_caption := l_tmp_obj.get('caption');
      l_tmp_obj_path    := l_tmp_obj.get('path');
      l_tmp_obj_attr    := l_tmp_obj.get('attributes');
    
      if (l_tmp_obj_attr.get_number = G_ItemAttributes_HIDDEN and
         upper(P_SHOW_HIDDEN) != 'T') then
        null;
      else
        PIPE ROW(bi_dashboard(l_tmp_obj_caption.get_string,
                              l_tmp_obj_path.get_string));
      end if;
    
    end loop;
  
    return;
  
  EXCEPTION
    when e_expected_err then
      null; --TODO
  END GET_DASHBOARDS;

END BI_UTILS;
/

Как вы, наверняка, заметили - в пакете используется парсинг JSON.
Это пакет PL/JSON, который очень просто скачать и установить в вашей рабочей схеме БД.

И, конечно же, не забудьте привести в соответствие со своей средой глобальные переменные пакета
G_BI_ADMIN_USER - логи административной учетной записи (из-под которой будет выполняться имперсонация).
G_BI_ADMIN_PASSWORD - пароль административной учетной записи.
G_BI_BASE_URL - URL доступа к рабочему серверу BI.

3) Если расширение PL/JSON вы установили, скомпилировали без ошибок пакет BI_UTILS, то теперь простым SQL-запросом вы можете получать список инфопанелей, доступных конкретной учетной записи test.

select dashboard_name, dashboard_path
  from table(bi_utils.get_dashboards('test'))


P.S.
Полагаю, что при необходимости можно усложнить приведенный тут код пакета, чтобы:
- кешировать результаты HTTP-запроса в таблице БД;
- в зависимости от параметра (P_FORCE) обращаться к кешу, при его наличии, либо принудительно обновлять кеш;
- добавить аналогичную функцию для получения списка страниц конкретной информационной панели;
- помимо "caption" и "path" можно использовать и ряд других свойств информационных панелей;
- можно добавить возможность поиска информационных панелей лишь внутри конкретной папки (например, "/shared" )

1 комментарий: