Oracle Hyperion Performance Lab

source vvsprog

Есть много всевозможных вариантов выборки данных и метаданных из Oracle Hyperion Planning
Здесь опишу способ с помощью которого можно данные Hyperion Planning Представить в виде view в базе данных Oracle
Для этого буду использовать Hyperion Provider Services который использует XMLA для предоставления данных
Подробней о XMLA можно почитать на xmla.org

Вот пример PL\SQL функции которая превращает MDX в XMLTYPE

FUNCTION get_XMLTYPE_from_MDX(l_sql VARCHAR2) RETURN XMLTYPE AS
l_http_request  UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_url           VARCHAR2(100) := ‘http://v.safronov:12345678@hypo:13080/aps/XMLA’;
l_text          VARCHAR2(32767);
l_clob          CLOB;
l_command       VARCHAR2(32000);
BEGIN
DBMS_LOB.createtemporary(l_clob, FALSE);
l_command := ‘<?xml version=”1.0″ encoding=”windows-1251″?><SOAP-ENV:Envelope
xmlns:SOAP-ENV=”http://schemas.xmlsoap.org/soap/envelope/”
xmlns:xsi = “http://www.w3.org/2001/XMLSchema-instance”
xmlns:xsd=”http://www.w3.org/2001/XMLSchema”>
<SOAP-ENV:Body>
<Execute xmlns=”urn:schemas-microsoft-com:xml-analysis”
SOAP-ENV:encodingStyle=”http://schemas.xmlsoap.org/soap/encoding/”>
<Command>
<Statement>
‘ || l_sql || ‘
</Statement>
</Command>
<Properties>
<PropertyList>
<DataSourceInfo>
Provider=Essbase;Data Source=localhost
</DataSourceInfo>
<Format>Multidimensional</Format>
<AxisFormat>ClusterFormat</AxisFormat>
</PropertyList>
</Properties>
</Execute>
</SOAP-ENV:Body>
</SOAP-ENV:Envelope>’;

l_http_request := UTL_HTTP.begin_request(l_url, ‘POST’, ‘HTTP/1.0′);
UTL_HTTP.set_header(l_http_request,
‘content-type’,
‘text/xml; charset=windows-1251′);
UTL_HTTP.set_header(l_http_request,
‘content-length’,
LENGTH(l_command));
UTL_HTTP.write_text(l_http_request, l_command);
l_http_response := UTL_HTTP.get_response(l_http_request);

BEGIN
LOOP
UTL_HTTP.read_text(l_http_response, l_text, 32767);

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_response);
END;

RETURN XMLTYPE(l_clob);
EXCEPTION
WHEN OTHERS THEN
UTL_HTTP.end_response(l_http_response);
DBMS_LOB.freetemporary(l_clob);
RAISE;
END get_XMLTYPE_from_MDX;

А вот Pipelined функция которая возвращает выборку используя функцию выше

FUNCTION get_mdx_query(p_query_text VARCHAR2) RETURN cube_t
PIPELINED IS
l_xml XMLTYPE := SYNC_PLANNING.get_XMLTYPE_from_MDX(p_query_text);
lc_xmlns         CONSTANT VARCHAR2(200) := ‘xmlns=”urn:schemas-microsoft-com:xml-analysis:mddataset”‘;
lc_ax0_xpath     CONSTANT VARCHAR2(200) := ‘//Axes/Axis[@name="Axis' || 0 ||
'"]/CrossProduct/Members/Member’;
lc_ax1_xpath     CONSTANT VARCHAR2(200) := ‘//Axes/Axis[@name="Axis' || 1 ||
'"]/CrossProduct/Members/Member’;
lc_ax_name_xpath CONSTANT VARCHAR2(200) := ‘//UName’;
BEGIN
FOR cur IN (WITH ax0 AS
(SELECT ROWNUM n,
EXTRACTVALUE(VALUE(p), lc_ax_name_xpath, lc_xmlns) val
FROM table(XMLSEQUENCE(l_xml.EXTRACT(lc_ax0_xpath,
lc_xmlns))) p),
ax1 AS
(SELECT ROWNUM n,
EXTRACTVALUE(VALUE(p), lc_ax_name_xpath, lc_xmlns) val
FROM table(XMLSEQUENCE(l_xml.EXTRACT(lc_ax1_xpath,
lc_xmlns))) p),
vals AS
(SELECT EXTRACTVALUE(VALUE(p), ‘//@CellOrdinal’, lc_xmlns) cell_num,
EXTRACTVALUE(VALUE(p), ‘//Value’, lc_xmlns) AS val,
EXTRACTVALUE(VALUE(p), ‘//FmtValue’, lc_xmlns) AS fmtval
FROM table(XMLSEQUENCE(l_xml.EXTRACT(‘//CellData/Cell’,
lc_xmlns))) p),
ax0ax1 AS
(SELECT ROW_NUMBER() OVER(ORDER BY ax1.n, ax0.n) – 1 AS cell_num,
ax0.n AS ax0_n,
translate(ax0.val, ’1[]‘, ’1′) AS ax0_name,
ax1.n AS ax1_n,
translate(ax1.val, ’1[]‘, ’1′) AS ax1_name
FROM ax0, ax1)
SELECT a.cell_num,
a.ax0_n,
a.ax0_name,
a.ax1_n,
a.ax1_name,
translate(v.val, ‘,’, ‘.’) + 0 as val,
v.fmtval
FROM ax0ax1 a
LEFT JOIN vals v
ON a.cell_num = v.cell_num) LOOP
PIPE ROW(cur);
END LOOP;

RETURN;
END get_mdx_query;

где

А вот и пример вызова

select tbl.ax1_name lvc_code,
tbl.val      value_code,
p_lvc_years  lvc_years
from table(SYNC_PLANNING.get_mdx_query(”SELECT
{[Период].[Весь год].Children} ON COLUMNS,
{[Счет].[Общие свойства затрат].Children} ON ROWS
FROM Бюджет.WF
WHERE  (
[Валюта].[Local] ,
[Версия].[Версия1],
[Вид помощи].[Нет вида помощи],
[Должность].[Нет должности],
[Номенклатура].[Нет номенклатуры],
[Организация].[Нет организации],
[Строка].[Нет строки],
[Сценарий].[План],
[Функциональная классификация].[Нет ФК],
[Год].2010,
[HSP_Rates].[HSP_InputValue]
)”))

В общем делаете любой MDX к своему кубику. И вставляете свой запрос в обертку
select * from table(SYNC_PLANNING.get_mdx_query(“Ваш MDX query”))

Да кстати
1) Для очень больших обьемов данных наверно лучше XMLTYPE не использовать.
2) При больших обьемах данных чтобы не делать в цикле большое количество вызовов можно формировать большее количество AXISов в запросе
3) С помощью XMLA можно вытаскивать как данные так и метаданные

Надеюсь кому нибудь поможет:)