Skip to content

BI Infrastructure setup in Azure for Self Hosted BI

This section guides you through the provisioning and configuration of Azure resources in the customer tenant.

An overview of the components to be created are on the right side of this image:

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 in any data breach for any artifact hosted outside of the IFS domain.

Prerequisites

  • IFS Cloud version should be on or later than
  • 21R2 - SU10 and up
  • 22R1 - SU4 and up
  • 22R2 - All versions
  • 23R1 - All versions
  • An Azure Subscription in the customer tenant
  • A VPN connection between IFS network and customer’s
  • This can be requested through https://support.ifs.com
  • Resources within the customer subscription
  • A Service Principal
    • To be set as Admin of AAS and Power BI workspace
  • Azure VNET Subnet
    • To connect the SQL Database and Azure Data Factory

Create a resource group

IFS recommends creating a separate resource group for IFS BI related resources.

1.

2.

Create a virtual network

If there is no Virtual network to host the Azure Data Factory, one will need to be created.

  1. Find Virtual network resource and create

  1. Provide Name

  1. Configure subnet

  1. Click on subnet and add service endpoints to subnet

Select Microsoft.Sql and Microsoft.Storage.

  1. Check subnet settings

Create a blob container

Create a new blob container to host the ODAC installation files. This will be used by the Azure Data Factory SSIS Integration runtime.

Create a new storage account first, if one does not exist.

  7. Create blob container inside the storage account. Select Containers on left pane.

  1. Provide a name

Upload ODAC installation files to the blob storage

Once the blob container is in place, copy the following 2 files into the blob Container 1. Main.cmd - download and unzip file from here 2. OADC121024xcopy_z64.zip - download from Oracle here

  1. Upload the two files to the container using the upload button.

  2. Once uploaded the files appear like this in the container.

Obtain SAS URL for the blob storage

Once the files are copied to the blob storage, click on "Shared Access tokens" on the left pane.

Generate a new SAS token and URL

  • The SAS URL requires the following Permissions: Read Add Create Write Delete.

  • Give a sufficient expiry date.

  • Save the URL for later use.

Create Azure SQL

Create the Azure SQL sever instance. This will be holding the data warehouse and the SSIS DB that will be automatically created by the ADF. Azure SQL server instance does not cost, cost is applicable on the databases that we create under it.

  1. Go to add new resource to resource group and select Azure SQL

  2. Select "Database server" from the Resource Type option

  3. Provide a server name and use SQL authentication. Keep the credentials to be used later.

  4. After the database is provisioned, go to Networking pane, and add a new vnet rule as below.

You would need to click on "Add your client IPv4 address …" to access database from SSMS in the below step.

Create a SQL Database

Create a SQL Database which will be the data warehouse for analysis models. After the initial creation most of the data warehouse work can be performed through IFS Cloud Web.

  • The "Compute + storage" options. The scale configuration is dependent on individual customer needs. If the data loads are infrequent and have usually nightly full loads, "Serverless" option would be cheaper, and efficient as well. If so, we recommend the following configuration.

  • With the option "Enable auto-pause" a further cost reduction can be achieved. However please note that the database resume takes about 1min and during that time the database will be unavailable for connections. Therefore, the very first data load might fail after a resume if this option is enabled.

  1. Once all completed, access the SQL server using SQL Server Management Studio (SSMS) should be successful. You should be able to see the created database.

Create Azure Data Factory

Create SSIS IR

Pre-requisites for SSIS-IR (Created through 3.2 ~ 3.9 above) * VNET and Subnet available * SQL Server and Database created * Storage Account and SAS Token created * Azure Data Factory created

  1. Go to ADF and "Launch studio"

  1. Click on Manage > Integration Runtimes and click "New"

  1. Select Azure-SSIS

  1. Under General Settings set the Name, Location and select the Node Size.

  • Node Size
  • Minimum node size compatible is D2_v3.
  • v2 series is not possible to be used.

  • D4_v3 is recommended if there are many parallel data loads (i.e., several large dimensions).

  • Under the Deployment settings, Check the box next to Create SSIS Select the Subscription, Location, and enter the SQL Server login credentials created earlier.

  1. Under Advanced Settings, enter the SAS URI created for the blob container earlier. Select the Subscription with relevant VNet and Subnet

NOTE: The SAS validation might fail if public access is not given.

  1. Once the setup is complete, start the SSIS-IR. This can take up to 1 hour.

  1. After the SSIS-IR is up the SSISDB should be visible in the SQL server.

Create an Integration Services Catalogue folder

  1. After the SSIS-IR has started, Log into SQL Server using SSMS and create an "Integration Services Catalogs" Folder.

Create an Analysis Services (AAS) instance

  1. Add the service principal as an admin to the server

Validate Setup

  1. Check SQL Server a. Open an SQL Server management Studio instance b. Check if it is possible to connect to SQL Server using the username and password. c. Check if you see both the databases: the database for the data warehouse and SSIS DB

  1. Check if it is possible to connect to Azure Analysis Services using the Service Principal ID and Secret Value.