Introduction
Data Explorer is used to manage labelling, creation and ongoing maintenance of data tables, metric and derived metrics. It enables users to explore raw item level data with simple or extensive queries, to generate and download data extracts.
Data Tables
All your data tables and their metadata can be access in the data explorer. All the visualisations available in the visualization library as created using one or more of these tables.
Editing tables
You are not able to edit or update the table data yourself. However, you can:
Create an Alias name for the table. This is the name that will appear in the dashboards. The name must be in snake_case (can only contain lowercase letters, numbers, and underscores).
Add description/notes about the specific tables for others to read
Make the table Visible or Non Visible: making the table Non Visible will stop this table data and any associated visualizations made based on this table showing up in the visualisation library. If the visualization was added to the dashboard and later the table was deleted, the dashboard item would remain in the relevant dashboard until the user deletes it themselves.
To edit the above double click on the table name or click on the three dot menu and then select Edit.
Data Fields
Each data table has metadata to describe its characteristics. This includes details like what the data is about, how it's structured, and any special notes or instructions.
Once the data table is expanded you can see all the relevant fields available in the table.
Most of these fields are what is coming through from the source system but you will also see other field items marked with a pink, purple or blue dot. These fields are not coming from your source system but are the extra metrics that we have enabled in the product for you.
Derived metrics - The pink dot indicates that this field is a derived metric. These metrics are calculated based on your existing source data. Some sort of formula or calculation has been done either by Dexibit or by your own administrator.
Dexibit's AI powered metrics - The purple dot indicates that this field is a Dexibit Powered AI metric. These metrics are created by Dexibit using artificial intelligence algorithms and techniques such as machine learning, natural language processing, computer vision etc to analyze and derive insights from data.
Enriched metrics - The blue dot indicates that this field is an enriched metric. These are metrics that have been augmented or enhanced with additional context, insights, or data to provide a more comprehensive insight. This enrichment often involves incorporating diverse sources of information, including third party data such as Census, benchmarks etc.
A variety of Enriched metrics are available as an add on option, please speak with your customer success representative.
Editing Fields
You can edit and change the following in a field:
Change the display name. Once changed, this is the name that will appear in the dashboards. The name must be in snake_case (can only contain lowercase letters, numbers, and underscores).
Add descriptions: You can describe and explain what a specific fields is referring to, how/when it it applied/used in your organisation or how it was calculated. A lot of your colleagues won't know what the specific fields in your source system mean - it's a good idea to provide context to the wider business about these. These descriptions will be accessible via the data dictionary in the studio.
For some derived/Ai/enriched fields, the descriptions are automatically populated by Dexibit so that users understand what these metrics are and how they are calculated.
You can also turn a source field into a derived metric but in most cases, you should create the derived metric from scratch so that you don't loose the important raw into about the specific field.
Make the field Visible or Non Visible: making the field Non Visible will stop this field showing up as an available options in visualisations. If you have added a visualization to you dashboard and created a filter query with the specific field, once you make the filed Non Visible, the query won't break and the visualization will continue to display the relevant data until you delete the query or the visualization yourself.
Library Items
Under the data fields you will all the visualizations that available in your visualization library.
These library items are created for you by Dexibit based on best practice insights and metrics for the visitor attraction sector.
Querying the data
Just like you would query any other existing data base you can query your raw data directly from Dexibit via our DXQL.
Queries
If you are new to the world of queries, here are some useful starting points for writing DXQL queries.
SELECT, FROM and LIMIT
A basic statement to get all columns from a dataset:
SELECT * FROM antarctic_tickets LIMIT 100
This will retrieve the first 100 rows in the table.
Table Names: Antarctic tickets in this example is the table name. The table name can be seen on the relevant visualisation. Each data source (ticketing, membership, retail, Google reviews etc) will have its own table and thus will have its own table name.
To select a particular column, you can write the column names like this:
SELECT booking_lead_time FROM antarctic_tickets LIMIT 100
To get a list of all entries into a particular attribute, you can return all the values possible in the dataset:
SELECT distinct ticket_product FROM antarctic_tickets
Searching for data on specific date
SELECT column_name FROM table_name WHERE date LIKE = '%yyyy-mm-dd%'
Example:
SELECT ticket_product FROM antarctic_tickets WHERE date_sold like '%2023-01-23%'
Searching for data between a date period
SELECT column_name FROM table_name WHERE date >= 'yyyy-mm-dd' and date < 'yyyy-mm-dd'
Example:
SELECT ticket_product FROM antarctic_tickets WHERE date_sold >= '2023-01-23' and date_sold < '2024-01-24'
ORDER BY
To sort by a particular column you can use ORDER BY:
SELECT * FROM antarctic_tickets ORDER BY booking_lead_time DESC LIMIT 100
Using DESC or ASC changes between descending (highest to lowest) or ascending (lowest to highest).
Aggregate functions
At this point, you may want to start finding out some aggregates. You can pair an aggregate function (SUM, AVG) in combination with a GROUP BY:
SELECT channel, AVG(booking_lead_time) FROM antarctic_tickets GROUP BY channel
The above gets the average booking lead time by channel.
To rename the aggregate column, you can use AS to give an alias, i.e.:
SELECT channel, AVG(booking_lead_time) AS Average_lead_time FROM antarctic_tickets GROUP BY channel
Here’s a different query that will get the total sales of each different product:
SELECT line_item_product_name, SUM(line_item_quantity) FROM antarctic_retail GROUP BY line_item_product_name
JOIN
You can also join multiple data sets together to find insights between data sources, for example to categorize the impact of weather on visitation (in this example, we've referred to visitation date as 'vd', visitation count as 'vc', weather date as 'wd', weather conditions as 'wc'):
SELECT sum(v.vc) AS total_visitation, w.wc AS weather_condition FROM ( SELECT sum(visitation_count) AS vc, time_series (visitation_date, DAY) AS vd FROM antarctic_tickets WHERE vd BETWEEN '2023-01-01' AND '2024-01-01' GROUP BY vd ORDER BY vd ) AS v LEFT JOIN ( SELECT time_series (date, DAY) AS wd, weather_conditions AS wc FROM weather WHERE wd BETWEEN '2023-01-01' AND '2024-01-01' GROUP BY wd, wc ORDER BY wd ) AS w ON v.vd = w.wd GROUP BY weather_condition