How to connect to an on-premise database

Overview

Most of the data organizations are using is stored in databases. This tutorial will show how to connect to on-premise databases using Birst Cloud Agent.

The steps listed here are applicable to Live Access connections too.

---

Tutorial

Difficulty: Easy
Estimated Completion Time: 10 mins

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 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 be naming this connection, SQL.
• Connection Type: Choose between ‘Import to Birst‘ or ‘Live Access‘. This tutorial will be sticking with 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: Live Access provides a real-time connection to data in local data marts and data warehouses without requiring that data to first be uploaded into the Birst data store (warehouse). With Live Access, a Birst logical model maps directly onto a local physical relational database. 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: Assuming you already have an agent installed, you can select the ‘Select agent’ option and choose an active agent from the agent selection menu by clicking on the ‘Show online agents only’ button. If you do not already have an agent installed, refer to the How to Install Birst Cloud Agent in Infor Birst (youtube.com) video and tutorial.
    
  • Database type: The specific category of your database system. Keep in mind that you will also need a driver, which serves as a communication bridge between Birst and the database. In this example, MSSQL will be used as the MS SQL Server JDBC driver comes with the Birst platform. However, if you are using a different type of database, you will need to download the driver yourself.
    • MSSQL (Microsoft SQL Server): A relational database developed by Microsoft and has features for data storage, management, and retrieval.
    • MySQL: A widely used open-source relational database that is used for web applications and is known for its speed, reliability, and ease of use.
    • Oracle 11g: A relational database developed by Oracle and has features for data management, high availability, scalability, and security. This is most widely used in enterprise-level applications.
    • PostgreSQL: An open-source object-relational database and has features for supporting JSON data, spatial data, and full-text search.
    • Redshift: A cloud-based data warehouse developed by Amazon Web Services (AWS) and is optimized for quick querying and analyzing large datasets.
    • SAP Hana: An in-memory relational database developed by SAP and is designed to efficiently store and process large quantities of data in real-time.
    • Snowflake: A cloud-based data warehousing platform known for its scalability, performance, and easy to use. This has features that support structured and semi-structured data, data integration, analytics, and sharing data across organizations.
      
  • Server Name: The hostname or the IP address of the server, where the database that you are connecting to is located.
  • Database Name: An identifier assigned to the database. This helps Birst identify and connect to the correct database.
  • Security Credentials: The username and password that is associated to an authorized user, who has permissions to access the database.

*Optional: Below the ‘Security Credentials‘ login box is a ‘More Options‘ button. You can click this button if you want to specify fetch size, port, source prefix, and transaction isolation level.

• Fetch size: The number of rows that are retrieved from the database during a query.
• Port: A numeric value that represents the network port where the database server is communicating with the Birst platform.
• Source prefix: A string of text or an identifier that is added to the beginning of the names of imported data tables within Birst. If you want to add a source prefix, be sure to click on the slide to allow for providing the desired text. In this example, the prefix, ‘sql‘, will be used so that the database being used is identified and duplicate tables from different sources are distinguished.
• Transaction isolation level: Determines the visibility of changes made by a single transaction to other concurrent transactions.
  • Read committed: Transactions can only see changes committed by other transactions and provides better data consistency.
  • Read uncommitted: Transactions can see changes made by other transactions, even if they are not committed yet and allows for high concurrency. But you are at risk of receiving inconsistent and potentially, incorrect data.
  • Repeatable read: Regardless of any changes made, the data seen within a transaction remains the same.
  • Serializable: Transactions are isolated from each other, and each transaction can only see the database as if it were the only one accessing it.

When finished with providing the necessary information, 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.