Data sources

You can use a new Data Source data object type to represent SQL data sources. To access data sources, go to Setup > Administration > Connectivity... > Data sources.

The connection attribute specifies the name of a data connection or simply . if it is for the Omada Identity database. The authRole attribute specifies the UID of one or more authorization roles that are allowed to use/run the data source.

The following is an example of a chart data source definition:

<?xml version="1.0" encoding="UTF-8"?>
<dataSource xmlns="http://schemas.omada.net/ois/2013/DataSourceML" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  connection="ODW" commandtimeout="PT5M" cachePeriod="PT1M" authRole="3862552b-623f-45dd-bd12-f71e14a13cdb">
  <sql>
    select top 20 sum(ResourceAssignmentCount) ResourceAssignmentCount, [Date]
    from [PUB_ResourceAssignmentCube]
    group by [Date]
    order by [Date] desc
  </sql>
</dataSource>

You can find the XML schema http://schemas.omada.net/ois/2013/DataSourceML in Setup > Administration > Developer configuration > Xml Schemas.

DAX query

You can target a data source query at any SQL database. It is typically targeted at the Omada Identity databases themselves. Data sources are used for chart dashboard widgets and KPIs.

A data source may be used to query Microsoft Analysis Services using DAX language. In order to perform a DAX query, the data source configuration XML should contain the dataSource element with the attribute 'language="DAX". A DAX data source will use the data connection in the same way as an SQL data source. The connection string is expected to point to Microsoft Analysis Services. The query will be performed using ADOMD.net provider.

The <sql> element has an optional isMemberOf attribute that is checked during execution against the current user groups. The data source may contain multiple <sql> elements. The elements will be considered for execution in the order in which they are placed inside the data source configuration xml. The first element without the isMemberOf attribute or containing the isMemberOf attribute with the GUID of the current user group inside will be executed. Only one query will be executed. This functionality works for both DAX and SQL queries. The relevant part of the data source XML can look like this:

<sql isMemberOf=""942E8812-BF35-45B2-8A19-2BD27AB801A3,BB411266-681F-48E0-BFD7-440DD7D41DDA"">

   ... example sql for user groups...

</sql>

<sql>

   ... example sql

</sql>

Data sources referenced by KPI indicators will have the @oisid parameter passed to the query containing the OISID property value of the current user identity, which can be utilized inside a DAX/SQL query.

You can also add the parameters page and rows in order to page the results that show the data set for a drill-down data source. See the following example:

<?xml version="1.0" encoding="UTF-8"?>

<dataSource xmlns="http://schemas.omada.net/ois/2013/DataSourceML" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

connection="RoPE" commandtimeout="PT5M" cachePeriod="PT1M" authRole="796945af-dd5e-49a7-bb76-c3ef1662160d">

  <parameters>

    <parameter name="rows" type="int"/>

    <parameter name="page" type="int"/>

  </parameters>

<sql>

select *

from RoPE.viewIdentity

where LastStatus = 2

ORDER BY id OFFSET @page*@rows ROWS FETCH NEXT @rows ROWS ONLY

</sql>

</dataSource>

Required data sources

While editing Compliance workbench or Match status data sources, you can provide the requiredDataSets element which allows you to specify an additional SQL query that can be executed in a database other than the main query for the data source. This data set can produce a temporary table in the main database before it is executed the main SQL query.

Below, you can find an example of the requiredDataSets element.

<requiredDataSets>
 <dataSet name="classificationResources" tablename="classificationResources"
  connection="." dataType="guid">
  <sql>
    <![CDATA[
    select distinct r.UID
    from tblDataObject r
     join tblDataObjectVersionPropertyValueSet classification
      on classification.PropertyID=(select ID from viewProperty where SystemName=’RECLASSIFICATION’)
      and classification.DataObjectVersionID=r.CurrentVersionID
      and classification.PropertyValueID in (@classificationIds)
    where
     r.DataObjectTypeID=951 and r.Deleted=0
    ]]>
  </sql>
 </dataSet>
</requiredDataSets>

A required data set consists of 4 elements:

• Connection is the name of a Data Connection element in the portal. If "." or "OIS" is entered, the connection the ES data is used.
• SQL is the statement which selects items for the temporary table.
• Table name is the name of the temporary table without #. It can then be used in a join or sub-select in the main query (with the #).
• Data type is the column type of the SQL result and the temporary table column.

