Data Gateway allows you to harness the full potential of data using a BI tool to visualize the data. It provides an effective and efficient way for data management such as streamlining data storage and improving accessibility to optimizing data usage and analysis within BI tools.
NB: Please contact us at info@synatic.com if you require to use the data gateway to get a quote since it will be an additional cost to your package.
The following instructions contain the process of creating a data gateway and accessing the data in Power BI:
Activate User Group and Set Up Security
Create and activate a user group and then add existing users on your organization that will have access to use the data gateway.
On the Synatic admin portal, click on the user menu which is located on the top right of the web page as highlighted in the image below. A menu will appear. Select User Groups to go to the User Groups page.
Create a User Group by clicking on the Add Group button and type a suitable name. Select the users that require access by clicking on the expander to open the drop-down list and then click on the Save button as illustrated in the image below. This group will later be added to the Data Gateway Security Group.
Create a Data Gateway
Click on the three dots circled in red next to Gateway and click on Create Data Gateway as shown in the below page. Type an applicable name and click on the Save button.
Data Gateway Configuration
To configure a gateway, follow these steps:
Open the configuration page in the data gateway by clicking on the expander located on the right of the Data Gateway name.
As an endpoint, tick on the Db checkbox which will show the Server and Database details as shown in the below image. These details will be used when connecting to Power BI.
Add the user group that was created in the Security Groups field.
Add an IP address to the whitelist if required. As a default, all IP addresses will be allowed to have access.
Select the required buffer/s that is being used as a database by clicking on the “Select a Buffer” drop-down list which is located at the top of the configuration page.
Click on the Generate Data Gateway Schema button in the configuration page as outlined in red as shown in the page below.
Get Synatic Credentials to Access Power BI
To access a database, user credentials are required for authentication to access. The Data Gateway user authentication uses the credentials from your Synatic instance.
To get your credentials for the Data Gateway, click on the user menu located on the top right of the web page and click on Your Profile as illustrated in the below image.
Click on the Generate Key button and save the username and password in a safe location. These credentials will be used to provide authentication to the database in Power BI.
Accessing Data in Power BI
In the Power BI tool, click on Get Data, More and select PostgreSQL database as the data source as highlighted in the image below. Click on the Connect button to connect to the database (buffer on Synatic).
Add the PostgreSQL Server and Database details in the fields located in the DataGateway configuration page. Select DirectQuery as the Data Connectivity Mode and click on the OK button as shown in the page below.
Capture the Database Credentials which was saved from Your Profile on your Synatic environment as illustrated in the below image (See step 4 for assistance). Click on the Connect button.
For the User Name field in Power BI, add the keyword “powerbi:” followed by the user name (email address) as shown in the example below.
In the Navigator page, select the table/database to use to view the report as illustrated in the image below.
The below image shows how the data from a buffer on Synatic can be used to create Power BI reports and dashboards.
How to view queries from Power BI using the Data Gateway
To view the query, click on the three dots next to the data gateway that was created and select View Logs as shown in the below image.
Click on the eye icon to view the SQL query command. It can be used in a Buffer Query source step in Synatic or used in a 3rd party management tool such as pgAdmin.
The page below illustrates an example of the query.
