PA Configuration database table: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
(36 intermediate revisions by 2 users not shown)
Line 15: Line 15:
||<span style="color:green;font-weight:bold;">SandboxDatabaseConnectionString</span>
||<span style="color:green;font-weight:bold;">SandboxDatabaseConnectionString</span>
||
||
||Connection string to scripting sandbox database (ETL). If not defined, ETL scripts cannot be run. Connection string for the scripting sandbox database is similar to the  [[Web.config_file#Connection_Strings_Section|QPR ProcessAnalyzer database connection string]].
||Connection string to scripting sandbox database (ETL). If not defined, SQL-based ETL scripts cannot be run. Connection string for the scripting sandbox database is similar to the  [[Web.config_file#Connection_Strings_Section|QPR ProcessAnalyzer database connection string]]. More information: [[Setting up Scripting Sandbox]].
|-
|-
||<span style="color:green;font-weight:bold;">DefaultUiLanguage</span>
||<span style="color:green;font-weight:bold;">DefaultUiLanguage</span>
Line 24: Line 24:
* English: '''en_US'''
* English: '''en_US'''
* German: '''de_DE'''
* German: '''de_DE'''
* Russian: '''ru_RU'''
* Spanish: '''es_ES'''
* Spanish: '''es_ES'''
* Swedish: '''sv_SE'''
* Swedish: '''sv_SE'''
Line 40: Line 39:
||false
||false
||Defines whether the 12-hour clock is used by default (instead of the 24-hour clock) for the new user accounts when showing time information in the UI. Defined as '''true''' or '''false'''. More information about the 12-hour clock: https://en.wikipedia.org/wiki/12-hour_clock.
||Defines whether the 12-hour clock is used by default (instead of the 24-hour clock) for the new user accounts when showing time information in the UI. Defined as '''true''' or '''false'''. More information about the 12-hour clock: https://en.wikipedia.org/wiki/12-hour_clock.
|-
||<span style="color:green;font-weight:bold;">SqlServerConnectionString</span>
||
||ADO.Net connection string for the SQL Server database containing the datatables data. It's recommended to use separate database, but it's also possible to connect to the same database as the [[Web.config_file#Database_Connection_String|metadata database]]. When this setting has been configured, new datatables are created to this database instead of the metadata database. The old datatables are still located in the metadata database, and new datatables cannot be created to the metadata database anymore. Note that the connection uses ADO.Net (not ODBC), so the connection string is similar to the metadata database connection string in the [[Web.config_file#Database_Connection_String|web.config]] file.
|-
||<span style="color:green;font-weight:bold;">SnowflakeConnectionString</span>
||
||ODBC connection string for the Snowflake connection, that needs to be configured to process models in the Snowflake. The connection string has following format:
<pre>
Driver={SnowflakeDSIIDriver};Application=QPR_ProcessAnalyzer;Server=<account_identifier>.snowflakecomputing.com;Database=QPRPA;Schema=QPRPA;Warehouse=QPRPA;Role=QPRPA;uid=QPRPA;pwd=<password>
</pre>
where <password> is the Snowflake user password and <account_identifier> is the Snowflake account identifier. In the example, the database, schema, warehouse, role and user are named to QPRPA. In addition to this setting, the Snowflake ODBC driver needs to be installed in the machine running the QPR ProcessAnalyzer Server. When this setting has been configured, users can create Snowflake stored datatables and models using Snowflake calculation. More information about [[Snowflake_Connection_Configuration|Snowflake connection configuration]].
|-
|-
||NumberOfParallelModelReaders
||NumberOfParallelModelReaders
Line 55: Line 66:


This setting affects only the model loading during the server startup and it doesn't restrict models loadings initiated by users.
This setting affects only the model loading during the server startup and it doesn't restrict models loadings initiated by users.
|-
||QueryTimeout
||300
||Timeout (in seconds) for requests made to /api/expression/query and /api/expression endpoints. When the timeout is exceeded, the query is stopped and a timeout error is returned. Purpose of the timeout is to protect the system against potentially too long running or even never-ending queries which might otherwise jam the system.
|-
|-
||AllowExternalDatasources
||AllowExternalDatasources
||True
||True
||Determines whether the [[Generic_Functions_in_QPR_ProcessAnalyzer#Other_functions|ImportOdbc]] function in the expression language and the [[QPR_ProcessAnalyzer_Model_Datasources#Loading_Data_from_ODBC_Datasource|ODBC model datasource]] can be used. This setting is for disabling the ODBC interfaces for data security reasons. By default, the ODBC interfaces are available.
||Determines whether the [[Generic_Functions_in_QPR_ProcessAnalyzer#Other_functions|ImportOdbc]] and CallWebService functions in the expression language and the [[QPR_ProcessAnalyzer_Model_Datasources#Loading_Data_from_ODBC_Datasource|ODBC model datasource]] can be used. This setting is for disabling external interfaces for security reasons. By default, external interfaces are allowed.
|-
|-
||AllowNonTemporaryETLTargetTable
||AllowNonTemporaryETLTargetTable
Line 79: Line 94:
||5000
||5000
||BulkCopyBatchSize given for sandbox SqlBulkCopy operations.
||BulkCopyBatchSize given for sandbox SqlBulkCopy operations.
|}
== SAML 2.0 Federated Authentication Settings ==
Note that the SAMLMetadataUrl and ServiceProviderLocation are mandatory for the federated authentication to work.
{| class="wikitable" style="text-align: left"
!Name !!Description
|-
||SAMLMetadataUrl
||
Metadata URL of the identity provider (IdP). Check that the metadata url can actually be opened using a web browser and is publicly available. The metadata is an XML document starting with '''<?xml version="1.0" encoding="UTF-8"?>''' followed by an '''EntityDescriptor''' tag. The metadata URL might look '''<nowiki>https://your.federated.identity.provider.com/saml/metadata</nowiki>'''. This setting is mandatory for the SAML authentication to work.
|-
||ServiceProviderLocation
||
Specifies the QPR ProcessAnalyzer server location (the root path which contains e.g. the ''ui'' folder). It's used by the url to redirect back to QPR ProcessAnalyzer after a successful authentication from the identity provider. The setting is defined in the following form: '''https://<hostname>/qprpa''', for example '''<nowiki>https://customer.onqpr.com/qprpa</nowiki>'''. Note that the actual redirect back url is '''https://<hostname>/qprpa/api/saml2/acs''' (/api/saml2/acs is automatically included to the url). This setting is mandatory for the SAML authentication to work. Note that if this reply url is configured the identity provider, it must match with the ServiceProviderLocation setting.
|-
||SAMLUserIdAttribute
||
Name of the SAML attribute in the assertion that will be used as the user's login name. If this field is not defined, the '''saml:Assertion''' > '''saml:Subject''' > '''saml:NameID''' attribute in the assertion is used. If this setting is given, one of the '''saml:Assertion''' > '''saml:AttributeStatement''' > '''saml:Attribute''' elements in the assertion is used (the '''Name''' attribute in the '''saml:Attribute''' element is used for matching). Please note that the saml:NameID element is different than the usual SAML attributes that are defined by the saml:Attribute elements. For example, if an email address is used as a user id, the value of the setting could be for example ''<nowiki>http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress</nowiki>''.
|-
||SAMLEncryptionCertificate
||This setting defines a PEM formatted X.509 certificate (defined in RCF 1422) used to encrypt SAML assertions. The public key of the certificate is published in the service provider metadata, where the identity provider can read it and encrypt SAML assertions. QPR ProcessAnalyzer as the service provider uses the corresponding private key of the certificate to decrypt SAML assertions. The setting needs to be a PEM formatted certificate file that is base64 encoded and it doesn't contain the BEGIN CERTIFICATE etc. header or footer lines. This setting is needed only when using the SAML assertions encryption. Even though this setting is defined, the SAML assertions are not required to be encrypted. More information how to create the certificate file (https://stackoverflow.com/questions/16480846/x-509-private-public-key) and convert it to base64 (https://stackoverflow.com/questions/46959822/base-64-encoded-form-of-the-pfx-file).
|-
||SAMLSigningCertificate
||This setting defines a PEM formatted X.509 certificate (defined in RCF 1422) used to sign SAML authentication requests sent from QPR ProcessAnalyzer to the identity provider. The public key of the certificate is published in the service provider metadata, where the identity provider can read it, to verify the authenticity of the SAML requests. The setting needs to be a PEM formatted certificate file that is base64 encoded and it doesn't contain the BEGIN CERTIFICATE etc. header or footer lines. If this setting is not defined, the internal hard-coded signing certificate is used. More information how to create the certificate file (https://stackoverflow.com/questions/16480846/x-509-private-public-key) and convert it to base64 (https://stackoverflow.com/questions/46959822/base-64-encoded-form-of-the-pfx-file).
|}
|}


Line 107: Line 147:
||SmtpEnableSSL
||SmtpEnableSSL
||'''True''' or '''False''' depending whether SSL connection to the SMTP server is used or not. If not defined, ''False'' is the default value.
||'''True''' or '''False''' depending whether SSL connection to the SMTP server is used or not. If not defined, ''False'' is the default value.
|}
== SAML 2.0 Federated Authentication Settings ==
Note that the SAMLMetadataUrl and ServiceProviderLocation are mandatory for the federated authentication to work.
{| class="wikitable" style="text-align: left"
!Name !!Description
|-
||SAMLMetadataUrl
||
Metadata URL of the identity provider (IdP). Check that the metadata url can actually be opened using a web browser. The metadata is an XML document, so it should start '''<?xml version="1.0" encoding="UTF-8"?>''' followed by an '''EntityDescriptor''' tag. The metadata URL might look something like '''<nowiki>https://your.federated.identity.provider.com/saml/metadata</nowiki>'''. This setting is mandatory for the federated authentication to work.
|-
||ServiceProviderLocation
||
This setting specifies the QPR ProcessAnalyzer server location (the root path which contains e.g. the ''ui'' folder). It's used by the url to redirect back to QPR ProcessAnalyzer after a successful authentication from the identity provider. The setting is defined in the following form: '''<nowiki>https://SERVERNAME/qprpa</nowiki>''', for example '''<nowiki>https://processanalyzer.onqpr.com/qprpa</nowiki>'''. Note that the actual redirect back url is '''<nowiki>https://SERVERNAME/qprpa/api/samlsignin</nowiki>''', i.e. '''/api/samlsignin''' is automatically included to the url. This setting is mandatory for the federated authentication to work.
|-
||SAMLUserIdAttribute
||
The name of the SAML attribute in the assertion that will be used as the user's login name. If this field is not given or is empty, the '''saml:Assertion''' > ''' saml:Subject''' > '''saml:NameID''' attribute is used in the assertion. If this setting is given, one of the '''saml:Assertion''' > '''saml:AttributeStatement''' > '''saml:Attribute''' elements in the assertion is used (the '''Name''' attribute in the '''saml:Attribute''' element is used for matching). Please note that the first mentioned saml:NameID element is different than the usual SAML attributes that are defined using saml:Attribute elements. If an email address is used as a user id, the value of the setting could be for example ''<nowiki>http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress</nowiki>''.
|}
== LDAP Authentication Settings ==
{| class="wikitable" style="text-align: left"
!Name !!Description
|-
||AuthenticationMethod
|rowspan="7"|Authentication related settings, see detailed information in [[LDAP/AD_Authentication|LDAP/AD Authentication]].
|-
||LDAPConnectionString
|-
||LDAPUserFilter
|-
||LDAPUserSearchBase
|-
||LDAPUserIdAttributeName
|-
||LDAPServerUserName
|-
||LDAPServerPassword
|}
|}


Line 155: Line 155:
|-
|-
|<span style="color:lightgrey;">DatabaseId</span>
|<span style="color:lightgrey;">DatabaseId</span>
||Unique identifier for the database. Unless the database has been copied from another database, there shouldn't be two databases with the same database id.
||Unique identifier for the QPR ProcessAnalyzer database, which is generated when the database is initialized during the server installation. If this setting is empty, a new unique database id is generated for this setting, and that value is used after that.
|-
|-
|<span style="color:lightgrey;">DatabaseVersion</span>
|<span style="color:lightgrey;">DatabaseVersion</span>
||Database schema version. It will be updated automatically when a QPR ProcessAnalyzer Server of a newer version connects to the database and performs migration for the database.
||Database schema version. It will be updated automatically when the newer version of QPR ProcessAnalyzer Server connects to the database and performs migration for the database schema.
|-
|-
||<span style="color:lightgrey;">InitializationScriptDatabaseVersion</span>
||<span style="color:lightgrey;">InitializationScriptDatabaseVersion</span>

Revision as of 12:59, 16 November 2022

QPR ProcessAnalyzer database has a configuration table PA_Configuration containing settings listed in the tables below. You need SQL Server Management Studio to edit the settings in the configuration table. QPR ProcessAnalyzer Server needs to be restarted (e.g. IIS application pool recycled) for the changes to take effect.

General Settings

Name Default value Description
SessionIdleTimeout 3600 Idle user session expiration timeout in seconds. User session expires if the session hasn't been used after this amount of time.
SessionMaximumDuration 86400 Maximum duration for a user session in seconds. Even if a session is used so that the SessionIdleTimeout is not reached, the session is expired after this amount of time.
SandboxDatabaseConnectionString Connection string to scripting sandbox database (ETL). If not defined, SQL-based ETL scripts cannot be run. Connection string for the scripting sandbox database is similar to the QPR ProcessAnalyzer database connection string. More information: Setting up Scripting Sandbox.
DefaultUiLanguage en_US Language code for the UI language that new user accounts get by default. Thus, a created user account has this language until the user changes her/his language. Also the login page is translated using this language when QPR ProcessAnalyzer is used for the first time in that web browser (when user has changed the language, it's remembered by the browser). This setting must be one of the supported language codes (xx_XX):
  • Finnish: fi_FI
  • French: fr_FR
  • English: en_US
  • German: de_DE
  • Spanish: es_ES
  • Swedish: sv_SE
  • Polish: pl_PL
DefaultDateFormat MM/dd/yyyy Default date format that new user accounts get by default. The date format does not contain the time part (e.g. hours, minutes and seconds). Defined using the .Net date format (https://docs.microsoft.com/en-us/dotnet/standard/base-types/custom-date-and-time-format-strings).
DefaultFirstDayOfWeek 0 Default first day of the week that new user accounts get by default. 0 is Sunday and 1 is Monday. This information is used by the UI when showing e.g. calendars.
DefaultUse12HourClock false Defines whether the 12-hour clock is used by default (instead of the 24-hour clock) for the new user accounts when showing time information in the UI. Defined as true or false. More information about the 12-hour clock: https://en.wikipedia.org/wiki/12-hour_clock.
SqlServerConnectionString ADO.Net connection string for the SQL Server database containing the datatables data. It's recommended to use separate database, but it's also possible to connect to the same database as the metadata database. When this setting has been configured, new datatables are created to this database instead of the metadata database. The old datatables are still located in the metadata database, and new datatables cannot be created to the metadata database anymore. Note that the connection uses ADO.Net (not ODBC), so the connection string is similar to the metadata database connection string in the web.config file.
SnowflakeConnectionString ODBC connection string for the Snowflake connection, that needs to be configured to process models in the Snowflake. The connection string has following format:
Driver={SnowflakeDSIIDriver};Application=QPR_ProcessAnalyzer;Server=<account_identifier>.snowflakecomputing.com;Database=QPRPA;Schema=QPRPA;Warehouse=QPRPA;Role=QPRPA;uid=QPRPA;pwd=<password>

where <password> is the Snowflake user password and <account_identifier> is the Snowflake account identifier. In the example, the database, schema, warehouse, role and user are named to QPRPA. In addition to this setting, the Snowflake ODBC driver needs to be installed in the machine running the QPR ProcessAnalyzer Server. When this setting has been configured, users can create Snowflake stored datatables and models using Snowflake calculation. More information about Snowflake connection configuration.

NumberOfParallelModelReaders 4 Models and datatable contents can be loaded with multiple simultaneous connections to the database to speed up the loading. This setting determines how many parallel loaders/readers at maximum (loaders are loading at the same time). For smaller models there are less parallel loaders than the defined limit: If there are less than 100000 rows in the table, there is only one loader. If there are less than 200000 rows in the table, there are only two loaders, and so on.

The more there are parallel loaders, the more processor load and network bandwidth is consumed, and other operations in QPR ProcessAnalyzer might slow down. Note also that the performance optimum is achieved with a certain number of parallel loaders which differs between environment. Thus to achieve the best performance, data loading should be tested with different number of parallel loaders. Increasing number of parallel loaders beyond the optimum decreases the performance.

StartupModelLoadingMaxParallelism 2 Maximum number of QPR ProcessAnalyzer models that are loaded into memory simultaneously by the Automatic Loading on Server Startup. If there are more models to be loaded on the server startup than this setting, loading for the rest of the models is started one by one when previous model loadings are completed. If this setting is not defined, 2 is used as a default value.

Loading more models at the same time will speed up the whole model loading process, but on the other hand, it causes more load on the system, which affects the system responsiveness for users. Model loading consists of (1) transferring data from the datasource to QPR ProcessAnalyzer and (2) loaded data preprocessing into a model. The former uses mainly network bandwidth (if datasource is in a different server) and the latter uses mainly processor capacity in the QPR ProcessAnalyzer server.

This setting affects only the model loading during the server startup and it doesn't restrict models loadings initiated by users.

QueryTimeout 300 Timeout (in seconds) for requests made to /api/expression/query and /api/expression endpoints. When the timeout is exceeded, the query is stopped and a timeout error is returned. Purpose of the timeout is to protect the system against potentially too long running or even never-ending queries which might otherwise jam the system.
AllowExternalDatasources True Determines whether the ImportOdbc and CallWebService functions in the expression language and the ODBC model datasource can be used. This setting is for disabling external interfaces for security reasons. By default, external interfaces are allowed.
AllowNonTemporaryETLTargetTable False Defined whether ETL scripts are allowed to create global temporary database tables (tables starting with ##). More information about temporary tables: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql?view=sql-server-ver15#temporary-tables.
DatabaseBulkCopyTimeout 600 BulkCopyTimeout given for QPR ProcessAnalyzer database SqlBulkCopy operations.
DatabaseBulkCopyBatchSize 5000 BulkCopyBatchSize given for QPR ProcessAnalyzer database SqlBulkCopy operations.
SandboxDatabaseBulkCopyTimeout 600 BulkCopyTimeout given for sandbox SqlBulkCopy operations.
SandboxDatabaseBulkCopyBatchSize 5000 BulkCopyBatchSize given for sandbox SqlBulkCopy operations.

SAML 2.0 Federated Authentication Settings

Note that the SAMLMetadataUrl and ServiceProviderLocation are mandatory for the federated authentication to work.

Name Description
SAMLMetadataUrl

Metadata URL of the identity provider (IdP). Check that the metadata url can actually be opened using a web browser and is publicly available. The metadata is an XML document starting with <?xml version="1.0" encoding="UTF-8"?> followed by an EntityDescriptor tag. The metadata URL might look https://your.federated.identity.provider.com/saml/metadata. This setting is mandatory for the SAML authentication to work.

ServiceProviderLocation

Specifies the QPR ProcessAnalyzer server location (the root path which contains e.g. the ui folder). It's used by the url to redirect back to QPR ProcessAnalyzer after a successful authentication from the identity provider. The setting is defined in the following form: https://<hostname>/qprpa, for example https://customer.onqpr.com/qprpa. Note that the actual redirect back url is https://<hostname>/qprpa/api/saml2/acs (/api/saml2/acs is automatically included to the url). This setting is mandatory for the SAML authentication to work. Note that if this reply url is configured the identity provider, it must match with the ServiceProviderLocation setting.

SAMLUserIdAttribute

Name of the SAML attribute in the assertion that will be used as the user's login name. If this field is not defined, the saml:Assertion > saml:Subject > saml:NameID attribute in the assertion is used. If this setting is given, one of the saml:Assertion > saml:AttributeStatement > saml:Attribute elements in the assertion is used (the Name attribute in the saml:Attribute element is used for matching). Please note that the saml:NameID element is different than the usual SAML attributes that are defined by the saml:Attribute elements. For example, if an email address is used as a user id, the value of the setting could be for example http://schemas.xmlsoap.org/ws/2005/05/identity/claims/emailaddress.

SAMLEncryptionCertificate This setting defines a PEM formatted X.509 certificate (defined in RCF 1422) used to encrypt SAML assertions. The public key of the certificate is published in the service provider metadata, where the identity provider can read it and encrypt SAML assertions. QPR ProcessAnalyzer as the service provider uses the corresponding private key of the certificate to decrypt SAML assertions. The setting needs to be a PEM formatted certificate file that is base64 encoded and it doesn't contain the BEGIN CERTIFICATE etc. header or footer lines. This setting is needed only when using the SAML assertions encryption. Even though this setting is defined, the SAML assertions are not required to be encrypted. More information how to create the certificate file (https://stackoverflow.com/questions/16480846/x-509-private-public-key) and convert it to base64 (https://stackoverflow.com/questions/46959822/base-64-encoded-form-of-the-pfx-file).
SAMLSigningCertificate This setting defines a PEM formatted X.509 certificate (defined in RCF 1422) used to sign SAML authentication requests sent from QPR ProcessAnalyzer to the identity provider. The public key of the certificate is published in the service provider metadata, where the identity provider can read it, to verify the authenticity of the SAML requests. The setting needs to be a PEM formatted certificate file that is base64 encoded and it doesn't contain the BEGIN CERTIFICATE etc. header or footer lines. If this setting is not defined, the internal hard-coded signing certificate is used. More information how to create the certificate file (https://stackoverflow.com/questions/16480846/x-509-private-public-key) and convert it to base64 (https://stackoverflow.com/questions/46959822/base-64-encoded-form-of-the-pfx-file).

SMTP Server Settings

SMTP server settings are neede for QPR ProcessAnalyzer to be able to send email messages. Email sending is used by the notifications functionality and the SendEmail function in the expression language.

Name Description
SmtpServer DNS name, host name or IP address of the SMTP server. Mandatory setting for the email sending to work.
SmtpPort TCP port number of the SMTP server. If not defined, port 25 is used by default.
SmtpAuthenticationUsername User name for authenticating to the SMTP server. If not defined, no authentication is used to connect to the SMTP server.
SmtpFromAddress Email address where email messages sent by QPR ProcessAnalyzer appear to be coming from. This doesn't need to be a real email address, although the address used may affect email spam filters. The setting configured here is the default email address to use in following cases:
  • From address is not set for the email notifications
  • From parameter is not defined for the expression language SendEmail function
  • EmailFrom parameter is not defined for the SQL Scripting SendEmail operation
SmtpAuthenticationPassword Password for authenticating to the SMTP server.
SmtpEnableSSL True or False depending whether SSL connection to the SMTP server is used or not. If not defined, False is the default value.

Readonly Information

Name Description
DatabaseId Unique identifier for the QPR ProcessAnalyzer database, which is generated when the database is initialized during the server installation. If this setting is empty, a new unique database id is generated for this setting, and that value is used after that.
DatabaseVersion Database schema version. It will be updated automatically when the newer version of QPR ProcessAnalyzer Server connects to the database and performs migration for the database schema.
InitializationScriptDatabaseVersion Database version that was when the database was initialized when the software was installed. Do not change this setting.
MinimumDatabaseVersion Minimum allowed database version for QPR ProcessAnalyzer Server connecting to the database. This is a legacy setting and it should not be used.