Configure SQL Server for Lobby and KPI Services

This page provides information on how to configure access to SSAS OLAP Cubes within IFS Lobby and configuring Financial KPIs in SQL Server Data Warehouse.

Contents

 

General

The configuration can be done using the following page of the IFS Installer.

When Add/Edit buttons are pressed, following detail window will be opened. The detail window will be used for defining/modifying the parameters.

Actions

  1. Available parameters:
    Name Description
    Used for Lobby The SSAS configuration details will be used by IFS Lobby to create sources based on SSAS OLAP cubes.
    Default Lobby Source The details will be used as the default SSAS OLAP Cube source when Lobby Data Sources are configured to use the DEFAULT connection.

     

    Note: Only one source can be configured as the Default Lobby Source.
    Used for KPI Services The details will be used to configure the KPI Services functionality.

     

    Note: Only one source can be configured for KPI Services.
    SSAS OLAP Cube Parameters SQL Server Analysis Services (SSAS) connections are made using the provided parameters.
    Name Description
    Server Name The host name of the SSAS server accessible from the client computers which IFS EE is being run.

    Note:  This setting is only used by KPI Services functionality.
    HTTP Endpoint The URL of a SSAS HTTP end point.
    Username A domain user which is defined as an Administrator in the SSAS Server.
    Password Password of the domain user which is defined under Username.
    Effective Username Format Defines the template which will be used when EffectiveUsername property of the SSAS connection string is generated.

    Valid place holder values are {USER_IDENTITY}, {DIRECTORY_IDENTITY}, {SQL_MAPPED_DOMAIN_IDENTITY}

     

    Place Holder Description
    {USER_IDENTITY} User Identity of the Foundation User
    {DIRECTORY_IDENTITY} Directory Identity of the Foundation User
    {SQL_MAPPED_DOMAIN_IDENTITY} Mapped Identity, fetched from a mapped configuration.
    Oracle and Domain User Mappings for SQL Server - IEE
    Oracle and Domain User Mappings for SQL Server - Aurena
    Example Usages:
    Foundation User SQL Server User
    User Identity Directory Identity Effective Username Format Resulting EffectiveUsername value
    FILA firstname.lastname@TESTDOMAIN.COM {USER_IDENTITY} FILA
    FILA firstname.lastname@TESTDOMAIN.COM {DIRECTORY_IDENTITY} firstname.lastname@TESTDOMAIN.COM
    FILA FILA TESTDOMAIN.COM\{USER_IDENTITY} TESTDOMAIN.COM\FILA
    FILA FILA {USER_IDENTITY}@TESTDOMAIN.COM FILA@TESTDOMAIN.COM
    FILA TESTDOMAIN.COM\FILA {DIRECTORY_IDENTITY} TESTDOMAIN.COM\FILA

     

    Note: When the Effective Username Format is empty, the EffectiveUsername will be constructed using {SQL_MAPPED_DOMAIN_IDENTITY}. But when there are no Domain User mappings defined, the {DIRECTORY_IDENTITY} will be used instead.
    It is also possible to set Effective Username Format to one fixed user identity, e.g. using the UPN (User Principle Name) without any placeholders. This user will then be used for all SSAS sources. This type of configuration will be uncommon in a customer scenario.
    Only IFS Lobby uses this parameter value.

    Note: KPI Services will not perform any impersonation. Only the administrator credentials will be used for any operations related to SSAS OLAP cube access via KPI Services.
    Max Number of  Connections Number of maximum connections maintained in the connection pool. Should be less than or equal to the ConnectionPoolSize of the HTTP end point.
    Max Active Connections Number of maximum active connections in the pool. Should be less than or equal to the Max Number of Connections.
    Query Execution Timeout Sets the maximum amount of time (in milliseconds) a connection should execute a query.
    Connection Wait Timeout Sets the maximum amount of time (in milliseconds) a request should wait for a connection (when all the existing connections are active) before throwing an error.
    SQL Server Data Warehouse Parameters SQL Server connections are made using the provided parameters.

     

    Note: These settings will only be used by KPI Services.

     

    Name Description
    Server Name The host name of the SQL Server to be accessed by the MWS with respect to KPI Services functionality
    Database Name The name of the SQL Server database where IFS Analysis Models has been deployed.
    Username This is a SQL Server authenticated user which is defined in SQL Server with necessary rights.
    Password Password of the SQL Server User defined under Username.
    Connection String Format Defines the template which will be used when JDBC connections are made to SQL Server.

    Valid place holder values are {SERVER_NAME} and {DATABASE_NAME}

     

    Place Holder Description
    {SERVER_NAME} The Server Name property will be substituted
    {DATABASE_NAME} The Database Name property will be substituted
    Max Number of  Connections Number of maximum connections maintained in the connection pool. This should be less than or equal to the ConnectionPoolSize.
    Max Active Connections Maximum number of connections in the pool which will be accessing the SQL Server at a given time.
    Should be less than or equal to the Max Number of Connections.
    Query Execution Timeout Sets the maximum amount of time (in milliseconds) a connection should execute a query.
    Connection Wait Timeout Sets the maximum amount of time (in milliseconds) a request should wait for a connection (when all the existing connections are active) before throwing an error.
       
  2. Click Next

About

Name Description
SSAS OLAP Cube Parameters SQL Server Analysis Services (SSAS) connections are made using these parameters.
The SSAS cube data source will in IFS Lobby display the configured SSAS HTTP endpoints using the order defined through the configurations.
SSAS connections will, for IFS Lobby and KPI Services, be made using the given credentials and for IFS Lobby an impersonation will be made when accessing the Analysis Services cube by different users.
HTTP Endpoint Format should be http://<host>:<port>/<path>

The endpoint should be configured as per the instructions in SSAS documentation.
Read Configure HTTP Access to Analysis Services on Internet Information Services for more information
Connection Pool Settings When connecting to SSAS/SQL Server, two separate pools are maintained inside IFS Applications to cater the requests sent from IFS Lobby/KPI Services. The parameters are used to configure the pool.

 

All the values are stored in a table called XLR_SQL_SERVER_PARAMETERS_TAB. Also the values are saved in instance configuration file of IFS Home for reconfiguration purposes. KPI Services related parameters are duplicated to KPI_CONFIGURATION_TAB

 

 

Verify

The SSAS OLAP Cube Parameters Username user account should be configured to enable credential delegation if the HTTP endpoint and SSAS are hosted in different servers.
Read https://msdn.microsoft.com/en-us/library/gg604620.aspx for more information.
NA