Data Extraction Wizard

The Data Extraction Wizard offers many useful functionalities when creating and customizing a data exrtraction. This section offers information on what the Data Extraction Wizard is, what its properties are, how to perform a data extraction, and how to drill down on a data extraction.

With the Data Extraction Wizard, you can extract raw data from a database to create reports. Once you have extracted the data, you can use formulas to customize your report and achieve your desired results.

The output of a data extraction is a table that allows you to format the header, total row and additional rows. You can also add extra columns. All customizations to the extraction will continue to work even after refreshing.

You can also use the Drill Down Wizard on a data extraction to get more specific information about the formulas in the extraction. The Data Extraction Wizard is located under the Analysis tab of a workbook.

Benefits

Why use the Data Extraction Wizard?

What uses cases can the Data Extraction Wizard answer?

Perform a Data Extraction

The following steps are a general procedure for creating a data extraction. Refer to the eLearning for a more specific version of these steps.

1. In the Data Extraction window, set the name of the extraction.
2. Add the fields and options for the data extraction (refer to Data Extraction Wizard Properties above for more information).
3. Add the filters you want to apply to the data extraction (refer to Data Extraction Wizard Properties above for more information).
4. Select Create. This creates a table based on the options you selected in the Data Extraction window. You can further format this table to create adjacent columns for formulas you want to include.

Important

When customizing column headers in a data extraction, each header must have a unique identifier; reusing names is not permitted, necessitating the creation of a distinct new column name for each customization.

Drill Down on a Data Extraction

You can drill down on a data extraction to get detailed information about a measure in a data extraction. To drill down on a data extraction:

1. Select a cell that contains a measure.
2. From the Analysis tab, click on Drill Down Wizard.
3. Set the Profile and appropriate fields in the Drill Down Wizard window. (For more information on setting up a drill down profile, refer to Using the Drill Down).
4. Click Update to only update the profile or click Update and Drill-down to both update the profile and display the formula drill-down. The drill-down table is displayed on the right-hand side.

Data Extraction Wizard Properties

Fields

Property	Description
Environment	Choose the environment for the data extraction. If you select the current environment, it will take data from the environment you are currently viewing (as selected in the title bar).
Data Model	Select the data model that you want to perform a data extraction on.
Extraction Name	Create a name for the data extraction. Note that: This name needs to be unique within the entire Workbook as it creates a table definition in the workbook. The name can only contain alphanumerical characters and underscores.
Fields	Group By: Choose which dimensions, descriptions, and measures to group the data by. Order By: Sort data in ascending order, descending order, or no order. Aggregation: Choose which operation (Min, Max, Count, Count Distinct, or None) to perform on the field. Note that any further operations performed on data in the data extraction will be based off the aggregation defined in the Data Extraction Wizard, not the raw data. Reset Group By: Reset all Group By, Order By, and Aggregation options you have set for your fields. Delete: Remove the dimension, measure or description from the data extraction.

Options

Property	Description
Refresh on Open	The data extraction refreshes automatically when the workbook is opened (toggle to enable/disable).
Automatic Refresh	The data extraction refreshes automatically when a filter that references it is changed (toggle to enable/disable).
Show Headers	Show headers in the data extraction (toggle to enable/disable).
Show Total Rows	Show the Total row of the data extraction (toggle to enable/disable).
Top X	Choose how many rows of data you want to appear in your data extraction.
Worksheet	Select the worksheet where the data extraction is created. Existing Worksheet: This will use an existing sheet within your workbook and create the Data Extraction in the cell defined. Location: This will display the sheet and the cell where the Data Extraction will be created. New Worksheet: This will create a new sheet in your workbook and put the Data Extraction in the first cell of the sheet.
Location	Set the cell location within the worksheet where the data extraction is created.

Filters

Property	Description
Add Filter/Add Group	Select Add Filter to add filters to the formula or select Add Group to add a set of filters that are grouped together. For both, you must also select the Field, Operator, and Value. The Value can be a referenced cell. You can use the prompt to select the field. Reference a cell Details If you wish to reference a cell, click the underscore in the Value field and select the cell. You can also enter the value in the cell. This is also applicable for Groups. Cell reference character values Details When you enter a character value, the workbook assumes that it is a cell reference by default. To specify the value as text, click the icon; its color then changes to blue . When selecting a character value using the prompt, the value is set to text automatically.
And / Or	Set Filters or Groups to AND or OR by clicking on the corresponding button.
Clear All Filters	Clear all filters and groups in the Data Extraction Wizard.
Cancel	Close the Data Extraction Wizard window (formulas are not saved automatically).
Create	Create the data extraction.

Additional Properties

Property	Description
Format Data Extraction Table	Data Extractions maintain the formatting applied to each row. The format applied to the entire column is based off the styling applied to the first row.
Customize Headers	In a data extraction, you can replace the default column headers, which are usually auto-generated from field descriptions within the data model. Once a data extraction table is created with these customized headers, the changes are retained. Subsequent refresh actions on the data extraction do not revert these headers to their original data model auto-generated names, ensuring that any customizations remain intact.
Add Adjacent Columns	Along with the data extraction created, formula columns can be added next to the Data Extraction which will also be included as a definition of the extraction. For example, formulas created using Formula Wizard or a simple Excel formula. The moment the cell is dedicated as a formula (includes '=') then it gets automatically added to the Data Extraction definition. In addition, the order of the columns, including the adjacent columns, can be changed by accessing the Data Extraction Wizard again. Select the ---- icon to click and drag the fields in the order you want. gif here