Create Dataset

A Physical Dataset is a dataset that directly maps to an existing database table or view. It is essentially a registered reference to a physical object in your connected database.

Example

Suppose you have a table incident_records in Database. Registering this table as a Dataset makes it a Physical Dataset. You can then define calculated columns (e.g., CASE WHEN status='Closed' THEN 1 ELSE 0 END) and reusable metrics (e.g., COUNT(incident_id)) on top of it.

To create a Physical Dataset, perform the following steps:

1. Navigate to Reports > Custom widget > Datasets.
   The Datasets page is displayed.
   
   Figure: Datasets
   

2. Click + Dataset to create a new Dataset.
   The New dataset page is displayed.
   
   Figure: New dataset
   

3. Enter the required details as described in the following table and click Create and explore dataset.

   Field	Description
   Database	Select or enter the connected data source. The selected data source identifies which database is pulling tables or views from.
   Schema	The logical namespace inside the database that organizes tables and views. When a database is selected, its default schema is automatically applied. The schema helps Superset locate the correct table for dataset creation.
   Table	Select or enter the physical table or view in the selected schema. Automatically reads the table’s columns and datatypes when registered. Selecting a table registers it as a physical dataset.

   
   The list of the configured table is displayed.
   
   Figure: Table list
   

Identifying the Table Name

Tables in the mart follow a defined naming convention:

Mart_<module code>_<functional name>

Mart_<module code>_customattributes_sv

Mart_<module code>_customattributes_mv

Note

Avoid using Physical Datasets to create charts. Instead, use a virtual dataset based on the required system data. For more information, refer Best Practise of Reports.

A Virtual Dataset is a dataset created from a custom SQL query rather than directly mapping to a physical table or view in the database.

Example

Suppose you want to analyze Incident Records linked with Change Requests:

• A physical dataset would only give you data from incident_records or change_requests.

• A virtual dataset could be created with a SQL query joining both tables:
  

  SELECT i.incident_id, i.priority, c.change_id, c.status
  FROM incident_records i
  JOIN change_requests c ON i.change_ref = c.change_id;

  
  This query can be saved in SQL Lab and converted into a Virtual Dataset, enabling dashboards that show incident–change correlations.

Prerequisites

The required guidelines to ensure the dataset supports row-level security, naming standards, and time zone handling.

mart_<module_code>_custom_<functional_name>

Create Virtual Dataset

To create a Virtual Dataset, perform the following steps:

1. Navigate to Reports > Custom widget > SQL > SQL Lab.
   The SQL page is displayed.
   
   Figure: SQL
   

2. Enter the required details as described in the following table.

   Field	Description
   Database	Select or enter the connected data source. The connected data source in reports where the SQL query will run. You must select the database against which your virtual dataset query will be executed.
   Schema	The logical namespace inside the selected database that contains tables and views. Choosing the schema ensures your SQL query references the correct objects.
   Table Schema	Select the table name from the list for creating a virtual dataset, the reports uses the schema context to validate and execute the query

3. Select the required table(s) to view their columns and structure and in the Query Editor window on the right, write your SQL query using the selected table(s).
   
   
   Figure: SQL Query
   

4. Click Run the query to validate syntax and preview results.

After registering a dataset, configure its column properties to control how the columns are used in charts, filters, and the Explore workflow.

The following table describes the available column properties.

When the source dataset schema changes, synchronize the dataset to update its column definitions.

To synchronize columns from the source, perform the following steps:

1. Open the Dataset.
   

2. Click Sync Columns from Source and click Save.

Synchronizing the dataset updates the column definitions to reflect changes in the source dataset, such as newly added or renamed columns.

Note

After synchronizing the dataset, update any charts or dashboards that use the modified columns to ensure they display the correct data.