You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@superset.apache.org by "zhaoyongjie (via GitHub)" <gi...@apache.org> on 2024/03/17 14:56:08 UTC
[I] [SIP-WIP] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension in all visualization [superset]
zhaoyongjie opened a new issue, #27544:
URL: https://github.com/apache/superset/issues/27544
### Background
The _period-over-period analysis_ is a BI technique that compares metrics from a recent period to the same period in the past. We already have this feature in Advanced Analytics and apply it to time-series analytic visualization. This feature utilizes `time_offsets` and `post_processing` in the `query_object` to achieve _period-over-period analysis_. Practically, this feature, along with the entire Advanced Analytics section, should have supported all visualizations because the legacy Time Section has been removed and the Time Filter has already moved into Adhoc Filter.
As an experiment, as long as the time dimension is provided and corresponding time filter populate in Adhoc Filter, any charts should easily incorporate this feature. I've opened a toy pull request to demonstrate that only 5 frontend lines (excluding import statements) are needed to integrate this feature into the current Table chart.
### Motivation
Currently, there are some limitations in time_offsets and Advanced Analytics:
1. Theoretically, Period-over-period analysis is a method of analyzing different data slices base on specific dimensions within the same dataset. Therefore, the comparisons should not only be based on temporal dimensions, but also on textual dimensions.
2. According to the first perspective, flexable filters should be provided when users compare data slices, not just time filters.
3. By default, time_offset uses the first occurrence of a time column in `query_object.columns` as the basis for time shifted. It cannot specify which time column to use for generating shifted time range because there can be multiple time filters in current Adhoc Filter.
4. The actual shifted time-range values cannot be displayed on the UI and there isn't an approach to provide it.
Based on the above analysis, I propose to enhance/refactoring the `time_offsets` field to achieve a more generalized `period-over-period analysis`.
### Proposed Changes
To illustrate what needs to change, I will construct a time comparison using the current `time_offsets` field and the `cleaned_sales_data` dataset. This `query_object` will generate a time comparison based on the `order_date`, comparing data slices from `1 week ago`, `1 month ago`, and `1 year ago` :
```json
{
"columns": [{
"timeGrain": "P1D",
"columnType": "BASE_AXIS",
"sqlExpression": "order_date",
"label": "order_date",
"expressionType": "SQL"
}],
"metrics": ["count"],
"filters": [
{
"col": "order_date",
"op": "TEMPORAL_RANGE",
"val": "2004-01-01 : 2004-01-31"
},
{
"col": "deal_size",
"op": "==",
"val": "Small"
}
],
"time_offsets": [
"1 week ago",
"1 month ago",
"1 year ago"
],
}
```
After the change, the `time_offsets` will replace with `comparison` field:
```json
{
"columns": [{
"timeGrain": "P1D",
"columnType": "BASE_AXIS",
"sqlExpression": "order_date",
"label": "order_date",
"expressionType": "SQL"
}],
"metrics": ["count"],
"filters": [
{
"col": "order_date",
"op": "TEMPORAL_RANGE",
"val": "2004-01-01 : 2004-01-31"
},
{
"col": "deal_size",
"op": "==",
"val": "Small"
}
],
"comparisons": {
"on": ["order_date"],
"how": "left",
"suffix": ["1 week ago", "1 month ago", "1 year ago"],
"time_filter_offsets": [
"1 week ago",
"1 month ago",
"1 year ago"
],
"filters": [
[
{
"col": "deal_size",
"op": "==",
"val": "Small"
}
],
[
{
"col": "deal_size",
"op": "==",
"val": "Small"
}
],
[
{
"col": "deal_size",
"op": "==",
"val": "Small"
}
],
],
},
}
```
_on_ : specifies which columns will be used to join the data slices.
_how_ : determines how the data slices will be joined, providing options "inner" and "left," with the default value being "left"
_suffix_ : defines suffixes for compared metrics
_time_filter_offsets_ : specifies the time offset delta if performing a time comparison; if performing a textual dimension comparison, it should be ignored
_filters_ : indicates the filters applied on the specific data slice
The following `query_object` payload demostrate how to generate a textaul dimension comparsion
```json
{
"columns": [{
"timeGrain": "P1D",
"columnType": "BASE_AXIS",
"sqlExpression": "order_date",
"label": "order_date",
"expressionType": "SQL"
}],
"metrics": ["count"],
"filters": [
{
"col": "order_date",
"op": "TEMPORAL_RANGE",
"val": "2004-01-01 : 2004-01-31"
},
{
"col": "deal_size",
"op": "==",
"val": "Small"
}
],
"comparisons": {
"on": ["deal_size"],
"how": "left",
"suffix": ["Medium", "Large"],
"filters": [
[
{
"col": "order_date",
"op": "TEMPORAL_RANGE",
"val": "2004-01-01 : 2004-01-31"
},
{
"col": "deal_size",
"op": "==",
"val": "Medium"
}
],
[
{
"col": "order_date",
"op": "TEMPORAL_RANGE",
"val": "2004-01-01 : 2004-01-31"
},
{
"col": "deal_size",
"op": "==",
"val": "Large"
}
],
],
},
}
```
### New or Changed Public Interfaces
### New dependencies
No new dependencies
### Migration Plan and Compatibility
Provide DB migration script
### Rejected Alternatives
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-121] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension [superset]
Posted by "betodealmeida (via GitHub)" <gi...@apache.org>.
betodealmeida commented on issue #27544:
URL: https://github.com/apache/superset/issues/27544#issuecomment-2030214425
> However, I am stuck on how to generate an appropriate time expression that contains time delta, as the time delta expression varies in different databases.
@zhaoyongjie can you give more context on why you'd need to compute a time delta?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-121] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie closed issue #27544: [SIP-121] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension
URL: https://github.com/apache/superset/issues/27544
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-121] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27544:
URL: https://github.com/apache/superset/issues/27544#issuecomment-2021608401
> Do you have an example of what the resulting sql query would look like from this api schema?
The SQL will be the same as the time comparison in the previous AA panel, consisting of multiple separate SQL queries. I have also been thinking about how to design a JOIN QueryObject to achieve this use case and replace the entire Pandas Join. However, I am stuck on how to generate an appropriate time expression that contains time delta, as the time delta expression varies in different databases.
In practice, the JOIN in Superset has been discussed many times in the community, but the original technical design doesn't seem to have considered this use case. The data modeling in Superset depends on SQLLab exposing a "virtual dataset" to achieve it.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-121] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension [superset]
Posted by "Antonio-RiveroMartnez (via GitHub)" <gi...@apache.org>.
Antonio-RiveroMartnez commented on issue #27544:
URL: https://github.com/apache/superset/issues/27544#issuecomment-2018331397
Hey @zhaoyongjie Thanks for putting this together, it looks really promising. On top of the question from @eschutho I would like to know if we should consider different databases capabilities like CTE and JOINS support when comparing and having a fallback as part of this new processing? Just to handle as mentioned before, cases where some of those are not supported.
Also, how datasets that use for example Jinja templates would be compared and processed with the Joins and the offsets? Should we consider cases where we simply don't support comparison and offer the aforementioned fallback methods? or simply ignore them.
Thanks.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-121] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27544:
URL: https://github.com/apache/superset/issues/27544#issuecomment-2018390608
> Hey @zhaoyongjie Thanks for putting this together, it looks really promising. On top of the question from @eschutho I would like to know if we should consider different databases capabilities like CTE and JOINS support when comparing and having a fallback as part of this new processing? Just to handle as mentioned before, there are some cases where some of those are not supported.
>
> Also, how datasets that use for example Jinja templates would be compared and processed with the Joins and the offsets? Should we consider cases where we simply don't support comparison and offer the aforementioned fallback methods? or simply ignore them.
>
> Thanks.
I'll reply the topic tonight, thanks for the mention.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-WIP] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension [superset]
Posted by "eschutho (via GitHub)" <gi...@apache.org>.
eschutho commented on issue #27544:
URL: https://github.com/apache/superset/issues/27544#issuecomment-2014036498
@zhaoyongjie this looks great, and I think it should solve what we're trying to build. Do you know if any UI changes will be needed in order to specify the `comparisons.on` values? And will that value always be a single element, or could it be multiple? I assume the `comparisons.on` values would come from the `dimensions` field, is that right?
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-121] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension [superset]
Posted by "zhaoyongjie (via GitHub)" <gi...@apache.org>.
zhaoyongjie commented on issue #27544:
URL: https://github.com/apache/superset/issues/27544#issuecomment-2030284790
@betodealmeida
If the different data slices would be filtered in datasource, we have to filter the `time filter` + `time delta` in the datasource. different database have different time delta expression.
<img width="1206" alt="image" src="https://github.com/apache/superset/assets/2016594/5dfde577-0c71-4712-80c9-01ffc7993f5f">
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org
Re: [I] [SIP-121] Proposal for Metrics Comparison base on the Temporal Dimension or/and Textual Dimension [superset]
Posted by "cannmenus-vib (via GitHub)" <gi...@apache.org>.
cannmenus-vib commented on issue #27544:
URL: https://github.com/apache/superset/issues/27544#issuecomment-2075147490
This is incredibly useful and something I'm surprised isn't built in to superset's tables already. Thanks for putting this together!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: notifications-unsubscribe@superset.apache.org
For additional commands, e-mail: notifications-help@superset.apache.org