Holistics Docs - End-to-End Business Intelligence Platform

Holistics Documentation

Welcome to the Holistics Documentation page. You'll find comprehensive guides and documentation to help you start working with Holistics as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started

SFTP Schedules

Setup regular schedules to export reports into an SFTP destination

Using SFTP schedules to setup report/dashboard to be sent to your SFTP folders every day. SFTP Schedules allow admins/analysts to schedule a report/dashboard to be sent to an SFTP destination.

Some common use cases:

  • Backup report data periodically to an SFTP server
  • Deliver data regularly to your partners' servers

How It Works

This section describes how you can set up Holistics to export report data to your SFTP server.

Configuring SFTP Integration

To manage your SFTP connections, please visit Integrations Page

To add new SFTP connection, click Add SFTP Connection and fill the required information below

  • Display name: Add integration title to manage your different connections
  • Host, Port: Address of the SFTP server
  • Remote Path: Destination files will be preceded by this path
  • Username, Password: Credentials to access the SFTP server
  • Holistics Public Key If you are using public key authentication instead of username/password, please add Holistics public key to your authorized keys

SFTP User Permissions

You will need to restrict the permissions of the SFTP user on your side. Typically, you would create an user dedicated for SFTP purpose, and allow this user to only read and write files within the root path. Example here.

Setup an SFTP Schedule

In any report, click Schedules on the toolbar, then choose New SFTP Schedule

Then fill the required information below for your schedule export

  • SFTP Connection: Choose the connection you want to create export schedule, you can manage it at Integrations Page
  • File path: The path of the destination file. Holistics will create folders/files if they do not exist. Otherwise, that file will be overwritten (CSV/Excel files are supported)
  • Schedule: Monthly/Weekly/Daily/Hourly...
  • Filter Values: Set the filter values for the report

Note: if you're on Object-based plan, each SFTP schedule will be 10 objects.

Writing Sequential Files

Sometimes when writing to destination, you want the system to add new files (based on date/timestamp) instead of overwriting existing files.

{{$today}}/{{$source_title}}_{{$timestamp}}.xlsx

The above will produce filenames like:

  • 2017-05-06/sales_pivo_t_table_1494106200.xlsx
  • 2017-05-07/sales_pivo_t_table_1614106200.xlsx

The variables you can use are:

  • $today, $yesterday: Today or yesterday in YYYY-MM-DD
  • $timestamp: Unix timestamp
  • $source_title: Title of the report, in lowercase and has its special characters replaced by underscores. E.g. 'Sales@Pivo^*?t Table' -> 'sales_pivo_t_table'

SFTP Schedule Execution Flow

A SFTP Schedule execution follows these steps:

  1. Execute the Query Report to get result data
  2. Write the result data to a temporary file in $REMOTE_PATH/.holistics_tmp/ ($REMOTE_PATH is configured in the SFTP connection)
  3. Prepare the destination path: create the non-existing directories in the destination path
  4. Move the temporary file to destination path

How to Setup SFTP User on Your Server

This section will give you a step-by-step instruction on setting up a new user on your SFTP server that is dedicated for SFTP purpose. The new user will be restricted to access a specific directory and use SFTP commands only.

Run the following commands as root.
Create the new user

adduser holistics_sftp

Create a new directory. Later, we will make sure the SFTP user can only access this directory by configuring ChrootDirectory. It needs to be owned by user root in order for the ChrootDirectory configuration to work

mkdir -p /holistics
chown root: /holistics

Since /holistics is owned by root, new user holistics_sftp should not be allowed to write to that directory. Thus, we will create an inner directory where holistics_sftp can write to.
In this example, we are creating a new folder named exported. Later when you fill in the settings of the SFTP Connection in Holistics, you should input exported in Root Path, so that all output files will reside within this directory.

mkdir -p /holistics/exported
chown holistics_sftp: /holistics/exported
chmod 0755 /holistics/exported

Restrict the access of new user by editing /etc/ssh/sshd_config

# override default of no subsystems
#Subsystem    sftp    /usr/lib/openssh/sftp-server
Subsystem sftp internal-sftp

Match User holistics_sftp
  X11Forwarding no
  AllowTcpForwarding no
  ChrootDirectory /holistics
  ForceCommand internal-sftp

The above config makes sure user holistics_sftp can only read the directory /holistics and can only use SFTP commands.

Restart sshd service

service sshd restart