Planning Attached ASO Cube Security with HFR and SmartView

                                

Audience:  Hyperion Solution Architects and Hyperion Database Administrator

Planning 11.1.2.3 provides an option to create ASO cube as part of planning application. I should be using word plan type rather than cube because it’s part of planning application. Creating ASO as plan type creates new ASO application on Essbase server. Planning reads ASO a plan type but Essbase does not. 

I have created a Planning application with five plan types including “Report-ASO” and BRTP is a separate application. Planning security filters are pushed to Essbase (BSO) application but Planning is not capable push security filters to ASO application. I tried to create security filters manually to Report-ASO cube but whenever I refresh the planning application; manually created security filters are wiped out from ASO application. I was really surprised to see planning refresh process was pushing metadata changes but not security filters. User can run reports regardless of security setup and creates ad-hoc grids in smartview. This is a major security risk. Users can see each other data.

  

Objective

The purpose of this article is to demonstrate the following:-

·         How to use Planning security with ASO cube when running report using HFR

·         How to hide Essbase (ASO) application from Smart-View to use Planning security

how To

How to use Planning security with ASO cube when running report using HFR:- I really don’t consider this as solution but can be used as a workaround.  We would creat a new HFR “Database connection as Planning” to leverage planning secutity. Planing is able to recognize ASO as part of plan type and will allow you to cteate a connection. By using Planing connection ASO can leverage the same security.

  

·         How to hide Essbase (ASO) application from Smart-View: - User can see available options such as Essbase, Planning and Reports. Planning security will not be applicable if a user select Essbase connection and access Report-ASO cube. User can create ad-hoc and run the report without any security restriction.  

The only workaround is to use planning connection and hide Essbase option from Smart-View

and disable provider services for and use planning connection.

Helpful KM 

Essbase Server is Not Listed in the Drop-Down List of the Shared Connections in SmartView 11.1.2.2 (Doc ID 1568573.1)

