jjmontesl / cubesviewer

Explore and visualize analytical datasets
http://www.cubesviewer.com
Other
441 stars 112 forks source link

Date sorting: inconsistent behaviour with mutiple hierarchies #71

Closed fpieront closed 5 years ago

fpieront commented 8 years ago

version: cubesviewer-server 2.0.1

date model:

"dimensions": [
          {
              "name": "d_date",
              "label": "Date",
              "role": "time",
          "cv-datefilter": "true",
              "levels": [
                      {

                          "name": "year",
                          "label": "Year",
                          "attributes": [{"name": "year", "label": "Year"}],
                          "role": "year",
              "order_attribute": "year"
                      },
                      {
                          "name": "quarter",
                          "label": "Quarter",
                          "attributes": [{"name": "quarter", "label": "Quarter"}],
                          "role": "quarter",
              "order_attribute": "quarter"
                      },
                      {   "name": "month",
                          "label": "Month",
                          "attributes": [{"name": "month", "label": "Month"}],
                          "role": "month",
              "order_attribute": "month"
                      },
                      {
                          "name": "week",
                          "label": "Week",
                          "attributes": [{"name": "week", "label": "Week"}],
                          "role": "week",
              "order_attribute": "week"
                      },
                      {
                        "name": "date",
                        "label": "Date",
                        "attributes": [{"name": "date", "label": "Date"}],
                        "role": "day",
            "order_attribute": "date"
                      }

                    ],
              "hierarchies": [
                                  {
                                      "name": "default",
                                      "label": "default",
                                      "levels": ["year", "month", "date"]
                                  },
                  {
                      "name": "weekly",
                      "label": "by week",
                      "levels": ["year", "week"]
                  }
                         ]
          }
"joins": [
            {
              "master": "test_cube.dim_date_date_id",
              "detail": {
                "table": "d_date_test",
                "column": "date"
                    },
              "method": "match"
            }
 "mappings": {
                          "d_date.year": "d_date_test.year",
                          "d_date.quarter": "d_date_test.quarter",
                          "d_date.month": "d_date_test.month",
                          "d_date.week": "d_date_test.week",
                          "d_date.date": "d_date_test.day"
                         }

PostgresSQL Backend - Database schema (date dimension table):

   Table "cubes.d_date_test"
  Column  |  Type   | Modifiers 
----------+---------+-----------
 year     | integer | 
 month    | integer | 
 week     | integer | 
 day      | integer | 
 week_day | integer | 
 date     | date    | 
 quarter  | integer | 

Excerpt from date dimension table:

 year | month | week | day | week_day |    date    | quarter 
------+-------+------+-----+----------+------------+---------
 2016 |     1 |    1 |   4 |        1 | 2016-01-04 |       1
 2016 |     1 |    1 |   5 |        2 | 2016-01-05 |       1
 2016 |     1 |    1 |   6 |        3 | 2016-01-06 |       1
 2016 |     1 |    1 |   7 |        4 | 2016-01-07 |       1
 2016 |     1 |    1 |   8 |        5 | 2016-01-08 |       1

The "dim_date_date_id" is of type date and contains values such as '2016-01-01'.

With one date hierarchy. date filtering and sorting works entirely as expected (using date hierarchy "default")

cubes1

When I use two, hierarchies, however, date sorting only works correctly for the hierarchy which is used for filtering first during the session! Example: (now using date hierarchy "by week")

cubes2

"Ascending" order is used, but weeks are not ordered correctly.

If I drill down to "Week" using the date hierarchy "by week" first, date ordering works correctly for this hierarchy, but not for the other one.

jjmontesl commented 8 years ago

A note: The "cv-datefilter": "true" attribute is deprecated, you don't need it (use "role": "time", which you already added). Even if, it's incorrectly placed, cv-* attributes must be inside an "info": {} block.

This is not related to the issue.

jjmontesl commented 8 years ago

Woah, nice catch. This one is tricky.

jjmontesl commented 8 years ago

Thank you very much for the detailed bug report. Very much helpful.

Problem is from upstream ui-grid: https://github.com/angular-ui/ui-grid/issues/4683 , so I reported it and worked around it.

fpieront commented 8 years ago

Woah, nice catch. This one is tricky.

My users will find every bug ;) Works as expected for me so far, thanks for the fix!

jjmontesl commented 8 years ago

Reopened: fix to grid sort function cache was incomplete. If two grids are loaded, clicking on a column in grid A could affect sorting in grid B.

jjmontesl commented 5 years ago

Closing as this is fixed in devel and is now superseded by #95 . This fix will be included in the next release.