Connect to Real-Time Sybase Data in Power Apps Using Dataflows to Build Custom Applications

Power Apps is a suite of apps, services, and connectors that allows users to build custom applications with minimal or no coding. It empowers businesses to create tailored apps that solve specific business challenges, automate workflows, and integrate with various data sources, including Microsoft Dataverse, SQL Server, and third-party services.

Dataflows in Power Apps simplify the process of importing, transforming, and loading external data into Microsoft Dataverse or other storage systems. They allow users to connect to multiple data sources (like Salesforce, Excel, or SQL databases), clean or shape the data, and store it in Power Apps. When paired with the CData Power BI Connector for Sybase, it provides access to Sybase data to build custom applications and more

This article demonstrates how you can easily connect to Sybase using the CData Power BI Connector for Sybase and integrate your Sybase data through the Power Apps on-premises data gateway.

Configure a DSN to connect to Sybase data

Installing the Power BI Connector creates a DSN (data source name) called CData PBI Sybase Sys. This the name of the DSN that Power BI uses to request a connection to the data source. Configure the DSN by filling in the required connection properties.

You can use the Microsoft ODBC Data Source Administrator to create a new DSN or configure (and rename) an existing DSN: From the Start menu, enter "ODBC Data Sources." Ensure that you run the version of the ODBC Administrator that corresponds to the bitness of your Power BI Desktop installation (32-bit or 64-bit).

To connect to Sybase, specify the following connection properties:

• Server: Set this to the name or network address of the Sybase database instance.
• Database: Set this to the name of the Sybase database running on the specified Server.

Optionally, you can also secure your connections with TLS/SSL by setting UseSSL to true.

Sybase supports several methods for authentication including Password and Kerberos.

Connect Using Password Authentication

Set the AuthScheme to Password and set the following connection properties to use Sybase authentication.

• User: Set this to the username of the authenticating Sybase user.
• Password: Set this to the username of the authenticating Sybase user.

Connect using LDAP Authentication

To connect with LDAP authentication, configure Sybase server-side to use the LDAP authentication mechanism.

After configuring Sybase for LDAP, you can connect using the same credentials as Password authentication.

Connect Using Kerberos Authentication

To leverage Kerberos authentication, begin by enabling it setting AuthScheme to Kerberos. See the Using Kerberos section in the Help documentation for more information on using Kerberos authentication.

You can find an example connection string below:

Server=MyServer;Port=MyPort;User=SampleUser;Password=SamplePassword;Database=MyDB;Kerberos=true;KerberosKDC=MyKDC;KerberosRealm=MYREALM.COM;KerberosSPN=server-name

Configure the on-premises data gateway to recognize the CData Power BI Connector for Sybase

In this section, we will configure the on-premises data gateway to detect the CData Power BI Connector for Sybase installed on your system. If you haven't installed the data gateway yet, you can download it from Microsoft's official website.

Set Up the Power BI Gateway

Follow the given process to configure the on-premise data gateway on your machine:

1. Download and install the on-premises data gateway (recommended) option. 👁 Install the On-Premise Data Gateway
   
2. Sign into the gateway. 👁 Sign into the gateway using your email-id
   
3. Create a name for the new gateway and specify a recovery key. 👁 Add a name and a recovery key to the gateway
   
4. Open the new gateway, navigate to the Connector tab, and select the path C:\Program Files\CData\CData Power BI Connector for Sybase from the folder. Click on Apply. 👁 The new gateway is now configured and ready to be used
   👁 Add the installation path to the CData Power BI Connector for Sybase
   

   NOTE: Select the folder where the gateway will search for the CData Power BI Connector.

5. Once the CData Power BI Connector for Sybase is identified by the gateway, you're good to go. 👁 The CData Power BI Connector for Sybase is identified by the gateway
   

Configure a dataflow connection in Power Apps

Once the on-premise data gateway is configured and a new gateway is created, follow these steps to create a dataflow that pulls in the Sybase data into Power Apps:

1. Open Power Apps. 👁 Open Power Apps
   
2. Select Dataflows from the left panel on the Power Apps screen and click + New Dataflow. 👁 Create a new dataflow
   
3. Provide a name to the dataflow and click Create. 👁 Add a name to the dataflow
   
4. Select ODBC from the list of data sources. 👁 Select ODBC
   
5. On the ODBC Connection settings screen, use the connection details you set up earlier by entering DSN=Connection name (in this case, DSN=CData PBI Sybase Sys) in the connection string. The on-premise data gateway will display available gateways for connection - select the one you created. Due to the current specifications of Power Apps dataflows, authentication is required for ODBC connections. Choose Basic as the authentication type and enter the Sybase Username and Password. Click Next. 👁 Configure the connection settings
   
6. Sybase is now connected to Power Apps. Under Display options, expand CData under ODBC and Sybase under CData, and a list of all the Sybase tables will appear in the panel. When you select any one of these tables, a preview will appear, showing that the Sybase data is correctly referenced through the Power BI connector and on-premise data gateway. Next, click on Transform Data. 👁 Click on Transform Data
   
7. On the query editing screen, if no column conversion is needed, you can skip this step and proceed by clicking on Next. 👁 Click on Next
   
8. In the Choose destination settings screen, you can select how the selected table needs to be loaded by choosing options like Load to new table, Load to existing table, and Do not load. You can also change the Table display name and description as required. 👁 Select how the table needs to be loaded
   👁 Choose the destination settings
   
9. Finally, choose how you'd like to update your data: Refresh manually or Refresh automatically. In this case, we have set it to Refresh automatically. By scheduling it to update every 45 minutes, as shown below, data will be collected and registered every 45 minutes timeframe, ensuring the most up-to-date information is always available. (You can select any timeframe based on your convenience) 👁 Choose among the two refresh settings
   
10. Click on Publish. The dataflow will now be created, published, and displayed as a part of the dataflow list on the Dataflows screen. 👁 Publish the dataflow
    

Get Started Today

At this point, you will have created a dataflow using live Sybase data and connected it to Power Apps. To learn more, explore the CData Power BI Connectors for Salesforce and download a free 30-day trial from the CData Power BI Connector for Sybase page.

Feel free to reach out to our Support Team with any questions.