Connect ERPNext to Google Data Studio

ERPNext
, , ,
1

Has anyone managed to successfully connect ERPNext with google data studio for data visualization? If anyone has any information on how to do this please share. Thanks

2

I tried to create datasource, It had option to connect mysql.

google-data-source-connection
google-data-source-connection456×574 18 KB

I didn’t try anything further, my mariadb is not accessible over internet.

1 Like
3

I am having the exact same problem as well. I was hoping there is a workaround

4

Hi
Have you created a database user with access from any IP?
Also, you have to change bind_address = 0.0.0.0 in my.cnf

Thanks

1 Like
5

Thanks, @hiimkhaled I have already tried that but with no success. Have you had any success with this? Since we also have an instance hosted on ERPNext is there a way we can connect that instance as well without access to the backend

6

Server port 3306 must allow connection.
Check if any firewall blocks port.

1 Like
7

Yes I have connected several mariadb databases with Google Data Studio and Google Apps Scripts using JDBC.

Btw, Have you opened the port 3306?

1 Like
8

Thanks everyone for the help, i have finally managed to connect MySQL to Google data studio i will be writing a detailed explanation on how to achieve this to help anyone who may be having issues then I will post it here

9

These steps are made thanks to Upande LTD Reach out to us if you need further help.

  1. Ssh to the server where the server is located e.g

     $ ssh <username>@<host ip>

  2. Set the MySQL server to listen on all IP addresses on the machine. To do this you will need to edit the mysql configurations file which will be different depending on mysql version.In my case i am using mariadb which i simply open the file using the following command:

    $ sudo nano /etc/mysql/my.cnf

For more information on this refer to the to the link below: https://linuxize.com/post/mysql- 
remote-access/

  3. Search for a line that begins with bind-address and set it to listen to 0.0.0.0

    Ie. change

          bind-address = 127.0.0.1 to bind-address = 0.0.0.0

  4. Look for the line skip-networking and delete it or comment it out i.e

    # skip-networking

  5. Restart the MySQL service depending on your system.If using ubuntu use the following command:

    $ sudo service mysql restart

 Next you need to create a specific mysql/mariadb user that will be allowed to access the 
database remotely

  6. Login to the mysql/mariadb shell using the following command:

    $ mysql -u root -p

  7. Create a mysql user that you are going to use for remote access:

    a.  Create user

MariaDB [(none)]>CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

b. Grant all privileges

MariaDB [(none)]> GRANT ALL PRIVILEGES ON * . * TO 'newuser'@'localhost';

Please note that the two asterisks represent the databases and tables that are all databases and

    all tables. You can specify database or tables if you like that.eg.

    MariaDB [(none)]> GRANT ALL PRIVILEGES ON database1. * TO 'newuser'@'localhost';

    (c) Reload Privileges

     MariaDB [(none)]> FLUSH PRIVILEGES;

  8. Allow the user to connect to the database from a specific IP address:

    MariaDB [(none)]> GRANT ALL ON database_name.* TO user_name@'ip_address' IDENTIFIED BY 'user_password';

    i. database_name is the name of the database that the user will connect to.
    ii. user_name is the name of the MySQL user.
    iii. IP_address is the IP address from which the user will connect. Use % to allow the user to connect
    from any IP address. In this case, however use the IP addresses specified by Google data studio
    (check 8 below)
    iv. user_password is the user password.

  9. Google Data studio gives a list of IP addresses that the user should be allowed to access from as given below. You can however also get the list from this link: Connect to MySQL - Looker Studio Help

    64.18.0.0/20

    64.233.160.0/19

    66.102.0.0/20

    66.249.80.0/20

    72.14.192.0/18

    74.125.0.0/16

    108.177.8.0/21

    173.194.0.0/16

    207.126.144.0/20

    209.85.128.0/17

    216.58.192.0/19

    216.239.32.0/19

As you may have noticed the IP address is in terms of ranges the best way to give access to these
ranges therefore is to make use of the wild card which gives access to all the ip within that range for
example if the ip address is 64.18.0.0/20 you can use this as the ip address with wildcards 64.18.%.%

Using the above example therefore in order to grant the first address access use the following
command

GRANT ALL ON <database_name>.* TO user_name@'64.18.%.%' IDENTIFIED BY 'user_password';
  1. Now head back to google data studio login enter the details: hostname/ip,database, username and password and you are good to go:

Untitled%20Report
Untitled%20Report1529×759 47 KB

5 Likes
10

Do you guys know why I get this error in Google Data Studio (Looker) :

image
image2473×1119 177 KB

Thank you for your help

11

Were you able to solve it? I get the same error.

image
image1920×965 78 KB

12

No I haven’t found a solution unfortunately

13

Nobody have connected to ERPNext to data studio?

14

I found a solution,

The problem is that all the Doctype that have a space between them (exemple : Sales Invoice) are not working in Looker. The Workaroud is this :

image
image1898×449 20.7 KB

2 Likes
15

I will test this out today itself. Meanwhile, I was duplicating the required tables to a different database without spaces in the table names. Thanks a lot.

16

Hello, is this possible in the cloud hosted version of ERPNext? I have a private bench in frappe cloud where my sites are running. Is it possible to connect Looker Studio to the DB that is powering the apps in these sites so that I can create better version of accounting reports? If yes, please provide the exact steps that need to be followed. Thanks in advance