February 26, 2014 | Alan
This post provides a walkthrough from first entering the AnalytiXagility platform to viewing and getting a feel for the data using the built-in tools provided by the underlying Chorus database.
While working through this post we shall:
- Produce some simple data visualisations.
- Derive a Custom View for further use in the AnalytiXagility platform.
Related Blog Posts
- Navigating the AnalytiXagility platform.
- Using the Chorus interface to get a feel for the data.
- Generating a simple view of the data for use in later analysis.
- Linking across different datasets using joins (optional).
- Some basic SQL queries (optional).
Step 1 – Choosing your workspace
Upon logging in you are taken to the Workspaces page which contains all the workspaces you have access to:
Clicking on a workspace displays the members of the workspace and any recent activity within that workspace in the sidebar. There are also tools which allow you to add notes and tags, or to leave comments to aid project collaboration. Click the name or adjacent folder icon of your desired workspace and you are taken to its summary page, with access to the full suite of analytic tools within the platform:
Next to the Summary tab at the top left of the page are the Data and Work Files tabs and at the top right of the page is the R Tools button:
- Click the Data tab to see a list of all the data contained in your chosen workspace and get access to tools for viewing and carrying out some preliminary analysis on this data.
- Click the Work Files tab to gain access to any scripts in the workspace, as well as to view and download images or reports which have been created within this workspace.
- Click R Tools to bring you to the R console (described in detail in Day 1: Getting Started with the R console in the AnalytiXAgility platform.
You can return to the summary page by clicking the Summary tab.
Step 2 – Choosing your dataset
For the moment simply click the Data tab to be taken to a page similar to the one below:
The sidebar shows any recent activity in the currently highlighted dataset and you can use the Preview Data tab to generate a view of the data. Notes, tags and comments can also be added from the sidebar. Click on the dataset you are interested in to reveal much more detail about that particular dataset and give you access to tools for preliminary exploration of the data.
Step 3 – Exploring your data
You should now be on a page similar to the image below:
A list of columns in the dataset is displayed, with the column name on the left and the type of data contained in that column on the right. Selecting one of these columns and then selecting the Information tab in the sidebar gives a more detailed overview of that column. If you want to see the data displayed as a table then use the Data Preview button, above the list of columns and just to the left of the sidebar, to show up to 10,000 rows from the table. The Visualize button, just above Data Preview, allows you to generate some basic plots which can help you to quickly understand the data and decide where to start your investigation.
For example, simply by selecting two columns and adding a filter as above, we can create a basic box plot:
Step 4 – Creating a Custom View
To the left of the Visualize button is the option to Derive a Custom View. If you are familiar with relational databases then you’ve 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 to be executed whenever the view is invoked and therefore reflects changes in the table to which it refers. A Custom View takes this abstraction a level further, existing purely as a metadata object which does not physically exist in the workspace’s underlying database. As such, a Custom View cannot be queried elsewhere in the AnalytiXagility Platform and so must be converted into a database view if this is required (see Step 5). Note that this means Custom Views cannot be deleted using an SQL script and so you must manually delete unwanted Custom Views while in the Data tab of the platform.
Selecting Derive a Custom View brings up an interface for subsetting your data using filters:
By default, the Custom View includes all columns in the dataset but you may choose specific columns for inclusion by selecting them from the list at the bottom of the interface. Currently-selected columns appear in the sidebar where they can be deselected, allowing for specific columns to be chosen for exclusion by first selecting the All option at the top left of the interface, and then deselecting the columns you would like to exclude from the sidebar.
Create filters using the option at the top right of the interface and then define them by selecting a column name and operator from the drop-down menus and entering a value in the text box. In the following example, four filters have been defined and four columns have been chosen to include in the Custom view; you can see these in the sidebar:
At any point while defining the parameters for your Custom View, pressing Preview SQL in the sidebar shows you the SQL query which is being defined by your filters and column selections. There is also the option to Add a Join in the sidebar which allows you to link together related datasets using SQL joins.
Once you’re happy with the filters to be applied and the columns to be included, click Verify Custom View in the sidebar to display the SQL query that will be executed to create the Custom View. If you are familiar with SQL you may edit the query here before it is executed (see the ‘What’s Next?’ section of this post for more information on how to create your own SQL work files in the AnalytiXagility platform). Otherwise you may preview the view that will be created, by clicking Data Preview at the top left, and then finalise your parameters by clicking Create Custom View at the bottom right and entering a name when prompted.
Step 5 – Creating a database view
After completing the previous step you are immediately taken to the overview page, as shown at the start of Step 3, for your newly-created Custom View. From here you can either:
- Continue to conduct preliminary analysis and subsetting on your data, by repeating steps 3 and 4 for this Custom View, or
- Move on to more in-depth analysis in other sections of the AnalytiXagility platform.
If you want to conduct further analysis on this newly-created view in different areas of the AnalytiXagility platform (namely SQL scripts or R scripts) you must transform the Custom View into a database view. To do that simply click Create as database view in the sidebar and enter a name for this view when prompted.
To continue this walkthrough with further analysis in the AnalytiXagility Platform using the R console see Day 1: Getting Started with the R console in the AnalytiXAgility platform.
As mentioned in Step 4, it is possible to edit the SQL query which generates the Custom View. In fact you can create your own SQL scripts by selecting the Work Files tab in your desired workspace then selecting the appropriate extension from the ✚▾ drop-down menu. This allows for more flexibility in the queries you create and will directly create database views or new tables rather than Custom Views.Tweet