BI+knowledge+base

**Thi page is intended to capture the knowledge that the BI Group has sparsed arround.**
Import & Export Web Analysis Reports Tutorial for importing and exporting WebAnalysis reports on Hyperion

SQL dictionary. It is used for understand the impact of any proposed change. It gives you information on any object that a DB has (views, fields, tables, functions, etc) Current state of all the jobs on a server. Store that shows the SQL commands that are actually running on the server, for each session. Example for the dynamic creation of a view that does a pivot driven by a definition on a table. QA for DW based on Agile Test Plan used on Microsoft for Continuous Integration. Oracle General Ledger. DFD and explanation of each table Some useful MDX scripts, very handful. WebAnalysis connections (via relational schema) How to Run a MAXL in Workspace - Step by step How to Run a Batch in Workspace V1 - Step by step How to Run a Batch in Workspace V2 (más detallado) - Step by step How to Run a VB in Workspace - Step by step

**Tips and trics**

 * DBA tips**

- On Microsoft Management Studio for SQL, if you execute "SET SHOWPLAN_XML ON" prior to execute an store procedure or any SQL query, when executing the store procedure it will only show you the execution plan without actually running the store procedure. For deactive, type "SET SHOWPLAN_XML OFF"

- On Oracle, you can use this SQL as the equivalent of the sp_who of mssql (combined with ALTER SYSTEM KILL SESSION 'SID, SERIAL' you can kill whatever session you want):

SELECT s.inst_id, s.sid, s.serial#, p.spid, s.username, s.program, s.SQL_ID, sql_fulltext FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id join gv$sqlarea l on s.SQL_ID = l.SQL_ID WHERE s.type != 'BACKGROUND' and s.username like 'SYSTEM' and s.sid not in (select sid from v$mystat where rownum <=1)

- On Oracle,you can use this SQL for knowing what objects are locked: SELECT * FROM V$LOCKED_OBJECT

- On Oracle, you can use this SQL for knowing what objects are locked (ID and Object) SELECT d.OBJECT_ID, substr(OBJECT_NAME,1,20), l.SESSION_ID, l.ORACLE_USERNAME, l.LOCKED_MODE from v$locked_object l, dba_objects d where d.OBJECT_ID=l.OBJECT_ID;

- On Oracle, use this SQL to obtain the PK and FK of your schema SELECT UC.TABLE_NAME, UCC2.CONSTRAINT_NAME,UCC2.COLUMN_NAME, UCC.TABLE_NAME, UC.R_CONSTRAINT_NAME, UCC.COLUMN_NAME FROM (SELECT TABLE_NAME, CONSTRAINT_NAME, R_CONSTRAINT_NAME, CONSTRAINT_TYPE FROM USER_CONSTRAINTS) UC, (SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM USER_CONS_COLUMNS) UCC, (SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM USER_CONS_COLUMNS) UCC2 WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND UC.CONSTRAINT_NAME = UCC2.CONSTRAINT_NAME AND uc.constraint_type = 'R' ORDER BY 1,2,3,4

- On Oracle, use this sentence for searching for a string inside the views definition (similar to sysobjects in MSSQL) set serveroutput on size 10000 declare cursor c_dbv is select owner,view_name,text from all_views ; txt varchar(32000); searchstring varchar(100) := 'CTA-01.03.00.00'; begin for ct in c_dbv loop if instr(lower(ct.text),lower(searchstring)) > 0 then dbms_output.put_line(ct.owner||'.'||ct.view_name); end if; end loop; end;


 * Hyperion Planning 11 Security**

On Planning 11, you can user this SQL to obtain the access and permissions to all the objects on each planning application

select GU.Object_Name as Usuario, HO.Object_Name as Grupo, UI.Object_Name as Objeto, case flags when 0 then 'member' when 5 then 'children' when 6 then 'IChildren' when 8 then 'Descendants' when 9 then 'IDescendants' else 'none' end as flags, case access_mode when 1 then 'Read' When 3 then 'Write' When -1 then 'Deny' when 4 then 'Launch Calc' else 'none' end as Nivel_Acceso, Type_Name from HSP_Object HO inner join hsp_access_control AC on HO.Object_ID = AC.USER_ID inner join HSP_Object UI on AC.Object_ID = UI.Object_ID inner join HSP_USERSINGROUP UG on HO.Object_ID = UG.GROUP_ID inner join HSP_Object GU on GU.Object_ID = UG.USER_ID inner join HSP_OBJECT_TYPE OT on HO.Object_type = OT.Object_Type order by GU.Object_Name

