Power Bi / Looker Stuido Conection
Good afternoon, has anyone tried to create a Twenty dashboard by connecting Power BI or Looker Studio directly to the application's database? I'm not able to make this connection.. 😢
11 Replies
You should be able to connect it to the postgres db, but if you are running it on docker, you will have to tweak the db container settings and db settings to allow outside connections. In that case also make sure you properly configure firewalls and use a good password :)
I'm confused on why you want to connect it to PowerBi though, do you want to use it to create graphs based on the twenty data?
@gazzoni : I had the same issue - my motivation was that I wanted to create an app in Teams that made some simple reports from the sales team to management.
Here's what I had to do to access the database from outside:
1) In the docker-compose.yaml file, you need to add a port mapping under the db: section, for instance under volumes:
ports:
- "5432:5432"
2) Rebuild the docker container for the db
3) Inside the twenty-db-1 docker container, you need to edit /var/lib/postgresql/data/pg_hba.conf and add a line like
host all all 0.0.0.0/32 md5
under # IPv4 local connections - you may want to change the IP range for additional security.
4) Restart the db docker container.
Then you should be able to access the database using the postgres user and the password that you find in the .env file.
no dont point it to the DB directly the fastest way to do it is via the API.
I got mine up and running in about 5 min via the API
true, I forgot Power Bi could grab data from APIs
i just wrote up a how to guide on how this can be done and did a pull request for the twenty website hopeully it gets approved so you can access it later
here is the guide: https://github.com/updateset/how-to/blob/main/Twenty%20-%20Power%20BI%20Integration.md
GitHub
how-to/Twenty - Power BI Integration.md at main · updateset/how-to
repository of how to documents. Contribute to updateset/how-to development by creating an account on GitHub.
Thanks! This is so much better and considerably less work!
My only issue with this is that I'm currently hosting twenty locally on an internal server without a trusted SSL certificate.
Need to fix that (which I should do at any rate...) before this will work for us, but that's a great motivation for doing so.
I extract all data of interest to a db and then hook up my BI tool of choise
If you want i can show you a guide on how you can do this with the docker images alot easier using a reverse proxy like traefik
this is a great method especially if you need to join and normalize multiple datasets with your twenty data. I think depending on the size and maturity of your organization is what drives the solution.
Thanks, that would be much appreciated... I've been doing quite a bit of research on the 'easiest' way to do this, but none of them seem to quite do it for me so far.
One thing that threw me off a bit here was that I could only fetch 60 records at the time from the endpoint by default... But I managed to fetch everything I needed by inserting ?limit=1000 after the endpoint. Don't know if the limit is by design or accident? Note that I've never worked with neither PowerBI nor REST APIs before, so this could just be my own ignorance.
limits are important for pagination and so you dont overload your apps