Working with Data

This chapter covers the following topics:

Viewing Data

To view your data

  1. Click File > Open. Or click the Open button.

  2. Click a worksheet in the list and then click Open.

    Depending on how Oracle Demantra was configured, it may or may not automatically run the worksheet that you open.

  3. If the worksheet does not show data immediately, click Data > Rerun. Or click the Run button.

To prefilter a worksheet

Depending on how the worksheet was configured, when you open the worksheet, Oracle Demantra may prompt you to choose how to filter it.

In this case, after you click the worksheet and click Open, one or more selection pages are displayed when the worksheet, as follows:

the picture is described in the document text

  1. On each selection page, select one or more choices or click Select All to select the whole list.

  2. Click Next or click Finish.

To load all data

When you first run a worksheet, Oracle Demantra does not load all the data. Instead, it loads data for the first item-location combination in the worksheet. When you switch to another combination, Oracle Demantra loads data for that combination. It does this so that you can see some data without having to wait for all the combinations to be loaded.

If worksheet was previously offline

If you previously took this worksheet offline and made changes to the data, you will see the following dialog box as soon as you open the worksheet:

the picture is described in the document text

For information on these options, see Reloading Offline Data.

Editing Data

You can usually edit data in a worksheet, although not all series are editable. Note that within a given worksheet, if some of the tabs or windows show data at a higher aggregation level, the data is not editable in those tabs or windows.

