Building a Query

Build a query to import data from an app or a datasource into a new or existing model.

To build a query, you move data elements from the Available Data area into the Selected Data and Filters areas to create new measures and dimensions to be imported to your model, and apply filters to them.

An entity () can be expanded to select one or more of its members. The following icons represent types of data you can select, and their properties:

Icon Type
Entities
Numerical properties
Generic properties (strings, Boolean values, UUIDs, and so on)
Complex properties

In SAP Universe queries – the set of data that can be selected from and/or filtered on (similar to the Entity in other queries)

Date type properties
In SAP Universe queries – data that can be filtered on

When building a query, you can select the icon to search for specific values in the Available Data area, select the icon to display the list of reports from your source data in real time, or select the icon to change the way values are displayed:

  • Description – The Description is displayed and used to search values.
  • Description and ID – The Description and ID are both displayed. The ID is used to search values.
  • ID – The ID is displayed and used to search values.

Filters

When you add a data element to the Filters area, you can use operators, such as equal to, except, greater than, and so on, and select the icon to open the Select Values From dialog. In this dialog, you can search and select the values you want to filter on. The list displays the unique values from the first 1000 records. It is possible to search for a value not displayed in the list using the search function.

Some entities, such as Boolean values, can have only a single filter value. Entities such as strings or integer values can have multiple values in a filter. To select multiple values, select the check box of each value you want to add to the filter or Select All to select all the values in the list. Selecting multiple values for a single filter treats the filter as a large OR function, filtering out values that do not match any of the selected values.

Incremental Load Filters

When you create a query, you can set an Incremental Load. An incremental load is based on time or numeric data fields and lets you brings the newest set of data since the last import when you refresh. For example, you can set an incremental load based on the "Release Date" field of a "Product" entity to bring in the newest data based on the release date.

You can set an incremental load while creating a new datasource or on an existing datasource.

To set an incremental load, in the Filters area, select Set Incremental Load and then drag and drop an incremental-load-enabled property to the incremental load box that appears.

After you create the model, the incremental load appears in the Import Settings section of the datasources tab.

The next time the source is refreshed, the latest value will be recorded for the Incremental Load Filter. Once the Incremental Load Filter has a value, you should switch the import method to Append.

Deleting or Replacing an Existing Incremental Load Filter

If you change the Incremental Load Filter to a different field, the new filter will not take effect until after the next refresh.

Example:

You set an Incremental Load based on the CreateDate field, and the latest row contains the date "Jan 14, 2018", so the system saves that value. The next time a data refresh occurs, a filter will be applied to grab only data that is later than "Jan 14, 2018".

If you change the Incremental Load to a different field, for example TransactionID, the system does not overwrite the old value until the next refresh. If you change the Incremental Load Filter back to CreateDate before a refresh, the load will still have the old value and, if you refresh now, it will still grab anything after "Jan 14, 2018".

To ensure the data is correct when you change an Incremental Load filter, make sure you perform the following steps:

  1. Change the Incremental Load Filter to a new field you want.
  2. Change the import method accordingly because the Incremental Load Filter will not apply right after you’ve switched it.
  3. Perform a manual refresh - The first refresh right after replacing or deleting the old incremental load filter will bring in the full set of data. Confirm a valid value is present for the incremental load filter after the manual refresh.
    Example of an Incremental Load Filter with a valid value
  4. Change the import method. More information.