Extracting Data

Once you are connected to the Longview data server, you are ready to work with the data contained within it. This chapter explains how to use the Longview Add-In for Office to extract data.

Understanding data extract output

You can use the Longview Add-In for Office to extract data from the data server repository and output it to a Microsoft Excel spreadsheet. Output data appears as a flat table where each data value is represented as a row and each dimension is represented as an intersecting column. This output format can be conveniently used in a Microsoft Excel Table or a PivotTable, which can further organize the data.

You can configure the data extract to your preferred settings in the Data Extract dialog. You can also save your preferred settings and use them to re-run the data extract.

Note: When the extract is rerun with Varying set to another option (for example, Timeperiods), the extract displays each selected time period as a separate column. The column headers reflect the individual time-period symbols, making it easy to compare values across periods within a single row.

Extracting base data

To extract data:

1. Click the Longview tab in Microsoft excel.
2. In the Insert group, click Data Extract. The Data Extract dialog opens with the Data Options page in view.

   Note: You must be connected to the data server to run a data extract.



3. Complete the following fields:

   Field	Description
   Schedule	Select a schedule from the drop list if you want to query data associated with a schedule. Note: Only schedules that have been defined in the user attribute UAccSchedules will be available in this drop list.
   Varying	Select the dimension you want to pivot in the data extract results. The selected dimension’s members appear as column headers instead of row values. When set to ‘None’ (default), all dimensions appear as row values with a single Value column.
   Cell Location	Specifies where the extract output begins when placed into a worksheet. Change this if you want the extract to load into a specific part of an Excel sheet rather than the default top-left position. If you must place multiple extracts in a single worksheet, Longview recommends that you ensure that the extract results do not overlap.

4. Specify the active dimensions for your extract:

• Displays the name of each Longview dimension (e.g., Accounts, Timeperiods, Entities).

5. For each dimension in your system, specify the following symbol specifications:

   Note: Certain combinations of Symbols, Spec, and Level are restricted because they will not return a valid set of results. If you attempt to use one of these combinations, either the combination will be restricted (for example, if you specify Leaf for Spec, the Level field is unavailable), or you will receive an error message.

   Specification	Description
   Symbols	Type a symbol name or click the symbol selector button to search the hierarchy for the selected dimension. For more information, see Using the Symbol Selector.
   Spec	Specify the type of symbol to query, using one of the following options from the drop-down list: All — To query all symbol types. Leaf — To query only leaf symbols. Parent — To query only parent symbols. Root and Parent — To query only root and parent symbols. The default value for this field is Leaf.
   Level	Type the number of levels of descendants of the selected symbol to appear in the results. You can select up to 99 levels of symbol detail. If you type 0, only the symbol you selected will appear. To include the selected symbol and one level of symbol detail below it, type 1. If you select 99 levels of symbol detail, all levels of symbol detail are included in the results.

   Note: Large extracts take longer to complete than smaller extracts. Be as specific as possible with the data intersection you are querying.

6. To add additional symbols to extract for a dimension, complete the following steps:
   1. Select the applicable dimension row.
   2. Click Symbol. A new row appears.
   3. For the new symbol, specify the symbol specifications, as described in step 5.
   4. Repeat step a to step c for each additional symbol that you want to include in your data query.

   Note: To delete additional symbols that you have added, select the row containing the symbol and click Delete. You can also change the order in which the symbols will appear in the query results by selecting the row containing the symbol and clicking Move Up or Move Down.

Specifying Query options for Data Extracts

Complete the following fields:

Do one of the following:

• To specify the format options for your data extract, proceed to Specifying format options for data Extracts.
• To run the data extract with the default format options, proceed to Running a data Extract.

Note: You may also quickly populate the Data Extract dialog with extract selections that you have previously saved by using the Load Query button. For more information, see Saving and loading query selections.

Specifying format options for data Extracts

Before you run a data extract, you can specify how the results of the extract are formatted in the Microsoft Excel worksheet. For more information on the layout of the query results, see Understanding data extract output.

To specify format options:

1. Click Format Options in the left pane of the Data Extract dialog. The Format Options page opens.

   

