Combine several data sources in one report

In some cases you may want to create a report that uses data from several sources. If these datasets cannot be merged on data source level for some reason (with data integration techniques) you still can do that with these built-in SeekTable capabilities:

• Resolve external lookups for dimensions like SQL join or vlookup in spreadsheets.
• Resolve external measure value to combine measures from 2 pivot tables with similar dimensions.

These capabilities are implemented as special functions of expression-type cube members.

In addition to that, it is possible to query multiple databases and combine results in one report using DuckDB engine capabilities (available only in on-prem SeekTable). The following databases/data sources can be attached and processed by DuckDB: MySql, PostgreSql, S3 (parquet, iceberg), BigQuery.

External lookup

To resolve external lookup from another cube create an expression-type dimension and use this function:

Cube("cubeId").Lookup(value,"keyDimension","lookupDimension")

where cubeId is an identifier of the cube you want to query (you can get this ID from URL of the cube view), value is a 'foreign key' that need to be resolved, keyDimension is a name of dimension in the cube that matches 'foreign key', lookupDimension is a name of dimension that contains a value to resolve.

Typical usage scenarious:

• resolve dimension attribute by ID. For example, if you have company_id in the main dataset you can resolve company name or location (country, city etc) or other company's properties that are stored in another database (or maybe even in CSV file).
• define custom mapping for dimension values. For example, for month dimension (that is usually has values in range 1-12) you can define own labels like "1-Jan", "2-Feb", ..., "12-Dec" by uploading simple CSV with 2 columns ("month_number", "month_title").

Notes:

• Cube that is used to resolve lookups is queried as for 'flat table' report type. In case of duplicates last returned row for concrete key value will be used.
• Max number of unique keys is 100,000. This limit can be increased in self-hosted version of SeekTable. Reporting engine creates in-memory hashmap for (key,value) and server should have enough RAM to keep it.

Combine measures

To combine measures from different cubes create expression-type measure and use this function:

Cube("cubeId").Measure("measureName", dimMappingDictionary)

where cubeId is an identifier of the cube you want to query (you can get this ID from URL of the cube view), measureName is a name of measure you want to get (measure names are present on cube configuration form), with dimMappingDictionary you can define a mapping between dimensions of source cube and target cube if they don't match, for example:
new dictionary{ {"timestamp_year", "date_year"}, {"timestamp_month", "date_month"} }.

With this capability you can define formulas that involve metrics from different data sources. For example, for report that displays actual sales or profit per period you can define 'planned' values with CSV and then calculate absolute or relative difference.

Notes:

• Cube that is used to resolve external measure should have all dimensions that are used in the pivot table report. If names of the dimensions are different you need to define the mapping. If target cube doesn't have some dimension you'll get an error.
• Target cube is queried as for 'pivot table' report type; aggregation results are cached for the short time.