Data source requirements

The following lists the requirements data source queries to use with the various type of data sources:

• Chart data sources:

  • Must contain either 2 or 3 columns.
  • The first column is used for the x-axis. The values can be either numbers of text.
  • The second column is used for the y-axis. The values must be numbers.
  • The third column is optional and is used for the data series.

• Pie chart data sources:

  • It must contain 2 columns. One with text values and another with numbers.

• KPI data sources:

  • Must return a scalar result.
  • The result must be a number.

Data source parameters

A chart data source can use parameters. An example is shown below. The example adds a parameter for template uid that allows you to filter a chart on a particular process template.

info

A KPI data source cannot use parameters.

<?xml version="1.0" encoding="UTF-8"?>
<dataSource xmlns="http://schemas.omada.net/ois/2013/DataSourceML" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  connection="." commandtimeout="PT5M" cachePeriod="PT1M" authRole="3862552b-623f-45dd-bd12-f71e14a13cdb">
  <parameters>
    <parameter name="templateuid" type="guid"/>
  </parameters>
  <sql>
    select top 10 convert(varchar, p_do.createtime, 106), count(*)
    from tblprocess p
    join tbldataobject p_do on p.id = p_do.id
    join tbldataobject template_do on p.TemplateID = template_do.ID
    where template_do.UID = @templateuid
    group by convert(varchar, p_do.createtime, 106)
    order by convert(varchar, p_do.createtime, 106) desc
  </sql>
</dataSource>

You can specify the value for a parameter in the configuration of a chart widget. An example is show in below.

{
  name: 'Chart',
  title: 'Stats for proc \'onboard contractor\'',
  height: 250, 
  colSpan: 1,
  params: { dataSource: 'Stats for specific process template', 
  dataSourceParams: { templateuid: '5cee5c44-8673-4082-b43e-fb84da42e3fb'}, 
  chartType: 'bars' }
},

Complex datasets

Some datasets can be difficult to express in SQL. One example of this is if you want to make an Omada Identity query that involves all identities for which the active user is a manager.

To make such queries easier to write, the data source concept allows you to prepare and include complex data sets that are resolved in .Net code.

The purpose of a ComplexDataSetBuilder is to build and fill in a temporary database table. You can use the temporary table afterwards in a data source query.

Omada Identity includes a set of pre-built implementations of IComplexDataSetBuilder. Expand the section below to learn more.

Pre-built implementations

Dataset builder	Description
ClassifiedResourcesDataSetBuilder	For use with Omada Identity Data Warehouse. It requires the presence of the Omada Identity Package Solution. Finds all resource data objects that have a certain classification in the RESCLASSIFICATION property and stores their business key in a database table in a column named BUSIKEY. Assumes that the business key is stored in the ODWBUSIKEY property on the resource data objects. Parameters: ClassificationUIds: Comma-delimited UIDs of set property values for the RESCLASSIFICATION property. A required parameter. SysOwnerMode: If you set this parameter to True, only resources belonging to a system that the active user is owner of are included.
OwnedContextIdentitiesDataSetBuilder	Finds all identities that are in a business context that the active user is owner of and stores their IDs in a database table in a column named IdentityID.
OwnedContextIdentityResourceAssignments DataSetBuilder	Finds all resource assignment data objects belonging to identities that are in a business context that the active user is owner of and stores their IDs in a database table in a column named RAID.
OwnedContextsDataSetBuilder	Finds all business context data objects that the active user is owner of and stores their IDs in a db table in a column named ContextID.
OwnedSystemsDataSetBuilder	Finds all system data objects that the active user is owner of and stores their IDs in a database table in a column named SystemID. If the parameter OutputBusinessKeys is set to true, it stores their "business keys" in a database table in a column named BUSIKEY. Assumes that the "business key" is stored in the ODWBUSIKEY property on the system data objects.

Data connections

Data Connections are used for holding database connection strings in data objects.

Data connections are used with data sources. Find this page in Setup > Administration > Connectivity... > Data connections.

The purpose of having data connections in separate configuration objects is to make it easier to transport configuration data between environments.

Connection strings in Data connections can be partially encrypted. If you want to encrypt a part of the string you need to use the StringEncrypter on the desired part of the connection string and then paste the encrypted part manually into the target string.