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 by dimension's values like SQL join or vlookup in spreadsheets.
• Resolve external measure to combine values from 2 cubes that have similar dimensions.
• Use DuckDB connector^{on-prem version only} to query multiple data sources in SQL. This approach is recommended for complex/advanced cases when near real-time reports should display efficiently combined data. Note that with SeekTable's custom SQL function cube_query DuckDB can load data from any data source (cube) supported by SeekTable.

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

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.