create user for hp_xmla

create user

CREATE USER hp_xmla PROFILE “DEFAULT”
IDENTIFIED BY hp_xmla DEFAULT TABLESPACE “USERS”
TEMPORARY TABLESPACE “TEMP”
ACCOUNT UNLOCK ;

setup rights for resource

grant connect to hp_xmla;
grant resource to hp_xmla;
grant create view to hp_xmla;
grant create session to hp_xmla;
grant alter session to hp_xmla;

setup rights for web connection (replace ` to ‘ )

set serveroutput on ;

begin
dbms_network_acl_admin.create_acl
( acl => ‘HP_XMLA.xml’,description => ‘Normal Access’,principal => ‘HP_XMLA’,is_grant => TRUE,privilege => ‘connect’,start_date => null,end_date => null);
commit;

dbms_network_acl_admin.assign_acl
( acl => ‘HP_XMLA.xml’,host => ‘*’,lower_port => NULL,upper_port => NULL);
commit;

end;

download backup and import this with following command

imp hp_xmla/hp_xmla file=hp_xmla.dmp fromuser=hp_xmla touser=hp_xmla ;

change connect string to the APS server in the packet XMLA_UTIL_PKG

vApsConectSting VARCHAR2(32767):=’http://hypadmin:hyperion@localhost:13080/aps/XMLA’;

execute MDX

select * from ( table( xmla_get_data_PKG.getMdxValueColumn( ‘
SELECT NON EMPTY {Jan:Dec} ON COLUMNS,
NON EMPTY
CrossJoin (CrossJoin ([Product].children, [Market].children ),
CrossJoin( {( [Actual])} , [Measures].children))
ON ROWS FROM Sample.Basic’ )