Data Model APIs¶
The ‘’’Data Model’’’ section allows user to
|
|
List of APIs¶
| API | Purpose |
|---|---|
| POST dataModel/loadQuerySources | Returns an array of visible query sources, paged and sorted, filtered by type and other search criteria. |
| POST dataModel/loadQuerySourceFields | Returns an array of query source fields in a query source. Will load from remote connection if they are not yet populated in system. |
| GET dataModel/basicConnectionsInfo/(tenant_id) | Returns an array of id and name of connections (filtered by tenant_id if given). Note The same as GET connection/basicInfo/(tenant_id) |
| POST dataModel/basicConnectionsInfo | Returns an array of connections infor with paging. |
| POST dataModel/connectionsInfo | Returns an array of Connection objects. |
| GET dataModel/basicQuerySourceCategoriesInfo/{connection_id} | Returns an array of the query source categories in the specified connection. New in version 2.0.3. |
| `POST dataModel/basicQuerySourceCategoriesInfo`_ | Returns an array of the query source categories with paging in the specified connection. New in version 2.4.0. |
| GET dataModel/basicQuerySourcesInfo/{connection_id}/(type) | Returns an array of id and name of the query sources in the specified connection, filtered by type (Table, View or Stored Procedure). |
| GET dataModel/querySourcesInfo/{connection_id}/(type) | Returns an array of the query sources in the specified connection, filtered by type (Table, View or Stored Procedure). |
| DELETE dataModel/querySourceField/{query_source_field_id} | Removes a query source field from data model. |
| GET dataModel/basicQuerySourceFieldsInfo/{query_source_id} | Returns an array of id and name of the query source fields in the query source specified by the {query_source_id} value. |
| POST dataModel | Saves a list of query sources. |
| GET dataModel/indicator/(tenant_id) | Returns the number of physical changes in Data Model for each type Table, View, Stored procedure and Function, filtered by tenant_id if provided. |
| POST dataModel/reloadQuerySourceFields | Returns the schema of stored procedures after using user-supplied parameters to try running them. |
| POST dataModel/validateCalculatedField | Validates a calculated field for duplicated name before saving. |
| POST dataModel/functionOperators/ | Returns an array of functions/operators that support calculated fields, filtered by tenant_id if provided. |
| POST dataModel/loadRelationships | Returns an array of relationships of visible data sources, paged and sorted, filtered by search criteria. |
POST dataModel/loadInvalidRelationships New in version 2.16.0. |
Returns an array of invalid/modified relationships of visible data sources, paged and sorted, filtered by search criteria. |
| POST dataModel/relationships | Saves an array of relationships. After adding a new relationship, POST DataModel/loadRelationships should be called to retrieve the new id value. |
| DELETE dataModel/relationship/{relationship_id} | Deletes the relationship specified by the {relationship_id} value. |
| GET dataModel/schema/(tenant_id) | Returns an array of query sources and an array of relationships with color properties (filtered by tenant_id if given). |
| POST dataModel/schema/updateConnectionColor | Updates the connection color. |
| GET dataModel/relationship/joinTypes | Returns an array of join types currently supported. |
| GET dataModel/databaseMapping | Returns an array of database mappings. |
| POST dataModel/databaseMapping | Saves an array of database mappings. |
| POST dataModel/loadDatabaseNames | Returns a paged array of database servers and database names. |
| POST dataModel/loadDatabaseObjects | Returns a paged array of connection names or schema names in the specified database. |
| POST dataModel/customQuerySource | Executes and saves a custom query source. |
| POST dataModel/validateCustomQuerySource | Validates that name of custom query source is unique. |
| POST dataModel/deleteCustomQuerySource | Deletes a custom query source. |
| GET dataModel/querySource/{query_source_id} | Returns the query source specified by query_source_id. |
POST dataModel/validateRelationships New in version 2.16.0. |
Validate the newly added or modified relationship(s). |
POST dataModel/loadQuerySources¶
Returns an array of visible query sources, paged and sorted, filtered by type and other search criteria.
Request
Payload: a QuerySourcePagedRequest object
Note
The keys for SearchCriteria that this API support:
- All
- Category
- DatabaseName
- DataSourceName
- DataSourceAlias
- ColumnName
- DataType
- ColumnAlias
- SchemaName
- DataObject
- DataObjectType
Response
A PagedResult object, with result field containing an array of QuerySource objects
Samples
POST /api/dataModel/loadQuerySources HTTP/1.1Request payload:
{ "querySourceType": "Table", "tenantId": "b5b3a5cc-9e55-424c-ae85-ba92ec3b934e", "criteria": [{ "key": "DataSourceName", "value": "order", "operation": 1 }], "pageIndex": 1, "pageSize": 10, "sortOrders": [{ "key": "Category", "descending": true }] }Sample response:
{ "result": [ { "realName": "orders_new", "id": "18f55edc-b521-4614-9c06-817dac726284", "name": "northwind.orders_new", "type": "Table", "parentQuerySourceId": null, "categoryId": "94454a0a-17a2-49e5-bebf-9c0d07d95587", "selected": false, "deleted": false, "connectionId": "6cc06e5b-0627-432c-bc33-708b0843c7c7", "connectionName": "northwind", "childs": null, "dataSourceCategoryId": null, "dataSourceCategoryName": null, "alias": null, "originalAlias": null, "querySourceFields": [], "querySourceCategoryName": "northwind", "querySourceCategory": null, "modified": "2017-09-14T03:37:29.653", "extendedProperties": null, "physicalChange": 0, "approval": 0, "existed": false, "checked": false, "belongToCopiedReport": false, "customDefinition": null, "isCustomQuerySource": false, "isCheck": false, "disabled": false, "fullPath": null, "indeterminate": false, "numOfChilds": 0, "numOfCheckedChilds": 0 } ], "pageIndex": 1, "pageSize": 10, "total": 1, "skipItems": 0, "isLastPage": false }
POST dataModel/loadQuerySourceFields¶
Returns an array of query source fields in a query source.
Will load from remote connection if they are not yet populated in system.
Request
Payload: a QuerySourceFieldPagedRequest object
Note
The keys for SearchCriteria that this API support:
- All
- Category
- DatabaseName
- DataSourceName
- DataSourceAlias
- ColumnName
- DataType
- ColumnAlias
- SchemaName
- DataObject
- DataObjectType
Response
A PagedResult object, with result field containing an array of QuerySourceField objects
Samples
POST /api/dataModel/loadQuerySourceFields HTTP/1.1Request payload:
{ "querySource" : { "id" : "9fa90af2-5329-44ac-a753-50c27f9d6fd5", "type" : "Table" }, "criteria" : [], "tenantId" : null, "pageIndex" : 1, "pageSize" : 1, "sortOrders" : [{ "key" : "Alias", "descending" : true } ] }Sample response:
{ "result": [ { "name": "ShipVia", "alias": "", "dataType": "int", "izendaDataType": "Numeric", "allowDistinct": true, "visible": true, "filterable": true, "querySourceId": "18f55edc-b521-4614-9c06-817dac726284", "parentId": null, "expressionFields": [], "filteredValue": "", "type": 0, "groupPosition": 0, "position": 7, "extendedProperties": "{\"PrimaryKey\":true}", "physicalChange": 0, "approval": 0, "existed": false, "matchedTenant": false, "functionName": null, "expression": null, "fullName": null, "calculatedTree": null, "reportId": null, "originalName": null, "originalId": "00000000-0000-0000-0000-000000000000", "isParameter": false, "isCalculated": false, "hasAggregatedFunction": false, "querySource": null, "querySourceName": null, "categoryName": null, "inaccessible": false, "originalAlias": null, "fullPath": null, "isCheck": false, "id": "0b0645cc-c401-4574-a868-aacaada4cb16", "state": 0, "deleted": false, "inserted": true, "version": null, "created": null, "createdBy": "acme", "modified": "2017-09-14T03:35:32.33", "modifiedBy": null } ], "pageIndex": 1, "pageSize": 1, "total": 14, "skipItems": 0, "isLastPage": true }
GET dataModel/basicConnectionsInfo/(tenant_id)¶
Returns an array of id and name of connections (filtered by tenant_id if given).
Note
The same as GET connection/basicInfo/(tenant_id)
Request
No payload
Response
An array of Item objects with:
- key is the id of the connection
- value is the name of the connection
Samples
GET /api/dataModel/basicConnectionsInfo HTTP/1.1Sample response:
[ { "key": "6cc06e5b-0627-432c-bc33-708b0843c7c7", "value": "northwind", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null } ]
POST dataModel/basicConnectionsInfo¶
Returns an array of connections infor with paging.
New in version 2.4.0.
Note
The same as GET connection/basicInfo/(tenant_id)
Request
Payload: a PagedRequest object.
Note
The keys for SearchCriteria that this API support:
- Id
- Name
Response
A PagedResult object with result field containing a list of Item objects whose each key is the connectionsId and value is the connectionName.
Samples
POST /api/dataModel/basicConnectionsInfo HTTP/1.1Sample response:
{ "result": [ { "key": "2046c03b-3830-4385-9ac0-bdc95e92ea49", "value": "[MSSQL]My Test 1", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }, { "key": "7e682625-52c8-4524-a19d-4d8225aa9ea8", "value": "[MSSQL]My Test 2", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null } ], "pageIndex": 1, "pageSize": 2, "total": 19, "skipItems": 0, "isLastPage": false }
POST dataModel/connectionsInfo¶
Returns an array of Connection objects.
New in version 2.7.0.
Request
Payload: a PagedRequest object.
Response
A PagedResult object with result field containing a list of Connection objects.
Samples
POST /api/dataModel/connectionsInfo HTTP/1.1Request payload:
{ "skipItems":-1, "tenantId":null }Sample response:
{ "result":[ { "id":"fa1c3381-d56a-4648-a280-480c759ef867", "name":"mydb", "originalName":null, "serverTypeId":"572bd576-8c92-4901-ab2a-b16e38144813", "databaseServer":"izenda-vm04", "serverTypeName":"MSSQL", "connectionString":"1YRqPAk9Lqdvvn/yborkLmvZ2CP4cI+xfMq4NZdEfvLVxFoMqDSGYBLPPOYIwpEWLhKw+f1nxXIcqfUZxE4mRA==", "visible":false, "deleted":false, "relateToConnectionId":null, "tenantId":null, "dBSource":{ "querySources":[] }, "relationships":null, "physicalChange":0, "checked":false, "isCheck":false, "databaseName":"mydb", "fullPath":null, "indeterminate":false, "numOfChilds":0, "numOfCheckedChilds":0 } ], "pageIndex":0, "pageSize":1000, "total":5, "skipItems":-1, "isLastPage":true }
GET dataModel/basicQuerySourceCategoriesInfo/{connection_id}¶
Returns an array of the query source categories in the specified connection.
New in version 2.0.3.
Request
No payload
Response
An array of Item objects with:
Samples
GET /api/dataModel/basicQuerySourceCategoriesInfo/2046c03b-3830-4385-9ac0-bdc95e92ea49 HTTP/1.1Sample response:
[ { "key": "34dc82ea-890d-4e3a-86a9-91ef171fd6f5", "value": "Application", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }, { "key": "e3354049-cffc-4b3d-1g25-e9f518d12d01", "value": "DataLoadSimulation", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null } ]
POST dataModel/basicQuerySourceCategoriesInfo/¶
Returns an array of the query source categories with paging.
New in version 2.4.0.
Request
Payload: a QuerySourceCategoryPagedRequest object.
Note
The keys for SearchCriteria that this API support:
- Id
- Name
Response
A PagedResult object with result field containing a list of Item objects whose each key is the id of the QuerySourceCategory and value is the name of the the QuerySourceCategory.
Samples
POST /api/dataModel/basicQuerySourceCategoriesInfo HTTP/1.1Sample response:
{ "result": [ { "key": "34dc82ea-890d-4e3a-86a9-91ef171f4ca5", "value": "Application", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null } ], "pageIndex": 1, "pageSize": 1, "total": 11, "skipItems": 0, "isLastPage": false }
GET dataModel/basicQuerySourcesInfo/{connection_id}/(type)¶
Returns an array of id and name of the query sources in the specified connection, filtered by type (Table, View or Stored Procedure).
Request
No payload
type values:
- Table
- View
- Stored%20Procedure
Response
An array of Item objects with:
Samples
GET /api/dataModel/basicQuerySourcesInfo/48733501-c57d-48ca-aded-501d5ebdaad9 HTTP/1.1Sample response:
[{ "key": "4e9aabda-9a95-4a00-8d80-0b8b1fbc7bc8", "value": "dbo.Suppliers", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }, { "key": "42f7c4ff-f44e-4460-bd50-10540d99a276", "value": "dbo.Order Details", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }]
GET dataModel/querySourcesInfo/{connection_id}/(type)¶
Returns an array of the query sources in the specified connection, filtered by type (Table, View or Stored Procedure).
Request
No payload
type values:
- Table
- View
- Stored%20Procedure
Response
An array of QuerySourceInfo objects
Samples
GET /api/dataModel/querySourcesInfo/5e8e56ce-ac29-48cf-ae0d-56cb5d9a935e/Table HTTP/1.1Sample response:
[ { "id": "77882ea1-6d82-45c2-b762-6c8612682b91", "name": "Categories", "alias": null, "category": "dbo", "serverTypeId": "00000000-0000-0000-0000-000000000000", "connectionStringId": "00000000-0000-0000-0000-000000000000", "connectionString": null, "connectionName": null, "querySourceCategoryName": null }, { "id": "55329213-9db0-4835-b465-44b3ac9b19fa", "name": "CustomerCustomerDemo", "alias": null, "category": "dbo", "serverTypeId": "00000000-0000-0000-0000-000000000000", "connectionStringId": "00000000-0000-0000-0000-000000000000", "connectionString": null, "connectionName": null, "querySourceCategoryName": null }]
DELETE dataModel/querySourceField/{query_source_field_id}¶
Removes a query source field from data model.
Request
No payload
Response
- true if the deletion is succesful
- false if not
Samples
DELETE /api/dataModel/querySourceField/da7be1b4-d4c0-43c4-a11b-5c87004c4837 HTTP/1.1Sample response:
true
GET dataModel/basicQuerySourceFieldsInfo/{query_source_id}¶
Returns an array of id and name of the query source fields in the query source specified by the {query_source_id} value.
Request
No payload
Response
An array of Item objects with:
Samples
GET /api/dataModel/basicQuerySourceFieldsInfo/4e9aabda-9a95-4a00-8d80-0b8b1fbc7bc8 HTTP/1.1Sample response:
[{ "key": "f8c2a34b-b304-4f1d-9d90-96c018ec3d2a", "value": "ContactName", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }, { "key": "a895434e-a77b-452e-8ed1-9b5fa339f1a8", "value": "CompanyName", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }, { "key": "3b266337-0142-4a4b-8351-ea0a74a7f234", "value": "SupplierID", "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }]
POST dataModel¶
Saves a list of query sources.
Request
Payload: a DataModel object
Response
An OperationResult object with success field true if the save is successful
Samples
POST /api/dataModel HTTP/1.1Request payload to save the aliases for column [dbo].[AWBuildVersion].[Database Version] and for table [dbo].[Categories]:
{ "tenantId" : null, "querySources" : [{ "id" : "c3330d53-cd8d-411c-9e7d-05849c7f2cc3", "name" : "dbo.AWBuildVersion", "type" : "Table", "parentQuerySourceId" : null, "categoryId" : null, "selected" : false, "connectionId" : "828e10df-dedb-42f6-8adf-b0785810837e", "connectionName" : "AdventureWorks2008R2", "childs" : null, "dataSourceCategoryId" : null, "dataSourceCategoryName" : null, "alias" : null, "querySourceFields" : [{ "id" : "dc4eca5c-ec25-4721-9f72-f98813f9b116", "name" : "VersionDate", "alias" : "", "dataType" : "datetime", "visible" : true, "filterable" : true, "deleted" : false, "querySourceId" : "c3330d53-cd8d-411c-9e7d-05849c7f2cc3", "parentId" : null, "children" : null, "modified" : "2016-04-06T04:20:37", "filteredValue" : "{}", "type" : 0, "position" : 0, "extendedProperties" : "", "physicalChange" : 0, "approval" : 0, "existed" : false, "matchedTenant" : false }, { "id" : "a3466647-d30b-4b21-868d-c05d074cba66", "name" : "Database Version", "alias" : "dbversion", "dataType" : "nvarchar", "visible" : true, "filterable" : true, "deleted" : false, "querySourceId" : "c3330d53-cd8d-411c-9e7d-05849c7f2cc3", "parentId" : null, "children" : null, "modified" : "2016-04-06T04:20:37", "filteredValue" : "{}", "type" : 0, "position" : 0, "extendedProperties" : "", "physicalChange" : 0, "approval" : 0, "existed" : false, "matchedTenant" : false } ], "querySourceCategory" : null, "modified" : null, "extendedProperties" : "{}", "physicalChange" : 0, "approval" : 0, "existed" : false }, { "id" : "f5e3450b-2b5b-4388-bce3-05efba5b8311", "name" : "dbo.Categories", "type" : "Table", "parentQuerySourceId" : null, "categoryId" : null, "selected" : false, "connectionId" : "8143ad74-fa73-4224-9299-b115252e1cc7", "connectionName" : "Northwind2014", "childs" : null, "dataSourceCategoryId" : "014e42b4-979a-4a7f-80cf-492142572d10", "dataSourceCategoryName" : "test", "alias" : "Cats", "querySourceFields" : [], "querySourceCategory" : null, "modified" : null, "extendedProperties" : "{}", "physicalChange" : 0, "approval" : 0, "existed" : false } ] }Request Payload to set dynamic for stored procedure [dbo].[CustOrdersDetail]:
{ "tenantId" : null, "querySources" : [{ "id" : "eabce774-10e4-4c9d-b0fd-7f8dc3b8a6be", "name" : "dbo.CustOrdersDetail", "type" : "Stored Procedure", "parentQuerySourceId" : null, "categoryId" : null, "selected" : false, "connectionId" : "38f89176-7113-4a20-aed0-9758cb65122a", "connectionName" : "AdventureWorks2008R2", "childs" : null, "dataSourceCategoryId" : null, "dataSourceCategoryName" : null, "alias" : null, "querySourceFields" : [{ "id" : "5d4c6339-1539-43ed-a1d4-fd6f423f6bd3", "name" : "@OrderID", "alias" : "", "dataType" : "int", "visible" : true, "filterable" : true, "deleted" : false, "querySourceId" : "eabce774-10e4-4c9d-b0fd-7f8dc3b8a6be", "parentId" : null, "children" : null, "modified" : "2016-04-13T08:55:15.803", "filteredValue" : "{}", "type" : 1, "position" : 1, "extendedProperties" : null, "physicalChange" : 0, "approval" : 0, "existed" : false, "matchedTenant" : false } ], "querySourceCategory" : null, "modified" : "2016-12-13T08:55:15.787", "extendedProperties" : "{\"Dynamic\":true,\"Static\":false}", "physicalChange" : 0, "approval" : 0, "existed" : false } ] }Request Payload to set Field Level and Expression Level for functions:
{ "tenantId" : null, "querySources" : [{ "id" : "b2972494-ca59-4904-9561-d4b609a6b806", "name" : "northwind.DateOnly", "type" : "Function", "parentQuerySourceId" : null, "categoryId" : null, "selected" : false, "connectionId" : "33244a6a-df64-46f8-8c5c-93eebe0f9c47", "connectionName" : "northwind", "childs" : null, "dataSourceCategoryId" : null, "dataSourceCategoryName" : null, "alias" : null, "querySourceFields" : [], "querySourceCategory" : null, "modified" : "2016-12-13T07:36:42.713", "extendedProperties" : "{\"ReturnedValue\":\"varchar\",\"InputParams\":\"InDateTime\",\"FieldLevel\":true,\"ExpressionLevel\":true}", "physicalChange" : 0, "approval" : 0, "existed" : false }, { "id" : "2224f941-a4e1-4211-8c52-fcba3dc14dd8", "name" : "northwind.MyRound", "type" : "Function", "parentQuerySourceId" : null, "categoryId" : null, "selected" : false, "connectionId" : "33244a6a-df64-46f8-8c5c-93eebe0f9c47", "connectionName" : "northwind", "childs" : null, "dataSourceCategoryId" : null, "dataSourceCategoryName" : null, "alias" : null, "querySourceFields" : [], "querySourceCategory" : null, "modified" : "2016-12-13T07:36:42.713", "extendedProperties" : "{\"ReturnedValue\":\"double\",\"InputParams\":\"Operand,Places\",\"FieldLevel\":false,\"ExpressionLevel\":true}", "physicalChange" : 0, "approval" : 0, "existed" : false } ] }Successful response:
{ "success" : true, "messages" : [], "data" : null }
GET dataModel/indicator/(tenant_id)¶
Returns the number of physical changes in Data Model for each type Table, View, Stored procedure and Function, filtered by tenant_id if provided.
Request
No payload
Response
An array of Item objects with:
- key is Either “Table”, “View”, “Stored procedure” or “Function”
- value is the number of changes for each type
Samples
GET /api/dataModel/indicator HTTP/1.1Sample response:
[{ "key" : "Table", "value" : 2, "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }, { "key" : "View", "value" : 1, "originalValue": null, "dataFormat": null, "intimePeriodType": null, "valueInTimePeriod": 0, "function": null }]
POST dataModel/reloadQuerySourceFields¶
Returns the schema of stored procedures after using user-supplied parameters to try running them.
Request
Payload: a ReloadQuerySourceRequest object
Note
The keys for SortOrder that this API support:
- ColumnName
- ColumnAlias
Response
A DataResult object, with data field containing a PagedResult object whose result field containing a list of QuerySourceField objects
Samples
POST /api/dataModel/reloadQuerySourceFields HTTP/1.1Request payload for Filter Lookup Key - Value:
{ "querySourceId" : "0cd0f186-48f1-47a9-9975-1f2bded3a5cc", "postedParameters" : [{ "id" : "8ccfac80-c883-446b-948d-18568dc4d173", "name" : "@OrderID", "filteredValue" : { "type":"1", "databaseName":"Northwind", "databaseId":"f7d00fd9-bfb4-40ae-b25a-61007781b196", "querySourceName":"dbo.Order Details", "querySourceId":"000e6c8a-89fd-4b38-8d6a-1b891c180daa", "lookupKeyQuerySourceFieldName":"OrderID", "lookupKeyQuerySourceFieldId":"a0acf5b0-4e47-49d6-af73-c953408df3ef", "displayQuerySourceFieldName":"OrderID", "displayQuerySourceFieldId":"a0acf5b0-4e47-49d6-af73-c953408df3ef", "userDefinedValues": [] } } ], "sortOrders" : [{ "key" : "ColumnName", "descending" : true } ] }Request payload for User Defined Filter Value:
{ "querySourceId" : "0cd0f186-48f1-47a9-9975-1f2bded3a5cc", "postedParameters" : [{ "id" : "8ccfac80-c883-446b-948d-18568dc4d173", "name" : "@OrderID", "filteredValue" : { "type" : "2", "userDefinedValues" : ["1", "2"] } } ], "sortOrders" : [{ "key" : "ColumnName", "descending" : true } ] }Sample response:{ "data": { "result": [ { "name": "ProductName", "alias": "", "dataType": "nvarchar", "izendaDataType": "Text", "allowDistinct": true, "visible": true, "filterable": true, "querySourceId": "00000000-0000-0000-0000-000000000000", "parentId": null, "expressionFields": [], "filteredValue": "{}", "type": 0, "groupPosition": 1, "position": 3, "extendedProperties": null, "physicalChange": 0, "approval": 0, "existed": false, "matchedTenant": false, "functionName": null, "expression": null, "fullName": null, "calculatedTree": null, "reportId": null, "originalName": null, "originalId": "00000000-0000-0000-0000-000000000000", "isParameter": false, "isCalculated": false, "hasAggregatedFunction": false, "querySource": null, "querySourceName": null, "categoryName": null, "inaccessible": false, "originalAlias": null, "fullPath": null, "isCheck": false, "id": "156f529f-af43-44b1-82b8-419e4a5ed086", "state": 0, "deleted": false, "inserted": true, "version": null, "created": null, "createdBy": "acme", "modified": "0001-01-01T00:00:00", "modifiedBy": null }, { "name": "@OrderID", "alias": "", "dataType": "int", "izendaDataType": "Int", "allowDistinct": true, "visible": true, "filterable": true, "querySourceId": "00000000-0000-0000-0000-000000000000", "parentId": null, "expressionFields": [], "filteredValue": "{}", "type": 0, "groupPosition": 1, "position": 2, "extendedProperties": null, "physicalChange": 0, "approval": 0, "existed": false, "matchedTenant": false, "functionName": null, "expression": null, "fullName": null, "calculatedTree": null, "reportId": null, "originalName": null, "originalId": "00000000-0000-0000-0000-000000000000", "isParameter": false, "isCalculated": false, "hasAggregatedFunction": false, "querySource": null, "querySourceName": null, "categoryName": null, "inaccessible": false, "originalAlias": null, "fullPath": null, "isCheck": false, "id": "086d5f37-d6b4-4aa4-807e-f6edac2f05b2", "state": 0, "deleted": false, "inserted": true, "version": null, "created": null, "createdBy": "acme", "modified": "0001-01-01T00:00:00", "modifiedBy": null }, ], "pageIndex": 0, "pageSize": 10, "total": 2, "skipItems": 0, "isLastPage": true }, "success": true, "messages": null }
POST dataModel/validateCalculatedField¶
Validates a calculated field for duplicated name before saving.
Request
Payload: a QuerySourceField object
Response
- true if the name is valid
- false if not
Samples
POST /api/dataModel/validateCalculatedField HTTP/1.1Request payload:
{ "name" : "UnitPrice", "querySourceId" : "9d18fa06-bf09-4908-9cc0-3ecb15c0e9e4" }Sample response:
{ "success": true, "messages": null, "data": null }
POST dataModel/functionOperators/¶
Returns an array of functions/operators that support calculated fields, filtered by tenant_id if provided.
Request
A CalculatedFieldFunctionParam object.
Response
An array of ReportFunction objects
Samples
POST /api/dataModel/functionOperators HTTP/1.1Sample Payload:
{ "tenantId" : "b5b3a5cc-9e55-424c-ae85-ba92ec3b934e", "querySourceIds" : [ "273badf8-d210-494f-a458-25e8f462891f", "5cc9e1dd-239c-43ac-8098-6b1c4b9e4478", "25ac2696-cabb-41df-a9aa-1b46f46c42f1", "f7ae5b5d-628e-4eaf-b8b2-fd823a484a35" ], "includeCustomJsonFunction" : false }Sample response:[{ "id": null, "name": "-", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression - expression", "expressionSyntax": "-", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "*", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression * expression", "expressionSyntax": "*", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "/", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression / expression", "expressionSyntax": "/", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "+", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression + expression", "expressionSyntax": "+", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "<", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression < expression", "expressionSyntax": "<", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "<=", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression <= expression", "expressionSyntax": "<=", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "<>", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression <> expression", "expressionSyntax": "<>", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "=", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression = expression", "expressionSyntax": "=", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": ">", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression > expression", "expressionSyntax": ">", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": ">=", "expression": null, "dataType": null, "formatDataType": null, "syntax": "expression >= expression", "expressionSyntax": ">=", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "AND", "expression": null, "dataType": null, "formatDataType": null, "syntax": "boolean_expression AND boolean_expression", "expressionSyntax": "AND", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "AVG", "expression": null, "dataType": null, "formatDataType": null, "syntax": "AVG (expression)", "expressionSyntax": "AVG", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "BETWEEN", "expression": null, "dataType": null, "formatDataType": null, "syntax": "BETWEEN (test_expression, begin_expression, end_expression)", "expressionSyntax": "BETWEEN", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "CASE WHEN...THEN...ELSE...END", "expression": null, "dataType": null, "formatDataType": null, "syntax": "CASE WHEN (boolean_expression) THEN (result_expression) [...n] [ELSE (else_result_expression)] END", "expressionSyntax": "CASE...WHEN...THEN...ELSE...END", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "CASE...WHEN...THEN...ELSE...END", "expression": null, "dataType": null, "formatDataType": null, "syntax": "CASE (input_expression) WHEN (when_expression) THEN (result_expression) [...n] [ELSE (else_result_expression)] END", "expressionSyntax": "CASE...WHEN...THEN...ELSE...END", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "CAST...AS", "expression": null, "dataType": null, "formatDataType": null, "syntax": "CAST (expression AS data_type)", "expressionSyntax": "CAST...AS", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "CONVERT", "expression": null, "dataType": null, "formatDataType": null, "syntax": "CONVERT (data_type [( length)], expression[, style])", "expressionSyntax": "CONVERT", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "COUNT", "expression": null, "dataType": null, "formatDataType": null, "syntax": "COUNT (expression)", "expressionSyntax": "COUNT", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "DATEADD", "expression": null, "dataType": null, "formatDataType": null, "syntax": "DATEADD (datepart, number, expression)", "expressionSyntax": "DATEADD", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "DATEDIFF", "expression": null, "dataType": null, "formatDataType": null, "syntax": "DATEDIFF (datepart, startdate, enddate)", "expressionSyntax": "DATEDIFF", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "DATEPART", "expression": null, "dataType": null, "formatDataType": null, "syntax": "DATEPART (datepart, date)", "expressionSyntax": "DATEPART", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "DISTINCT", "expression": null, "dataType": null, "formatDataType": null, "syntax": "DISTINCT (column) or DISTINCT column", "expressionSyntax": "DISTINCT", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "GETDATE", "expression": null, "dataType": null, "formatDataType": null, "syntax": "GETDATE ()", "expressionSyntax": "GETDATE", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "IF...THEN...ELSE...END", "expression": null, "dataType": null, "formatDataType": null, "syntax": "IF (boolean_expression) THEN (true_expression) [ELSE (false_expression)] END", "expressionSyntax": "IF...THEN...ELSE...END", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "IIF", "expression": null, "dataType": null, "formatDataType": null, "syntax": "IIF (boolean_expression, true_expression, [false_expression])", "expressionSyntax": "IIF", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "ISNULL", "expression": null, "dataType": null, "formatDataType": null, "syntax": "ISNULL (check_expression, replacement_value)", "expressionSyntax": "ISNULL", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "LEN", "expression": null, "dataType": null, "formatDataType": null, "syntax": "LEN (expression)", "expressionSyntax": "LEN", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "MAX", "expression": null, "dataType": null, "formatDataType": null, "syntax": "MAX (expression)", "expressionSyntax": "MAX", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "MIN", "expression": null, "dataType": null, "formatDataType": null, "syntax": "MIN (expression)", "expressionSyntax": "MIN", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "NOTBETWEEN", "expression": null, "dataType": null, "formatDataType": null, "syntax": "NOTBETWEEN (test_expression, begin_expression, end_expression)", "expressionSyntax": "NOTBETWEEN", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "OR", "expression": null, "dataType": null, "formatDataType": null, "syntax": "boolean_expression OR boolean_expression", "expressionSyntax": "OR", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "ROUND", "expression": null, "dataType": null, "formatDataType": null, "syntax": "ROUND (expression, length[, function])", "expressionSyntax": "ROUND", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "RUNNING AVG", "expression": null, "dataType": null, "formatDataType": null, "syntax": "RUNNINGAVG (column)", "expressionSyntax": "RUNNINGAVG", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "RUNNING COUNT", "expression": null, "dataType": null, "formatDataType": null, "syntax": "RUNNINGCOUNT (column)", "expressionSyntax": "RUNNINGCOUNT", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "RUNNING SUM", "expression": null, "dataType": null, "formatDataType": null, "syntax": "RUNNINGSUM (column)", "expressionSyntax": "RUNNINGSUM", "isOperator": false, "userDefined": false, "extendedProperties": {} }, { "id": null, "name": "SUM", "expression": null, "dataType": null, "formatDataType": null, "syntax": "SUM (expression)", "expressionSyntax": "SUM", "isOperator": false, "userDefined": false, "extendedProperties": {} }]
POST dataModel/loadRelationships¶
Returns an array of relationships of visible data sources, paged and sorted, filtered by search criteria.
Request
Payload: a RelationshipPagedRequest object
Response
A PagedResult object with result field containing an array of Relationship objects
Samples
POST /api/dataModel/loadRelationships HTTP/1.1Request payload:
{ "querySourceId": null, "tenantId": "b5b3a5cc-9e55-424c-ae85-ba92ec3b934e", "criteria": [{ "key": "All", "value": "SQL-North", "operation": 1 }], "pageIndex": 1, "pageSize": 1, "sortOrders": [{ "key": "DatabaseName", "descending": true }] }Sample response:
{ "result": [ { "joinConnectionId": "5e97b5e4-bf85-4be8-8244-cf195bdf4739", "foreignConnectionId": "5e97b5e4-bf85-4be8-8244-cf195bdf4739", "joinQuerySourceAlias": null, "foreignQuerySourceAlias": null, "joinFieldAlias": "", "specifictJoinFieldAlias": null, "foreignFieldAlias": "", "specifictForeignFieldAlias": null, "alias": null, "systemRelationship": true, "joinType": "Inner", "parentRelationshipId": null, "position": null, "relationshipPosition": 0, "relationshipKeyJoins": [], "reportId": null, "foreignAlias": null, "joinQuerySourceUniqueName": null, "joinFieldUniqueName": null, "forgeinQuerySourceUniqueName": null, "forgeinFieldUniqueName": null, "tempId": null, "aliasTempId": null, "originalId": "00000000-0000-0000-0000-000000000000", "isForeignDataObjectAlias": false, "selectedForeignAlias": "c7341994-3506-4f4a-acd9-96f9b8936ec2_Orders", "joinQuerySourceName": "Order Details", "joinQuerySourceId": "7f9cd714-9b06-4aaf-9a8b-5475ea0cdefc", "joinFieldId": "b577df4a-4419-4229-aefd-ad9caa0543ce", "joinFieldType": null, "foreignQuerySourceName": "Orders", "foreignQuerySourceId": "c7341994-3506-4f4a-acd9-96f9b8936ec2", "foreignFieldId": "da29f67c-6cc5-4aa8-b456-0b192ec07fa1", "foreignFieldType": null, "joinFieldName": "OrderID", "foreignFieldName": "OrderID", "joinDataSourceCategoryId": "31df942e-62be-4cda-8866-7ae7a65365f0", "joinDataSourceCategoryName": "Northwind-MSSQL", "foreignDataSourceCategoryId": "31df942e-62be-4cda-8866-7ae7a65365f0", "foreignDataSourceCategoryName": "Northwind-MSSQL", "comparisonOperator": null, "id": "fb386813-a7f8-4033-862a-4a9cafbad38c", "state": 0, "deleted": false, "inserted": true, "version": null, "created": null, "createdBy": "Pika Chu", "modified": "2017-09-14T10:12:06.667", "modifiedBy": null } ], "pageIndex": 1, "pageSize": 1, "total": 7, "skipItems": 0, "isLastPage": false }
POST dataModel/loadInvalidRelationships¶
Returns an array of invalid/modified relationships of visible data sources, paged and sorted, filtered by search criteria.
Request
Payload: a RelationshipPagedRequest object
Response
A PagedResult object with result field containing an array of Relationship objects
Samples
POST /api/dataModel/loadInvalidRelationships HTTP/1.1Request payload:
{ "querySourceId": null, "tenantId": null, "criteria": [ { "key": "All", "value": "", "operation": 1 } ], "pageIndex": 1, "pageSize": 10, "modifiedRelationships": [ { "id": null, "joinConnectionId": "a202de0e-649d-4c7b-b282-ccdae94585e6", "foreignConnectionId": "a202de0e-649d-4c7b-b282-ccdae94585e6", "joinQuerySourceId": "5fba843d-9c64-4079-b727-c110bce2ee3f", "foreignQuerySourceId": "21c34241-c070-47c4-9cc4-86c9893daa5a", "joinFieldId": "f3fd1ac9-254e-4527-a66c-304e05f369bc", "foreignFieldId": "9d927e1a-1abe-4656-b2a6-bd662c49efe3", "alias": "", "systemRelationship": false, "joinType": "Inner", "position": "61" }, { "id": null, "joinConnectionId": "a202de0e-649d-4c7b-b282-ccdae94585e6", "foreignConnectionId": "a202de0e-649d-4c7b-b282-ccdae94585e6", "joinQuerySourceId": "de6cc7be-98bb-4222-94ff-14f1ec6c5042", "foreignQuerySourceId": "5fba843d-9c64-4079-b727-c110bce2ee3f", "joinFieldId": "3f2e55c6-7796-4daf-b8d2-0b95e7ecdcf7", "foreignFieldId": "f3fd1ac9-254e-4527-a66c-304e05f369bc", "alias": "", "systemRelationship": false, "joinType": "Inner", "position": "60" } ], "sortOrders": [ { "key": "DatabaseName", "descending": true } ] }Sample response:{ "result": [ { "invalid": true, "errors": [ { "key": "Relationships", "messages": [ "Relationship cannot be created from the selected fields. Please check the data type of the joined fields." ] }, { "key": "Relationships[1]positionId", "messages": [ "This positionID is duplicated." ] } ], "joinConnectionId": "a5f9033a-1ddf-4cfe-a152-b0e062b98dc4", "foreignConnectionId": "a5f9033a-1ddf-4cfe-a152-b0e062b98dc4", "joinQuerySourceAlias": null, "foreignQuerySourceAlias": null, "joinFieldAlias": null, "specifictJoinFieldAlias": null, "foreignFieldAlias": null, "specifictForeignFieldAlias": null, "alias": "", "systemRelationship": false, "joinType": "Inner", "parentRelationshipId": null, "position": "1", "relationshipPosition": 0, "hasBeenModified": false, "relationshipKeyJoins": [], "reportId": null, "foreignAlias": null, "joinQuerySourceUniqueName": null, "joinFieldUniqueName": null, "forgeinQuerySourceUniqueName": null, "forgeinFieldUniqueName": null, "tempId": null, "aliasTempId": null, "originalId": "00000000-0000-0000-0000-000000000000", "isForeignDataObjectAlias": false, "positionId": 1, "selectedForeignAlias": "8c65d897-e945-40a8-916f-bfbdc082e83a_G", "joinQuerySourceName": "D", "joinQuerySourceId": "00766ac5-08ea-4306-aac9-6bf2aee58d77", "joinFieldId": "d151cb50-a159-4673-90a9-5887232c9ca7", "joinFieldType": null, "foreignQuerySourceName": "G", "foreignQuerySourceId": "8c65d897-e945-40a8-916f-bfbdc082e83a", "foreignFieldId": "a93ac69e-9bf7-4a3c-8507-466eff16cb79", "foreignFieldType": null, "joinFieldName": null, "foreignFieldName": null, "joinDataSourceCategoryId": "00000000-0000-0000-0000-000000000000", "joinDataSourceCategoryName": null, "foreignDataSourceCategoryId": "00000000-0000-0000-0000-000000000000", "foreignDataSourceCategoryName": null, "comparisonOperator": null, "id": null, "state": 1, "deleted": false, "inserted": false, "version": null, "created": null, "createdBy": "An Truong", "modified": null, "modifiedBy": null }, { "invalid": true, "errors": [ { "key": "Relationships", "messages": [ "This relationship is duplicated." ] }, { "key": "Relationships[3]positionId", "messages": [ "This positionID is duplicated." ] } ], "joinConnectionId": "a5f9033a-1ddf-4cfe-a152-b0e062b98dc4", "foreignConnectionId": "a5f9033a-1ddf-4cfe-a152-b0e062b98dc4", "joinQuerySourceAlias": null, "foreignQuerySourceAlias": null, "joinFieldAlias": "", "specifictJoinFieldAlias": null, "foreignFieldAlias": "", "specifictForeignFieldAlias": null, "alias": "", "systemRelationship": false, "joinType": "Right", "parentRelationshipId": null, "position": "3", "relationshipPosition": 0, "hasBeenModified": false, "relationshipKeyJoins": [], "reportId": null, "foreignAlias": null, "joinQuerySourceUniqueName": null, "joinFieldUniqueName": null, "forgeinQuerySourceUniqueName": null, "forgeinFieldUniqueName": null, "tempId": null, "aliasTempId": null, "originalId": "00000000-0000-0000-0000-000000000000", "isForeignDataObjectAlias": false, "positionId": 1, "selectedForeignAlias": "9326420d-8217-4b08-922d-926678b1c0bb_C", "joinQuerySourceName": "B", "joinQuerySourceId": "c2896b5b-8895-4a54-a402-25728b17d6dd", "joinFieldId": "3e580a0b-5d11-4441-adc3-fa94d93fec9b", "joinFieldType": null, "foreignQuerySourceName": "C", "foreignQuerySourceId": "9326420d-8217-4b08-922d-926678b1c0bb", "foreignFieldId": "8a1bbdf7-320a-49d6-b256-3cbef5b55920", "foreignFieldType": null, "joinFieldName": "Id", "foreignFieldName": "Id", "joinDataSourceCategoryId": "1cf932d6-8cea-4aa1-a5c0-23ddcb71be72", "joinDataSourceCategoryName": "CycleRelationship", "foreignDataSourceCategoryId": "1cf932d6-8cea-4aa1-a5c0-23ddcb71be72", "foreignDataSourceCategoryName": "CycleRelationship", "comparisonOperator": null, "id": "eec27f1b-dc25-48be-9f5c-a5eda552e521", "state": 0, "deleted": false, "inserted": true, "version": null, "created": null, "createdBy": "An Truong", "modified": "2018-12-05T10:53:29", "modifiedBy": null }, { "invalid": true, "errors": [ { "key": "Relationships", "messages": [ "This relationship is duplicated." ] } ], "joinConnectionId": "a5f9033a-1ddf-4cfe-a152-b0e062b98dc4", "foreignConnectionId": "a5f9033a-1ddf-4cfe-a152-b0e062b98dc4", "joinQuerySourceAlias": null, "foreignQuerySourceAlias": null, "joinFieldAlias": null, "specifictJoinFieldAlias": null, "foreignFieldAlias": null, "specifictForeignFieldAlias": null, "alias": "", "systemRelationship": false, "joinType": "Right", "parentRelationshipId": null, "position": "2", "relationshipPosition": 0, "hasBeenModified": false, "relationshipKeyJoins": [], "reportId": null, "foreignAlias": null, "joinQuerySourceUniqueName": null, "joinFieldUniqueName": null, "forgeinQuerySourceUniqueName": null, "forgeinFieldUniqueName": null, "tempId": null, "aliasTempId": null, "originalId": "00000000-0000-0000-0000-000000000000", "isForeignDataObjectAlias": false, "positionId": null, "selectedForeignAlias": "9326420d-8217-4b08-922d-926678b1c0bb_C", "joinQuerySourceName": "B", "joinQuerySourceId": "c2896b5b-8895-4a54-a402-25728b17d6dd", "joinFieldId": "3e580a0b-5d11-4441-adc3-fa94d93fec9b", "joinFieldType": null, "foreignQuerySourceName": "C", "foreignQuerySourceId": "9326420d-8217-4b08-922d-926678b1c0bb", "foreignFieldId": "8a1bbdf7-320a-49d6-b256-3cbef5b55920", "foreignFieldType": null, "joinFieldName": null, "foreignFieldName": null, "joinDataSourceCategoryId": "00000000-0000-0000-0000-000000000000", "joinDataSourceCategoryName": null, "foreignDataSourceCategoryId": "00000000-0000-0000-0000-000000000000", "foreignDataSourceCategoryName": null, "comparisonOperator": null, "id": null, "state": 1, "deleted": false, "inserted": false, "version": null, "created": null, "createdBy": "An Truong", "modified": null, "modifiedBy": null } ], "pageIndex": 1, "pageSize": 3, "total": 3, "skipItems": 0, "isLastPage": true }
POST dataModel/relationships¶
Saves an array of relationships.
After adding a new relationship, POST DataModel/loadRelationships should be called to retrieve the new id value.
Request
Payload: an array of Relationship objects
Response
An OperationResult object with success field true if the save is successful
Samples
POST /api/dataModel/relationships HTTP/1.1Request payload to insert one new relationship and update another:
[{ "id" : null, "joinConnectionId" : "ca24a47e-ffdd-4391-a82a-254f48b451e5", "foreignConnectionId" : "ca24a47e-ffdd-4391-a82a-254f48b451e5", "joinQuerySourceId" : "d310d0ec-06b3-409f-b48c-1f519d0a51d5", "foreignQuerySourceId" : "9fb719f8-8a70-4f4e-91d5-4e8372413d92", "joinFieldId" : "79c398b3-bc5d-4c68-9329-111a7125ad0d", "foreignFieldId" : "aff13fd8-b7dc-439d-bfbf-1cd1a1728565", "alias" : "", "systemRelationship" : false, "joinType" : "Inner", "position" : "191" }, { "id" : "c7288fb3-1f9d-49c3-897e-1587d6ccda5f", "joinConnectionId" : "ca24a47e-ffdd-4391-a82a-254f48b451e5", "foreignConnectionId" : "ca24a47e-ffdd-4391-a82a-254f48b451e5", "joinQuerySourceId" : "e03b8805-60ae-41df-b69a-f3bece9721c5", "foreignQuerySourceId" : "9fb719f8-8a70-4f4e-91d5-4e8372413d92", "joinFieldId" : "322d9f3d-1f65-4d60-9cac-933a2c40db9d", "foreignFieldId" : "484817ea-f130-417b-a096-32c13249b7d0", "alias" : "", "systemRelationship" : false, "joinType" : "Inner", "modified" : "2016-04-15T03:57:37.803", "position" : "185" } ]Sample response:
{ "success" : true, "messages" : [], "data" : null }
DELETE dataModel/relationship/{relationship_id}¶
Deletes the relationship specified by the {relationship_id} value.
Request
No payload
Response
An OperationResult object with success field true if deletion is successful
Samples
DELETE /api/dataModel/relationship/457dbf49-9b1d-42d0-9026-0e67ee86a912 HTTP/1.1Successful response:
{ "success" : true, "messages" : [], "data" : null }Response when trying to delete a system relationship:
{ "success": false, "messages": [ { "key": "", "detail": null, "messages": [ "System relationship cannot be deleted." ] } ], "data": null }
GET dataModel/schema/(tenant_id)¶
Returns an array of query sources and an array of relationships with color properties (filtered by tenant_id if given).
Request
No payload
Response
A DataModelSchema object
Samples
GET /api/dataModel/schema HTTP/1.1Sample response for 2 relationships “Customer” Left joins with “Orders” and “Orders” Inner joins with “Order Details”:
{ "querySources": [{ "id": "8aa52ba9-8324-4b8e-bf42-619a3f050aa5", "name": "dbo.Customers", "type": "Table", "color": null, "connectionId": "8195a480-ddd8-4915-95a0-432e24fed0ad", "modified": "2016-04-19T03:08:56.091528", "fields": [{ "name": "ContactName", "properties": "" }, { "name": "CustomerID", "properties": "{\"PrimaryKey\":true}" }] }, { "id": "66dcf36e-e4b0-4c9b-9919-b9ba49377784", "name": "dbo.Orders", "type": "Table", "color": null, "connectionId": "8195a480-ddd8-4915-95a0-432e24fed0ad", "modified": "2016-12-19T03:08:56.091528", "fields": [{ "name": "CustomerID", "properties": "" }, { "name": "OrderDate", "properties": "" }, { "name": "OrderID", "properties": "{\"PrimaryKey\":true}" }] }, { "id": "26efbdf4-c724-4824-bd9c-6ae1e2dc7435", "name": "dbo.Order Details", "type": "Table", "color": null, "connectionId": "8195a480-ddd8-4915-95a0-432e24fed0ad", "modified": "2016-12-19T03:08:56.091528", "fields": [{ "name": "OrderID", "properties": "{\"PrimaryKey\":true}" }, { "name": "ProductID", "properties": "{\"PrimaryKey\":true}" }, { "name": "Quantity", "properties": "" }, { "name": "UnitPrice", "properties": "" }] }], "relationships": [{ "joinQuerySourceId": "8aa52ba9-8324-4b8e-bf42-619a3f050aa5", "foreignQuerySourceId": "66dcf36e-e4b0-4c9b-9919-b9ba49377784", "twoWays": false }, { "joinQuerySourceId": "66dcf36e-e4b0-4c9b-9919-b9ba49377784", "foreignQuerySourceId": "26efbdf4-c724-4824-bd9c-6ae1e2dc7435", "twoWays": true }] }
POST dataModel/schema/updateConnectionColor¶
Updates the connection color.
Request
A ConnectionColor object.
Response
An OperationResult object.
Samples
POST api/dataModel/schema/updateConnectionColor HTTP/1.1Request Payload:
{ "connectionId" : "6cc06e5b-0627-432c-bc33-708b0843c7c7", "color": null, "modified": "2017-09-15T03:11:24" }Sample Response:
{ "success": true, "messages": null, "data": null }
GET dataModel/relationship/joinTypes¶
Returns an array of join types currently supported.
Request
No payload
Response
An array of string values.
Currently supported are: “Inner”, “Left”, “Right”, “Full” and “Cross”.
Samples
GET api/dataModel/relationship/joinTypes HTTP/1.1Sample response:
["Inner", "Left", "Right", "Full", "Cross"]
GET dataModel/databaseMapping¶
Returns an array of database mappings.
Request
No payload
Response
An array of GlobalDatabaseMapping objects
Samples
GET api/dataModel/databaseMapping HTTP/1.1Sample response:
[ { "fromServer": "SERVER1", "toServer": "SERVER2", "fromDatabaseName": "[MSSQL] Northwind", "fromDatabaseUser": null, "type": 2, "fromObject": "connection_name", "toDatabaseName": "[MSSQL] northwind", "toDatabaseUser": null, "toObject": "connection_name_2", "selectAllTenants": true, "tenantIds": "null", "tenants": null, "errorType": 0, "id": "258bbcf9-4bd1-49de-8728-1578bb4aefa7", "state": 0, "deleted": false, "inserted": true, "version": 1, "created": "2017-04-14T04:18:50.4000000-07:00", "createdBy": "John Doe", "modified": "2017-04-14T04:18:50.4000000-07:00", "modifiedBy": "John Doe" } ]
POST dataModel/databaseMapping¶
Saves an array of database mappings.
Request
An array of GlobalDatabaseMapping objects
Response
An OperationResult object, with success field true if the save is successful
Samples
POST api/dataModel/databaseMapping HTTP/1.1Request Payload:
[ { "id": null, "fromServer": "SERVER1", "fromDatabaseName": "[MSSQL] Northwind", "fromDatabaseUser": "User1", "type": 2, "fromObject": "connection_name", "toServer": "SERVER2", "toDatabaseName": "[MSSQL] northwind", "toDatabaseUser": "User1", "toObject": "connection_name_2", "tenants": null, "state": 1, "selectAllTenants": true } ]Sample response in case user has System Admin Permission:
{ "success":true "messages" : [], "data" : null }Sample response in case user does not have System Admin Permission:
{ "message" : "You don't have permission to perform this action", "detail" : "NoPermission" }
POST dataModel/loadDatabaseNames¶
Returns a paged array of database servers and database names.
Request
A DatabaseMappingPagedRequest object
Notes: Key for SearchCriteria support to search: “databaseName”
Response
A PagedResult object, with result field containing an array of the following object:
Field Description Note serverTypeName
stringThe type of the database server (MSSQL, Oracle, MySQL, etc.) databaseServer
stringThe name of the server databaseName
stringThe name of the database databaseUser
stringThe database username (encrypted) New in version 3.10.0.
Samples
POST api/dataModel/loadDatabaseNames HTTP/1.1Request Payload:
{ "pageIndex": 1, "pageSize": 10, "loadFromDatabase": true }Sample response:
{ "result": [ { "serverTypeName": "AZSQL", "databaseServer": "abc.database.windows.net", "databaseName": "Northwind", "databaseUser": "ijoBJ2mYel+pdMBQ==" }, { "serverTypeName": "MSSQL", "databaseServer": "localhost", "databaseName": "Northwind", "databaseUser": "X2hL0wD8EeMj8rfXw==" }, { "serverTypeName": "MYSQL", "databaseServer": "192.168.1.1", "databaseName": "northwind", "databaseUser": "gJybx9qkcwecg==" }, { "serverTypeName": "ORACL", "databaseServer": "192.168.1.1:1521/orcl", "databaseName": "orcl", "databaseUser": "sLpuLgFFUcKwxUBw==" }, { "serverTypeName": "PGSQL", "databaseServer": "192.168.1.1", "databaseName": "DB", "databaseUser": "obgdUndXx6y0JYk==" } ], "pageIndex": 1, "pageSize": 10, "total": 5, "skipItems": 0, "isLastPage": true }
POST dataModel/loadDatabaseObjects¶
Returns a paged array of connection names or schema names in the specified database.
Request
A DatabaseMappingPagedRequest object
Response
A PagedResult object, with result field containing an array of strings.
If DatabaseMappingPagedRequest.``type`` is
2(Database), return connection names, else return schema names.Notes: Key for SearchCriteria support to search: “objectName”
Samples
POST api/dataModel/loadDatabaseObjects HTTP/1.1Request Payload for Database:
{ "pageIndex": 1, "pageSize": 10, "databaseServer": "SERVER1", "databaseName": "[MSSQL] Northwind", "type": 2 }Sample response for Database:
{ "result": [ "connection_name" ], "pageIndex": 1, "pageSize": 10, "total": 1, "skipItems": 0, "isLastPage": true }Request Payload for Schema:
{ "pageIndex": 1, "pageSize": 10, "databaseServer": "SERVER1", "databaseName": "[MSSQL] Northwind", "type": 1 }Sample response for Schema:
{ "result": [ "dbo" ], "pageIndex": 1, "pageSize": 10, "total": 1, "skipItems": 0, "isLastPage": true }
POST dataModel/customQuerySource¶
Executes and saves a custom query source.
Request
A QuerySource object
Response
The saved QuerySource object.
Samples
POST api/dataModel/customQuerySource HTTP/1.1Request Payload:
{ "name": "test", "connectionId": "6cc06e5b-0627-432c-bc33-708b0843c7c7", "categoryId": "94454a0a-17a2-49e5-bebf-9c0d07d95587", "customDefinition": "Select OrderID\nfrom orders" }Sample response:{ "realName": "test", "id": "193a0a94-decf-4217-9e0c-1956f0335f59", "name": "northwind.test", "type": "View", "parentQuerySourceId": null, "categoryId": "94454a0a-17a2-49e5-bebf-9c0d07d95587", "selected": true, "deleted": false, "connectionId": "6cc06e5b-0627-432c-bc33-708b0843c7c7", "connectionName": null, "childs": null, "dataSourceCategoryId": null, "dataSourceCategoryName": null, "alias": "", "originalAlias": null, "querySourceFields": [ { "name": "OrderID", "alias": "", "dataType": "INT", "izendaDataType": "Numeric", "allowDistinct": true, "visible": false, "filterable": false, "querySourceId": "193a0a94-decf-4217-9e0c-1956f0335f59", "parentId": null, "expressionFields": [], "filteredValue": "", "type": 0, "groupPosition": 0, "position": 1, "extendedProperties": "", "physicalChange": 0, "approval": 0, "existed": false, "matchedTenant": false, "functionName": null, "expression": null, "fullName": null, "calculatedTree": null, "reportId": null, "originalName": null, "originalId": "00000000-0000-0000-0000-000000000000", "isParameter": false, "isCalculated": false, "hasAggregatedFunction": false, "querySource": null, "querySourceName": null, "categoryName": null, "inaccessible": false, "originalAlias": null, "fullPath": null, "isCheck": false, "id": "269987d3-7a92-4ba4-9e99-3829905f471b", "state": 0, "deleted": false, "inserted": true, "version": null, "created": null, "createdBy": "System5 Admin5", "modified": "2017-09-15T04:52:03.0678276", "modifiedBy": null } ], "querySourceCategoryName": null, "querySourceCategory": null, "modified": "2017-09-15T04:52:02.9811771", "extendedProperties": null, "physicalChange": 0, "approval": 0, "existed": false, "checked": false, "belongToCopiedReport": false, "customDefinition": "Select OrderID\nfrom orders", "isCustomQuerySource": true, "isCheck": false, "disabled": false, "fullPath": null, "indeterminate": false, "numOfChilds": 0, "numOfCheckedChilds": 0 }
POST dataModel/validateCustomQuerySource¶
Validates that name of custom query source is unique.
Request
A QuerySource object, with name and categoryId fields populated.
Response
An OperationResult object, with success field true if View Name is unique and other custom view contents are valid.
Samples
POST api/dataModel/validateCustomQuerySource HTTP/1.1Request Payload:
{ "name": "Test", "connectionId": "3562497d-07a1-4a0a-823d-811b8a098d73", "categoryId": "533f001b-88ef-46d5-b0c1-69b774c81aba", "customDefinition": "SELECT customerID,employeeID\nFROM Orders " }Sample response:
{ "success": true, "confirmation": false, "messages": [] }
POST dataModel/deleteCustomQuerySource¶
Deletes a custom query source.
Request
A QuerySource object, with id field populated.
Response
An OperationResult object, with success true if the deletion is successful.
Samples
POST api/dataModel/deleteCustomQuerySource HTTP/1.1Request Payload:
{"id":"e26a908a-5d76-473e-a364-91afe9147023"}Sample Response:
{ "success": true }
GET dataModel/querySource/{query_source_id}¶
Returns the query source specified by query_source_id.
Request
No payload
Response
A QuerySource object.
Samples
GET api/dataModel/querySource/c7341994-3506-4f4a-acd9-96f9b8936ec2 HTTP/1.1Sample Response:
{ "realName": null, "id": "c7341994-3506-4f4a-acd9-96f9b8936ec2", "name": "Orders", "type": "Table", "parentQuerySourceId": null, "categoryId": "942c529a-38c7-4ffc-9e46-044c3f364130", "selected": true, "deleted": false, "connectionId": "00000000-0000-0000-0000-000000000000", "connectionName": null, "childs": null, "dataSourceCategoryId": "31df942e-62be-4cda-8866-7ae7a65365f0", "dataSourceCategoryName": null, "alias": null, "originalAlias": null, "querySourceFields": [], "querySourceCategoryName": null, "querySourceCategory": null, "modified": "2017-09-15T03:57:38", "extendedProperties": null, "physicalChange": 0, "approval": 0, "existed": false, "checked": false, "belongToCopiedReport": false, "customDefinition": null, "isCustomQuerySource": false, "isCheck": false, "disabled": false, "fullPath": null, "indeterminate": false, "numOfChilds": 0, "numOfCheckedChilds": 0 }
POST dataModel/validateRelationships¶
Validate the newly added or modified relationship(s).
Request
An array of Relationship object.
Response
An object with the following properties:
Field NULL Description Note success
booleantrue if there is no invalid relationship (duplicate relationship, duplicate positionId, inconsistent datatype)
false if there is at least one invalid relationshiphasPositionId
booleanN Indicate whether there is any relationship has positionId
Samples
POST api/dataModel/validateRelationships HTTP/1.1Request Payload:
{ "relationships": [ { "id": null, "joinConnectionId": "a202de0e-649d-4c7b-b282-ccdae94585e6", "foreignConnectionId": "a202de0e-649d-4c7b-b282-ccdae94585e6", "joinQuerySourceId": "5fba843d-9c64-4079-b727-c110bce2ee3f", "foreignQuerySourceId": "21c34241-c070-47c4-9cc4-86c9893daa5a", "joinFieldId": "f3fd1ac9-254e-4527-a66c-304e05f369bc", "foreignFieldId": "9d927e1a-1abe-4656-b2a6-bd662c49efe3", "alias": "", "systemRelationship": false, "joinType": "Inner", "position": "61" }, { "id": null, "joinConnectionId": "a202de0e-649d-4c7b-b282-ccdae94585e6", "foreignConnectionId": "a202de0e-649d-4c7b-b282-ccdae94585e6", "joinQuerySourceId": "de6cc7be-98bb-4222-94ff-14f1ec6c5042", "foreignQuerySourceId": "5fba843d-9c64-4079-b727-c110bce2ee3f", "joinFieldId": "3f2e55c6-7796-4daf-b8d2-0b95e7ecdcf7", "foreignFieldId": "f3fd1ac9-254e-4527-a66c-304e05f369bc", "alias": "", "systemRelationship": false, "joinType": "Inner", "position": "60" } ] }Sample Response:
{ "success" : true, "hasPositionId" : false }