In this post we'll see how to create a general date dimension for our entire Elasticube. This way, we'll be able to use the same date field for queries run over all our separated tables.
That is, we'll be able to use this generic date field in the rows or axes section of a widget.
Lets say we have two tables in the Elasticube with date fields which we'd like to create a date dimension for, tbl_a and tbl_b.
We'll create a custom table in the Elasticube, name it Dim_Date, and we'll pull all the distinct date values into this table. In this custom tables expression we'll place the following statement:
SELECT * FROM (
SELECT Distinct tbl_a.dates FROM tbl_a
SELECT Distinct tbl_b.dates FROM tbl_b
We'll now get a table named 'Dim_Date' with one column named 'Date' of a date-time data type, with all the distinct values in the date columns of the two fields.
You will then be able to merge the date fields from the two tables, tbl_a and tbl_b to the date field in the new custom table we created and use the date fields from this custom table for queries.
Please note that one can add additional date fields from additional tables in the Elasticube, by adding an additional row into the outer encapsulating SELECT statement,
for instance, if we had an additoinal table; tbl_c we'll add the following line into the parenthesis: SELECT tbl_c.dates FROM tbl_c
Don't forget to add an additional UNION before this line.