How to connect to an on-premise database

1

Create a new space

Once you are successfully logged into your Birst account, create a new space, if none have been created already. To do so, click the 'Create New Space' hyperlink located in the middle of the Infor Birst welcome page. Otherwise, you can select an existing space.

From the 'Create New Space' menu, choose to create an Enterprise , Professional , or **Usage Tracking **space. Make sure to name your space appropriately and save by clicking the green check button when finished.

• Enterprise: The most advanced and comprehensive edition, which offers support for complex data transformations, advanced scheduling, and robust governance and security controls. This is most suitable for large organizations with complex data integration requirements and a demand for a high level of governance and security.
• Professional: The mid-tier edition, which offers a balanced set of features for data integration but has limitations to its capabilities compared to the Enterprise edition. This is most suitable for medium-sized organizations with moderate data integration.
• Usage Tracking: A specialized edition that focuses on tracking and monitoring data usage and access patterns. This edition provides analytics and reporting capabilities to gain insights into how the data is being used within Birst. You are able to monitor user activity, track report usage, and analyze data consumption patterns. This is most suitable for organizations that want to understand user engagement and optimize their data analytic processes.

To open an existing space, go to the 'Global Navigation Menu' and your current space is displayed at the top of the menu. Click on the 'Switch Space' button next to the name of your space and select your desired space.

2

Create a connection to a data source

Before you create the connection, make sure you have placed the driver for your database in the Birst Cloud Agent Drivers directory. Only Microsoft SQL Server driver is included with the platform.

Go to the 'Modeler Connect' page to create a connection to a data source. In the top left of the welcome page is the 'Global Navigation Menu' button, which is symbolized by 3 horizontal lines. Click the button to expand the menu, expand the 'Modeler' section, and the select 'Connect' tab.

Click on the plus sign to 'Create a new connection to a data source'.

Then click on the 'Select the Connector' dropdown menu. In this tutorial, we will be using SQL Databases.

3

Fill in the connection details

In the 'Create Connection' side panel, provide the following information to create a connection to the SQL database:

Connection Details

• Connection Name: Decide on a meaningful and descriptive name that helps you identify this particular connection from others in your Birst environment. In this example, we will name this connection SQL.
• Connection Type: Choose between "Import to Birst" or "Live Access". This tutorial will use the "Import to Birst" option.
• Import to Birst: The data will be extracted, transformed, and loaded into the Birst platform. Data can be updated/refreshed periodically and incrementally, according to your requirements. This is the standard way of using data in Birst.
• Live Access: Provides a real-time connection to data in local data marts and data warehouses without requiring the data to first be uploaded into the Birst data store (warehouse). A Birst logical model maps directly onto a local physical relational database, making it suitable for real-time access.
• Agent Selection: Choose and assign a specific Birst Cloud Agent to perform tasks within the platform.
• Cloud Databases: You can skip the agent by clicking on the toggle switch.
• Non-Cloud Databases: If you already have an agent installed, select "Select agent", and choose an active agent from the selection menu by clicking on "Show online agents only".
• If you do not have an agent installed, refer to the How to Install Birst Cloud Agent in Infor Birst video and tutorial.
• Database Type: The category of your database system. You will also need a driver, which serves as a communication bridge between Birst and the database.
• MSSQL (Microsoft SQL Server): Relational database developed by Microsoft with features for data storage, management, and retrieval.
• MySQL: Open-source relational database used for web applications, known for speed, reliability, and ease of use.
• Oracle 11g: Enterprise-grade relational database with high availability, scalability, and security.
• PostgreSQL: Open-source object-relational database supporting JSON data, spatial data, and full-text search.
• Redshift: Cloud-based data warehouse by AWS, optimized for querying large datasets.
• SAP Hana: In-memory relational database designed for real-time data processing.
• Snowflake: Cloud-based data warehousing platform known for scalability, performance, and ease of use.
• Server Name: The hostname or IP address of the database server you are connecting to.
• Database Name: An identifier assigned to the database, helping Birst connect to the correct database.
• Security Credentials: The username and password for an authorized user with database access.

Optional Settings

• More Options: Click the "More Options" button below the "Security Credentials" login box to specify:
• Fetch Size: Number of rows retrieved from the database during a query.
• Port: Numeric value representing the network port where the database communicates with Birst.
• Source Prefix: A text identifier added to imported table names within Birst. To enable, click the slide switch and enter the desired prefix (e.g., "sql" to identify the source and distinguish duplicate tables).
• Isolation Levels:
  • Read Committed: Transactions see only committed changes, ensuring better data consistency.
  • Read Uncommitted: Transactions can see uncommitted changes, allowing high concurrency but risking inconsistent data.
  • Repeatable Read: Data within a transaction remains unchanged despite external modifications.
  • Serializable: Transactions are fully isolated, ensuring data consistency as if only one transaction is accessing the database.

Finalizing Setup

Once all necessary information is provided, click the green "Save" button at the bottom of the menu.

4

Choose the schema

After the connection has been created, choose a schema. From the 'schemas in SQL ' menu, check the boxes next to the desired schema name that you are going to work with. This tutorial will be selecting 'dbo '. Click the blue 'Apply ' at the bottom of the menu when finished.

5

Choose the tables to be imported

Similar to the previous step, import specific tables from the selected schema. Again, check the boxes next to the desired table names that you want to import. When you check the box, you will be allowed to preview the data. In this case, the 'Accounts ' table will be imported. When you are done, click the blue 'Done ' at the bottom of the menu.

• Note: You can choose to not import certain columns by unchecking the box next to the column name. By default, all the columns will be imported. The 'NetSuiteCustomerID** ' column is unselected for this tutorial.
• Note: You can also filter through the lines of each column by providing an expression indicating what rows are to be included or what rows are to be excluded. In this case, the 'Segment** ' column is filtered by selecting the '≠ Not Equals ' operator and providing the value, 'SMB '. Thus, the column will only contain the rows that are not labeled 'SMB '.

6

Import a query-based object

Another way to import data is using a query-based object. In the SQL import data menu, click the '...' button to display the drop-down list for more options. Select the 'Add query-based object ' option and provide the following information. When finished with providing the necessary information, click the green 'Save ' button at the bottom of the menu.

• Source Name: The identifier assigned to the data table that you are retrieving data with a query. The name 'SMB Accounts ' is used to represent the lines of data retrieved that contain 'SMB '. * Query: The request or command that is sent to the database to retrieve, manipulate, or modify the stored data. In this example, a query is written to bring in all the columns from the 'Accounts ' table and the rows of the 'Segment ' column contain 'SMB '. * SELECT * FROM Accounts WHERE Segment = 'SMB'

7

Preview and import the data

Preview the data tables that you want to import by selecting it from the list on the left. Click the green 'Import SQL Data ' button when ready.

8

Go to Modeler to see the imported data

Once the import is complete, go to the 'Prepare' page to view them. In the Modeler toolbar, select 'Connect', which will display a drop-down menu. From there, select the 'Prepare ' option.

In the 'Prepare' page, you can see that the imported data sources are grayed out, which means that they are in an 'ignored state '. The 'ignored state ' means that the data sources are not currently being used. However, you can still preview each data source by clicking on them from the 'Sources' menu.