Saturday, March 30, 2013

OBIEE 11g - Hyperion Integration With OBIEE

Hyperion Essbase Integration with OBIEE

•OBIEE can connect to multidimensional as well as relational sources
  Microsoft Analysis Services, SAP B/W, Essbase
•Import process converts the multidimensional view into a relational view
•Essbase cubes, dimensions, hierarchies converted to a logical relational star schema
•Essbase data can then be combined with data from sources
•OBIEE 11g introduces new OLAP query capabilities making Answers a first-class ad-hoc query tool for Essbase cubes
•Other use case is to bring in budget, forecast data etc from Essbase and use to enhance relational data.


New Essbase Cube and Metadata Features in OBIEE 11gR1


•Essbase outlines are still imported in and converted to relational stars
•However outlines are now handled better
-Accounts dimensions keep their hierarchy
-Parent-child (value-based) hierarchies are supported
-Alias tables supported
-UDAs supported
•No longer any need to recreate as outline evolves (additional generations, members)
•Makes OBIEE a more practical reporting environment for financial-style Essbase cubes.


Importing an Essbase Cube Step 1 : Review Essbase Outline

Review Essbase Outline
Review the Essbase outline using Essbase Administration Services
•Be aware of the number and type of dimensions
•Check usage of alias tables, UDAs etc
•Be sure to use correct Essbase versions
-Other releases will partially work, but drilling etc may not work.

Installing and Configuring Oracle Financial Management Analytics

Overview

Oracle Financial Management Analytics provides executive access to a unified financial and nonfinancial picture of the performance of the organization through a series of dashboards. Prebuilt consolidation dashboards display key performance indicators, reports, and metrics by using data from EPM System applications. The dashboards enable users to analyze the data in an accessible format, according to various business and financial scenarios.
In this tutorial, you install and configure Oracle Financial Management Analytics.

Software and Hardware Requirements

The following is a list of software version requirements for Oracle Financial Management Analytics:
Oracle Business Intelligence Enterprise Edition 11.1.1.5. Please note that the following patches must be applied:
  • Patch 11.1.1.5.1 MLR
  • Patch 11.1.1.5.1_12698366
  • Financial Management 9.3.3.1, 11.1.1.3, or 11.1.2.1. Please note that the following patches must be applied:
  • For version 11.1.2.1, apply patch 11.1.2.1.102
  • For version 11.1.1.3, apply patch 11.1.1.3.500 and 11.1.1.3.500_12843958.
  • Financial Close Management 11.1.2.1
  • Hyperion® Shared Services Note: You must use the same version of Shared Services that you use for Financial Management.

Prerequisites

Before starting this tutorial, you should:
1.Have installed Oracle Business Intelligence Enterprise Edition 11.1.1.5 and its components.
2.Have installed the Financial Management client and the Financial Management ADM driver on the same server as Oracle Business Intelligence Enterprise Edition.
3 .Have installed Shared Services.
4 .Have created the Financial Management application that you want to analyze with Oracle Financial Management Analytics.
5 .Have registered the Financial Management application in the Financial Management client on the Oracle Business Intelligence Enterprise Edition server.
6 .Have ensured that the administrative user for Oracle Financial Management Analytics has write access to the directory that is used for the installation and has administrative rights to all associated software.
7 .Have created the FCC_OFMA table. See the "Creating the FCM_OFMA Table for Financial Close Management for Oracle Databases Only" section of Oracle Hyperion Financial Management Analytics Administrator Guide for details.(Financial Close Management dashboards only.)
8 .Have created the required FCM database views. For details, see "Setting Financial Close Management Views on Oracle Databases Only" section in the Oracle Financial Management Analytics Administrator Guide for details. (Financial Close Management dashboards only.)

Installing Oracle Financial Management Analytics


