Connect to your data

Semarchy xDM Dashboard connects to data using queries executed on datasources.

Managing queries requires the Dashboard Management privilege. For more information, see the privileges description.

Semarchy xDM Dashboard datasources first need to be configured on the platform.

Create a datasource

To create a datasource:

  1. Select Datasources in the navigation drawer.
    The Datasources list opens.

  2. Click on the add button Create new datasource floating action button.

  3. In the New Datasource dialog

    1. Select the Platform Datasource corresponding to a datasource configured on the platform.

    2. Enter a Name for the datasource.

    3. Click Create.
      The datasource editor opens.

  4. In the datasource editor, change if necessary the Database Type to the technology of your datasource. The type is automatically detected.

  5. Click the save Save button in the toolbar.

Manage datasources

From the datasource list, you can perform the following operations:

  • Open a datasource by clicking its name in the list.

  • Rename a selected datasource using the rename Rename action. Note that when renaming a datasource, the incoming references to this datasource are not automatically updated.

  • Copy a selected datasource using the copy Copy action. When copying a datasource, you are prompted for the name of the new datasource.

  • Delete a selected datasource using the delete Delete action. This operation cannot be undone.

Renaming or deleting a datasource renders the queries that rely on this datasource and the charts that use these queries invalid. Hence, renaming and deleting operations must be performed with care.

Create a query

To create a query:

  1. From the navigation drawer, select Queries.
    The list of queries opens.

  2. Click the add button Create new query floating action button.

  3. In the New Query dialog:

    1. Select the Datasource containing the data that you want to query.

    2. Enter a Name for the query.

    3. Click Create.
      The query editor opens.

  4. In the query editor, enter an SQL Query to access your data. This query must be a SELECT statement returning a dataset.

    Database views deployed in data locations are accessible by queries.

  5. Use the run query Run query button in the toolbar or use the Control+Enter shortcut (Command+Enter on macOS) to run your query:

    • If the query is invalid, a detailed error message is prompted to help you pinpoint and rectify any syntax mistakes or logical errors (e.g., incorrect usage of keywords, missing or misplaced punctuation, references to nonexistent database objects, improper filtering conditions, etc.).

    • If the query is valid, the list of query attributes and records returned by the query appears in the lower section of the editor, respectively in the Query attributes or Data Preview table. You can switch between these two views using the preview Show query attributes/Show data preview button in the toolbar.

To be valid, a query should:

  • Parse properly and return records when executed in an SQL client (such as DBeaver).

  • Specify aliases for complex expressions involving functions, concatenation operators, CASE WHEN clauses, etc.

  • Not include a trailing semicolon.
Query attributes are limited to string, numeric, and date/time datatypes.

  1. Configure each attribute in the Query attributes table:

    1. Click in the Label column to define a label.
      This label applies by default to a dimension or measure based on this query attribute and may be overridden for each dimension or measure. Note that the Name attribute is used by default as the label.

    2. Define the default Format for a numeric or datetime attribute. This format is used by default when displaying a value based on this query attribute and may be overridden in the chart.

    3. Define whether this query attribute may be used as a Measure or Dimension in your charts. Unselecting a checkbox prevents using the attribute as a measure/dimension when designing charts. Note that the Time Dimensions checkbox is for future use.

    4. Select Show in drilldown to make this query attribute visible in the drilldown data tables.

    5. Select the Sortable option to allow users to use this query attribute to sort drilldown data tables. Consider disabling this option for attributes not suitable to sort the dataset for performance reasons.

  2. Click the Save button in the toolbar to save the query.

When used in charts, queries are nested as SQL subqueries. As a consequence, they should not use capabilities that prevent nesting, such as SQL Server common table expressions (CTE).

Additionally, because SQL Server imposes restrictions on subqueries, Semarchy xDM does not support ORDER BY clauses when running queries on datasources in an SQL Server environment, unless TOP is also specified.
You do not necessarily need to filter datasets in the queries. You can filter the query results in the charts that use them, and additionally when you use these charts in the dashboards.

Configure export and query actions

When drilldown is enabled for a chart (using the Enable drilldown property), users can click on a shape or measure value in that chart to open a drilldown data table. This table shows the records behind the aggregated measure.

Depending on the query’s setup, users can have access from a drilldown data table to:

  • The Data export of the drilldown data.

  • Contextual Query actions.

