It is recommended that you create a new database user/credential to connect to Holistics.
Read-only or write permission?
To function properly, Holistics only requires a read-only permission to your database.
However, if you want to use Imports and Persistence Transforms functionalities, you will need to grant additinal write permission. The write permission can be limited to a specific schema in your database.
- Import Models allow you to load data from simple 3rd party places (MongoDB, Google sheets, CSV files) into your SQL database.
- Transform Models (persistent) allow you to pre-aggregate/summarize data and write the results back to your SQL database.
Create User & Grant Permissions to tables
Assuming that the DB Username is holistics, the below section shows you how to create the DB user, and grant necessary access to it.
Fail to select data: Access denied for user...
Sometimes when executing the query, you may encounter this error:
The reason is that behind the scene Holistics also needs permission to run a CREATE TEMPORARY TABLE query to interpret your query result's data types.
If Holistics's DB user is not allowed such permission, the query will fail and throw an error. In this case, please grant the user permission to create the temporary table using this command:
- Oracle's schema is basically the set of all tables and other objects owned by user account, so it is roughly equivalent to a user account.
- Oracle supports four different character data types namely: CHAR, VARCHAR2, NCHAR, NVARCHAR2.