Page History

Remote storage enables to store Data set in a remote database instead of the database used by BellaDati. It is used to decrease the load on the main server and to increase the performance when working with large amounts of data.

Compatibility

Remote storage is currently available for these types of databases:

setting up remote storage

Remote Storage can be configured for the whole domain or for each data set individually.

Domain settings

It is possible to set up a global remote storage for the whole domain. When used, all data sets will be stored in the selected database by default. 

The remote storage can be enabled in the Advanced settings of the domain.

As a next step, the user has to select an existing database connection or create a new one, by filling in the following information:

• host
• database
• user
• password

After clicking on Continue, the Remote storage will be connected and it will be used as the default storage for all newly created data sets. Existing data set will remain unchanged, including the data. To apply the changes, the users need to sign out and sign in again.

Data set settings

Remote storage can be set up by opening Advanced settings in the Settings menu on Data Set detail page and clicking on Edit link in the Remote storage section.

You can use already existing database connection or create a new connection to a database.

switching to local STORAGE

To switch Data set using Remote storage to use Local storage (the database used by BellaDati) open Advanced settings in the Settings menu on Data Set detail page and clicking on Edit link in the Remote storage section.

Under New storage setting check Set to local storage and hit Continue.

Joined data sets

When using remote storage, data set can be joined only with data sets located in the same remote storage.

Using Microsoft SQL Server as Remote Storage

It is possible to use Microsoft SQL Server as remote storage. Currently, versions Server 2016 and Server 2017 are supported. When defining the database connection, users can switch from PostgreSQL to Microsoft SQL Server. Further steps are the same as when working with PostgreSQL. Currently, there are following known issues:

• unsupported aggregations: median, all regression functions
• unsupported functions: histogram
• not supported features:
  
  • joining
  • partitioning
  • GIS
  • translation and appearance

USING Oracle Database AS REMOTE STORAGE

It is possible to use Oracle Database as remote storage. Currently, version Oracle 12c is supported. Also, the container databases are not supported at the moment. When defining the database connection, users can switch from PostgreSQL to Oracle Database. Further steps are the same as when working with PostgreSQL. Currently, there are following known issues:

• unsupported aggregations: median, quartile1st, quartile3rd, correlations
• unsupported functions: histogram, xy chart
• not supported features:
  
  • partitioning
  • joined data sets

The user, which is used to connect to the Oracle database, has to have a specific set of privileges. For testing purposes and internal environments, it is possible to use a database administrator (DBA). For production environments, we recommend creating a user which will have only the necessary privileges, assigned by following queries (executed by the administrator):

GRANT CONNECT TO NAME_OF_THE_USER;
GRANT CREATE SESSION TO NAME_OF_THE_USER;
grant SELECT on "SYSTEM"."SOURCE" to "NAME_OF_THE_USER";
GRANT CREATE USER TO NAME_OF_THE_USER;
GRANT ALTER SESSION TO NAME_OF_THE_USER;
GRANT CREATE VIEW TO NAME_OF_THE_USER;
GRANT UNLIMITED TABLESPACE TO NAME_OF_THE_USER WITH ADMIN OPTION;
GRANT CREATE ANY TABLE TO NAME_OF_THE_USER;
GRANT CREATE ANY SEQUENCE TO NAME_OF_THE_USER;
GRANT SELECT ANY SEQUENCE TO NAME_OF_THE_USER;
GRANT SELECT ANY TABLE TO NAME_OF_THE_USER;
GRANT LOCK ANY TABLE TO NAME_OF_THE_USER;
GRANT ALTER ANY TABLE TO NAME_OF_THE_USER;
GRANT CREATE ANY INDEX TO NAME_OF_THE_USER;
GRANT DROP ANY TABLE TO NAME_OF_THE_USER;
GRANT INSERT ANY TABLE TO NAME_OF_THE_USER;
GRANT UPDATE ANY TABLE TO NAME_OF_THE_USER;
GRANT DELETE ANY TABLE TO NAME_OF_THE_USER;

When the remote storage is configured, BellaDati will automatically create a new user in the Oracle database. The user will have the same name as the domain in BellaDati (e.g. DS_DOMAIN1). It is necessary to assign a tablespace quota for this user in tablespace users (or the tablespace which is used instead of the default one).

alter user DS_DOMAIN1 quota unlimited on users;

Importing data

When Oracle is used as remote storage, a special SQL query without logging is used when using the import method Delete all rows in data set. This is done to improve the performance of the import.