Connect a BI system to ELMA365 On-Premises (with Power BI as an example)

You can connect BI systems to ELMA365 On-Premises. They allow you to build reports and visualize data and analytics. Below you can find a step-by-step guide for connecting a BI system with Power BI as an example.

начало внимание

Note that editing data directly can cause undesired operation of the whole application. Use connection to a BI system only to read data.

конец внимание

Install ELMA365

Install the ELMA365 server to Ubuntu. Read more about it in the Install ELMA365 to MicroK8s article.

Prepare the initial data

After successfully installing the ELMA365 server, you need to get the PostgreSQL database login and password and forward the port that will be used to connect to the database. This step needs to be performed for ELMA365 Standard if the PostgreSQL database is within MicroK8s. For ELMA365 Enterprise with a database outside MicroK8s, this preparation step is not required.

1. Get the login and password to the PostgreSQL database by running the following command:

echo postgres:$(sudo kubectl get secrets postgres -o jsonpath='{.data.postgresql-password}' | base64 -d)

2. Create a file with the following content. Note that each space and indent matters!

apiVersion: v1
kind: Service
metadata:
  name: postgres-external
  namespace: default
spec:
  type: NodePort
  ports:
  - port: 5432
    nodePort: 30432
  selector:
   statefulset.kubernetes.io/pod-name: postgres-0

To create a file using the Ubuntu Server terminal:

• Launch the terminal-based text editor Nano. To do that, enter nano.
• Copy and paste the code into the editor. Close the editor by clicking Ctrl + X.
• Save the changes by clicking Y.
• Enter the name of the file, for example, pgportforward.yaml.

3. Run the following command, passing the file you created:

sudo kubectl apply -f pgportforward.yaml

4. Now you can connect to the database from another machine in the network via the IP address and port 30432.

Install Power BI

1. Download Power BI and deploy it on the client machine following the instructions.
2. Establish connection with the ELMA365 server using the PostgreSQL database.
3. Enter the server’s address, port 30432, the database name, and the PostgreSQL login and password that you obtained on the preparation step (step 1).
4. When the connection is successfully established, get the list of tables in the ELMA365 database.
5. Choose the tables you want to create a report for.
6. Load the tables to Power BI. If data in table rows is displayed in an incorrect format, go to the Transform tab, click the Parse option, and select JSON.
7. Configure which fields need to be displayed in columns using the button.
8. Set connection between tables if you need to make a selection from two or more.
9. In the Report view, select the data visualization format: table, chart, or map.
10. Create a report and display it as a PDF file or publish it using the Power BI features.

If you stop using Power BI, you need to close the port that is used to connect to the PostgreSQL database. This can be done with one of the commands below.

To delete the postgres-external service, run the following command:

sudo kubectl delete service postgres-external

To delete the postgres-external service using the pgportforward.yaml file, run:

sudo kubectl delete -f pgportforward.yaml

Was this helpful?

Thanks for your feedback!

Please specify why:

Recommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

How we can improve it?

YesNo

Please specify whyRecommendations did not help meArticle is hard to understandDidn`t answer my questionContent does not match the topicOther

Found a typo? Highlight the text, press ctrl + enter and notify us