Data Model APIs

The Data Model section allows users to:

manage data sources and data source fields
add computed columns

delete columns
manage relationships

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/loadQuerySourcesHTTP/1.1

Request 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/loadQuerySourceFieldsHTTP/1.1

Request 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/basicConnectionsInfoHTTP/1.1

Sample 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/basicConnectionsInfoHTTP/1.1

Sample 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/connectionsInfoHTTP/1.1

Request 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:
- key is the id of the QuerySourceCategory
- value is the name of the QuerySourceCategory

Samples

GET/api/dataModel/basicQuerySourceCategoriesInfo/2046c03b-3830-4385-9ac0-bdc95e92ea49HTTP/1.1

Sample 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/basicQuerySourceCategoriesInfoHTTP/1.1

Sample 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:
- key is the id of the QuerySource
- value is the name of the QuerySource

Samples

GET/api/dataModel/basicQuerySourcesInfo/48733501-c57d-48ca-aded-501d5ebdaad9HTTP/1.1

Sample 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/TableHTTP/1.1

Sample 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-5c87004c4837HTTP/1.1

Sample 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:
- key is the id of the QuerySourceField
- value if the name of the QuerySourceField

Samples

GET/api/dataModel/basicQuerySourceFieldsInfo/4e9aabda-9a95-4a00-8d80-0b8b1fbc7bc8HTTP/1.1

Sample 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/dataModelHTTP/1.1

Request 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/indicatorHTTP/1.1

Sample 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/reloadQuerySourceFieldsHTTP/1.1

Request 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}]}

{"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/validateCalculatedFieldHTTP/1.1

Request 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/functionOperatorsHTTP/1.1

Sample 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}

[{"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/loadRelationshipsHTTP/1.1

Request 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/loadInvalidRelationshipsHTTP/1.1

Request 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}]}

{"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/relationshipsHTTP/1.1

Request 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-0e67ee86a912HTTP/1.1

Successful 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/schemaHTTP/1.1

Sample 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

POSTapi/dataModel/schema/updateConnectionColorHTTP/1.1

Request 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

GETapi/dataModel/relationship/joinTypesHTTP/1.1

Sample 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

GETapi/dataModel/databaseMappingHTTP/1.1

Sample 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

POSTapi/dataModel/databaseMappingHTTP/1.1

Request 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
string The type of the database server (MSSQL, Oracle, MySQL, etc.)
databaseServer
string The name of the server
databaseName
string The name of the database
databaseUser
string The database username (encrypted)
New in version 3.10.0.

Samples

POSTapi/dataModel/loadDatabaseNamesHTTP/1.1

Request 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

POSTapi/dataModel/loadDatabaseObjectsHTTP/1.1

Request 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

POSTapi/dataModel/customQuerySourceHTTP/1.1

Request Payload:

{"name":"test","connectionId":"6cc06e5b-0627-432c-bc33-708b0843c7c7","categoryId":"94454a0a-17a2-49e5-bebf-9c0d07d95587","customDefinition":"Select OrderID\nfrom orders"}

{"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

POSTapi/dataModel/validateCustomQuerySourceHTTP/1.1

Request 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

POSTapi/dataModel/deleteCustomQuerySourceHTTP/1.1

Request 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

GETapi/dataModel/querySource/c7341994-3506-4f4a-acd9-96f9b8936ec2HTTP/1.1

Sample 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
boolean true if there is no invalid relationship (duplicate relationship, duplicate positionId, inconsistent datatype)
false if there is at least one invalid relationship
hasPositionId
boolean N Indicate whether there is any relationship has positionId

Samples

POSTapi/dataModel/validateRelationshipsHTTP/1.1

Request 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}

Field	Description	Note
serverTypeName string	The type of the database server (MSSQL, Oracle, MySQL, etc.)
databaseServer string	The name of the server
databaseName string	The name of the database
databaseUser string	The database username (encrypted)	New in version 3.10.0.

Field	NULL	Description	Note
success boolean		true if there is no invalid relationship (duplicate relationship, duplicate positionId, inconsistent datatype) false if there is at least one invalid relationship
hasPositionId boolean	N	Indicate whether there is any relationship has positionId

Data Model APIs

List of APIs

POST dataModel/loadQuerySources

POST dataModel/loadQuerySourceFields

GET dataModel/basicConnectionsInfo/(tenant_id)

POST dataModel/basicConnectionsInfo

POST dataModel/connectionsInfo

GET dataModel/basicQuerySourceCategoriesInfo/{connection_id}

POST dataModel/basicQuerySourceCategoriesInfo/

GET dataModel/basicQuerySourcesInfo/{connection_id}/(type)

GET dataModel/querySourcesInfo/{connection_id}/(type)

DELETE dataModel/querySourceField/{query_source_field_id}

GET dataModel/basicQuerySourceFieldsInfo/{query_source_id}

POST dataModel

GET dataModel/indicator/(tenant_id)

POST dataModel/reloadQuerySourceFields

POST dataModel/validateCalculatedField

POST dataModel/functionOperators/

POST dataModel/loadRelationships

POST dataModel/loadInvalidRelationships

POST dataModel/relationships

DELETE dataModel/relationship/{relationship_id}

GET dataModel/schema/(tenant_id)

POST dataModel/schema/updateConnectionColor

GET dataModel/relationship/joinTypes

GET dataModel/databaseMapping

POST dataModel/databaseMapping

POST dataModel/loadDatabaseNames

POST dataModel/loadDatabaseObjects

POST dataModel/customQuerySource

POST dataModel/validateCustomQuerySource

POST dataModel/deleteCustomQuerySource

GET dataModel/querySource/{query_source_id}

POST dataModel/validateRelationships

Related articles