Advanced Analytics

Introduction

Are you finding yourself overwhelmed with raw data, struggling to discover the insights you need? Are your decisions based more on instinct rather than proves? Your solution is right here — Powerdrill launches its new feature Advanced Analytics.

Advanced Analytics can generate visualizations such as charts and maps after analyzing raw data. With Advanced Analytics, all you need to do is to “tell” Advanced Analytics what you want in natural language and let it uncover the trends and patterns in your data.

We are excited to announce our extension on this feature — SQL Advanced Analytics. This powerful new tool allows you to seamlessly integrate your SQL databases as data sources. Once connected, Powerdrill empowers you to delve into your data with the full suite of SQL commands, unlocking sophisticated analytical possibilities.

How to use general Advanced Analytics

Sign in to Powerdrill.
Select Advanced Analytics and select the files you want to analyze.

Only CSV, TSV, and Excel files are supported. Up to 10 files can be uploaded at a time.
Chat with Powerdrill to start your conversation over your dataset.

If you just want to see the effect of Advanced Analytics before uploading your own data, you can try our demo to take a glimpse.

How to use SQL Advanced Analytics

To use SQL Advanced Analytics, you must first connect your SQL databases to Powerdrill. Currently, Powerdrill supports two types of SQL databases: MySQL and PostgreSQL.

At Powerdrill, we prioritize the security of your data and privacy above all else. We have implemented robust security measures to ensure that your keys and passwords are never accessed or stored by our system.

Sign in to Powerdrill.
In the left sidebar, select Datasets.
On the page that is displayed, click + Dataset in the upper-right corner.
On the Create Dataset page, click the SQL Databases tab.

If this is your first time using this feature, the platform will prompt you to specify a user with read-only access to your database. For details about how to create a user with read-only privileges on the database, see Set user privileges to read-only.
Select your SQL database type.
- For PostgreSQL-compatible databases, select PostgreSQL.
- For MySQL-compatible databases, select MySQL.
Configure credentials.
- If you choose the General form, you need to specify the hostname, port, and name of the database to connect.
- If you choose the Advanced form, fill in the domain name of the database to connect to complete the URL.
Configure authentication information, including the username and password used to connect to the database.
If the database is configured with IP allowlists or blocklists, ensure the IP addresses provided on the page are in the allowlists.
Click Test Connection. If the connection is successful, click Finish.

Now, you can ask questions about anything you want to know from your database.

Set user privileges to read-only

The detailed procedure varies with your database type:

Connect to your PostgreSQL database as a superuser or a user with the necessary privileges to create roles and assign permissions.

For example, run the following command in the psql CLI:
```
psql -U postgres
```
Replace postgres with your superuser or administrative username.
Run CREATE ROLE to create a role.
```
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';
```
Replace readonly_user with the desired username and password with a strong password. Alternatively, you can run CREATE USER as a shortcut for CREATE ROLE ... WITH LOGIN.

Grant the CONNECT privilege on the database to the user.

GRANT CONNECT ON DATABASE <database_name> TO readonly_user;

Grant the USAGE privilege on the schema where your data resides.
```
GRANT USAGE ON SCHEMA <schema_name> TO readonly_user;
```

Grant the SELECT privilege on the tables in the schema.

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO readonly_user;

Set default privileges to allow readonly_user has the SELECT privilege on tables future created in the schema.
```
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO readonly_user;
```
Check whether the user privileges are configured as expected in psql:
```
psql -U readonly_user -d database_name
```

Connect to your PostgreSQL database as a superuser or a user with the necessary privileges to create roles and assign permissions.

For example, run the following command in the psql CLI:
```
psql -U postgres
```
Replace postgres with your superuser or administrative username.
Run CREATE ROLE to create a role.
```
CREATE ROLE readonly_user WITH LOGIN PASSWORD 'password';
```
Replace readonly_user with the desired username and password with a strong password. Alternatively, you can run CREATE USER as a shortcut for CREATE ROLE ... WITH LOGIN.

Grant the CONNECT privilege on the database to the user.

GRANT CONNECT ON DATABASE <database_name> TO readonly_user;

Grant the USAGE privilege on the schema where your data resides.
```
GRANT USAGE ON SCHEMA <schema_name> TO readonly_user;
```

Grant the SELECT privilege on the tables in the schema.

GRANT SELECT ON ALL TABLES IN SCHEMA <schema_name> TO readonly_user;

Set default privileges to allow readonly_user has the SELECT privilege on tables future created in the schema.
```
ALTER DEFAULT PRIVILEGES IN SCHEMA <schema_name> GRANT SELECT ON TABLES TO readonly_user;
```
Check whether the user privileges are configured as expected in psql:
```
psql -U readonly_user -d database_name
```

Use a MySQL CLI to connect to your MySQL server as the root user or another user with sufficient privileges to create new users and grant permissions.

Following uses the root user and the mysql CLI as an example.
```
mysql -u root -p
```
Enter the password as prompted.
Create a user and set a password for the user.
```
CREATE USER 'readonly_user'@'localhost' IDENTIFIED BY '<password>';
```
- readonly_user is an example only. Replace it as needed.
- If you want to allow the user to connect from any host, replace localhost with %.
Grant the SELECT privilege on all tables in the database to the readonly_user user.
```
GRANT SELECT ON <database_name>.* TO 'readonly_user'@'localhost';
```
Make the privileges take effect.
```
FLUSH PRIVILEGES;
```
Check whether the user privileges are configured as expected in mysql:
```
mysql -u readonly_user -p
```

Get Started

Features

Best Practices

Legal

Introduction

How to use general Advanced Analytics

How to use SQL Advanced Analytics

Set user privileges to read-only

Need more help?

Post to our help community

Contact us

Get Started

Features

Best Practices

Legal

​Introduction

​How to use general Advanced Analytics

​How to use SQL Advanced Analytics

​Set user privileges to read-only

​Need more help?

Post to our help community

Contact us

Introduction

How to use general Advanced Analytics

How to use SQL Advanced Analytics

Set user privileges to read-only

Need more help?