User Community Service Desk Downloads
If you can't find the product or version you're looking for, visit support.ataccama.com/downloads

Data Sets

Data sets are custom reports formed by querying the RDM Repository using SQL. Any SQL functions and clauses for selecting data can be used, like AVG,COUNT, SUM, JOIN.

For example, a bank might be interested in the number of branches by region or number of products offered by each branch.

After creating a data set and deploying the configuration in the web application, the administrator needs to set the permissions for it.

Create a data set

To configure a data set:

  1. Navigate to RDM Logical Model > Data sets.

  2. Right-click Data sets and select New Data set.

  3. In the General tab, fill in the attributes:

    Field Required Description

    Dataset name

    Yes

    Technical name of the data set written to the database.

    Some names are reserved and cannot be used. See Reserved Words and Keywords.
    The name must be unique across tables, views, and data sets.

    Dataset label

    Yes

    Name of the data set as shown in the web application.

    Description

    No

    Free text describing the data set.

  4. Switch to the SQL Query tab and specify the SQL query that generates this data set.

  5. Switch to the Columns tab and specify the attributes of the generated table (based on the SQL query):

    Field Required Description

    Name

    Yes

    Technical name of the column (or its alias) as written in the SQL query.

    See also SQL query syntax.

    Label

    Yes

    Name of the data set as shown in the web application.

    Type

    Yes

    Data type of the column.

    Format

    No

    Value display format; works the same way as for domains. See Domains.

  6. Switch to the Order columns tab and specify one or more attributes by which the results are sorted.

SQL query syntax

While the general format of the query and SQL functions are used, there are a few requirements to the SQL queries used for generating data sets:

  • The resulting column names should conform to the naming of rules of the database used: the safest way is to use letters, numbers, and the underscore (_). This can be easily achieved by using the SQL aliases: as.

  • Table names should be specified as follows: $table_[actual_table_name]$. For example, for table CITY, $table_CITY$ should be used.

  • Using column names that require quoting (such as aliases, for example, "Group") is not supported. As an alternative, you can create a custom plan to generate the required table instead.

Sample SQL query
select r.name as region, count(b.code) as branch_num
from $table_BRANCH$ b
    left join $table_CITY$ c on b.city=c.name
    left join $table_REGION$ r on  c.province=r.abbrev
group by r.name

Was this page useful?