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 similar to JOIN in SQL or VLOOKUP in spreadsheets.
• Resolve external measure to combine values from 2 cubes that have similar dimensions.
• Use DuckDB connector 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 a calculated dimension and use this function:

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

where:

cubeId

an identifier of the 'lookup' cube. You can find this ID in the cube's URL.

value

dimension's value variable that need to be resolved via lookup. This dimension should be specified as an argument of the expression.

keyDimension

a name of dimension in the 'lookup' cube that is used as a primary key.

lookupDimension

a name of dimension in the 'lookup' cube that should be returned as a result of lookup.

Configuration example:

• Let's assume that we have 2 cubes
  • Orders that has "order_user_id" dimension (this is a foreign key that needs to be resolved via lookup)
  • Users (cube ID=123abc) that has "user_id" (this is a PK) and "user_name" dimensions.
• In Orders reports we want to show "user_name" values instead of "order_user_id" identifiers.
• For this purpose a new calculated dimension "order_user_name_resolved" can be added into the Orders cube configuration:
  

Typical usage scenarious:

• resolve dimension attributes 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").

Technical limitations:

• Lookup dictionary data is populated via non-aggregate ("flat table") query to the 'lookup' cube. If duplicates exist, the last returned row for that key will be used.
• Max number of unique keys is 100,000. This limit can be increased in self-hosted SeekTable.

Combine measures

To combine measures that are present in different cubes you can add a calculated measure and use this function:

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

where:

cubeId

an identifier of the target cube that contains a measure you want to merge. You can find this ID in the cube's URL.

measureName

a name of the measure in the target cube.

dimMappingDictionary

a mapping between dimension names of source cube and target cube (if they don't match), for example:
{"timestamp_year": "date_year", "timestamp_month", "date_month"}.

This capability allows you to define formulas that involve metrics from different data sources. For example, the report that displays actual sales (or, say, profit per period) can be extended with 'estimated' values that come from the uploaded CSV and then the difference can be calculated.

Technical limitations:

• Target cube (that contains a measure to be merged) must have all dimensions that are used in the pivot table report. If names of the dimensions are different this can be handled by the mapping definition. If target cube misses a dimension that is used in the report, you'll get an error.
• Target cube is queried as for 'pivot table' report type; aggregation results are cached for the short time.