Load & Explore Data

Actable AI provides numerous ways for you to connect and explore data. This documentation presents you with easy hands-on instruction on the following:

  1. Connect your database with Actable AI
  2. Upload new data from your PC
  3. Edit and delete your data
  4. Use provided functions to explore your data

However, if you’re just testing Actable AI, we provide several sample data for you to explore our functionalities. You can jump to Exploring Data and Analytics.

Connect to a new database

Actable AI is capable to analyse datasets that exist in your own database once you set up the connection. To connect Actable AI with your database, please follow the following steps. First, click the button Databases on the top right corner to let Actable AI gain access to your database.

_images/database_button.png

One could see the list of connected databases. Second, click the plus button on the top right corner to add a new database.

_images/connect_to_database_plus_button.png

The following screen would allow you to configure the connection options, make sure you already have a database configured and can connect to it from the instance on which you’re running Actable AI.

_images/connect_to_database_details.png

You can test your connection before saving the configuration by clicking the Test Connection button. Click the Save button to store the configuration once you are sure the connection configuration is correct. The browser will redirect back to the Databases view and the newly setup database would appear in the list.

Now that you’ve configured a database, let’s connect specific tables from your database to Actable AI that you’d like to query. Click the Connect to Table button. Select the table you want to connect with Actable AI by choosing the target Database, Schema and Table Name. It is worth mentioning you can type the name in order to find the option from the long list.

_images/connect_to_table.png

If the table exists in the “default” schema (e.g. the public schema in PostgreSQL or Redshift), you can leave the schema field blank.

Click on the Save button to save the configuration and to be redirected back to the list of tables. You should see a message indicating that your table was created.

The following RDBMS are currently supported:

  1. Amazon Athena
  2. Amazon Redshift
  3. Apache Drill
  4. Apache Druid
  5. Apache Hive
  6. Apache Impala
  7. Apache Kylin
  8. Apache Pinot
  9. Apache Spark SQL
  10. BigQuery
  11. ClickHouse
  12. Google Sheets
  13. Greenplum
  14. IBM Db2
  15. MySQL
  16. Oracle
  17. PostgreSQL
  18. Presto
  19. Snowflake
  20. SQLite
  21. SQL Server
  22. Teradata
  23. Vertica

Upload New Data

If you would like to work with new data, you can use our upload functions. Actable AI supports both CSV and Excel files.

_images/upload_csv.png

For Excel, we support users to specify the exact sheet. If the user leaves it blank, we only upload the first sheet in the excel file.

_images/upload_excel.png

To get more control, you can acces more options by clicking the More Option button. In these settings, you can specify the database schema you want to upload to, whether to replace if the table exists, rows to read/skip, delimiter character (for CSV only) and etc.

_images/upload_excel_more_control.png

Control Your Data

We respect users’ privacy. You always have full control of the table connected with Actable AI. You can edit, delete a connected table or a database.

In order to edit a database, go to the Databases view, click the edit button for a database source. You can also decide to delete an existing connection.

_images/databases_control_button.png

After clicking the edit button, you can change the database connection in the following page.

_images/edit_database.png

To edit the table, go back to the Data view, click the edit button for the table, or even remove the table connection. N.B. These operations would not affect your own data source.

_images/button_modify_table.png

After clicking the edit button, you can see the following window.

_images/edit_table.png

We provide two useful features for you to enrich your dataset: Metrics and Calculated Columns.

The Metrics let you customize additional operations for dataset columns, and the Calculated Columns let you create additional columns.

The is temporal field should be checked for any date or time fields. It helps the app to slice & dice the column(s) in time series charts.

_images/is_temporal.png

You may notice you can even edit a specific column in this view, such as disable the Filterablility/Groupablity and even override the column type. But be cautious while you do these operations, it might cause the app to fail to read the table correctly.

As with the configurations above, click the Save button to save these settings.

Exploring Data

Analytics View

To start exploring your data, simply click on the table name you just created in the list of available tables. Actable AI provides a search bar on the top right corner if you are not able to find your table. We are going to use wb_heatlth_population as an example.

_images/example_data.png

Once the data is loaded (it might take slightly longer to load a large dataset), you can see the following screen.

_images/analysis_view.png

The screen has three parts, control bar, visual panel, edit panel.

In the control bar (top), we display the analytic title, row count, task elapsed time. You can also run/save your analytics, duplicate your analytics, download the result and share your analytics with your friends and colleagues. N.B. The task elapsed time includes both tasks queuing time and computation time. We only charge for the computation time.

The visual panel (left) may vary according to the analytics type. We will cover this in the Analytics section.

The edit panel (right) is where you control your analytics and tune your parameters. The table view to explore your data is a certain type of analytics in Actable AI.

Let’s walk through a basic example to get the count of all records in our table for a certain period time. First, we’ll need to change the Time range filter to capture the range of our data. Set up a filter using Custom and Start/end.

_images/analysis_time_range_filter.png

Look for the Metrics section under the GROUP BY header, and start typing “Count” You’ll see a list of metrics matching what you type:

_images/analysis_metrics.png

Select any column you want to count. In this example, we are going to count for the country_name column. Remember to remove the columns, otherwise, the app will try to display all columns as well as the aggregate count column, which will cause the error. Your final setting should look like the following:

_images/analysis_final_setting.png

Click the Run button in the control bar, wait for a few seconds, you should be able to see the final results in the visual panel.

We support custom SQL in the Metrics control, click on the COUNT(country_name) we just did, switch to the custom SQL tab, write your SQL. For example, we would like to count unique values for a country name. We can do COUNT(DISTINCT(country_name)), such as the following screen.

What is the count for unique country names for each country code for this table? Let’s do a group by.

_images/analysis_with_group_by.png

Press the Run button again. After a few seconds, we would be able to see the following result.

_images/analysis_final_result.png

We showed a simple example of how Actable AI could help you explore your data. But in most cases, you would like to explore your data more. Now let’s introduce SQL Lab a modern, feature-rich SQL IDE for you to explore your data more.

SQL Lab

_images/sqllab_preview.png

It provides the following features:

  1. Connects to just about any database backend.
  2. A multi-tab environment to work on multiple queries at a time.
  3. A smooth flow to visualize your query results using Actable AI’s rich visualization capabilities.
  4. Browse database metadata: tables, columns, indexes, partitions.
  5. Support for long-running queries.
  6. A query history tab to find queries executed in the past.
  7. Save query and share query to enable query reusability.

To get started, please set up the database and schema. The IDE would focus on the connection you selected.

_images/sqllab_setup.png

The preview would appear when interacting with the IDE input box. SQLLab has both table schema and content preview. Preview content would be available to be copied by clicking the clipboard button.

_images/sqllab_table_preview.png

Previous executed query are listed in Query History tab, one could use the action buttons to edit or delete the queries.

_images/sqllab_query_history.png

The Save Query button would save SQL result as a new table for future analysis.