You can connect BI systems to BRIX 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 BRIX
Install the BRIX server to Ubuntu. Read more about it in the Install BRIX to MicroK8s article.
Prepare the initial data
After successfully installing the BRIX 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 BRIX Standard if the PostgreSQL database is within MicroK8s. For BRIX Enterprise with a database outside MicroK8s, this preparation step is not required.
echo postgres:$(sudo kubectl get secrets postgres -o jsonpath='{.data.postgresql-password}' | base64 -d)
- 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.
- Run the following command, passing the file you created:
sudo kubectl apply -f pgportforward.yaml
- Now you can connect to the database from another machine in the network via the IP address and port 30432.
Install Power BI
- Download Power BI and deploy it on the client machine following the instructions.
- Establish connection with the BRIX server using the PostgreSQL database.
- 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).
- When the connection is successfully established, get the list of tables in the BRIX database.
- Choose the tables you want to create a report for.
- 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.
- Configure which fields need to be displayed in columns using the
button. - Set connection between tables if you need to make a selection from two or more.
- In the Report view, select the data visualization format: table, chart, or map.
- 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