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:
These capabilities are implemented as special functions of expression-type cube members.
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:
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).
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:
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: