Python Cubes Olap Framework Date PointCut
So I am trying to implement some BI with Python Cubes Framework and I am running into some problems.
Basically I am trying to do a "simple" PointCut / slice and dice and have no luck with it. I am using PostgreSQL database with PostGis.
My model.json
:
{
"dimensions": [
{"name": "user", "attributes": ["id", "username"]},
{"name": "resources", "attributes": ["id", "resource_simple_name"]},
{"name":"created_on", "role": "time"}
],
"cubes": [
{
"name": "users_resources_likes",
"dimensions": ["user", "resources", "created_on"],
"mappings": {
"user.id": "auth_user.id",
"user.username": "auth_user.username",
"resources.id": "resources.id",
"resources.resource_simple_name": "resources.resource_simple_name",
"created_on": "created_on"
},
"joins": [
{
"master": "user_id",
"detail": "auth_user.id"
},
{
"master": "resource_id",
"detail": "resources.id"
}
]
}
]
}
if i try to do poincut with date on slicer
aggregate?drilldown=created_on&cut=created_on:2012
i get a DataError: (DataError) invalid input syntax for type timestamp with time zone: "2012"
After a while, I read that it might be because my postgresql database is timestamped:
created_on timestamp with time zone NOT NULL DEFAULT '2014-02-10 00:00:00+00'::timestamp with time zone,
CO, I tried to do:
?drilldown=created_on&cut=created_on:2012-09-15T09:37:59+00:00
and I get:
{
error: "unknown_user_error",
message: "Wrong dimension cut string: 'created_on:2012-09-15T09:37:59 00:00'"
}
What am I doing wrong? the problem is mine model.json
?
source to share
I found that adding more information to the date in the model solves the problem:
{
"name": "created_on",
"label": "Date Created",
"role": "time",
"info": {
"cv-datefilter": true,
"cv-datefilter-hierarchy": "weekly"
},
"levels": [
{
"name":"year",
"label":"Year",
"info": { "cv-datefilter-field": "year" }
},
{
"name":"quarter",
"label":"Quarter"
},
{
"name":"month",
"label":"Month"
},
{
"name":"week",
"label":"Week",
"info": { "cv-datefilter-field": "week" }
}
],
"hierarchies": [
{
"name": "weekly",
"label": "Weekly",
"levels": [ "year", "week"]
},
{
"name": "monthly",
"label": "Monthly",
"levels": [ "year", "quarter", "month"]
}
]
}
and display:
"mappings": {
"created_on.year": {"column":"created_on", "extract":"year"},
"created_on.month": {"column":"created_on", "extract":"month"},
"created_on.week": {"column":"created_on", "extract":"week"},
"created_on.day": {"column":"created_on", "extract":"day"},
"created_on.quarter": {"column":"created_on", "extract":"quarter"}
}
source to share