Security Filters For ASO Planning Application (Doc ID 1625712.1

Because of inactivity, your session has timed out and is no longer Active Click Ok to reload the page- Hyperion Planning

                                                                       Author:-Sanjay Purohit

Audience:  Hyperion Developer and Hyperion Planning Database Administrator

 I am the person who works on multiple tasks at same time and cannot resist myself to swing here and there.  On the recent Hyperion implementation I came across an annoying issues and it was preventing me to work on multiple data forms and data load screen.  I was trying to open an application on multiple tabs and work simultaneously.

But I was not able to work due to planning timing out error. The planning was timing out and prompting for new login

Objective

The purpose of this article is to increase time out setting of Hyperion Planning and avoid the “Because of inactivity, your session has timed out and is no longer Active Click Ok to reload the page “error message.  This common error can be seen when multiple tabs are opened on same web browser.  I goggled and found few discussion and articles online but none of them were clear enough to understand the changes.

how To

The timing out setting is control by WebLogic   “CLIENT_STATE_MAX_TOKENS “parameter. We would find this parameter under “web.xml’ file.   The files is located under /Middleware/user_projects/domains/EPMSystem/servers/Planning0/tmp/_WL_user/PLANNING_11.1.2.0/3i5tnd/war/WEB-INF/web.xml

The default “CLIENT_STATE_MAX_TOKENS” is 5. Increase this value to 15 and then increase gradually if needed.

After making the changes clean the browser cookies and  start working on multiple tabs !!!!!!!


Or alternatively you  need to go to  
Oracle/Middleware/EPMSystem11R1/products/Planning/AppServer/InstallableApps/Common/HyperionPlanning.ear

and modify the HyperionPlanning.ear\HyperionPlanning.war/WEB-INF/web.xml file

CAUTION

The changes was made to tmp folder and will not hold the same value if any patch is applied or tmp folder is deleted . 

Related blogs and URLs

Understanding “Client_state_Max_tokes”

http://www.ateam-oracle.com/understanding-client_state_max_tokens/

ADF

http://docs.oracle.com/cd/E25054_01/web.1111/b31973/ap_config.htm

Oracle Discussion

https://community.oracle.com/thread/2512032

  

Oracle Meta-Link

Session Timeout while Accessing Web Pages (Doc ID 1396642.1)

Drill-Back to Source System to Source System Oracle General Ledger using FDMEE via Planning/Essbase

Author:-Sanjay Purohit

I have seen few posting on Oracle community regarding the FDMEE Drill Through functionality setup and required Oracle-GL responsibilities. FDMEE provides drill through functionality from planning to source systems. This functionality is very useful for financial/function users who want to verify the data accuracy from GL and also wants to know the source of transaction.

Objective

The purpose of this article is to demonstrate how to setup the drill through form Hyperion Planning to Oracle GL using FDMEE. I will not cover source system/data load and other FDMEE related setup.

Enter the Oracle GL URL by going to . Make sure you enter the URL such as http://Sanjay.epmcult.com:18000

Navigate -> Administer - > Data Management -> Setup -> Source System  

Enabling “Drill Region” to Target Application by going . Make sure you enter

Navigate -> Administer -> Data Management ->Setup -> Target Application -> Application Option

Setup the Drill region to “Yes

Setting UP Appropriate GL responsibility

Navigate -> Administer -> Data Management ->Setup -> Source Accounting Entities and select “GL Inquiry/Reports User” responsibility

Make sure when data is loaded you should be “Crate Drill Region HPL_DRILL_REGIONS”

Now let’s go to Planning application and drill through to Oracle-GL. The data from should have drillable views

 By  click you should be landed to FDMEE Data load location. This location stores the EBS URL

You should be able to see the General Ledger screen with dollar value. Make sure you login user should have “GL Inquiry/Reports User”  Responsibility

Hyperion Account Properties

http://docs.oracle.com/cd/E17236_01/epm.1112/cpx_admin/frameset.htm?apas03.html

Depp Dive into Drill-Through

http://akafdmee.blogspot.com/2014/05/a-deeper-insight-of-drill-through-from.html

http://essbase-ish.blogspot.com/2012/03/hyperion-essbase-planning-drill-through.html

 Essbase Documentation

https://docs.oracle.com/cd/E36352_01/epm.1112/esb_tech_ref/frameset.htm?maxl_credrillthrough.html

Retrieving Hidden Password from EPM Registry

Author:-Sanjay Purohit

I normally take screen shots and make notes whenever I install Hyperion but sometime I forget to document the schema password details  or  DBA forgot to setup the password profile as never expire and schema password gets expired and you are stuck. Your DBA reset new password and you have to re-run the configurator. 

Objective

The purpose of this article is to demonstrate how to retrieve the hidden password of configured components.  Retrieving the password is two-step process. First you would need to run the epmsys_registry.sh/bat   from /Middleware/user_projects/epmsystem1/bin.

 The output of the report will be stored under

/Middleware/user_projects/epmsystem1/diagnostics/reports as registry.html. The report can be opened in any browser. I am going to select FDMEE (aif_product) to retrieve the password.

  

The same information can be retried by using

sh epmsys_registry.sh view aif_product/database_conn/@dbPassword

But the password is encrypted. We would need to decrypt the password. Run the below given command to decrypt the value.

sh epmsys_registry.sh viewencryptedproperty aif_product/database_conn/@dbPassword

sh epmsys_registry.sh view encrypted property planning_product/database_conn/@dbPassword

The hidden password is welcome1.

Related Webpages and Blogs

 Oracle Blog-EPM troubleshooting Utilities

https://blogs.oracle.com/proactivesupportEPM/entry/epm_troubleshooting_utilities

How to deleted registry components

http://docs.oracle.com/cd/E17236_01/epm.1112/epm_install_1112200/frameset.htm?apfs02.html

Syncing Business Rules With Planning Application

Author:-Sanjay Purohit

There is a time when developer creates business rules and no longer needed or business rules are delete from calculation manger but are still visible in planning application.  I get many calls from client regarding planning and calculation manger business rule syncing issues therefore I am posting the article.

Objective

The purpose of this article is to demonstrate how to delete business rules from planning application which are already deleted from calculation manger and how to sync renamed business rules with planning application.

·         Newly create business rules are not visible in planning application :-> Business rules are created in calculation manger but are not visible in planning application.  On the below given image you can see  business rule are visible in calculation manger but are not in planning application

The business rule are not visible in Vision Planning application

HOW To

We will sync business rules from calculation manger and rule will visible in planning application. To sync business rule you would need to Login to Calculation manger by navigating to Navigate > Administer > Calculation Manager.

Make sure you are In System View, and select planning application -> right-click -> Deploy.
Make sure you are deploying at application level not at the plan type level.

   

After deploying the rules should be visible to planning application

The same theory applies to

·         When business rule are renamed and new name should be push to planning application.

·         When business rules are delete from Calculation Manger and but still visible in Planning.

·         Newly created business rules are not visible in planning application. 

Changing Hyperion Planning Default RMI Port 11333

                                                                        Author: Sanjay Purohit
Audience:  Hyperion Solution Architects and Hyperion Planning Database Administrator

Hyperion web services have default port no and listing ports rang.  I try to use default port in my installation whenever possible. The default ports are always easy to remember. But In some cases you have to change the default port no.

  Goal

Changing the default RMI port no from 11333 to any other port. There are many articles on meta-link (Oracle support) but most of them are old and poorly written. None of Oracle knowledge document worked for me when I had to change the port no. Changing default RMI port requires following steps.

·         HyperionRMI_Port.properties:- Change the value to new desired port. The default location of this file is EPMSystem11R1/common/RMI/11.1.2.0/HyperionRMI_Port.properties

·         COMMON_RMI_PORT:- You will need to add new Planning system property on workspace by going Administration>Application>Property. This property should update the HSPSYS_PROPERTIES database.

·         The third step is to modify the RMI.properties so start scripts can pick up right port :- The default location of this file is user_projects/epmsystem1/config/starter/RMI.properties

·         The last step is to modify the RMI.properties :- The default location of this file is

Middleware/user_projects/epmsystem1/RMI/HyperionRMIRegistry

·         Restart Planning, RMI, EAS and Calc Manager to run RMI on new port no.

  

Related Oracle Knowledge Article.

·         How to Change Hyperion RMI default port [11333] in Linux (Doc ID 1276021.1)

·         How to reconfigure or change the default planning ports in EPM 11.1.1.x (Doc ID 873963.1)

Please note:- This was tested with EPM - 11.1.2.3

Deleting Smartlist Entry from Hyperion Planning

 Author: Sanjay Purohit

Audience:  Hyperion Solution Architects and Hyperion Planning Database Administrator

In the tradition Planning world dimension/member intersection are used to store the data and most of us know that Hyperion Planning also provides a drop down feature called Smartlist to capture the data. But this feature comes with few limitations such as:-

Security Assignment: - The security cannot be assigned to Smartlists.

Data Export: - The Smartlists are saved on Hyperion Planning sachem and numeric values are pushed to Essbase. Users can get only numerical values in Essbase if data is exported from Essbase.

 Goal

Creating and loading Smartlists is an easy task but deleting them requires lots of manual efforts. Let’s assume a case where you load 1000 entries to a Smartlist but later we decide to use only 400 and we need to delete 600 of them. You are only left with two choices either delete them manually one by one or delete the Smartlist itself. Let’s how we can delete Smartlist entries.

We have created a csv file and will load the Smartlist from workspace:-

   

·         The Smartlist load is hidden under the New Dimension. Select New Dimension and then Dimension Type Smart List.

·         The smart list is loaded after running the file.

·         Now we will load the below given file to delete the Smartlist entries.

·         After loading this file. All the entries are removed from Smartlist except one.

Similar Posting

Hyperion Planning Smartlist

http://www.mpowersolutions.com.au/news-events/tips-tricks/hyperion-planning-smartlists

Setting up Smartlist

http://www.adistrategies.com/resources/knowledge-base/id0125-setting-up-hyperion-planning-smart-lists/

Where has my Smartlist gone

http://camerons-blog-for-essbase-hackers.blogspot.com/2013/02/stupid-planning-queries-11-where-and.html

Replacing Old Version of SmartView with New Version on EPM Workspace

Author:-Sanjay Purohit

Audience:  Hyperion Solution Architects and Hyperion Database Administrator

Oracle Hyperion Smart-view is a powerful tool integrates with Microsoft office products such as word, power point and excel.  Oracle is frequently updating the version of SmartView. Most of users are aware and download the latest version from web but few of them relies on workspace.  This is the task of Hyperion system admin to replace the old version of SmartView with new version.

Objective

The purpose of this article is to demonstrate how to replace old version of SmartView from EPM workspace in two steps.

how To

The default location of Smartview is installer is

Oracle/Middleware/EPMSystem11R1/common/epmstatic/wspace/SmartView

Just replace this file with latest version.

Related blogs and URLs

Installing EPM System Clients from EPM Workspace http://docs.oracle.com/cd/E40248_01/epm.1112/epm_install/frameset.htm?ch05s09s04.html

Oracle Smart View for Office

http://www.oracle.com/technetwork/middleware/smart-view-for-office/overview/index.html

Oracle EPM Previous Version document

http://www.oracle.com/technetwork/middleware/smart-view-for-office/documentation/index.html

http://docs.oracle.com/cd/E57185_01/epm.1112/epm_install/frameset.htm?ch05s09s05.html

Other Client Location

Hyperion-EPM 11.1.2.3 Installation error On RedHat Linux (6.5)

Author:-Sanjay Purohit

Audience:  Hyperion Solution Architects and Hyperion Database Administrator
Oracle has done a great job to package all the EPM installation files. The EPM 11.1.2.3 installation is pretty smooth and work well. I personally have not seen any issue with installer but this time I had to install Hyperion 11.1.2.3 on RedHat-6.5. As per the certification matrix Red Hat EL 6 (UL1+) is certified to install EPM. The UL1+ refers the version update of operating system. By seeing the certification matrix I assume that 6.5 is also certified. However Oracle policy and EPM 11.1.2.3 support matrix contains the following information:

*************************************************************************************************************

“OS Version: Red Hat EL 6 (UL1+)

A plus sign (+) after a version number indicates that this and all higher versions at that decimal level are certified. Oracle's Backward Compatibility Policy: Oracle acknowledges and supports the backward compatibility assertions for platform software as provided by its vendor. Therefore when vendors assert backward compatibility, subsequent maintenance releases and service packs may be used. If an incompatibility is identified, Oracle will specify a patch release on which EPM System should be deployed (and remove the incompatible version from the supported matrix) or provide a maintenance release or service fix to the EPM System product software. “(Source-Oracle documentation)

*************************************************************************************************************

If we convert Oracle statement in simple English, Oracle assume that Red Hat Enterprise Linux Server release 6.5 can do what Red Hat EL 6 (UL1) is able to do. If Red Hat Linux 6.5 stops supporting a feature that was supported in Red Hat EL 6 (UL1), there could be some issues.  I assume Oracle has never tested EPM installer with RedHat-6.5.

Objective

The purpose of this article is to demonstrate the issue and fix of EPM installer on Red Hat 6.5. I will not clam this as a fix but its workaround to pass the installer error. After making a small change on /etc folder. You should be able to install Hyperion components.

how To

If you are running installer on Red Hat (6.5). You would get following error. I am not sure what is RedHat-Red and how the installer is picking it up.

After spending few hours I figured out that installer is picking up operating system name from /etc/redhat-release file. The original file contains the name of operating system. The secret of Red is still unknown to me.

I just replaced the entire line to “6” and I was able to run the EPM installer with no error.

Related blogs and URLs

EPM 11.1.2.3 Installation and configuration changes

http://john-goodwin.blogspot.com/2013/04/11123-installation-and-configuration.html

EPM 11.1.2.3 Installation and configuration guide.

http://docs.oracle.com/cd/E40248_01/epm.1112/epm_install.pdf

Hidden Version of Hyperion Products after the Upgrade

                                                                             Author: Sanjay Purohit
Audience:  Hyperion Solution Architects and Hyperion Database Administrator
I had to upgrade an environment from 11.1.2.2 to 11.1.2.3. The upgrade path is pretty simple and did not take long time to apply maintenance release and configuration. Oracle recommends not to follow the upgrade procedures but to apply the maintenance release. After applying the maintenance release, I noticed version mix match on workspace. Few of products upgraded to 11.1.2.3 but not all. I have not seen any error during upgrade and configuration. Logs.

Objective

The purpose of this article is to check the hidden product version on jar files. I will demonstrate only one components Hyperion Financial Reporting (HFR) version check steps but same applies to all most all the components.  

how To

To get the version file we would need to look for the file HReports.jar file from below given location Oracle/Middleware/EPMSystem11R1/common/financialreporting/11.1.2.0/lib and follow the below given steps.

·         Copy the file server to your local machine. I prefer to use Winscp. It’s free and easy to use. 

·         Rename HReports.jar to HReports.zip.

·         Right click on it and select Open from the pop-up menu (this allow view of zip contents without extraction)

·         Double click on "META-INF" folder.

·         Right click on file "MANIFEST.MF" and select Open from the pop-up menu.

·         Open file in a text editor such as Notepad or Word.

·         The product version is indicated by the following parameters in the MANIFEST.MF file.

·         The details are you looking for are:-

o    Implementation-Version:

o    Specification-Version:

Related blogs and URLs

Hyperion (EPM) supported Upgrade Paths

http://docs.oracle.com/cd/E40248_01/epm.1112/epm_install/frameset.htm?ch11s01s01.html

Applying Maintenance Release (Installation)

http://docs.oracle.com/cd/E40248_01/epm.1112/epm_install/frameset.htm?ch10s05.html

Reason to upgrade

http://hyperionvirtuoso.blogspot.com/2014/01/top-5-reasons-to-upgrade-to-11123.html

EPM Cumulative Feature Overview tool v 11.1.2.2.x is live

https://blogs.oracle.com/proactivesupportEPM/entry/epm_cumulative_feature_overview_tool