2. In the General section, complete the following fields:

   Field	Description
   Output for Each Dimension	Select whether each dimension in the extract displays the symbol name only, or both the symbol name and symbol description in separate columns. Options: One column: Symbol Name - Displays symbol name only for each dimension. Two columns: Symbol Name, Symbol Description - Displays both symbol name and description in separate columns for each dimension. The default for this field is One column: Symbol Name Note: When a varying dimension is selected, this setting also controls how the pivoted column headers display: One column: Shows symbol names only (e.g., ‘P01YTD’, 'P02YTD') Two columns: Shows symbol name and description combined (e.g., 'P01YTD - Period 1 - YTD', 'P02YTD - Period 2 - YTD')
   Sort Order	Specifies how dimension members are sorted in the extract output. Options: Hierarchically - Dimension members appear according to their hierarchy structure. Alphabetically - Dimension members appear in alphabetical order (A-Z), regardless of hierarchy. The default for this field is Hierarchically.

3. In the Data section, complete the following fields:

   Field	Description
   Exclude Zeros	Controls whether rows with zero values are included in the extract output. Options: All - Excludes all rows where values equal zero. Do not exclude - Includes rows with zero values in the extract. The default value for this field is All.
   Separate Numeric and String Values	Select this field to output numeric and string values in separate columns, which is useful when downstream processing or formulas require numeric-only fields. When not selected, all values appear in the same column regardless of type.
   Use Thousand Separator	Select this field to display numeric values with thousand separators based on your system's regional settings. This improves readability of large numbers in the extract output. Default is unchecked.

4. Proceed to Running a Data Extract.

Running a data Extract

When you are finished specifying the data and format options for the extract, you can run the extract.

To run a data extract:

1. Click Run Extract.
2. Click the File tab.
3. Click Save.
4. If you are saving the workbook for the first time, the 'Save As' dialog opens. Enter a name for the workbook and click Save.

Rerunning a data Extract

You can use the Longview Add-In for Office to rerun an existing data extract. Rerunning a data extract refreshes data values in the extract and allows you to modify the extract as necessary.

When you open a workbook containing data extracts, the associated values are not updated to show the latest values in the Longview data server repository. The data will be exactly the same as when you last saved and closed the workbook. To see the most up-to-date data from the data server repository, you must rerun your data extracts.

To rerun a data extract:

1. Right-click any cell in the results of the data extract that you want to rerun and select Rerun Extract. The Data Extract dialog opens, with the Data Options page displayed.
2. Make any changes to the data extract as necessary.
3. Clear or select the 'Clear previous results' checkbox.
• Selected (default): Removes all existing extract results and formatting before displaying the new results.
• Cleared: Retains existing extract results and formatting, overlaying the new results on top of the existing data.

4. Click Run Extract.

Note: Any existing data and formatting (font, borders, or alignment for example) is retained when you reuse the existing worksheet or worksheets with the Clear previous results check box not selected. This includes formatting automatically applied to the existing data extract results.

Saving and loading Query selections

You may save the extract selections that you have selected in the Data Options and Format Options tabs of the Data Extract dialog. This will enable you to quickly prepopulate the Data Extract dialog in the current session or within a future session.

The extract selections are saved to a file. You can name and save the file in a local or network drive. This enables you to give the extract a meaningful name and the ability to share your extract with others.

Saving Query selections

To save your extract selections, you must first specify the selections you wish to save in the Data Extract dialog. For more information, see Extracting base data.

To save data extract selections:

1. Complete the fields in the Data extract dialog.
2. Click Save Query.
3. The Save As dialog opens. Enter a name for the extract and click Save.

Loading Query selections

You may quickly prepopulate the Data Extract dialog with selections from a saved query.

To load data extract selections:

1. Click the Longview tab.
2. In the Insert group, click Data Extract. The Data Extract dialog opens with the Data Options page in view.

   Note: You must be connected to the data server to run a data query.

3. Click Load Query.
4. The Open dialog appears.
5. Select the extract file that you wish to load and click Open.
6. The fields of the Data Extract dialog are populated with the extract selections from the selected file.
7. You may modify these selections before running the extract.