EPM Hyperion Workspace Repository Items SQL

By Robert Reid May 21, 2014 Essbase 11.1.2, Essbase 11.1.2.2, Essbase 9.3, HFM, HFM 11.1.2.2, Hyperion Planning, Hyperion Planning 11.1.2.2, Oracle Database, Oracle Financial Reporting, Oracle Hyperion 9.3.1, Oracle Hyperion EPM 11.1.1.3, Oracle Hyperion EPM 11.1.2.1, Oracle Hyperion EPM 11.1.2.2 3 Comments

Advertisements

[ad#ad-post]

I had the need to look up all Financial Reporting documents in workspace. I compiled the following list of SQL commands to run against the Oracle database repository. The last command is the winner.

http://hyperion-consulting.blogspot.com/2011/09/in-which-tables-reporting-objects-are.html

http://hyperion-consulting.blogspot.com/2011/09/in-which-tables-reporting-objects-are.html (posted by Thigulla Krishna)
Financial Reporting Report:

select * from v8_container where meta_type_uuid = 'ID200'

Financial Reporting Snapshot Report:

select * from v8_container where meta_type_uuid = 'ID201'

Financial Reporting Batch:

select * from v8_container where meta_type_uuid = 'ID204'

Financial Reporting Book:

select * from v8_container where meta_type_uuid = 'ID202'

http://www.network54.com/Forum/58296/thread/1354673858/pull+the+list+of+workspace+reports-folders+into+flat+file-excel (posted by Celvin Kattookaran)

select a.name, b.folder_full_path from v8_container a, v8_folder b where a.parent_folder_uuid = b.container_uuid;

http://www.network54.com/Forum/58296/thread/1354673858/pull+the+list+of+workspace+reports-folders+into+flat+file-excel (posted by VK)
Re: pull the list of workspace reports/folders into flat file/excel
No score for this post December 6 2012, 10:49 AM
This sql extracts all FR reports and Books under Workspace root directory with full path listing. If you need get additional objects just change META_TYPE_UUID

SELECT
A.CONTAINER_UUID,
A.CREATION_DATE ,
A.LAST_MODIFIED_DATE ,
A.OWNER_LOGIN ,
A.META_TYPE_UUID,
A.NAME,
A.DESCRIPTION,
B.FR_PATH
FROM EPMBIPLUS.V8_CONTAINER A, (
SELECT
EPMBIPLUS.V8_CONTAINER.NAME ,
PARENT_FOLDER_UUID,
CONTAINER_UUID,
CONNECT_BY_ISLEAF "IsLeaf",
SYS_CONNECT_BY_PATH(EPMBIPLUS.V8_CONTAINER.NAME,'/') AS FR_PATH
FROM
EPMBIPLUS.V8_CONTAINER
WHERE
\--CONNECT_BY_ISLEAF = 0 AND
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 ('ID202','ID200')
ORDER BY A.META_TYPE_UUID ASC, FR_PATH ASC

[ad#ad-post]

About Author

Robert Reid

3 Comments

Safa February 21, 2017

I’m getting an error for V8_container, says invalid object name. please advise.
Reply
Safa February 21, 2017

I’m getting an error for V8_container, says invalid object name. please advise.
Reply
mario October 9, 2019

The right SQL is this one:
— ———————
— Root Work Repository
— ———————
SELECT A.CONTAINER_UUID,
A.CREATION_DATE,
A.LAST_MODIFIED_DATE,
A.OWNER_LOGIN,
A.META_TYPE_UUID,
A.NAME,
A.DESCRIPTION,
B.FR_PATH
FROM hypra.V8_CONTAINER A,
( SELECT NAME,
PARENT_FOLDER_UUID,
CONTAINER_UUID,
CONNECT_BY_ISLEAF “IsLeaf”,
SYS_CONNECT_BY_PATH ( NAME, ‘/’ ) AS FR_PATH
FROM hypra.V8_CONTAINER
WHERE 1=1
AND CONNECT_BY_ISLEAF = 0
AND 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 A.META_TYPE_UUID IN (‘ID202’, ‘ID200’)
ORDER BY A.META_TYPE_UUID ASC, FR_PATH ASC
/
Reply

Related Posts

About Author

Robert Reid

3 Comments

Add a Comment