Skip to content

AM Infrastructure setup on-prem for Self-hosted BI (Scenario 1)

Disclaimer

Please note that this is a guideline, and the setup will still need to be verified by your company security board to ensure your company standards and policies have been adhered to. IFS takes no responsibility for any data breach for any artifact hosted outside of the IFS domain.

This section guides you through the provisioning and configuration of resources on-prem for Self-Hosted BI.

1. SQL Server Installation

  • Download and run the setup for SQL Server. (You can use Standard or Enterprise Edition).
  • Under Feature Selection, select the following features:
    • Database Engine Services
    • Analysis Services
    • Integration Services
  • Under Instance Configuration, you can select the default instance or a named instance.

  • Under Server Configuration, since the SQL Server Agent is needed, it is recommended to have the Startup Type to Automatic.

  • Under Database Engine Configuration, select the Authentication Mode as Mixed Mode. You would need to set a sa password and also add administrators.

  • Under Analysis Services Configuration, select the Server Mode as Tabular Mode. You would need to select administrators as well.
  • Complete the setup with default options.

2. Create a new Database for the IFS Data Warehouse

  • You may keep the Recovery model as Simple for improved performance and space.
    • e.g. ifs-sql-dw

3. User Setup

3.1 User Setup Overview

Two users need to be created as in the below image and description in subsections below.

3.2 SQL Server user

  • Create a login with SQL Server authentication.
    • e.g. ifs-bi-user
  • Grant db_owner for the database created above as ifs data warehouse.
  • Grant db_owner for the SSISDB.
  • Grant SELECT, EXECUTE GRANT on msdb.
  • Grant SQLAgentOperatorRole role on msdb.
  • Important:
    • The user needs elevated privileges during the SQL environment setup step.
    • Therefore before you run the Environment Setup Assistant grant sysadmin privilege to this user
      • This is needed to deploy the SSIS Project and create the SSIS Agent Job under the SSAS user.
    • After the setup assistant finishes (takes usually about 5 minutes), remove the sysadmin privilege.
    • If the in-house security team agrees, it is ok to keep sysadmin privilege granted always to the user.

3.3 SSAS User

  • Create a domain account.
    • e.g. <domain>\ifsbiuser
  • This account needs to be set up as an administrator in SSAS.
  • The Agent job service should be run using this user.
  • Alternatively, if you want to use the existing domain account running the Agent Job
    • Make that user an Administrator in SSAS,
    • Use it as the SSAS username in the CSM Portal Form here.

4. Create a new Catalogue Folder in Integration Services

  • Login in to SQL Server using SSMS.
  • Expand the Integration Services Catalogs folder and right-click on SSISDB.
  • Choose the Create Folder... option.
    • e.g. ifs-etl

5. ODAC Installation

  • You need to install this driver on the same machine that has SQL Server.
  • 64-bit ODAC 12c Release 4 (12.1.0.2.4) for Windows x64.
  • This file is available as a download from Oracle here.