- On Planning 11 you can use this SQL to obtain the permission setting for all the folders in Workspace: Select 'Usuario' as Tipo, c.name as "User Name", path, rpt.name as "File Name", ( CASE d.name WHEN 'role.modify' THEN 'Modify' ELSE d.name END ) "Access" FROM EPM11.v8_access_ctrl a, Epm11.v8_css_user B,  Epm11.css_users C,  Epm11.V8_Identity D,  (Select A.Name, A.Container_Uuid, b.path  FROM EPM11.v8_container a,  (SELECT v8_container.name, parent_folder_uuid, container_uuid, connect_by_isleaf "IsLeaf", Sys_Connect_By_Path(V8_Container.Name, '/') As Path FROM EPM11.v8_container WHERE meta_type_uuid = 'ID03' Connect By Prior Container_Uuid = Parent_Folder_Uuid Start With Container_Uuid = 'REPORTMART') B Where A.Parent_Folder_Uuid = B.Container_Uuid  --AND b.path LIKE '%/Reportes_BI' Mention parent folder here  Order By A.Meta_Type_Uuid Asc,  path ASC) rpt where b.subject_id=a.accessor_id and c.identity_id=b.css_identity And Rpt.Container_Uuid = A.Resource_Id and a.grant_id = d.id

union all

SELECT 'Grupo' as Tipo, c.name AS "Group Name", path, '' as vacio, --rpt.name AS "File Name", ( CASE d.name WHEN 'role.modify' THEN 'Modify'  ELSE d.name  END ) "Access" FROM EPM11.v8_access_ctrl a, Epm11.V8_Css_Group B,  Epm11.Css_Groups C,  Epm11.V8_Identity D,  (Select A.Name, A.Container_Uuid, b.path  FROM EPM11.v8_container a,  (SELECT v8_container.name, parent_folder_uuid, container_uuid, connect_by_isleaf "IsLeaf", Sys_Connect_By_Path(V8_Container.Name, '/') As Path FROM EPM11.v8_container WHERE meta_type_uuid = 'ID03' Connect By Prior Container_Uuid = Parent_Folder_Uuid Start With Container_Uuid = 'REPORTMART') B Where A.Parent_Folder_Uuid = B.Container_Uuid  --AND b.path LIKE '%/Reportes_BI' Mention parent folder here  Order By A.Meta_Type_Uuid Asc,  path ASC) rpt WHERE b.group_id = a.accessor_id AND c.identity_id = b.css_identity And Rpt.Container_Uuid = A.Resource_Id AND a.grant_id = d.id;


 * Hyperion Workspace list of objects:**

On the Oracle schema where EPM metadata is contained, use this query to obtain a list of Webanalysis objects, its folder, owner, and creation and modificacion dates and also conection data:

Select CREATION_DATE , LAST_MODIFIED_DATE , Owner_Login , NAME, DESCRIPTION, Fr_Path, File_System_Url, Substr(Replace (Extract(Xmltype(Csreport),'/ /HYAOLAPDBConnection'),'<HYAOLAPDBConnection OLAPType="1" uri="',), 0, -- Remove space between slashes Instr(Replace (Extract(Xmltype(Csreport),'/ /HYAOLAPDBConnection'),'<HYAOLAPDBConnection OLAPType="1" uri="',),'"')-1) As Conexion, -- Remove space between slashes  Substr(Replace(Extract(Xmltype(Csreport),'/ /HYAAbstractADMDatabase'),'HYAAbstractADMDatabase',), 2, -- Remove space between slashes  INSTR(Replace(Extract(Xmltype(Csreport),'/ /HYAAbstractADMDatabase'),'HYAAbstractADMDatabase',),'<HYADBFormats')-3) as Datos -- Remove space between slashes  from (  Select  A.Creation_Date,  A.LAST_MODIFIED_DATE ,  A.Owner_Login ,  A.NAME,  A.DESCRIPTION,  B.Fr_Path,  A.CONTAINER_UUID  FROM V8_CONTAINER A, (  SELECT  V8_CONTAINER.NAME ,  Parent_Folder_Uuid,  V8_CONTAINER.CONTAINER_UUID,  CONNECT_BY_ISLEAF "IsLeaf",  SYS_CONNECT_BY_PATH(V8_CONTAINER.NAME,'/') AS FR_PATH FROM V8_CONTAINER WHERE META_TYPE_UUID = 'ID03' -- and PARENT_FOLDER_UUID = NULL CONNECT BY PRIOR CONTAINER_UUID=PARENT_FOLDER_UUID START WITH CONTAINER_UUID='REPORTMART') B WHERE  A.Parent_Folder_Uuid = B.Container_Uuid  And A.Meta_Type_Uuid In ('ID300')  Order By A.Last_Modified_Date Desc, Fr_Path Asc) A Inner Join V8_Data_Container X On A.Container_Uuid = X.Container_Uuid Inner Join Atf_Ne_Info C On A.Container_Uuid = C.Atf_Id Inner Join HYA_REPORT B on C.OBJECTID = B.ATF_ID

If you are modifying properties and do not specify a value, the alias is not changed in the Planning application. If you specify  or as the value, the alias in the Planning application is deleted.
 * ODI and Planning: **


 * ODI and Hyperion Essbase- "Delete when the field is empty": **

Delete when the field is empty—If the option is selected, Essbase considers the field as NULL or empty and deletes the associated information from the outline or resets the information to its default value. The option applies to member properties, formulas, UDAs, and attribute associations (assuming that the required dimension option is selected in the Dimension Build Settings dialog box for the relevant dimension).

This is useful when you need to reset or clear out values with an ODI process.

 code &#x2F code