Connect to your data

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

Managing queries requires the Dashboard Management privilege. Refer to the privileges description for more information.
Semarchy xDM Dashboard datasources defined on datasources that must be configured in the platform.

Create a datasource

To create a datasource:

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

  2. Click on the Create new datasource floating action button in the lower-right corner of the screen.

  3. In the New Datasource dialog

    1. Select the Datasource Name corresponding to a datasource configured in 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

Datasources in the datasource list support the following operations:

  • Open a datasource by clicking it in the list.

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

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

  • delete Delete selected datasources. This operation cannot be undone.

Renaming or deleting a datasource makes invalid the queries relying on this datasource and the charts using these queries. These two operations must be performed with care.

Create a query

To create a query:

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

  2. Click on the Create new query floating action button in the lower-right corner of the screen.

  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 in the New Query

    3. Click Create.
      The query editor opens.

  4. In the query editor, enter the 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 (or Control+Enter on macOS) to run your query:

    • If the query is invalid, an error appears.

    • If the query is valid, the list of Query Attributes returned by the query or the Data Preview showing the returned records appears in the second half of the editor. You can switch between these two views using the preview Show Query Attributes/Show Data Preview toggle 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.

  1. Configure each attribute in the Query Attributes table:

    1. Click in the Label column to define a label.
      This label is used by default for a dimension or measure based on this query attribute and may be overridden for each dimension or measure. Note that the attribute Name is used by default as its 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 sub-queries. As a consequence, they should not use capabilities that prevent such nesting, such as SQL Server Common Table Expressions (CTE).
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:

  • 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. Optionally, select the Export required role in the case when only a certain role needs 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 open 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 use 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 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 is used for 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. Optionally select a Required role to view this action in the menu. This role must be configured in the Semarchy xDM platform.

  8. Optionally 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. Optionally select an Icon used in the action menu item.

  10. Click Create to create the action. It 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:

    • Number values use JSON-like number formatting.

    • String values as rendered as is.

    • Date and datetime values the use 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 Delete a query action.

  • Use the drag Reorder icon to drag and reorder query actions in the list.

Manage queries

Queries in the query list support the following operations:

  • Open a query by clicking it in the list.

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

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

  • delete Delete selected query. This operation cannot be undone.

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