Introduction

This article outlines how to use the Astrato data view editor.

Data View Editor | Getting Started | Select | Define - Data, Custom Data, Dimensions and Measures, Joins | Workbook | Astrato Data Articles

Data View Editor

When you're working with data views in Astrato, you'll use the Data View Editor to create new data views, and edit the data view properties. You can open the data view editor from several places on the Astrato site.

  • Connect a new Data Source - The data view editor opens when you connect to a data source.

  • Create / Edit a Data View - The data view editor opens when you click Create Data View in the Data homepage, or Edit an existing data view, either from the list on the Data homepage, or clicking on the data view name inside a workbook.

Please note: joins are available for Snowflake and PostgreSQL data sources.

Back to Top

Getting Started

There are three main stages to working in the data view editor - Select, Define and Workbook. The Select data screen opens by default (Figure 1), so we're using that as an example here. We've allocated a number to each part of the which you can match to the number in the diagram. Descriptions start at the top of the screen and work down.

  1. The far left sidebar has three links - at the top the Astrato logo links to the Astrato homepage, and there are icons linking to the Select and Define screens.

  2. The left sidebar has a Stepper to help with navigation, the current screen is highlighted and you can open each screen by clicking one of the circles. This section of the data view editor contains the controls and features for managing your data view (Figure 1 has a Select data function).

  3. The top bar shows the data view name (you can click to edit), and the data source, database and description. There's also a button to move to the next step on the far right (Define data view in this example).

  4. The content area shows the data you're currently working with (this example shows a table and fields, and includes a Data preview button) which will update when you change the settings held in the left sidebar.

Figure 1: Data View Editor Overview

Back to Top

Select

The Select screen (Figure 2) is where you choose the data you to use in your visualization.

  • Use the checkboxes in Select data to choose specific tables, or simply select them all. Once you've selected a table the fields (columns) will be displayed in the content pane, listing the Field Name, Source Name and Data Type.

  • Organize the data to apply changes if you need to - use drag and drop to re-arrange data items, look for items with the search bar and click on the field to edit the field name.

  • Click Data Preview to check the changes you've applied are correct, then move to the Define screen, either by using the stepper or clicking Define data view.

Figure 2: Select

Back to Top

Define

The Define screen (Figure 3) is where you model your data for the visualization. There are three tabs - Data, Dimensions & Measures and Joins. If you add any settings in Your data view (e.g. a measure or a join), they'll be displayed in the tab. You can check the results as you go along in the content panel.

Figure 3: Define

Data

In Data, you'll see all the tables listed in the content area, with a panel suggesting any possible joins if you're working with a Snowflake data source. Joins link data held in different tables in a database or data view, and can be helpful when you're working with tables where the data is related. An example would be a table holding information about customers, and a table containing details of the orders customers have placed. If you add a join, you'll be able to add data from both tables to your workbook.

  • Click on a table name to see more detailed field properties and view any joins which have been applied.

  • If you need to change a field name, click on it to open the editor and check your results using Data Preview.

  • If you'd like to add more data from the data source, click Add to be taken to the Select screen.

  • When you enable Show Joins and hover over a join suggestion in the panel, it will be displayed with a broken line. If you save a join the line changes to a solid one.

  • You can also right-click on a field and choose to add it as a dimension or measure, as well as choose a function for the measure (e.g. sum). Once it's created you'll see it in the Dimensions and Measures sections in the left hand panel.

  • Click Apply all to save all join suggestions. You can edit or delete joins in the Joins tab.

Figure 4: Data

Back to Top

Custom Data (Custom SQL & CSV upload)

Custom Data contains options for adding more data to your dataset. Click Add to display the custom options (Figure 5).

Figure 5: Custom Data

Custom Query (Figure 6) lets you create your own customSQL query.

  • Click Add to open the form.

  • Select the Schema you want to use, enter the custom SQL in the text area and give your query a Name.

  • Click Save to add the query to the data view.

    ⚠ Please ensure that you remove a semicolon from the end of your query

    ℹ To support time travel, please add {TT} after each table name

Figure 6: Custom Query

The next option is Upload CSV file (Figure 7).

Astrato's CSV Upload feature allows you to upload and analyze CSV data in Astrato. This feature stores a copy of the CSV directly in your data warehouse. As such, it must be enabled on both your organization and individual warehouse connection by an organization admin.

The permission requirements are:

If you don't have a specific schema you wish users to upload their data to, then grant the following privileges to the role:

Create Schema

If you do have a dedicated schema created for CSV uploads, then grant the following privileges to the role:

Create Table

For more information on how to set privileges in ❄️Snowflake, please check out their documentation:

https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html

Uploading a CSV file

  • Click Add to open the upload form.

  • Add the CSV file you want to use (you can either use drag and drop, or browse using the file explorer).

  • Select the data you'd like to use in the modelling tool, then click Save. You should now see the CSV tables added to data view.

Figure 7: CSV Upload

Inline Table is going to be launched soon!

Back to Top

Dimensions & Measures

In the Dimensions & Measures tab you want to add a dimension or measure, click Add in the sidebar Dimensions or Measures section to open the Create Dimension or Create Measures form (Figure 5).

  • For dimensions, enter a field Name, or choose an option from the Fields dropdown (which populates the name automatically). You can also add a Description.

  • For measures, enter a field Name manually, or choosing one from the Fields dropdown list (which populates the name automatically). You can also choose an Aggregation for the measure and enter a Description. Click Save when you're finished.

  • You'll find any dimensions and measures you've added in the content pane. If you need to edit or delete a dimension/measure, click the dots to the right of the dimension/measure name and select the option you need.

Figure 5: Dimensions & Measures

Back to Top

Joins

In the Joins tab you can add and manage the joins for your data view.

Note: Astrato supports full outer joins to help you connect visualizations and dashboards across multiple tables. They're defined on any field of the same type, which means you shouldn't get any loops or circular joins when you create them.

  • Click Add Join to open the pop-up (Figure 6), select a Table and Fields from the box on the left, then select a Table and Fields from the box on the right. Click Save when you're finished, it will be added to the Joins tab.

  • If you want to edit or delete a join, click on the edit or delete icons next to the join name.

Figure 6: Joins

Back to Top

Workbook

Once you're finished, you can move to the Workbook step and start using the data view to develop your workbook.

  • Open the workbook editor, either by using the stepper, clicking Create workbook (which creates a new workbook) or selecting a connected workbook (a workbook you've already created) from the dropdown. (Figure 7).

Figure 7: Connected Workbooks

  • If you need to make changes to the data view after you've opened the workbook editor, click the data view name in the header to the right of the Astrato symbol to open the data view editor (Figure 8).

Figure 8: Go to Data View

Back to Top

Did this answer your question?