1.Download and unzip the Oracle Financial Management Analytics installer files from Oracle edelivery.
Screenshot for Step
2.Double-click setup.exe.
Note: there are separate installers for 32-bit and 64-bit installations. Select the installer version that is appropriate for your environment. In this tutorial, you install the 32-bit version.
Screenshot for Step\
Oracle Universal Installer is displayed.
3.Review the Welcome panel, and click Next.
Screenshot for Step
4.On the Select Installation Type panel, select Complete, and click Next.
The complete installation automatically installs the following components of the application:
  • Repository (FinancialManagementAnalytics.rpd), which contains metadata such as subject area, metrics, and dimensions. Note: the default password for the repository is welcome1.
  • Oracle Business Intelligence Presentation catalog (FinancialManagementAnalytics), which contains reports and dashboards.
  • Oracle Financial Management Analytics Configuration Utility.
Note: English is the default language for Oracle Financial Management Analytics. If you want to use a different language, click Product Languages. For details, see "Setting the Application Language" in the Oracle Financial Management Analytics Administrator Guide.
Screenshot for Step
5.On the Specify Home Details panel, enter the following details for the installation, and click Next.
  1. Enter the name of the installation. By default, the name is Ora_OFMAx, where x is the number of the previous installation of Oracle Financial Management Analytics + 1.
  2. Enter the path, or browse to the location where you want to install Oracle Financial Management Analytics. By default, the path is C:\OFMAHome_x, where x is the number of the previous installation of Oracle Financial Management Analytics + 1.
Screenshot for Step
6.On the Summary panel, review the components to be installed, and click Install.
Screenshot for Step
Oracle Universal Installer installs the product with the specified options.
7 .On the End of Installation panel, confirm that the installation was successful, and click Exit.
Screenshot for Step
8 .At the exit prompt, click Yes.
Screenshot for Step
You successfully installed Oracle Financial Management Analytics.

Running the Configuration Utility

In this topic, you run the configuration utility to configure Oracle Financial Management Analytics.
1.Select Start, then All Programs, then Oracle Financial Management Analytics, and then Configuration Utility.
Screenshot for Step
Oracle Financial Management Analytics Configuration Utility is displayed.
2.In the Catalog And Repository Details section, click Browse next to the Catalog Location field.
Screenshot for Step
The Select Catalog Location dialog box is displayed.
3 .Select FinancialManagementAnalytics, and click OK.

By default, the catalog is located in C:\OFMAHome_1\OFMA\.
Screenshot for Step
4 .In the Catalog And Repository Details section, click Browse next to the Repository Location field.
Screenshot for Step
The Select RPD File dialog box is displayed.
5 .Select FinancialManagementAnalytics.rpd, and click OK.

By default, the catalog is located in C:\OFMAHome_1\OFMA.
Screenshot for Step
6 .In the BI Admin Server Details section, complete these fields for Oracle Business Intelligence Enterprise Edition:
  • In Host Name, enter the server name where Oracle Business Intelligence Enterprise Edition is installed.
  • In Port, enter the port number.
  • In User Name, enter the Oracle Business Intelligence Enterprise Edition user name.
  • In Password, enter the password.
