Creating data sets

Create data sets to group customized collections of data items that you use frequently.

If a data set is based on a package with multiple data server connections or signons, the connection or signon that you choose is saved with the data set. If the package connection information changes later, users might see the ambiguous connection message. To avoid this message, edit the data set choosing the new connection or signon, and save the data set using the Save as option. Select yes when asked whether you want to overwrite the data set. The data set is saved with the new connection or signon and its subsequent refreshes use the new information.

Before you begin

Review the Best practices for improving query performance on uploaded files and data sets.

The package or data module that you plan to use as a source for your data set must already be saved in Team content or My content.

About this task

The list in the data set can be associated with only one query. If you want to add data items from different queries to your data set, you can create a custom query in the Queries view that contains data items from different queries.

When creating or editing data sets, you can reuse queries from Cognos® Analytics reports. For more information, see Reusing report queries in data sets.

Procedure

Locate the package or data module in Team content or My content.
From the package or data module context menu , click Create data set.

The data set editor is opened in the Page design mode.
Drag the data items from the Insertable objects pane to the work area. The items appear as columnar data in a similar fashion to a list report.

To preview data in the data set, switch from the Page design mode to the Page preview mode.
For relational data or for data modules, select the Summarize detailed values, suppressing duplicates, for relational data sources checkbox.

If you aren't sure if this checkbox must be selected, clear it and then select it again to see how the data is aggregated. Condensed data with fewer rows usually leads to better performing reports and dashboards. A reason for not aggregating the data in your data set is that you lose some details in the process, and the data from one system might not match the data from another system. This is especially true for calculations, such as an average.
Select Row suppression if you want to hide rows with no data or zeros.

Suppressing rows without data gives you a more concise view of your data set.
Refine the data in the data set by using the options in the on-demand toolbar .

To view the toolbar, click any column.

To add filters to the columns or individual items of the data set, click the item, and then click the filter icon in the toolbar. You can add a customized filter or use one of the predefined filters.

To sort the values, click the sort icon , and select from the available sort options.

To view the column expression, click the More icon , and select Edit Query Expression.
Use the Query view to access more data set functionality.

From the Data set menu, click Queries to open the Query Explorer.

In this view, you can copy and paste queries from existing reports, manage advanced filters and prompts, or rename the queries.

Here is an example of a data set in the Queries view:

Note: The query names are used as table names when the data set is used to create data modules. Use logical names that clearly describe the data when renaming the queries.
Click the More icon to access additional functionality:

Click Validate report to validate the data set, or click Show generated SQL/MDX to view the data set SQL.
Click the save icon , and choose one of the following options to save the data set:
- To save the data set for the first time or to save changes to the data set, click Save. This option saves the metadata, but doesn’t load the data. Depending on the data set, loading data might take some time.
- To save an updated data set as a new data set, click Save as. This option saves the metadata, but doesn’t load the data. Depending on the data set, loading data might take some time.
- To save the data set and load the data, click Save and load data. In addition to saving the new or changed metadata, this option loads data. The data is immediately available when you create a dashboard or story.

Results

The data set object Data set icon

is created in a location that you saved it to.

What to do next

To edit the data set, open it from Team content or My content.

You can replace data items in the data set with data items from a different query. In the Page design or Page preview mode, click the Reset button. The previously selected data items are removed, and you can start adding new ones to the list.

You can also replace the data module or package that was used as a source for the data set. Right-click the source name in the Insertable objects pane, and select the Replace data module or Replace package option, as shown in the following screen capture: