Skip to content

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

Prerequisites

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

1. Create a Resource Group

Note

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

1.1 Go to Resource Groups and click on Create.

1.2 Then you must provide the Project Details and Region.

2. Create a Virtual Network

If there is no Virtual network to host the Azure Data Factory, you need to create one.

2.1 First, find the Virtual network resource and select Create.

2.2 Provide the required details.

2.3 Then, configure the subnet.

2.4 Click on the subnet and add service endpoints to the subnet.

Select Microsoft.Sql and Microsoft.Storage.

2.5 Finally, verify the subnet settings.

3. Create a Blob Container

This section explains how to 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.

3.1 First, find the Storage account resource and select Create.

3.2 Then, fill in the Project details and Instance details.

3.3 Next, select and verify the Security and Blob storage details.

3.4 Select the required Network connectivity and Network routing options.

3.5 Configure your Data protection details.

3.6 Configure your Encryption details.

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

3.8 Provide a name for the New container.

4. Upload ODAC Installation Files to the Blob Storage

Once the blob container is in place, copy the following two files into the blob Container.

4.1 Main.cmd - download and unzip the file from here.

4.2 ODAC121024Xcopy_x64.zip - download from Oracle here.

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

4.4 Once uploaded, the files appear like this in the container.

5. Obtain the SAS URL for the Blob Storage

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

5.2 Then, you need to generate a new SAS token and URL.

5.3 The SAS URL requires the following Permissions:

  • Read

  • Add

  • Create

  • Write

  • Delete

  • You need to provide a sufficient expiry date.

  • Finally, save the URL for later use.

6. Create Azure SQL

This section explains creating the Azure SQL server instance.

This will be holding, a. the SQL data warehouse and b. the SSIS DB (that will be automatically created by the ADF).

Azure SQL server instance does not create any costs, costs only apply to the databases that we create under it.

6.1 Go to add a new resource to the resource group and select Azure SQL.

create-asql-1

6.2 Select Database server from the Resource type option.

create-asql-2

6.3 Provide a server name and use SQL authentication. Save and keep the credentials to use at a later stage (Refer to section 9 below).

create-asql-3

6.4 Select the option Allow Azure services and resources to access this server.

create-asql-4

6.5 After the database is provisioned, go to Networking pane, and add a new vnet rule as below. Additionally, you would need to click on Add your client IPv4 address… to access the database from SSMS in the below step.

create-asql-5

7. Create a SQL Database

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

7.1 Click on the Create database button.

7.2 Check and verify the details on the Basics tab.

  • 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, the Serverless option would be cheaper, and more efficient as well. If so, IFS recommends the following configuration.

  • With the option "Enable auto-pause", you can achieve a further cost reduction.

  • Refer to the Microsoft documentation for SQL Server serverless for more details.

7.3 Check and verify the configuration settings on the Networking tab.

7.4 Once you complete the setup, you can access the SQL server using SQL Server Management Studio (SSMS). You can see the created database.

8. Create Azure Data Factory

8.1 First, find the Data Factory resource and select Create.

8.2 Check and verify the details on the Basics tab.

8.3 Check and verify the details on the Git configuration tab.

8.4 Check and verify the details on the Networking tab.

9. Create SSIS IR

Pre-requisites for SSIS-IR :

  • VNET and Subnet available (Please refer to section 2 above.)
  • SQL Server and Database created (Please refer to section 6-7 above.)
  • Storage Account and SAS Token created (Please refer to sections 3-5 above.)
  • Azure Data Factory created (Please refer to section 8 above.)

9.1 Go to ADF and click on Launch Studio.

9.2 Click on Manage > Integration Runtimes and click on New.

9.3 Select Azure-SSIS.

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

  • Node Size
  • The 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).

9.5 Under the Deployment settings, Check the box next to Create SSIS and select the Subscription, and Location, and enter the SQL Server login credentials created earlier (Refer to section 6 above).

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

Note

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

9.7 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.

10. Create an SSIS Catalogue Folder

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

10.2 In Properties, it is recommended to keep the Operations Log properties as follows, where Clean Logs Periodically is set to True and Retention Period is 30 days.

image-20231021123833635

11. Create AAS

11.1 First, find the Analysis Services resource and select Create.

11.2 Fill in all the mandatory fields as follows.

11.3 Finally, add the service principal as an admin to the server.

12. Validate Setup

12.1 Validate the 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

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