Report with wrong query_payload on meltano.meltanodata.com
While checking meltano.meltanodata.com, I found out that the Meltano Web Traffic
dashboard was not loading due to errors:
https://meltano.meltanodata.com/dashboard/meltano-web-traffic
After investigating the issue, I found out that the origin of the problem was in the Google Analytics > Google Analytics Locations > Audience Stats by Location - High to Low Total User Count
report:
By checking the Meltano UI logs in meltano.meltanodata.com, I can see the following error is generated while trying to load the report:
... ... ...
File "/var/meltano/.venv/lib/python3.6/site-packages/meltano/api/controllers/dashboards.py", line 64, in get_dashboard_reports_with_query_results
post_data
File "/var/meltano/.venv/lib/python3.6/site-packages/meltano/api/controllers/dashboards_helper.py", line 42, in get_dashboard_reports_with_query_results
sql_dict = sqlHelper.get_sql(design, report["query_payload"])
File "/var/meltano/.venv/lib/python3.6/site-packages/meltano/core/sql/sql_utils.py", line 8, in get_sql
definition=incoming_json, design_helper=design, schema=schema
File "/var/meltano/.venv/lib/python3.6/site-packages/meltano/core/sql/base.py", line 686, in __init__
self.parse_definition(definition)
File "/var/meltano/.venv/lib/python3.6/site-packages/meltano/core/sql/base.py", line 776, in parse_definition
table_def = self.design.find_table(related_def["name"])
File "/var/meltano/.venv/lib/python3.6/site-packages/meltano/core/sql/base.py", line 98, in find_table
raise ParseError(f"{name} not found in design {self.name}")
meltano.core.sql.base.ParseError: google_analytics_website_overview not found in design google_analytics_locations
That's very strange, cause there is no google_analytics_website_overview
defined in the google_analytics_locations
design (which only uses the google_analytics_locations
table) or the table for that design:
https://gitlab.com/meltano/model-google-analytics/blob/master/models/google_analytics.topic.m5o
The google_analytics_website_overview
table is only referenced in google_analytics_website_overview.table.m5o
and its respective design which also uses only that table.
So there should be no cross reference to that table from reports generated for Google Analytics > Google Analytics Locations
So, I went and checked this report:
analyze/reports/audience-stats-by-location-high-to-low-total-user-count.report.m5o
{"chart_type": "LineChart", "created_at": 1575508419.6846542, "design": "google_analytics_locations", "filters": {"aggregates": [], "columns": []}, "id": "F53GC4RPNVSWY5DBNZXS64DSN5VGKY3UF5QW4YLMPF5GKL3SMVYG64TUOMXWC5LENFSW4Y3FFVZXIYLUOMWWE6JNNRXWGYLUNFXW4LLINFTWQLLUN4WWY33XFV2G65DBNQWXK43FOIWWG33VNZ2C44TFOBXXE5BONU2W6===", "model": "google_analytics", "name": "Audience Stats by Location - High to Low Total User Count", "namespace": "model-google-analytics", "order": {"assigned": [{"attribute": {"description": "Total Users", "label": "Total Users", "name": "total_users", "selected": true, "source_label": "Location Stats", "source_name": "google_analytics_locations", "sql": "{{table}}.users", "type": "sum"}, "direction": "desc"}], "unassigned": [{"attribute": {"description": "Full Date", "hidden": false, "label": "Date", "name": "report_date", "primary_key": true, "selected": true, "source_label": "Location Stats", "source_name": "google_analytics_locations", "sql": "{{table}}.report_date", "type": "string"}, "direction": "asc"}, {"attribute": {"description": "City", "hidden": false, "label": "City", "name": "city", "primary_key": true, "selected": true, "source_label": "Location Stats", "source_name": "google_analytics_locations", "sql": "{{table}}.city", "type": "string"}, "direction": "asc"}]}, "path": "/var/meltano/project/analyze/reports/audience-stats-by-location-high-to-low-total-user-count.report.m5o", "query_payload": {"name": "google_analytics_website_overview", "columns": ["report_date"], "aggregates": ["total_users"], "timeframes": [], "joins": [], "order": [{"direction": "desc", "source_name": "google_analytics_locations", "attribute_name": "total_users"}], "limit": 50, "loader": "target-postgres", "filters": {"aggregates": [], "columns": []}}, "slug": "audience-stats-by-location-high-to-low-total-user-count", "version": "1.0.0"}
formatted version of the file for easy reading
{
"chart_type":"LineChart",
"created_at":1575508419.6846542,
"design":"google_analytics_locations",
"filters":{
"aggregates":[
],
"columns":[
]
},
"id":"F53GC4RPNVSWY5DBNZXS64DSN5VGKY3UF5QW4YLMPF5GKL3SMVYG64TUOMXWC5LENFSW4Y3FFVZXIYLUOMWWE6JNNRXWGYLUNFXW4LLINFTWQLLUN4WWY33XFV2G65DBNQWXK43FOIWWG33VNZ2C44TFOBXXE5BONU2W6===",
"model":"google_analytics",
"name":"Audience Stats by Location - High to Low Total User Count",
"namespace":"model-google-analytics",
"order":{
"assigned":[
{
"attribute":{
"description":"Total Users",
"label":"Total Users",
"name":"total_users",
"selected":true,
"source_label":"Location Stats",
"source_name":"google_analytics_locations",
"sql":"{{table}}.users",
"type":"sum"
},
"direction":"desc"
}
],
"unassigned":[
{
"attribute":{
"description":"Full Date",
"hidden":false,
"label":"Date",
"name":"report_date",
"primary_key":true,
"selected":true,
"source_label":"Location Stats",
"source_name":"google_analytics_locations",
"sql":"{{table}}.report_date",
"type":"string"
},
"direction":"asc"
},
{
"attribute":{
"description":"City",
"hidden":false,
"label":"City",
"name":"city",
"primary_key":true,
"selected":true,
"source_label":"Location Stats",
"source_name":"google_analytics_locations",
"sql":"{{table}}.city",
"type":"string"
},
"direction":"asc"
}
]
},
"path":"/var/meltano/project/analyze/reports/audience-stats-by-location-high-to-low-total-user-count.report.m5o",
"query_payload":{
"name":"google_analytics_website_overview",
"columns":[
"report_date"
],
"aggregates":[
"total_users"
],
"timeframes":[
],
"joins":[
],
"order":[
{
"direction":"desc",
"source_name":"google_analytics_locations",
"attribute_name":"total_users"
}
],
"limit":50,
"loader":"target-postgres",
"filters":{
"aggregates":[
],
"columns":[
]
}
},
"slug":"audience-stats-by-location-high-to-low-total-user-count",
"version":"1.0.0"
}
As you can see, google_analytics_website_overview
is referenced in this report for query_payload.name
while it should have been google_analytics_locations
(at least that's how other reports behave)
We should investigate why and how this report was generated and also fix the issue on meltano.meltanodata.com