These are chat archives for DataBrewery/cubes

30th
Jun 2017
Vkynluv
@Vkynluv
Jun 30 2017 08:52
Is there any possibilities to create model from a stored procedure
Christian Ledermann
@cleder
Jun 30 2017 09:33
Date sorting with CubesViewer 2.0.3-devel -
Server version: 1.1
Cubes version: 1.1
API version: 2
and a cube defined as
Vkynluv
@Vkynluv
Jun 30 2017 09:36
In my case i have to create a model with data from stored procedure. is it is possible to create model from sp?
Christian Ledermann
@cleder
Jun 30 2017 09:36
    "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"]
                }
            ]
        }
    ]
}
Vkynluv
@Vkynluv
Jun 30 2017 09:37
@cleder the mention above model data is from sp?
Christian Ledermann
@cleder
Jun 30 2017 09:37
image.png
however the sorting is rather 'abritrary'
sp? @Vkynluv
Vkynluv
@Vkynluv
Jun 30 2017 09:40
stored procedure. instead or retrieving data from tables and view. shall we fetch data from MSSql stored procedure?
Christian Ledermann
@cleder
Jun 30 2017 09:41
well it should be possible how does sqlalchemy retrieve data from a stored procedure?
I think as an alternative approach you could define a view that calls the stored procedure
Vkynluv
@Vkynluv
Jun 30 2017 09:44
Am not using sqlalchemy. am directly using
[Store]
url : mssql+pyodbc://Username:Password@Host:Port/DbName?driver=SQL+Server+Native+Client+11.0
Christian Ledermann
@cleder
Jun 30 2017 09:45
this will use sqlalchemy with the mssql driver afaik
Vkynluv
@Vkynluv
Jun 30 2017 09:50
Now i am not using prepare data file which contains query. I am directly using model.json file contains joins.
Christian Ledermann
@cleder
Jun 30 2017 09:53
I lost you here
Vkynluv
@Vkynluv
Jun 30 2017 09:53
Where?
Christian Ledermann
@cleder
Jun 30 2017 09:54
Now i am not using prepare data file which contains query. I am directly using model.json file contains joins.
Vkynluv
@Vkynluv
Jun 30 2017 09:55
Okay. any other solutions to create model from store procedure.
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.
Christian Ledermann
@cleder
Jun 30 2017 10:00
cubes (sqlalchemy) needs a table or view to operate on, so you have to materialize the results of the sp
Vkynluv
@Vkynluv
Jun 30 2017 10:00
have to materialize the results of the sp ? means
Christian Ledermann
@cleder
Jun 30 2017 10:01
create a table with the result set
how good are your AngularJS skills?
Christian Ledermann
@cleder
Jun 30 2017 10:07
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"
Christian Ledermann
@cleder
Jun 30 2017 10:42
cubes viewer is written with angularjs and d3js charts so to integrate amcharts you will have to rewrite it