Drilldown data export

When the feature is activated for a query, users can export the data from a drilldown. CSV and Excel formats are available.

To enable data export on a query:

  1. In the query editor, select the edit Open query properties toolbar button. The chart properties side nav opens.

  2. Select the Export enabled option.

  3. (Optional) Select the Export required role in the case when only a certain role needs to be allowed to export data. This role must be declared in the platform configuration first.

When data export is activated for a query and the user has the required role (if configured on the query), this user can trigger the export from a drilldown data table with the following options:

  • Format: comma-separated value (csv) or Excel 2007+ (xlsx).

  • Export all columns: this option is available only when a subset of the columns is selected and displayed. It enables exporting all columns marked as Show in drilldown for the query. Otherwise, only the columns selected as visible by the user are part of the export.

Configure query actions

Query actions are available contextually on records of the drilldown data table, each query action appearing as a menu item. Clicking a query action opens a URL that is parameterized with the query attribute values of the record.

Query Action Examples

A Products query returns product records, including the BrandName and BrandID query attributes.

Possible actions for this query are:

  • Search for Brand: this action opens the search results for the brand, using the following URL: http://google.com/search?q=${BrandName}.

  • Open in Semarchy: this action opens a given Semarchy application on a business object showing the brand information, using the following URL: mdm-app/ProductRetail/ProductRetailMDM/browsing/ProductsByBrand/${BrandID}.

In both cases, you contextualize the URL with the values of BrandName and BrandID for the record for which this action is triggered.

Note that the first URL uses an absolute path (starting with http:), and the second one uses a relative path which works for a data management application running in the same Semarchy xDM

To configure query actions:

  1. In the query editor, select the edit Open query properties toolbar button. The chart properties side nav opens.

  2. Expand the Query actions section. The list of query actions that are already configured appears.

  3. Click Add Action.

  4. In the New Query action dialog:

  5. Enter a Name and a Label for the new query action. The label applies to the menu item corresponding to this action.

  6. Define the URL for the query action, parameterized with query attributes using the following syntax: ${query-attribute-name}. For example: ${PRODUCT_ID}.

  7. (Optional) Select a Required role to view this action in the menu. This role must be configured in the Semarchy xDM platform.

  8. (Optional) Select a Target where the URL should open:

    • Current tab replaces the entire browser tab with the URL.

    • New tab opens the URL in a new tab.

    • Chart container opens the URL constrained in the chart element, supporting the navigation back to the drilldown table.

  9. (Optional) Select an Icon to be used in the action menu item.

  10. Click Create to create the action, which appears in the action list.

Query attribute values in URLs

When composing the URL the query attribute values are processed as described below:

  1. Null values are replaced with empty strings and not processed further.

  2. Existing values are formatted as follows:

    • Numeric values use JSON-like number formatting.

    • String values are rendered as is.

    • Date and DateTime values use the ISO-8601 format, that is YYYY-MM-DD or YYYY-MM-DDTHH:mm:ss.SSSZ.

  3. Formatted values are then percent-encoded for the URL, replacing unsafe ASCII characters with a % followed by two hexadecimal digits.
Relative or absolute URLs

URL can be relative or absolute. Relative URLs only have a path and do not start with <scheme>:// or /. Relative URLs are resolved from the Server Base URL defined in the platform configuration.

For example,

mdm-app/CustomerB2CDemo/CustomerB2C/browsing/Person/${PERSON_ID}

would resolve to

http://xdm.acme.com:8088/semarchy/mdm-app/CustomerB2CDemo/CustomerB2C/browsing/Person/${PERSON_ID}

if the Server Base URL is set to http://xdm.acme.com:8088/semarchy in the platform configuration.

In the list of query actions, you can:

  • Click a query action to open its sidesheet and edit its properties.

  • Delete a query action using the delete Delete button.

  • Drag and reorder query actions in the list using the drag Reorder button.

Manage queries

From the query list, you can perform the following operations:

  • Open a query by clicking its name in the list.

  • Rename a selected query using the rename Rename action. Note that when renaming a query, the incoming references to this query are not automatically updated.

  • Copy a selected query using the copy Copy action. When copying a query, you are prompted for the name of the new query.

  • Delete a selected query using the delete Delete action. This operation cannot be undone.

Renaming or deleting a query makes invalid the charts that rely on this query. These two operations must be performed with care.