Work with a Workbook's Datasets

This topic describes what you need to know to work with a workbook's datasets.

Topics:

• Add Datasets to a Workbook

• Replace a Dataset in a Workbook

• Remove a Dataset from a Workbook

• Modify a Workbook's Datasets

• Blend Datasets

Add Datasets to a Workbook

Your workbook can use more than one dataset.

When you add more than one dataset, Oracle Analytics blends the data. You can check the default blending, or update or add data blending between datasets. See Blend Datasets.

1. On the Home page, hover over a workbook, click Actions, then select Open.
2. In the Data panel, click Add, and select Add Data.
   
   Description of the illustration data_set_add_to_project_button.jpg
   
3. Optional: Enter search criteria and click Enter to display matching results. See Search Options.
   Tip: When you browse datasets, to see the name of the data model where the data originated, hover over datasets icon (in Grid View) or hover over the column name (in List View).
4. Select a dataset, then click Add to Workbook.

Replace a Dataset in a Workbook

When you replace a dataset, Oracle Analytics suggests how you can re-map the columns from the dataset that you replaced to the replacement dataset. You can accept or update these suggested mappings.

For example, you might replace a workbook's test dataset with a production dataset. Or you can copy a workbook and its visualizations, and then use the duplicate workbook as a template. After you rename the duplicated workbook, you can add a different dataset.

When you replace the dataset, any data mapping that you specify is applied to the workbook. For example, if you map a data element to None, the specific data is removed from the workbook's visualizations, calculations, and filters.

1. On the Home page, hover over the workbook to use as a template, click Actions, and then select Open.
2. From the Visualize page, click Data.
3. In the Data page, click the Data Diagram.
4. Locate the dataset to replace and click Actions.
   
   Description of the illustration data_set_replace_data_set_in_project.jpg
   
5. Select Replace Dataset.
6. In the Replace Dataset dialog, click the replacement dataset. Click Select.
7. Review, update, and specify the column mappings for the workbook's visualizations, calculations, and filters.
8. Click Replace.
9. Optional: If you want to add blending between datasets, then in the Data Diagram, click between the datasets that you want to blend, and in the Blend Data dialog add column matches.

Remove a Dataset from a Workbook

You can remove a dataset from a workbook. When you do this, you remove all of the dataset's columns from the workbook's visualizations, filters, and calculations.

1. On the Home page, hover over a workbook, click Actions, then select Open.
2. In the Visualize canvas, go to the Data Panel pane, right-click the dataset that you want to remove, and select Remove from Workbook.
   
   Description of the illustration data_set_remove_data_set_from_project.jpg
   
3. Click Save.

Modify a Workbook's Datasets

You can use the Workbook Editor's Data page to navigate to the Dataset editor or Transform editor to view or modify a workbook's datasets.

When you locate a dataset's icon in the data diagram and click Edit, a new browser tab opens that contains the editor that you use to view or modify the dataset. The Workbook Editor remains open in its own browser tab and immediately reflects any saved dataset changes.

Description of the illustration navigating-project-editor-data-tab-data-set-editor.gif

Note:

A dataset can be used in multiple workbooks and data flows. Modifying a dataset impacts all workbooks and data flows that use the dataset.

See About the Dataset Editor and Enrich and Transform Your Data.

1. On the Home page, hover over a workbook, click Actions, then select Open.
2. In the workbook editor, click Data to go to the Data page.
3. In the data diagram, locate the dataset and click its Open button.
   The tab that's displayed depends upon the dataset you selected:

   • If the dataset uses an Oracle EPM Cloud, Oracle Essbase, or Google Analytics connection, then the Transform Editor is opened in a tab.
   • If the dataset uses a connection that supports multiple tables (for example, Oracle Database or Oracle Autonomous Data Warehouse), then the Dataset Editor is opened in a tab.
4. Optional: If the Dataset editor is displayed, click the tab for the table that you want to view or modify and the Transform Editor is displayed.
5. Modify and save the dataset.

Blend Datasets

This topic explains blending and how to blend a workbook's datasets.

Topics:

• Understand Blending

• About Mismatched Values in Blended Data

• Blend Datasets

• Change Data Blending in a Visualization

Understand Blending

When you add more than one dataset to a workbook, Oracle Analytics tries to match columns between the datasets that you added. This matching is called blending.

For example, Dataset A might contain new dimensions that extend the attributes of Dataset B. Or Dataset B might contain new facts that you can use alongside the measures that already exist in Dataset A.

Blending automatically matches external dimensions where they share a common name and have a compatible data type with attributes in the existing dataset.

Datasets that aren't blended are divided by a line in the workbook's Data Panel.

There are restrictions between data elements and visualizations when a workbook contains datasets that aren't blended. For example, if your workbook contains two datasets that aren't blended, then you can't include the data elements of one dataset in the filters, visualizations, or calculations of the other dataset.

