Data Retrieval and Adjustment

CDM uses Excel objects to retrieve source data. The data is retrieved into worksheet cells in an Excel object.

You can add more than one data query to the same Excel object in different worksheets, as well as add multiple data queries from different data sources to the same worksheet in an Excel object. However, each cell or table can have only one data source associated with it.

You cannot modify the data source worksheet (the worksheet that retrieves an underlying data from a query), unless the data query is an OLAP data query that is configured to allow data writeback.

For more information about creating and editing OLAP data queries for write back, see the CDM Administration Guide.

You can add text or data manually to the data worksheet (the worksheet that is organized for generation). The data worksheet is originally named Sheet1, but you can rename it if necessary. However, such data cannot be refreshed from a data source unless there is a direct link to the Database tab where the query is inserted.

Load Data into Excel Objects

You can import data in an Excel object in CDM from an external source. The source can be OLAP, relational, an external Excel file, HFM servers, IBM® Cognos® TM1®, IBM Cognos Controller, IBM Cognos Analysis for Microsoft™ Excel, IBM Cognos Business Intelligence, and IBM Cognos for Microsoft Office. Cognos Analysis for Microsoft Excel, and Cognos for Microsoft Office work through the Excel add-in, and Cognos® Controller also uses the add-in.

A data query must be defined before you can load data. When the query is defined, it must be associated with the report.

You need permission to create and associate queries and to load data into an Excel object.

Cognos Analysis for Microsoft Excel, and Cognos for Microsoft Office work through the Excel add-in, and Cognos Controller also uses the add-in. Data from Cognos Analysis for Microsoft Excel, Cognos for Microsoft Office, Cognos Controller, and Cognos TM1 Perspectives cannot update automatically if the source data has changed. You must manually click the Refresh button on the add-in to update the data. This concept is known as dynamic refresh, and is not supported in CDM when you use data from these add-ins.

CDM can directly access an OLAP or relational data source and retrieve data from the source into the report.

Data can also be imported from an Excel file into CDM. Multiple ranges can be imported from the same file, for example, A1:B20, C10:F30. As well, CDM dynamically refreshes the data directly from the source file. You can also add specific named ranges.

When data is retrieved from the underlying data source, it is imported into the default Database worksheet in the Excel object. In CDM, one worksheet can have multiple data sources contained in it, for example, an OLAP source and a relational source.

When querying chart reports using a Business Intelligence query, only the data is brought into CDM.

After the data is retrieved, it can then be referenced by any other Word or Excel object in the report by using variables.

To load data into an Excel object:

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. In the Data tab for Excel object on the ribbon, click Insert Data. The Insert data for report object window opens.
5. Select the data query that you want to insert in the Excel object. Click Next.
6. Select the location of the worksheet that will contain the query.
7. Accept or modify the default named range that is to be associated with the query.

   Note: The named range is used when managing data queries to navigate quickly to where the query is located in the report object for review. If a data query in an Excel object has no named range associated with it, the default named range is assigned to it the first time the report object is checked out.

8. If you want to override report object query variables in the query, specify the variables, values, and a comment if necessary.
9. Click Finish.
10. Save your changes to the Excel object.
11. Click the Default View Mode icon in the status bar.
12. Save your changes to the report and check in the Excel object.

Edit OLAP Write-Back in Excel Objects

A write-back process is a process that gives users the ability to edit the data in a query and save the updated data back to an OLAP source. You can edit the data in some OLAP data queries in an Excel object in CDM and save the changed data back to the data source.

A data query must be defined and loaded in an Excel object before you can edit data.

You need permission to write back data in an OLAP data query.

You can use a data query in CDM to directly access an OLAP data source and retrieve data into the report. OLAP data queries can be configured to write data back to the data source.

Important: To write data back to OLAP, all dimension members in the data query must be leaf-level members. A leaf-level member is a member of a hierarchy that has no children. For information about data queries, see the CDM Administration Guide.

By default, OLAP data queries are protected to prevent editing. You can use the Unprotect command in the Data tab to allow selected cells to be edited. The Protect command prevents editing of an unprotected cell.

Tip: After you unprotect a cell, that cell will remain unprotected until you protect the cell again.

To edit OLAP write-back in an Excel object:

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Select one or more calls in the data query that you want to edit.
4. In the Data tab for Excel object on the ribbon, click Unprotect.
5. Edit the data in the unprotected cells, as necessary.
6. Save your changes to the Excel object.
7. If you want to save changes data query, select Include OLAP Changes. The updated data is written back to the data source.
8. If you are done editing an unprotected cell, select that cell and click Protect in the Data tab.

   Note: If you do not have appropriate permissions, the write-back fails.

9. Save your changes to the report and check in the Excel object.

Manage Data Queries in an Excel Object

A query is a request for information from a database that is based on specific conditions. You can view the queries that are inserted in an Excel object in CDM and then you can make changes.

