Support of Nested Data Structures in Self-Service Analytics

Your software supports aggregations for nested (or hierarchical) data structures for some data stores.

Self-Service Analytics supports aggregations for nested (or hierarchical) data structures for some data stores.

Support for this feature by connector is shown in the following table.

Key:Y - Supported; N - Not Supported; N/A - not applicable

Connector	Supported?
Amazon Redshift	N/A
Amazon S3	N/A
Apache Drill	N/A
Apache Phoenix	N/A
Apache Phoenix Query Server (QS)	N/A
Apache Solr	N
BigQuery	N/A
Business Central Jet	N/A
Cloudera Impala	N/A
Cloudera Search	N/A
Couchbase	N/A
Dremio	N/A
Dundas BI (Managed)	source-dependent
Elasticsearch 7.0	Y
Elasticsearch 8.0	Y
File Upload (Upload API)	N/A
HDFS	N/A
Hive	N/A
Jira	N
MemSQL	N/A
Microsoft SQL Server	N/A
MongoDB	Y
MySQL	N/A
OpenSearch	Y
Oracle	N/A
PostgreSQL	N/A
Python	N
Real Time Sales	N/A
Salesforce	N
SAP Hana	N/A
SAP S/4HANA	N/A
SAP IQ	N/A
Spark SQL	N/A
Snowflake	N/A
Teradata	N/A
TIBCO DV	N/A
Trino	N/A
File Upload (Upload API)	N/A
Vertica	N/A

There are two ways to store nested structure:

1. Store all hierarchy as a single document, for example, in JSON format (nested documents).
2. Store hierarchy items as separate documents and additional info on hierarchical links internally (block join).

Nested Documents

Hierarchical structure can be represented in JSON format. In MongoDB and Elasticsearch, storing such structures is supported.

Consider the following example. We need to store a hierarchy of divisions by country with two divisions in country. Also we need to store some general country information, for example, foundation year.

In this case, the following JSON is sent to the index document:

{
	"country":"Germany",
	"foundation year":2008,
	"divisions":[
		{
			"city":"Berlin",
			"sales":200,
			"manager":{
				"first name":"Robert",
				"last name":"Simmons",
				"years in company":4
				}
		},
		{
			"city":"Munich",
			"sales":200,
			"manager":{
				"first name":"Robert",
				"last name":"Simmons",
				"years in company":4
				}
		}
	]
}

In MongoDB, you can store such documents and then query them as is, without any restrictions. However, the performance may be slow if the document contains a lot of arrays.

In Elasticsearch, we recommend using the “nested” type for complex objects before the document is indexed.

Block Join Support

There is another way to store hierarchical structures. All hierarchy items are stored as separate elements, with information about the hierarchical links stored internally. Apache Solr supports this approach.

Consider the following example. We need to store a hierarchy of divisions by country with two divisions in country. Also we need to store some general country information, for example, foundation year.

In this case, the following JSON is sent to the index document:

{ "country":"Germany", "foundationYear":2008, "_childDocuments_":[ { "city":"Berlin", "sales":200, "managerFirstName":"Robert", "managerLastName":"Simmons", "managerYearsInCompany":4 }, { "city":"Munich", "sales":200, "managerFirstName":"Robert", "managerLastName":"Simmons", "managerYearsInCompany":4 } ] }

As a result, there are three documents in the index. Information on hierarchical linking of these objects is stored internally in Solr.

{ "country":"Germany", "foundationYear":2008 }, { "city":"Berlin", "sales":200, "managerFirstName":"Robert", "managerLastName":"Simmons", "managerYearsInCompany":4 }, { "city":"Munich", "sales":200, "managerFirstName":"Robert", "managerLastName":"Simmons", "managerYearsInCompany":4 }

You must specify what fields are used in parent documents. To do this, you must select the checkbox in the Parent Field column on the Fields tab while creating or modifying the data source configuration .

Visualize Schemas and Joins

Visualize the fields and tables in schemas included with your connection, and created by other users. Add relationships as needed. Visualize join nodes in your data sources.

When you create join nodes in a source, you can visualize and update your joins as well. See Fuse Data Sources.

Edit and View Relationships in Schemas

Users with appropriate permissions can view and edit the relationships using the Relationship tab for a supported connection. Select a schema from the list in the drop-down, then make and save any additions to the relationships as needed.

View or edit a schema for a connection

1. Open a supported connection and navigate to the Relationship tab. A work area opens you can use to navigate among the tables and relationships of a selected schema.

2. Select an available schema from the drop-down list. Larger data sets may take a few moments to load.

3. View the existing relationships and any user defined relationships.

4. Optionally, draw more relationships in this work area, then Save your changes.

   To remove a user-applied join node, double-click to select the join node, then select the backspace key. The relationship is removed.

5. View or edit as many schemas as you need.

If you add joins that create one-to-many relationships here, Self-Service Analytics may return an error that prevents use of the data in a visual. For best results, when you create a one-to-many relationship with a specific left entity, any additional joins must refer to that table as the right entity. See Recommended Joins.

For Postgres connections, both tables and data relationship information are read from the connection. Other supported connections include table information but do not read relationship information. See Connector Support for Schema Visualization. No information is provided for unsupported connections.

View Relationships of a Schema in a Source

You can view the relationships present in schemas you are using in your sources that use a supported connection. The Source Creation tab of the source includes a Schema drop-down you can use to select a schema for the source, with a view button that opens the work area.

View a schema in a source

1. Create or edit a source that uses a schema.
2. Select the view icon next to your selected Schema. A schema work area opens you can use to view the tables and relationships of that schema. Larger data sets may take a few moments to load.
3. View the existing relationships and any user defined join nodes to better understand the relationships present.
4. Close the schema or join nodes modal when you’re done viewing the schema information, and repeat for other schemas if needed.

Connector Support for Schema Visualization

Support for this feature by connector is shown in the following table.

Key:Y - Supported; N - Not Supported; N/A - may not be supported.

Connector	Supported?
Amazon Redshift	Y
Amazon S3	Y
Apache Drill	N/A
Apache Phoenix	N/A
Apache Phoenix Query Server (QS)	N/A
Apache Solr	N/A
BigQuery	N/A
Cloudera Impala	Y
Cloudera Search	N/A
Couchbase	N/A
Dundas BI (Managed)	N/A
Dremio	N/A
Elasticsearch 7.0	N
Elasticsearch 8.0	N/A
File Upload (Upload API)	N/A
HDFS	Y
Hive	N/A
Jira	Y
MemSQL	Y
Microsoft SQL Server	Y
MongoDB	Y
MySQL	Y
Oracle	Y
PostgreSQL	Y
Python	N/A
Real Time Sales	Y
Salesforce	Y
SAP Hana	N/A
SAP S/4HANA	N/A
SAP IQ	N/A
Spark SQL	N/A
Snowflake	Y
Teradata	N/A
TIBCO DV	N/A
Trino	N/A
File Upload (Upload API)	N/A
Vertica	N/A