Screenshot for Step
7 .In the HFM Server Details section, complete these fields for Financial Management:
  • In Server Name, enter the name of the cluster or server where the Financial Management application is deployed.
  • In Application Name, enter the name of the Financial Management application.
  • In User Name, enter the user name of the Financial Management administrator associated with the application.
  • In Password, enter the password of the Financial Management administrator associated with the application.

    Screenshot for Step
    8 .Click Next.
    Screenshot for Step
    The Configuration Utility connects to the specified Financial Management application and reads the dimension hierarchy information. This information is used to configure the dashboards in the next several steps.
    9 .On the Entity dimension screen, perform the following actions:
    1. Select the regional entities to be displayed on the dashboards. You select only the parent regions that you want to drill down from in the reports.
    2. Click the right arrow button to move them to the Regions section.
    3. Click Next.
    Screenshot for Step
    10 .On the Account dimension screen, perform the following actions:
    1. Select an account group from the drop-down list, such as Balance Sheet Accounts, Income Accounts, or Performance Indicators Accounts.
    2. In the Account Hierarchy list, select the accounts to be displayed on the dashboards. You must select at least one account for each account group, but you may select as many as you need.
    3. Click the right arrow button to move them to the Accounts section.
    4. Continue selecting accounts and account groups until you have selected at least one account for each account group.
    5. Click Next.
    Note: For the Performance Indicator account group, the accounts that you select are displayed in the Associate Account drop-down list on the next screen. You must select four accounts. If you select more than four, the additional selections are not displayed in the report.
    Screenshot for Step
    11 .On the Performance Indicators screen, perform the following actions:
    1. Select an associate account from the drop-down list. The accounts in this list are the accounts that you selected in the previous screen for performance indicators.
    2. Select the accounts to be associated with the performance indicator. You must select at least one account for each performance indicator, but you may select as many as you require. In this example, for the QuickRatio Performance Indicator account, the associate accounts are Current Assets (111000), Inventory (112000), and Current Liabilities (211000).
    3. Click the right arrow button to move them to the Performance Ratio section.
    4. Continue selecting accounts and performance iIndicators until you have selected at least one account for each performance indicator.
    5. Click Next.
    Screenshot for Step
    12 .On the Product Dimension screen, perform the following actions:
    1. In the Select Product drop down list, select the custom dimension that contains the product hierarchy in your application. In this example, you select Custom1.
    2. Select the required products. You can choose multiple products.
    3. Click the right arrow button to move them to the Products section.
    4. Click Next.
    Screenshot for Step
    13 .On the Currency Constant Rate Dimension screen, perform the following actions:
    1. In the Select Currency Rate drop-down list, select the custom dimension that contains the hierarchy for performing constant rate analysis in your application. If your application does not use a custom dimension for constant rate analysis, select Scenario. In this example, you select Custom3.
    2. Select the ConstantRate hierarchy. This setting is used to neutralize fluctuating currency values so that changes in exchange rates do not incorrectly reflect as a profit or loss. For details, see the Oracle Financial Management Analytics Administrator’s Guide for details.
    3. Click the right arrow button to move it to the Constant Rate section.
    4. Click Next.
    Screenshot for Step
    14 .On the Default Dimension Member screen, set the default point of view (POV) for the dashboards by performing the following actions:
    1. Select a dimension from the Default Dimension drop-down list.
    2. Select the default member for each POV dimension.
    3. Click the right arrow button to move the selected to the Default Member section.
    4. Repeat steps a through c for the following dimensions:
    5. Scenario
    6. Year
    7. Period
    8. View
    9. Entity
    10. Value
      • Click Next.
      Screenshot for Step
      15 .Complete the following information to enable connection between Financial Management and Oracle Business Intelligence Enterprise Edition:
      1. Select Perform OPMN Changes Required to Connect to HFM Data Source. Note: This step is required only when you perform a first-time configuration.
      2. For OPMN File Path, enter the full path to the opmn.xml file, or click the Browse icon to navigate to the full path to the opmn.xml file.
      3. Select Upload Repository.
      4. Enter and confirm the RPD password for the selected repository.
      5. Select Upload Catalog to upload the Oracle Financial Management Analytics catalog to the business intelligence (BI) server.
      6. Click Next.
      Screenshot for Step
      16 .On the Summary screen, review your selections, and click Submit.
      Screenshot for Step
      17 .Verify that all changes were completed successfully, and click OK.
      Note: This operation may take several minutes as the services are restarted.
      Screenshot for Step
      Oracle Financial Management Analytics Configuration Utility closes.

      Setting Connection Pool Parameters

      After uploading the repository to the BI Server, you must open the repository in online mode and define the connection parameters in the Oracle Financial Management Analytics repository (RPD) file to enable the connection between Oracle Financial Management Analytics and the Financial Management application.
      1.Select Start, then All Programs, then Oracle Business Intelligence, and then BI Administration.
      Screenshot for Step
      The Oracle BI Administration Tool is displayed.
      2.Select File, then Open, and then Online.
      Screenshot for Step
      3 .Perform the following actions:
      1. For Repository Password, enter the password for the RPD file.
      2. For User, enter the Oracle BI administrator user name.
      3. For Password, enter the Oracle BI administrator password.
      4. Optional: select Load all objects on startup.
      5. Click Open.
      Screenshot for Step
      The repository is opened in online mode.
      4 .In the Physical layer panel, expand HFM, and double-click on Connection Pool.
      Screenshot for Step
      5 .At the prompt to check out connection pool, click Yes.
      Screenshot for Step
      6 .In the URL field, enter the Financial Management server or cluster name and the application
      name to which you want to connect, and click OK. Use the following format:
      adm:native:HsvADMDriver:<HFM Server or Cluster>:<HFM Application Name>
      Screenshot for Step
      7 .In the "Connection properties" section, perform the following actions:
      1. Select the connection method. In this example, you select SSO using CSS Token, so that you can connect by using single sign-on.
      2. Click OK.
      Screenshot for Step

      Setting the Financial Management Application Name

      In this topic, you set the Financial Management application name in the RPD file.
      1.In the Oracle BI Administration Tool, on the Physical layer, expand HFM, and double-click HFM Application.
      Screenshot for Step

      2 .At the prompt to check out HFM Application, click Yes.
      Screenshot for Step
      The Cube Table dialog box is displayed.
      3 .In the External Name field, enter the name of your Financial Management application, leave all other settings at their default values, and click OK.
      Note: If your application name contains restricted characters, you can use the string “OFMA”.
      Screenshot for Step

      Renaming Custom Dimensions

      After you install Oracle Financial Management Analytics, the custom dimension names in the Oracle Financial Management Analytics repository files are displayed as placeholders, such as Custom1, Custom2, Custom3, and Custom4. If your Financial Management application uses aliases for the custom dimensions, you must rename these placeholder values to reflect the custom dimensions in your application (for example, “Products," “Customers," or “Sales Channels”).
      1 .In the Oracle BI Administration Tool, on the Physical layer, perform the following actions:
      1. Expand HFM.
      2. Expand HFM Application.
      3. Double-click Custom1.
      Screenshot for Step
      2 .At the prompt to check out Custom1, click Yes.
      Screenshot for Step
      The Physical Dimension - Custom1 dialog box is displayed.
      3 .Perform the following actions:
      1. In the Name field, enter the name of the associated custom dimension in the selected Financial Management application. In this example, you enter Products.
      2. In the Default Member field, enter a valid member value corresponding to your application. In this example, you enter Total.
      3. Click OK.
      Screenshot for Step
      4 .Expand the renamed Custom1 dimension to display all members in the hierarchy, and double-click Custom1.
      Screenshot for Step
      5 .At the prompt to check out Custom1, click Yes.
      Screenshot for Step
      The Physical Hierarchy - Custom1 dialog box is displayed.
      6 .Select the Levels tab, and perform the following actions:
      1. In the Name field, enter the name of the associated custom dimension in the selected Financial Management application.
      2. Click OK.
      Screenshot for Step

      7 .In the expanded Custom1 dimension list, double-click the first column in the database hierarchy.
      Screenshot for Step
      The Physical Dimension - Custom1 dialog box is displayed.
      8 .At the prompt to check out Custom1 ClassType, click Yes.
      Screenshot for Step
      The Physical Cube Column dialog box is displayed.
      9 .Perform the following actions:
      1. In the Name field, modify the database column name to reflect the name of the column in the associated
        Financial Management application. In this example, you change Custom1 ClassType to Products ClassType.
      2. Click OK.
      Screenshot for Step
      10 .Repeat steps 7, 8, and 9 for each column in the custom dimension.
      Screenshot for Step
      11 .Repeat steps 1 through 10 for each additional custom dimension; for example, Custom2, Custom3, and Custom4.
      Screenshot for Step
      12 .Click Check In Changes to check in your selections.
      Screenshot for Step
      13 .Select File, and then Save to save your selections.
      Screenshot for Step

      Setting Default Members in the RPD File

      In this topic, you use the Oracle BI Administration Tool to set the default member values in the online
      repository file (RPD).
      1 .In the Oracle BI Administration Tool, on the Physical layer, perform the following actions:
      1. Expand HFM.
      2. Expand HFM Application.
      3. Double-click Account.
      Screenshot for Step
      2 .At the prompt to check out Account, click Yes.
      Screenshot for Step
      The Physical Dimension - Account dialog box is displayed.
      3 .Select the Hierarchies tab, and perform the following actions:
      1. In the Default Member field, enter the default member for the Account dimension from the Financial Management application.
      2. Click OK.
      Screenshot for Step
      4 .Repeat steps 1, 2, and 3 for the remaining dimensions.
      Screenshot for Step
      5 .Click Check In Changes to check in your selections.
      Screenshot for Step
      6 .Select File, and then Save to save your selections.
      Screenshot for Step
      7 .Select All Programs, then Oracle Business Intelligence, and then Stop BI Services.
      Screenshot for Step
      8 .At the prompt, enter your Oracle WebLogic admin user name and password, and press Enter.
      Screenshot for Step
      Oracle BI services are stopped.
      9 .Select All Programs, then Oracle Business Intelligence, and then Start BI Services.
      Screenshot for Step
      Oracle BI services are started. The connection pool, custom dimension names, and other configuration details are updated.

      Launching Oracle Financial Management Analytics

      After installation and configuration, you access Oracle Financial Management Analytics through Oracle Business Intelligence Enterprise Edition.
      In this topic, you access Oracle Financial Management Analytics in a web browser.
      1 .In a browser, enter the URL for Oracle Business Intelligence Enterprise Edition. The URL is in the formathttp://server name:port number/analytics, where:
      • Server name is the name of the computer hosting the Oracle Business Intelligence Enterprise Edition server.
      • Port is the Oracle Business Intelligence Enterprise Edition server port number.
      • Analytics is the Virtual Directory set for Oracle Financial Management Analytics on the Oracle Business Intelligence Enterprise Edition server.

        Screenshot for Step
        The Oracle Business Intelligence Enterprise Edition Sign-In screen is displayed.
        2 .Perform the following actions:
        1. Enter the user ID for the Financial Management administrator. This user must have the required permission in both Financial Management and Oracle Business Intelligence Enterprise Edition.
        2. Enter the password for the Financial Management administrator.
        3. Click Sign In.
        Screenshot for Step
        Oracle Business Intelligence Enterprise Edition is displayed.
        3 .Select Dashboards, and then Oracle Financial Management Analytics.
        Screenshot for Step
        4 .Verify that the Oracle Financial Management Analytics dashboard tabs are displayed.
        Screenshot for Step

        HFM Connectivity with OBIEE
        As part of that i was testing the HFM connectivity that was introduced in BI EE 11.1.1.3. In this post i shall be covering the basics of how the integration works in general. I will follow this post up with a more in-depth walk through of the connectivity in the next part. Before we get into the details, couple of points to keep in mind. BI EE 11.1.1.3 is certified only on HFM 11.1.2(though will work with the older releases). If you are on HFM 11.1.1.3 or HFM 9.3.3, you need to upgrade BI EE to 11.1.1.5.
        BI EE connects to HFM through JavaHost. So, the setup for configuring the connectivity is to basically make the EPM environment variables available for access to BI EE. Before making the changes, there are a couple of things to be aware of
        1. Ensure that DCOM is enabled on the BI Server machine. This is needed for HFM client to work/connect properly with HFM Server.
        2. Ensure that HFM Client is installed.
        The above assumes EPM 11.1.2.x version. If you are on Hyperion 9.3.3, then you need to have the ADM drivers installed manually. All of this is documented clearly. So, rather than re-iterating that i will just point to the link here. Ensure that you follow all the steps mentioned in the docs else you will have issues in the connectivity. Once all the pre-requisite steps are done, we start off with the metadata import process.
        NewImage
        NewImage
        The import process will basically reverse engineer all the HFM hierarchies. In addition, interestingly it also brings in attributes like CellText, CurrencyType etc. So, not only does this provide pure data reporting capabilities it can also report on supporting documents within HFM. A clear screenshot of the physical layer post import is given below
        NewImage
        Like Essbase & Oracle OLAP sources, creation of metadata for other layers like BMM and Presentation layer is just a matter of drag & drop.
        NewImage
        But the interesting thing to note here is the way hierarchies are imported. If you notice below, all the major dimension hierarchies are imported as parent child hierarchies.
        NewImage
        This makes sense as HFM hierarchies rarely are balanced. Let’s now try creating a report containing just the Entity hierarchical column and then look at the query generated.
        NewImage
        NewImage
        NewImage
        Interestingly similar to SQL sources (HFM also has a Relational data store – But BI EE uses the ADM driver to extract the data), it does generate separate queries for each level. Also the Queries themselves don’t look to be SQL. Let’s now fire a query to extract the data from HFM.
        NewImage
        This looks good. And it does seem to generate the right queries as well. But what are the queries that get generated? They are not normal SQL queries. Also, how does BI Server generate them. Does it delegate all the data extraction to the ADM driver natively or does it let JavaHost extract the data. If it is the latter, it will be very interesting as this is the first time i am noticing JavaHost being used for data extraction from within BI Server. Perhaps this is how the planned BI EE 11g-Planning integration will work in the future. Anyway to answer these queries, i will follow this post with another one with more details.

        Embedding OBIEE11g into EPM 11.1.2.1 Workspace
        Prerequisites
        • A working OBIEE 11g install
        • Working OFMA dashboards (to come in more detail later post)
        • A working 11.1.2.1 environment

        ENABLE IFRAME

        Being a web based application ideally we could embed OBIEE into the EPM workspace much like a web portal.  If you try it fails with

        “OBIEE content cannot be displayed in an IFrame”.

        Follow the below steps to remove it.

        1) Locate the instanceconfig.xml file at the location:

        [OBIEE_HOME]\instances\instance1\config\OracleBIPresentationServicesComponent\coreapplication_obips1\
        Update the instanceconfig.xml file to add the highlighted entry within the "security" tag (possible values are 'allow', 'prohibit' and 'sameDomainOnly'):

        <Security>
        <InIFrameRenderingMode>allow</InIFrameRenderingMode>
        <!--This Configuration setting is managed by Oracle Business Intelligence Enterprise Manager-->
        <ClientSessionExpireMinutes>210</ClientSessionExpireMinutes>
        </Security>

        Now, this will only get you half way. You'll still run into the error, which really doesn't help you any. The 2nd piece is as follows:

        2) Edit the 'web.xml' file at the following location:

        [OBIEE_HOME]\oracleBI1\bifoundation\web\app\WEB-INF\

        Add the following highlighted entry to the file. The syntax is the opposite of what the syntax for instanceconfig.xml is, since the 'never' means to never block the rendering within IFrame (you can choose 'never' to always allow rendering in the IFrame, or 'differentDomain' to only allow rendering if it is in the same domain).


        <servlet-mapping>
        <servlet-name>SAWBridge</servlet-name>
        <url-pattern>/saw.dll/*</url-pattern>
        </servlet-mapping>

        <servlet-mapping>
        <servlet-name>RelatedContent</servlet-name>
        <url-pattern>/RelatedContent</url-pattern>
        </servlet-mapping>

        <context-param>
        <param-name>oracle.adf.view.rich.security.FRAME_BUSTING</param-name>
        <param-value>never</param-value>
        </context-param>

        <login-config>
        <auth-method>CLIENT-CERT</auth-method>
        </login-config>



        3) Now that you've completed both steps, restart the OBIEE System Components using opmnctl (Stop opmnctl and Start opmnctl). If you need to find this, you'll find it in your start menu on the server under the 'Oracle Business Intelligence' folder, called 'Stop BI Services' and 'Start BI Services' respectively.

        4) Before testing, be sure to clear your browser cache.
        GO URL
        Now to look at the GO URL

        Sample GO URL:

        http://localhost:9704/analytics/saw.dll?PortalPages&PortalPath=/shared/Paint%20Demo/_portal/&Page=Brand+Analysis&Syndicate=siebel&nquser=Administrator&nqpassword=Administrator

        As you see above the following are the parameters
        1. PortalPath (Direct path to the Dashboards)
        2. Page (This points to the page within the dashboard)
        3. nquser (Username)
        4. nqpassword (Password)


        But if you want more customizations the only way is to create custom styles and skins. Lets test out the above GO URL.

        Posted by at
        Labels: