Create a Dataset from Oracle Enterprise Performance Management (Oracle EPM)

Connect to an Oracle Enterprise Performance Management (Oracle EPM) application and visualize the data in a workbook. Data is accessed as a live query.

Before you start, see About Accessing an Oracle EPM Data Source With Data Security Enabled.
  1. In the Oracle Analytics Cloud homepage, click Create, then Connection, and select Oracle EPM Cloud.
  2. In the Create Connection dialog, enter connection details for your Cloud EPM Platform application.
    • In Authentication, select Use the active user's credentials.
  3. From the home page, click Create, then Dataset.
  4. At the Create Dataset dialog, select the Oracle EPM connection that you created in Step 2.
  5. In the Add Dataset dialog, select the cube to visualize, then click Add.
    Description of epm-2.png follows
    Description of the illustration epm-2.png

  6. Click Create Workbook, then add the data elements to the design canvas.
    Tip: Use filters to precisely locate the data in your cubes.
    Description of emp-3.png follows
    Description of the illustration emp-3.png

About Accessing an Oracle EPM Data Source With Data Security Enabled

When you create workbooks based on an Oracle EPM data source with data filters applied, you need to ensure than non-admin users can access the data by using an authorized leaf member instead of the root member.

.

In Oracle EPM, non-admin users often do not have access to the root member of a secured dimension. If the workbook doesn't explicitly include that dimension, Oracle Analytics might generate a query that uses the root member by default. This can cause the report to return no data for non-admin users.

Example

Suppose you build a workbook using this logical SQL:

Select 
"pbcs"."Year"."Year Name" as s_1, SORTKEY("pbcs"."Year"."Year Name") s_2, "pbcs"."Year"."Year Default" s_3, "pbcs"."Entity"."Entity Name" as s_4, SORTKEY("pbcs"."Entity"."Entity Name") s_5, "pbcs"."Product"."Product Name" as s_6, SORTKEY("pbcs"."Product"."Product Name") s_7, "pbcs"."Account"."Account Name" s_8, SORTKEY("pbcs"."Account"."Account Name") s_9, "pbcs"."Period"."Period Name" as s_10, SORTKEY("pbcs"."Period"."Period Name") s_11, "pbcs"."Scenario"."Scenario Name" as s_14, SORTKEY("pbcs"."Scenario"."Scenario Name") s_15, "pbcs"."Version"."Version Name" s_16, SORTKEY("pbcs"."Version"."Version Name") s_17, "pbcs"."Plan1"."Value" as s_20 
From "pbcs" 
Where 
"pbcs"."Scenario"."Scenario Name" = 'Plan' and "pbcs"."Version"."Version Name" = 'Working' and "pbcs"."Year"."Year Name" = 'FY17' and "pbcs"."Period"."Period Name" in ('Rolling') and "pbcs"."Account"."Account Name" in ('Statistics') and "pbcs"."Entity"."Entity Name" in ('Entity', '000', 'Administrative VP') and "pbcs"."Product"."Product Name" in ('P_100','P_291','Product')
;

This query does not select or filter on the HSP_View dimension. Because of that, the physical ALE query might use the root member of HSP_View by default:

SELECT value from "Plan1"
 
Where
FilterBySecurity(),SuppressMissing()

PROJECT ON EDGE COLUMN WITH
(SELECT preName, "Name", "Sort Order" FROM "Account" where Member("Statistics","Account")),
(SELECT preName, "Name", "Sort Order" FROM "Period" where Member("Rolling","Period"))

PROJECT ON EDGE ROW
WITH
(SELECT preName, "Name", "Sort Order" FROM "Entity" where ((Member("000","Entity")) or (Member("Administrative VP","Entity"))) or (Member("Entity","Entity"))),
(SELECT preName, "Name", "Sort Order" FROM "Product" where ((Member("P_100","Product")) or (Member("P_291","Product"))) or (Member("Product","Product"))),
(SELECT preName, "Name", "Sort Order" FROM "Scenario" where Member("Plan","Scenario")),
(SELECT preName, "Name", "Sort Order" FROM "Version" where Member("Working","Version")),
(SELECT preName, "Default", "Name", "Sort Order" FROM "Year" where Member("FY17","Year"))
 ON SEGMENT 1

PROJECT ON EDGE SLICER WITH
(SELECT preName, Name FROM "HSP_View" where Member("HSP_View","HSP_View"))

How to Address This Issue

At the workbook design stage, add the HSP_View dimension to the workbook filter or prompt, and select the leaf member that the user is allowed to access.

For example, instead of letting Oracle Analytics use:

Member("HSP_View","HSP_View")

the workbook should explicitly use a permitted leaf member, such as:

Member("BaseData","HSP_View")

With the leaf member added in the workbook, the query respects Oracle EPM data security, and the report returns data correctly for non-admin users.