Blending relationships are stored in the tables and not in the workbook. So any blending matches that you add or remove impact the other datasets that use the same tables.

Datasets that use Oracle Essbase or Oracle EPM Cloud connections aren't available for blending.

Your workbook might contain a dataset that includes tables from different connections and schemas. When a dataset contains multiple tables, the tables will contain joins. See Understand Dataset Table Joins. In your workbook, you can blend datasets that contain multiple tables and joins. For example, you can add and blend datasets with multiple tables when you want your workbook to contain cross fact analysis.

About Mismatched Values in Blended Data

In some cases when the rows of data that you expect to see in a dataset are missing, then you must specify which dataset to use for data blending.

Sometimes rows of data are missing when your workbook includes data from two datasets that contain a mixture of attributes and values, and there are match values in one source that don’t exist in the other.

Suppose we have two datasets (Source A and Source B) with slightly different rows, as shown in the following image. Note that Source A doesn‘t include IN-8 and Source B doesn’t include IN-7.

Description of the illustration blending-example-source-tables.gif

The following results are displayed if you select the All Rows data blending option for Source A and select the Matching Rows data blending option for Source B. Because IN-7 doesn’t exist in Source B, the results contain null Rep and null Bonus.

Description of the illustration blending-all-rows-results.gif

The following results are displayed if you select the Matching Rows data blending option for Source A and select the All Rows data blending option for Source B. Because IN-8 doesn’t exist in Source A, the results contain null Date and null Revenue.

Description of the illustration blending-matching-rows-results.gif

The visualization for Source A includes Date as an attribute, and Source B includes Rep as an attribute, and the match column is Inv#. Under dimensional rules, you can’t use these attributes with a measure from the opposite table unless you also use the match column.

There are two settings for blending tables that contain both attributes and measures. These are set independently in each visualization based on what columns are used in the visualization. The settings are All Rows and Matching Rows and they describe which source rows the system uses when returning data to be visualized.

The system automatically assigns data blending according to the following rules:

• If the visualization contains a match column, then the system sets sources with the match column to All Rows.
• If the visualization contains an attribute, then the system sets its source to All Rows and sets the other sources to Matching Rows.
• If attributes in the visualization come from the same source, then the system sets the source to All Rows, and sets the other sources to Matching Rows.
• If attributes come from multiple sources, then the system sets the source listed first in the workbook's elements panel to All Rows and sets the other sources to Matching Rows.

Blend Datasets

If your workbook contains multiple datasets, then you can blend data in one dataset with data in another dataset.

When you add more than one dataset to a workbook, the system tries to find matches for the data that’s added. It automatically matches external dimensions where they share a common name and have a compatible data type with attributes in the existing dataset. Blending relationships are stored in the tables and not in the workbook. So any blending matches that you add or remove affects the other datasets that use the same tables.

See Understand Blending.

Datasets that use Oracle Essbase or Oracle EPM Cloud connections aren't available for blending.

Note:

Datasets can include tables from different connections and schemas. These datasets use joins, which are defined in the Dataset editor. To reduce or eliminate blending in workbooks, Oracle suggests that whenever possible you create datasets that contain multiple tables and use joins. See Create a Dataset From a Connection.

1. On the Home page, hover over a workbook, click Actions, then select Open.
2. To add another dataset to the workbook, then click Data, and in the Data page, go to the Data Panel, click Add (+), and then Add Dataset.
3. In the Add Dataset dialog, select a dataset and click Add to Workbook.
4. In the Data page, go to the Data Diagram and locate the datasets to blend.
5. Click the number between the dataset icons.
   
   
6. Optional: To change the match for a column, click the name of the column and select a different column.

   If columns have the same name and same data type, then they’re recognized as a possible match. You can customize this and specify that one column matches another by explicitly selecting it even if its name isn’t the same. You can select only those columns with a matching data type.

7. Optional: Click Add Another Match, and in the table, select the columns that you want to join.
8. Optional: For a measure that you’re uploading for the first time, specify the aggregation type such as Sum or Average.
9. Click OK.

Change Data Blending in a Visualization

You can modify a visualization's properties to override the workbook's data blending settings.

Datasets that use Oracle Essbase or Oracle EPM Cloud connections aren't available for blending.

See Understand Blending.

1. On the Home page, hover over a workbook, click Actions, then select Open.
2. In the workbook, select a visualization, and in the Properties Panel click Datasets.
   
   
3. To change the default blending, click Data Blending, and select either Auto or Custom.
   If you choose Custom, you can set the blending to either All Rows or Matching Rows.
   • You must assign at least one source to All Rows.
   • If both sources are All Rows, then the system assumes that the tables are purely dimensional.
   • You can’t assign both sources to Matching Rows.