You can change the name of the worksheet that contains the query and you can delete a query. However, if you change the worksheet name or delete the worksheet outside the Manage data queries window, the renamed worksheet will no longer be connected to the query and a new worksheet will be created with the same name as the original worksheet. A warning message will appear informing you that the Excel object is manually protected.

You can also modify the query variables associated with the query and navigate to the query where it is located in the current report object or to Administration for editing.

To manage data queries in an Excel object:

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. In the Data tab for Excel object on the ribbon, click Manage Data Queries. The Manage Data Queries window opens.
5. To delete a data query, select it and delete.
6. To edit a data query, complete the following steps:
   1. Select it and then edit.
   2. To change the name of the worksheet that contains the data query, specify the name that you prefer.
   3. If you want to associate query variables in the Excel object with the query, select the variables.

      Tip: To see a variable, it must be overridden at report object level.

7. To navigate to and edit the data query properties in Administration, select the query and then select Data Query from the Navigate to drop-down menu.
8. To navigate to where the query is located in the current report object, select the query and then select Named Range from the Navigate to drop-down menu.
9. Save your changes to the Excel object.
10. Click the Default View Mode icon in the status bar.
11. Save your changes to the report and check in the Excel object.

Associate HFM/EPM Connections with Excel Objects

You can associate Hyperion Financial Management (HFM)/Oracle EPM connections with Excel objects in CDM using HFM or Smart View compatible Oracle EPM formulas to refresh the data.

An administrator must first define HFM/EPM data sources in CDM before you can associate an HFM/EPM connection with an Excel object.

Before you can edit HFM formulas, the HFM Smart View add-in must be installed.

You can use these methods to define HFM formulas:

• Formula with connection name
  You specify the HFM data source connection name in the formula. The connection name must match the name of the data source defined in CDM.
• Formula without connection name
  You can choose to not include a connection name in the formula.

For both methods, formulas are refreshed when you associate an HFM connection with the Excel object. If there is no connection associated with the Excel object, for formulas defined using the second method, the default connection is used. The default connection must be selected in the Manage HFM/EPM Servers window.

CDM automatically classifies formulas containing the pattern URLFN| as Oracle EPM Financial Consolidation and Close Service (FCCS) formulas. You can use both HFM and FCCS formulas within the same workbook or report. This enables you to unify reporting across Oracle's two consolidation platforms.

The following example shows a fully qualified FCCS Smart View formula:

You can associate multiple HFM/EPM connections with an Excel object. However, if the multiple connections are to different data sources, the formulas must be defined using the first method. You then specify the connection to associate with the formula.

As stated earlier, the HFM Smart View add-in must be installed before you can edit HFM formulas. However, the refresh process to retrieve the latest values from the HFM data source is performed on the application server, so the refresh process does not require the add-in. During report generation, the data refresh is also performed on the application server. To refresh a formula at the report object level, use manual refresh in add-in mode.

To associating HFM/EPM connections with an Excel object:

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. In the HFM/EPM servers area, select the connections that you want to associate with the Excel object.
5. To specify the default connection to use for formulas defined using the second method, select the server that you want to be the default server.
6. Save your changes to the Excel object.
7. Click the Default View Mode icon in the status bar.
8. Save your changes to the report and check in the Excel object.

Enable the HFM Smart View Add-in

You can enable the HFM Smart View Microsoft™ Excel add-in in CDM so that it can be used in Excel objects. HFM Smart View must be installed before it can be enabled.

1. Open the report that you want to work with.
2. Open and check out the Excel object that you want to edit.
3. Click the Section View Mode icon in the status bar.
4. In the data area, enable add-ins.
5. Click the Default View Mode icon in the status bar.
6. Save your changes to the report and check in the Excel object.

Connect to an HFM Data Source

You must provide connection details to a Hyperion Financial Management (HFM) data source to use so you can create and refresh HFM formulas at the report object level in CDM.

There must be HFM data sources defined in CDM and you must have an Excel object open and checked out in add-in mode.

When HFM is installed and HFM data sources are defined, the Connect to an HFM Data Source window will be displayed.

To connect to an HFM data source:

1. If a default HFM connection exists and you want to use that connection, select the Use HFM default connection checkbox.
2. To select another HFM data source, ensure that the Use HFM default connection checkbox is not selected and specify the following connection parameters:
   • User Name - Enter the username of the account.
   • Password - Enter the password for the account.
   • Name - Select the data source.
   • Machine Name - This name appears after the server connection is made. It is not editable.
   • Cluster name - This name appears after the server connection is made. It is not editable.
   • Application - The name of the application that holds the HFM data. This field is not editable.
   • Domain - Enter the domain name of the server that contains the HFM data.
   • Url - Enter the URL of the HFM server.
3. Click OK.

   Tip: After you initially log in to the HFM server, you are automatically logged in after each refresh, check in, or check out of an Excel object for the same session.