Creating a date dimension from a dates column - comes out not sorted.

Skip to first unread message

Boris Tseytlin

May 25, 2017, 10:06:02 AM5/25/17
to Cubes
I have a table "fires" which includes a date column "date". I want to get a fires per day/month/year cube, so that the chart is a time series.
I realised I need one dimension: time. And one aggregate: record count of fires.
I followed examples and came to this model:

"name": "Firedata",
{"name": "ao"},
"hierarchies": [
"label": "Daily",
"levels": [
"name": "daily"
"info": {
"cv-datefilter": true,
"cv-datefilter-hierarchy": "daily"
"levels": [
"attributes": [ "year" ],
"key": "year",
"name": "year",
"label": "year",
"label_attribute": "year",
"role": "year"
"attributes": [ "month" ],
"key": "month",
"name": "month",
"label": "month",
"label_attribute": "month",
"role": "month"
"attributes": [ "day" ],
"key": "day",
"label_attribute": "day",
"label": "day",
"name": "day",
"role": "day"
"label": "Date",
"role": "time",

"cubes": [
"key": "fire_id",
"dimensions": ["date", "ao"],
"aggregates": [
"function": "count",
"label": "Fire count",
"name": "record_count"
"name": "firedata_fire"
"mappings": {
"date.year": {"column":"date", "extract":"year"},
"date.month": {"column":"date", "extract":"month"},
"": {"column":"date", "extract":"day"}

The problem is that the dates don't appear sorted.

Also I see that when I aggregate "Fires count per day" there is no count for days on which there were no fires (so no fire records with matching dates).

My question: is the only way to get what I want, a time series of fires on each day, is to use a table of dates? If not, how do I go about this?

Reply all
Reply to author
0 new messages