User Guide

Working with data

 

Accessing datasets

datasets icon, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasetsTo view all the database tables associated with a workspace, select the Datasets tab.

To filter the list of available database tables, use the drop-down labelled Show to filter the list.
datasets with menu, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets

You can also search for and filter the shown items by typing the name of the table into the search box at the top of the list.
datasets with search box, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets

datasets sidebar, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasetsOnce the database table you want to work with is visible, you can view information about it. To do this click anywhere on the row, other than the table name or icon, to highlight it; information about the table is then shown in the panel to the right hand side of the window.

Note the Activity and Information tabs on this panel. The Information’ tab shows summary information about the table. This information can be out of date if the table has been recently updated, and can be refreshed by clicking the Run analyse button.

To view the contents of a database table, click the Preview data button.
data preview, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets

 

Opening a database table

In the Datasets tab, clicking on either a table name or its associated icon opens a new Table page. This page shows the table’s fields and visualisation options. Clicking on multiple tables will open multiple Tables pages.
table, grabbed on 24.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets/8185

To only show certain fields belonging to the table, use the Search textbox to enter part of a column’s name.

 

Rapid visualisation

To visualise data within a data table, click the Visualise button, then:

  • Choose one of the chart types.
  • Choose one or more fields for the display (some charts only allow one field).
  • Optionally set up one or more filters, choosing field, operator, and value.
  • Click the Create chart button.

visualise options, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets/8185

The resulting data visualisation will appear in a popup window.
visualise popup, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets/8185

show options button, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets/8185You can edit the filters used to create the original visualisation by clicking the Show options button. Once you have changed the filters, click the Refresh visualisation button to see how the output has changed. If you wish to return to the original visualisation, simply click on the Revert button.

 

Create custom views

If you are familiar with relational databases then you have almost certainly encountered views and have probably used them in certain situations where it is preferable to creating new tables within your schema. In simple terms, a view (in SQL) is stored as a query on a particular dataset; this query is executed whenever the view is invoked, and therefore reflects changes in the table to which it refers.
custom view, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets/8185
custom view tools, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets/8185

A Custom View provides a way to refer to a database table with an SQL statement applied, typically to filter the rows and columns. In a Custom View, the definition of the view is held within the platform rather than the database, and can easily be modified and copied. However, a Custom View cannot be queried elsewhere in the AnalytiXagility platform (e.g. within the R console) and so must be converted into a Database View if this is required.

To create a Custom View, open the relevant table and click the Derive a custom view button, then:

  • Optionally set up one or more filters, choosing field, operator, and value.
  • Optionally choose one or more fields by clicking the list. The chosen fields appear in the panel in the right hand side of the window and can be removed from there by clicking on the X button.
  • Click the verify custom view button.

In the popup, review the SQL statement. Note that you can manually edit the SQL statement.
verify custom view, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#/workspaces/1152/datasets/8185

In the popup click the Create custom view button and provide a name e.g ‘anon_bed_days_2’. A newly created custom view of the dataset will appear in the datasets list in the Datasets tab.
datasets list, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#

To create a Database View from a Custom View, highlight the Custom View on the Datasets tab then click the Create as a database view button.

 

Joining tables

create a custom view, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#workspaces/1152/datasets/8185To join tables select the Add a join button.

Select a dataset to join, you can always preview columns by selecting clicking on Preview Columns. After selecting a table to join, click Join Table and select table keys on which tables should be joined and select the type of join required.

add a join, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#workspaces/1152/datasets/8185add a join, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#workspaces/1152/datasets/8185create a custom view, grabbed on 17.01.17, using XAP version 1.19.1 2740-8759a, from https://edcvaatest04.aridhiatest.net/#workspaces/1152/datasets/8185

Now in the Datasets tab you can preview joined tables and SQL.

A summary of how tables are joined is provided in the Joins sidebar menu.

Select the Verify custom view button to create a view of the joined tables.

 

To contact support, send an email to the Aridhia service desk: servicedesk@aridhia.com.