Advanced Analytics
Gives full play to the value of your data
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.
-
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 andpassword
with a strong password. Alternatively, you can runCREATE USER
as a shortcut forCREATE 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 theSELECT
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