Note: In Oracle Demantra, do not use the special characters single quote ('), double quote ("), and ampersand (&). If you use these characters, unexpected results may occur.

To change a data value manually

  1. Select an editable data cell (white).

    The cell appears contained in a box.

  2. Edit the value as needed. If the cell contains a date, then you can click the arrow on the right side of the cell and choose a date:

    the picture is described in the document text

    Or if the cell contains a dropdown list, double-click the cell to display the list and then click a list element:

    the picture is described in the document text

To update multiple cells

  1. In the table, select a range of cells.

  2. Right-click the selection and then select Edit Cells.

    The Edit Cells dialog box appears.

  3. In the dialog box, type a new value and then click OK.

To undo

Do one of the following:

To redo an action

Click Edit > Redo.

See also:

Saving and Viewing Changes

Apart from minor adjustments to the worksheet layout, the changes you make are not saved automatically.

Saving Changes

You save changes to data separately from changes to the worksheet definition:

See Worksheet Definition, Layout, and Local Adjustments.

Viewing Pending Changes to Data

To see pending data changes

  1. Click Data > Update to save your data changes.

  2. Click Data > Process Monitor. Oracle Demantra displays a screen that shows all data saves that are waiting to be processed:

    the picture is described in the document text

    If you are working at a relatively high level, this takes longer to run.

Sorting Worksheet Data

By default, the worksheet table is sorted in ascending order, according to the current contents of the x-axis, which is usually time. You can sort the worksheet table in either ascending or descending order, according to any series that is displayed in the table.

To sort the worksheet table

Double-click the header of the column by which you want to sort the worksheet.

Oracle Demantra then sorts the entire worksheet table, putting entries in this column in descending order. A downward arrow is added to the column header, to indicate that the table is sorted by this column.

the picture is described in the document text

To resort in ascending order, double-click the column header again. Oracle Demantra resorts the table and changes the direction of the arrow.

To restore the default sorting order of the worksheet table

Click View > Reset Table Sort.

Copying and Pasting Data

You can copy and paste data within the worksheet table.

Note: From a worksheet, you can also paste data to different Windows products.

To copy data

  1. Within a worksheet, do one of the following:

    • In the table, select a cell whose value you want to copy.

    • To copy values from more than one cell, click the first cell, and then drag the mouse to select all cells you want to include.

  2. Right-click and then select Copy. Or click Edit > Copy.

To paste data

  1. Do one of the following:

    • In the table, select the cell into which you want to paste the data.

    • To paste values into more than one cell, select the upper left data cell of the block of cells into which you want to paste.

  2. Right-click and then select Paste. Or click Edit > Paste.

See also Saving and Viewing Changes.

Copying and Pasting to and from Microsoft Excel

You can copy and paste data from a worksheet to Excel and vice versa.

To copy and paste data to Excel

  1. Within a worksheet, copy data as described in Copying and pasting Data.

  2. Start Microsoft Excel, open a worksheet, and select a cell or block of cells.

  3. Right-click and then select Paste.

To copy and paste data from Excel

  1. Start Microsoft Excel and open a worksheet.

  2. Select one or more cells.

  3. Right-click and then select Copy.

  4. In Oracle Demantra , select the cell or cells into which you want to paste the data.

  5. Right-click and then select Paste. Or click Edit > Paste.

See also Exporting Data.

Exporting Data

You can export worksheet data to a tab-delimited file with UTF-16 encoding, which can then be viewed in external programs like Microsoft Excel. Exporting data from a worksheet can also be used to create import templates. However, depending on the options you select when exporting data, this data may not be compatible with Demantra's import format. That is, data must be exported a certain way for it to be suitable for re-import back into Demantra later. For example, when a worksheet includes a members browser, the members selected in the browser are concatenated in a string in the export file.

The export format includes a summary line, something that would not likely be included when importing data. When exporting data, you may select options that create an import-ready format. The summary line can be included or excluded using the IncludeSummaryExportToFile parameter. This ensures that the data is fully described and, optionally, will eliminate the summary line from the export. Enabling the Fully Described check box labels all rows and columns in the export file with combination information. If this option is disabled, then only one label is included at first row of data per combination. This check box can be configured using the FillValueExportToExcel parameter. As well, you may use the OpenFileAfterExport parameter to automatically open the file with whatever program is associated with UTF-16 (.csv) files. Enable the Fully Describe Data option to label all rows and columns with combination information. If this option is disabled, then only one label is included at first row of data per combination. For more information on setting these parameters, see System Parameters in the Oracle Demantra Implementation Guide.

The export to Microsoft Excel uses memory on the Oracle Demantra server to complement the memory on your personal computer. It can export any amount of data. However, for extremely large sets of data, the export process may split the exported data among multiple Excel worksheets. This is because Excel has a limit on the maximum number of rows in a worksheet.

The best format for export will have 'Fully Describe Data' checked and 'Include Summary Line' unchecked. These options are explained in the procedure below. Also, a worksheet cannot include any hidden levels, as they will create summary expressions that are incompatible with import. Be sure to remove any hidden levels from the worksheet before exporting data. Selecting these options results in fully described data in the worksheet, as follows:

The import file should contain a cell with the ' / ' characters, which will mark the end of the Header Titles area. Since the file can contains the Level/Series/Time Axis in different order than exist in the Worksheet, there is no other way to identify the Header Titles area than searching for the first cell which contains the ' / ' characters.

To export data:

  1. Within a worksheet, click File > Export, or click the Export Data button.

    An export dialog box appears.

  2. Select from the following options:

    • In the Content Selection section, choose Current Page Items to export data from this worksheet page, or Entire View to export all worksheet data.

    • Enable the Fully Describe Data option to label all rows and columns with combination information. If this option is disabled, then only one label is included at first row of data per combination.

    • Enable the Include Summary Line option to include a summary line in the exported data.

    • Enable the Open File after Export option to automatically open the file after export. The application used to open the file is determined by the 'Opens with' application associated with files of type .xls.

  3. Click OK.

    A Save page appears.

  4. Define the location where the worksheet data will be saved and enter a name for the file.

  5. Click Save.

    Note: Your computer's regional settings can affect the results of a data export. When receiving data from an external source, Microsoft Excel uses the Regional Options in the Windows Control Panel to determine whether a given cell should be formatted as Currency or General (as is or text). This means that if you export Oracle Demantra data that is formatted with a currency symbol, and if the Windows Control Panel uses a different currency symbol, those cells will appear in Excel as text values.

    If you export to Excel and you receive text values where you wanted currency values, do the following:

    • Open the Windows Control Panel.

    • Double-click Regional and Language Options.

    • On the Regional Options tab, make sure that the Currency setting uses the same currency symbol as Oracle Demantra .

    • Export again from Oracle Demantra .

    Microsoft Excel formats date cells based on the default date format of the local machine. If you export a worksheet into Microsoft Excel and the dates do not match, change the default date format of the local machine to match the date format of the Oracle Demantra worksheet. Then, restart Microsoft Excel.

Later versions of Excel provide an option for converting problematic cells that it recognizes.

See also Copying and Pasting to and from Microsoft Excel.

Importing Data

Exporting data from a worksheet can be used to create import templates. This template is a standard tab-delimited file with UTF-16 encoding. Since this is a standard tab-delimited format, you may choose to generate the file manually, ensuring the structure is compatible with the worksheet structure. The following rules must be followed in using files for import in Demantra:

Invalid import file entries are copied to an error file which is located in the user_home/demantra/import folder. For example, C:\Documents and Settings\Owen\demantra\import.

Note: The data import mechanism is intended for smaller amounts of data (typically on the order of hundreds of rows), and is not a substitute for the EP_LOAD and workflow-driven integration via transfer steps. During the import process, users have the option to preview the data in a worksheet before the data is saved to the database.

Note: Date formats in the import file must match the display format in Demantra's import locale.

Any summary line(s) in the import file will be ignored and noted in the log file.

To import data

  1. Within a worksheet, click File > Import, or click the Import Data button.

    The Import Data dialog appears.

    the picture is described in the document text

  2. Select one of the following options:

    • Bulk Upload of all Values: Choose this option to commit the imported data to the database without reviewing it in the worksheet.

    • Upload to worksheet and view before saving: Choose this option to preview the imported data in a worksheet before saving to the Demantra database.

  3. Click OK.

  4. The Import Data dialog appears.

  5. Select the tab-delimited, Unicode text file that you want to import, and then click Import.

    Demantra loads all valid values from the import file to Demantra. If the import file format is incompatible with the worksheet (for example, it does not contain all the required levels, or includes more levels than are in the worksheet), then an error message appears. In this case, data is not loaded into the worksheet and the import file must be corrected before re-importing. If the import completes with errors, you can open the log file (.xls file) directly from the error dialog to view the errors.

    If you chose Bulk Upload of all values' in step 3, the changes are saved to the database automatically. If you chose to upload the data to the worksheet and view before saving, you must manually save the data after reviewing it, if desired.

    Note: An icon may appear in any cells that contain new or changed data. This is controlled by the ChangedDataIndicator parameter. For more information, see System Parameters in the Oracle Demantra Implementation Guide.

Linking Data into Third-Party Reporting Tools (through DOL)

When you work within a third-party reporting tool, you can import a Oracle Demantra worksheet through Oracle's Dynamic Open Link (DOL) technology. This technology lets you use any reporting tool that can connect to the Oracle Demantra database.

These steps describe how to link Oracle Demantra data into Microsoft Excel, from

Note: The details here may vary depending on the version of Excel. Consult the Excel documentation for information about “web queries.”

To link Oracle Demantra data into Microsoft Excel, from outside

  1. Enter the DOL login URL on a browser, for example, http://myserver:8080/demantra/portal/DOLLogin.jsp.

  2. Log into Oracle Demantra.

  3. Copy the right worksheet URL from the middle column on the received page.

  4. Open Microsoft Excel > Data > Import External Data > New Web Query. This is the Microsoft Excel 2003 path.

  5. Paste the copied DOL Web Query URL into the address edit field and click Go.

To link Oracle Demantra data into Microsoft Excel, from within

  1. Open Microsoft Excel > Data > Import External Data > New Web Query. This is the Microsoft Excel 2003 path.

  2. Enter the DOL login URL on a browser, for example, http://myserver:8080/demantra/portal/DOLLogin.jsp.

  3. After the table page is loaded, click on the Open URL link of the relevant worksheet.

Printing

When you print a worksheet, the printed results match the screen display (what you see is what you get).

To print the displayed data

  1. Click File > Print. Or click the Print button on the toolbar.

    The Print Settings dialog box appears.

    the picture is described in the document text

  2. Select the required options.

    Content selection Specify whether to print the currently selected item-location combination, all combinations in the worksheet, or the screen.
    Layout Choose portrait or landscape orientation.
    Paper Choose a paper size.
    Font Choose a font size.
    Fit Select Scale width to page to force the columns to print on one page left-to-right, without your having to resize the worksheet columns. The output uses the paper size and print orientation that you choose. It respects the font size that you choose as much as possible but may use a smaller font if necessary to accommodate the paper size and print orientation.

  3. If this worksheet includes a Gantt chart, optionally click the Gantt Settings tab. On this tab, specify which promotion attributes to include in the printout.

  4. Click OK.

  5. Oracle Demantra displays the normal system print dialog box, where you choose the printer to use, the number of copies to make, and so on.

  6. Make selections as you would normally do.

  7. Click OK.

Viewing Notes

You can add notes to record comments and reminders. You attach the note to combination and date or dates. The worksheet table displays indicator symbols on those dates, generally in only some of the columns (depending on how your system is configured). The following shows an example of the indicator:

the picture is described in the document text

The graph displays an indicator as well:

You can see notes in two different areas:

the picture is described in the document text

Notes/Attachments subtab, if this is included in your worksheet. This subtab also displays any associated attachments.

the picture is described in the document text

To open the Notes page

Do one of the following:

To display the Notes/Attachments subtab

See Specifying the Worksheet Elements in a View.

Editing Notes

First display the Notes page or the Notes/Attachments subtab; see Viewing Notes. In the upper part of the worksheet, click the element to which you want to attach this note.

To add a note

  1. If the Note box is gray (uneditable), click the Add button.

  2. Type a note in the Note box.

  3. If you are working in the Notes/Attachments subtab, you can optionally upload an attachment (see Viewing and Managing Attachments).

  4. Click the Save button.

To edit a note

  1. Select a note and click the Edit button.

  2. Type a note in the Note box.

  3. If you are working in the Notes/Attachments subtab, you can optionally upload an attachment (see Viewing and Managing Attachments to Notes).

  4. Click the Save button.

To delete a note

  1. Select a note and click the Delete button.

    The Confirm Delete message box appears.

  2. Select one of the following:

    • Partial Deletion (deletes the note only from the current member and date)

    • Total Deletion (deletes the note from all members and dates)

  3. Click OK.

  4. Click Save.

See also:

Viewing and Managing Attachments to Notes

You can attach files to the notes, and Oracle Demantra uploads those files into the database (in compressed form). To view and manage attachments, you work in the Notes/Attachments subtab of your worksheet.

To add an attachment to a note

  1. Click the note.

  2. Click Details.

    Oracle Demantra displays the Note Details screen:

    the picture is described in the document text

  3. Click Add.

  4. Find the file that you want to attach and then click Open.

    Oracle Demantra uploads a compressed version of the file into the database.

  5. Click OK.

To remove an attachment from a note

  1. Click the note.

  2. Click Details.

    Oracle Demantra displays the Note Details screen:

  3. Click the row that lists the attachment and then click Remove.

  4. Click OK.

To view and download an attachment

  1. Click the note.

  2. Click Details.

    Oracle Demantra displays the Note Details screen:

  3. Click the row that lists the attachment and then click:

    • View if you want to see the attachment immediately. Oracle Demantra recognizes the attachment file type and opens the application for viewing it.

    • Download... if you want to save it.

See also:

Specifying Note Permissions

In general, notes (and their optional attachments) can be viewed by other users. You can specify permissions for the notes, to determine who can read the note and what type of access they are permitted. You can specify permissions for groups or for specific users.

To specify permissions for a note and its attachments

  1. On the Details tab, select the note.

  2. Click the Permissions tab.

  3. Select the User Permissions or Group Permissions tab.

  4. For each user or group that should have non-default settings for this note, move that user or group from the left list to the right list (Selected Users or Selected Groups). Use any of the techniques in Working with Lists.

    Note: Read-only is the default setting for non-selected users or groups.

  5. Remove any unwanted users or groups from the right list.

  6. Then for each user or group in the right list, do the following:

    • Click the user or group.

    • Select a setting from the Permission dropdown list at the bottom of the page. The following options are available:

      Read and Write Enables the user or group to read the notes list, edit notes, and add notes.
      Read Only Enables the user or group to read the notes list.
      Not Visible Makes the note list not visible to this user or group.
      Full Control Enables the user or group to read the notes list, edit notes, add notes, and delete notes.

  7. Click Save.

  8. Click Close.

Group and user permission priorities

User permissions take precedence over permissions of any groups the user belongs to.

If a user belongs to more than one group, the highest level of permissions from the associated groups will be granted.

See also Viewing Notes.

Refreshing the Local Worksheet Cache

Depending on the definition of a worksheet, Oracle Demantra may store a local copy of the worksheet data on your machine, for faster performance. Also depending on the worksheet definition, the cache may be refreshed automatically or may require periodic refreshing by you.

To refresh your local worksheet cache

Click Data > Refresh worksheet data cache.

See also:

Viewing the Audit Trail

Oracle Demantra automatically records changes in an audit trail. The audit trail includes all changes due to user edits within worksheets, updates created by the Business Logic Engine, copy/paste actions, and changes caused by import.

Note: You can see only changes that were made by you or other users who are in the same collaboration groups as you.

Oracle Demantra provides two ways to view the audit trail:

In either case, the audit trail looks like this:

the picture is described in the document text

The top part of the page lists the changes. When you select a row in this table, the bottom part of the page lists details corresponding to that row.

Viewing the Audit Trail for a Specific Combination

To view the audit trail for a combination

  1. In a given worksheet, select a combination.

  2. Click Data > Audit Trail.

    If any changes have occurred to this combination, the Audit Trail screen appears, displaying a table with one row for each recorded change. The following information is displayed:

    Audit Date Date when change was made.
    User Name User who made the change.
    Update Mode Indicates the type of change: Manual, BLE (Business Logic Engine) or Integration.
    Unit Name Unit of measure that was used at the time of the change.
    Index Name Index that was used at the time of the change.
    Series List Series affected by the change.
    Status In Progress, Fail, or Success.

  3. To see specific details on this change, click a change in this table and then click the Series Values tab.

    This tab displays the following information:

    Series Name Name of the series that was changed.
    Series Value New value used in this series.
    Original Value Original value in this series.
    From Date First date in the series when the change occurs.
    To Date Last date in the series when the change occurs.

  4. To see the specific aggregation levels to which this change applies, click the Aggregate Level Population tab. The change could have occurred at a different aggregation level.

  5. To see the filtering that was used at the time of this change, click the Filtered Population tab.

Viewing the Audit Trail for Other Data

An audit trail profile enables you to select a specific portion of the audit trail so that you can view changes that affect more than one combination.

To create or modify an audit trail profile

  1. Click File > Audit Trail.

  2. Then:

    • To create a new profile, click New.

    • To modify a profile, click an audit trail profile name and click Modify.

      The wizard displays a properties screen.

  3. In the Name and Description fields, type a name and optional description.

  4. If you want to share this audit trail profile with others, click Public.

  5. Click Next.

    The wizard prompts you for information about the changes you want to see.

  6. For Source Type, select the check box for each type of change you want to see. Here, BLE represents the Business Logic Engine.

  7. For each user whose changes you want to see, move the name from Available Users to Selected Users.

  8. Click Next.

    The wizard prompts you for the range of time you are interested in.

  9. For Time Filter, select Relative or Fixed, depending on whether you want to define a window of time relative to today.

  10. For From Date and To Date, specify a range of dates.

  11. Click Next.

    The wizard prompts you to specify the aggregation levels you are interested in.

  12. For each aggregation level that you are interested in, move that level from Available Filter Levels to Selected Filter Levels.

  13. Click Next.

    The wizard prompts you to specify the series you are interested in.

  14. For each series that you are interested in, move that series from Available Series to Selected Series.

  15. Click OK.

  16. Click Close to exit the wizard.

To use an audit trail profile

  1. Click File > Audit Trail.

  2. Click an audit trail profile.

  3. Click Execute.

    Oracle Demantra displays the specified part of the audit trail. See Viewing the Audit Trail for a Specific Combination.

To delete an audit trail profile

  1. Click File > Audit Trail.

  2. Click an audit trail profile.

  3. Click Delete.

  4. Click Yes.