This article applies to the Admin Console > General > Database Settings.

The Database Settings allow administrators to adjust how Exago interfaces with the data sources. Additional type-specific settings allow you to specify which driver to utilize when connecting to each data source.

Settings

Select your version of Exago from the Application Version dropdown on the right-side of this article to show only the settings available in your version.

The following Database Settings are available:

Database Settings

Database Timeout

A value that is passed to the data provider's CommandTimeout property as Exago queries the business intelligence data source. It is up to the data provider what to do with this value. This timeout has no effect on connections to Folder Management or Storage Management databases, if applicable.

Caution

This value is passed on to the data source provider when the report is executed. Each data source driver, data source or individual database may interpret this setting slightly differently. Consult the official product documentation associated with your data source implementation for complete details how this setting will impact your environment.

Note

This setting will also control the maximum number of seconds that a Web Service API method can run. If set to 0 the Web Service API time out will be infinite.

Database Row Limit

Maximum number of rows returned on an execution. This only applies to Tables, Views and Functions. Set to 0 to return all rows.

Disable Non-Joined Data Objects

If True users are not able to add Data Objects to a report that do not have a join path with at least one other Data Object on the report. Set to False to disable this restriction.

Enable Special Cartesian Processing

If True any one-to-many Joins will cause special processing to avoid data repeating on the report. Set to False to disable this behavior.

Type-Specific Database Settings

Each type of Data Source has the following settings available.

Click the AutoFill icon to automatically fill the Data Provider, Table Schema Properties, View Schema Properties, Function Schema Properties and Procedure Schema Properties fields for each database type. Fields will be filled with data from the drivers provided with the application.

Data Provider

Specify the name that can be used programmatically to refer to the data provider.

This matches the InvariantName found as a property of DbProviderFactories in the server's machine.config file.

Note

The machine.config file is located in the %SystemRoot%\Microsoft.NET\Framework\%VersionNumber%\CONFIG\ directory.

Table Schema Properties

Specifies how to retrieve the schema of tables.

View Schema Properties

Specifies how to retrieve the schema of views.

Function Schema Properties

Specifies how to retrieve the schema of functions.

Procedure Schema Properties

Specifies how to retrieve the schema of procedures.

Note

For any of the Schema Property settings you can dynamically refer to properties from the Data Source’s connection string by surrounding the property name in @ symbols. For example, "@database@" will be replaced with the database name from the connection string of the Data Source being queried.

Converting Formula Sorts to SQL

When Convert Formula Filters and Sorts to SQL is True, Exago will attempt to push all formula sorts to the database instead of running them in memory. This feature converts formula sorts on a report to SQL syntax so that they can be appended to the ORDER BY clause of the query. When applicable, formula sorting will then be pushed to the database level of report execution, improving execution performance.

For example, a report may be sorted by the following formula, which organizes the data based upon whether or not an Order has a Revenue of greater than or equal to $1000.

=(OrderDetails.Revenue)>=1000

This formula sort would be converted to the following SQL statement and appended to the ORDER BY clause of the query during report execution.

ORDER BY
  (CASE WHEN dbo.[OrderDetails].[Quantity] * dbo.[OrderDetails].[UnitPrice] >=
   1000 THEN '1' ELSE '0' END) asc

Formula Dictionary

All defined Exago formula to SQL statement translations can be found in the dbconfigs.json file. The translations are located under the Formula Dictionary property of each database type listed in this file. See Managing the dbconfigs.json File for more information.

For example, the following contains part of the Formula Dictionary for the MySQL database:

"FormulaDictionary": 
{
   "and": ["({0} AND {1})"],
   "false":["(1=0)"],
   "if": ["(CASE WHEN {0} THEN {1} ELSE {2} END)"],
   "or": ["({0} OR {1})"],
   "len": ["CHAR_LENGTH({0})"],
   "true":["(1=1)"],
   "date": ["STR_TO_DATE('{0}/{1}/{2}', '%Y/%m/%d')"],
   ...
},

SQL syntax translations may be added in this file for any custom functions that are defined. Functions may also be overloaded, meaning that a client can define multiple versions of a function that can take a variable amount of arguments and Exago will choose the correct function based on the number of arguments that are passed to it. Functions with an unbounded number of arguments, however, cannot be defined.

Notes on Behavior

Please take the following information into consideration when utilizing this feature:

• Non-SQL databases (e.g., Mongo, ElasticSearch, Excel) do not support this feature. All sorting for reports using non-SQL databases will occur in-memory.
• DB2 is supported by this feature; however, there are limitations regarding what formulas can be translated to SQL when using this database type. In v2020.1.2+, most functions will be translated to SQL except for DateAdd, DateDiff, MonthName, Time and Mid.
• SQLite supports translating most functions to SQL with the exception of the Financial and Arithmetic & Geometric functions and some of the Database & Data Type and String functions.
• If a sort contains reference to an object from a different data source than the one that is being queried for report execution, then all sorting will occur in-memory.
• If a sort contains reference to an object from an Excel data source, then all sorting will occur in-memory.
• If no sorts exist on a report then no SQL will be appended to the query.
• Boolean expressions will prioritize False values when set to ascending and True values when set to descending.
• If sorts that cannot be properly translated to SQL exist on a report, then SQL statements will be generated and appended to the database query up to the sort that cannot be translated. For example, if the third sort on a report is untranslatable, then only the first two sorts will be translated and appended.
• Calculations that are pushed to the database may differ in comparison to those run in-memory by Exago. For example, pi (π) is rounded to the 15th digit in Exago but is only rounded to the 6th digit in MySQL by default. These differences in calculations vary depending on the database type and should be taken into consideration when enabling this feature.

Security Considerations

Protect your data from unauthorized SQL injection

This feature converts some Exago formulas to SQL — e.g., If(a, b, c) is converted to CASE WHEN a THEN b ELSE c END — so that they can be evaluated in the database for improved performance. During this process, function arguments are inserted into the SQL as-is. Without proper precautions, users could potentially inject malicious SQL into formulas in order to modify or view unauthorized data.

Warning

Please contact your database administrator to ensure that the connection string to the Data Source is read-only. Do not enable formula conversion to SQL without a restricted connection string for each accessible source.

Test for data inconsistencies

There are innate differences between how Exago and databases evaluate formulas. Equivalent formulas may return inconsistent data in some situations. Mathematical equations may have differing levels of precision, which can alter the result. Date functions are known to have some divergent behavior as well.

Test this feature in a staging environment to ensure that there are no material abnormalities in formula output. If experiencing issues, either disable this feature or consider editing the function conversion table to better reflect your desired output. See Managing the dbconfigs.json File.