"cubes": [
{
"name": "posts_to_social",
"label": "Posts to Social Media",
"dimensions": ["site", "social_provider", "posted_at"],
"aggregates": [
{
"name": "record_count",
"function": "count"
}
],
"mappings": {
"posted_at.year": {"column":"posted_at", "extract":"year"},
"posted_at.quarter": {"column":"posted_at", "extract":"quarter"},
"posted_at.month": {"column":"posted_at", "extract":"month"},
"posted_at.week": {"column":"posted_at", "extract":"week"},
"posted_at.day": {"column":"posted_at", "extract":"day"}
},
"joins": [
{"master":"site_id", "detail":"site.site_id"}
]
}
],
"dimensions": [
{ "name": "site", "attributes": ["site_id", "name"] },
{ "name": "social_provider" },
{ "name": "posted_at",
"label": "Posted",
"template": "date",
"order": "desc"
},
{
"comment": "This is used as a template for other date dimensions.",
"name": "date",
"label": "Date",
"role": "time",
"levels": [
{
"name": "year",
"order": "desc",
"order_attribute": "year",
"label": "Year"
},
{
"name": "quarter",
"label": "Quarter",
"order_attribute": "quarter",
"order": "desc"
},
{
"name": "month",
"label": "Month",
"order_attribute": "month",
"order": "desc"
},
{
"name": "week",
"label": "Week",
"order_attribute": "week",
"order": "desc"
},
{
"name": "day",
"label": "Day",
"order_attribute": "day",
"order": "desc"
}
],
"hierarchies": [
{
"name": "weekly",
"label": "Weekly",
"levels": ["year", "week"]
},
{
"name": "monthly",
"label": "Monthly",
"levels": ["year", "quarter", "month"]
},
{
"name": "daily",
"label": "Daily",
"levels": ["year", "month", "day"]
}
]
}
]
}
however the sorting is rather 'abritrary'
sp? @Vkynluv
I think as an alternative approach you could define a view that calls the stored procedure
[Store]
url : mssql+pyodbc://Username:Password@Host:Port/DbName?driver=SQL+Server+Native+Client+11.0
url : mssql+pyodbc://Username:Password@Host:Port/DbName?driver=SQL+Server+Native+Client+11.0
You cannot call a stored proc from inside a view. It is not supported. However you can make views call other views, or table-valued user-defined functions.
how good are your AngularJS skills?
also I cannot see the order by in the SQL:
DEBUG SQL(aggregation drilldown):
SELECT EXTRACT(year FROM posts_to_social.posted_at) AS "posted_at.year", EXTRACT(quarter FROM posts_to_social.posted_at) AS "posted_at.quarter", EXTRACT(month FROM posts_to_social.posted_at) AS "posted_at.month", count(posts_to_social.id) AS record_count
FROM posts_to_social GROUP BY EXTRACT(year FROM posts_to_social.posted_at), EXTRACT(quarter FROM posts_to_social.posted_at), EXTRACT(month FROM posts_to_social.posted_at)
or the get request:
"GET /cube/posts_to_social/aggregate?drilldown=posted_at%40monthly%3Amonth HTTP/1.1"
slicer sql denormalize --config=slicer.ini
Error: config should be a ConfigParser instance, but is <class 'str'>
or
slicer sql denormalize slicer.ini
Error: config should be a ConfigParser instance, but is <class 'str'>slicer serve slicer.ini
2017-07-26 17:59:16,286 DEBUG Models root set to current directory
2017-07-26 17:59:16,286 DEBUG Workspace calendar timezone: None first week day: 0
2017-07-26 17:59:16,417 DEBUG Registered store 'default'
2017-07-26 17:59:16,417 DEBUG Loading model main
2017-07-26 17:59:16,417 DEBUG Importing model from model.json. Provider: None Store: None NS: None
...
Cubes version 1.1
@cleder on Apr 04 I asked the same question about denormalization. If you scroll back far enough on this thread, you can see the interaction I had with @Stiivi about it. His suggestions got me going. In short there is a bug in the denorm stuff and you have to edit the py code to correct for some changes that have been made. In my case I got it working but found the denorm really did almost nothing for the performance. Including better indexes in my sql model was way more effective.
Also, note that de-norm does not pre-aggregate (like the MS Analysis services product does). I had assumed that would be the effect. Instead it flattens out any joins making a single flat table of your data.
We built a django app for product warehousing. Increasingly, the requests are for reporting such as knowing the quantity of the SKU by date etc.
I have looked at cubes. I am not sure if it's overkill to have cubes and Django at the same time. I could add new features to the django app but i was wondering if there's a better way to do this than to sprinkle a bunch of update statements in order to do aggregates that are then stored in another datatable in the postgres database.
Currently, the number of reports we are asked to do is 2. It may grow to 8.
Advice?
@priti-energysavvy If you're using timezone aware timestamps, you can set timezone at connection time -- https://www.postgresql.org/docs/9.1/static/sql-set.html
SQLAlchemy doesn't seem to have a hook when connection is created, and cubes seems to use engine directly.
Is it possible to join fields from 2 tables into a single dimension? In my case I have a fact table joined to a dim table (dim_drugs), which has a column which is a FK to another lookup table (dim_drug_admin). I'd like to create a single dimension that has levels from both the dim_drugs and dim_drug_admin table such that drill downs can be performed through hierarchy information from both tables. It seems this should be possible, but I haven't seen an examples which show how this could work. Is it possible to accomplish this or is the only solution to flatten the 2 dim tables into a single one with the cross product of data from both? (assume the dim_drug and dim_drug_admin tables are join on a unique id for instance)
Is it possible for slicer to filter the results based on the value of an aggregate? In my case I'd like only to return the rows where record_count is greater than a value. I've tried expressing this in the cut and in the aggregation clause but neither seem to work. Is this even possible it must it be done only in post processing?
