List of APIs

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).

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.

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.

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.

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.

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

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

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

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/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"}

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

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}