Getting started with Scai

Once logged in to the Scai platform, you will see the sidebar opened and several tabs that can be clicked. Let's go through them one by one.

Connect to a JDBC data source

To connect to a new data source, follow these steps:

  • visit Supported databases section and, in the Remarks section, check whether or not you need to install an additional connector to connect Scai to your data source. If a connector needs to be installed, then follow these instructions
  • go to Settings, Data sources
  • click the Create new from the right side of the header
  • after the menu pops up, fill in all the JDBC information, such as:
    • vendor: the vendor of the database you are trying to connect to. All the supported vendors will be shown in the version of ScaiPlatform you are using, e.g. MySQL, PostgreSQL, Redshift, H2, SqlServer, etc.
    • name: an easy to remember name for the data source. Please make sure that all data sources have different names and avoid scai_meta since it is given to the default metadata database.
    • url: the JDBC url to use for the connection. This field will be automatically filled in for you with an example of how that url looks like for the chosen database vendor.

      Make sure to replace the variable parts of the url, e.g. [host] with the host name or ip address of the database and [database] with the name of the database

    • username
    • password
    • shared - whether the source is shared with the users or not. If the source is shared with other users, make sure to restrict table and column visibility, otherwise all the other users will be able to see all the source data
  • click the Save button
  • once the data source is added to the data sources table, you can click on its name to switch to it
  • click on yes and you will be automatically switched to the data source

Troubleshooting the connection to the data source

Here are a few reasons why the connection to the data source may not work:

  1. The database server is down/not active
  2. The credentials used are incorrect (username or password)
  3. The url introduced is incorrect
    • make sure that the host [host] part of the JDBC url is correct and the database host is accessible by the Scai server
    • make sure that the port used in the JDBC url is open on the [host] machine and the Scai server is allowed to connect to it. Depending on the system where the database server resides on, you may need to configure the firewall rules to allow access to the specified port. On the cloud platforms, this probably means adding some network security configuration for the database instances/services.
  4. The [database] used in the JDBC url does not exist
  5. The appropriate JDBC connector has not been installed together with the ScaiPlatform. By default, ScaiPlatform comes bundled with connectors to MySQL (5.5, 5.6, 5.7), PostgreSQL, H2, SqlServer. It does not come, by default, with connectors to MySQL 8 or Amazon Redshift, for example. Please make sure to check the connectors section for more information on how to install needed connectors.

Exploring Data

This Data sidebar tab will let you explore the data inside the SQL data source you just connected and switched to. Once this tab is opened, you can see all the schemas that are visible to Scai and that have not been hidden in Scai's configuration tables. Clicking on any schema listed here should display a list of the tables that are visible to Scai and belong to the given schema. Finally, clicking on any of these tables will lead you to viewing the table's contents in list view.

Open table or view

Search for the name of table or view in the left sidebar. Once found, simply click on the its link to open it.

List table or view

Once a table is opened, you will see its contents. Actually, you will usually only see the first 20 rows of this table. These rows will be listed inside a widget. The widget containing the table rows is draggable and resizable. In fact, all widgets that you will run into while playing with Scai are draggable and resizable.

You can move them by either:

  • dragging the header
  • dragging from the top-left corner

You can resize them by focusing one of the corners except the top-left one and dragging until the widget size meets your needs.

To navigate through different pages, press one of the numbers (or arrows) in the bottom-left corner.

Seeing more than 20 rows

You can choose how many rows you would like to see per widget by selecting a different number from the bottom-right select box.

Ordering rows

To order the table contents, you can click on any column name once. This will make the data be ordered by the values on that particular column. Click twice to sort in descending order. You will see that in both cases a symbol will appear next to the column header describing the ascending or descending order.

If multiple column headers are clicked, then the data will be sorted according to the order the column headers were pressed. You will see that each column header will have, attached to the symbol, a small number that will describe the priority of the column in the final ordering (lower numbers have higher priority).

Filtering data

Here is an example on how you can drill down on your interesting data subset starting from a dashboard.

To filter the table data, you can click on the magnifying glass attached to any column header.

  • this will open a small window where you can choose how the filtering on that column should be performed
  • once the text input is focused, some suggestions of values will appear. As you type further in this text input, you will see suggestions that match the text you have entered
  • clicking on the top-right check mark will trigger the data filtering.

By default, all the rows that have a value on the filtered column that contains the entered text will be selected. This is because, when we opened the small window, we left the operation type (like) unchanged. There are other operation types that can be selected:

  • like filters all values that contain the entered text
    • only available for string columns
  • = filters all values that are exactly the same as the entered characters/number/date
  • not like filters all values that do not contain the entered characters
    • only available for string columns
  • not filters all values that are not exactly the same as the entered characters/number/date
  • > filters all values that are greater than the entered number or after the entered date
    • only available for number and date columns
  • filters all values that are greater than or equal to the entered number or on or after the entered date
    • only available for number and date columns
  • < filters all values that are smaller than the entered number or before the entered date
    • only available for number and date columns
  • filters all values that are smaller than or equal to the entered number or on or before the entered date
    • only available for number and date columns

Load file

Go to the main left sidebar, click Data, then Load file or simply search Load file and then follow these steps.

Create a graph

Firstly, open the table or view based on which you would like to create a graph, then click the Graph . The graph sidebar of that particular table or view will be opened with all the columns specific to it. Select your columns to plot and click Preview. If you want to save the graph to a dashboard, click Save.

Create a dashboard

If you want to create a new dashboard, go to the main sidebar menu, click Dashboards and then Create new dashboard or, simply search for Create new dashboard and then click it. Then, a new dashboard will be created and you can start adding graph widgets by clicking the + button from the header.

Create a view

If you have permissions and you want to create a new view, simply search for Console or click Data, then click Console. Type your SQL code and click the Submit button to execute the code. If you want to save the view, click the Create View button from the header and save the view to a specific schema.

Another way of creating a view is by going to a table or view and clicking the button and then on the Create view button.

If you do not see the Create view button in the right side of the opened window, it may be that you do not have permissions to create a new view in the database (you are not the owner of the data source connected to). If you are sure that you are the owner of the data source you are connected to, then it may be that you are trying to create a new view based on the data coming from a different data source (than the one you are trying to save to).

Then, choose the schema and name for the new view and click Save. A new table widget will be opened with the contents of the view/report.