cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Wrong X-Axis Order when Visualization is Put on Dashboard

Wout
Contributor

I have a visualization in which the X-axis values are displayed correctly in the Query Editor, in the order produced by the SQL query. However, when I add the visualization to a dashboard, the values are suddenly not sorted anymore.

How is this possible? Is this is bug in the Databricks UI? How do I fix the problem?

Correct (visualization in query editor):correct 

Wrong (visualization on dashboard):

wrong 

I think this problem might be similar to https://community.databricks.com/s/question/0D58Y00009AICBTSA5/order-by-disabled-in-sql-dashboard; unfortunately, that question does not have an answer.

1 ACCEPTED SOLUTION

Accepted Solutions

Wout
Contributor

We have further analyzed the visualization problem and found two solutions.

The original visualization consists of 1 series and has aggregation enabled in the UI (but is unused, since the query itself aggregates already).

We found that the following two changes/workarounds solved the problem:

  • Adding a 2nd series that visualizes a constant that uses a window function that repeats the desired ordering: "max(0) OVER (ORDER BY binSortUnit, binSortValue ASC) as constantWindowFunction"
  • Disabling aggregation in the UI: in the visualization edit window, on the "General" tab, click on the three vertical dots next to "Y columns" and click on "Disable aggregations"

The second solution is the easier one, obviously.

disable-aggregations

View solution in original post

6 REPLIES 6

Debayan
Esteemed Contributor III
Esteemed Contributor III

Hi @Wout Maaskant​ , Could you please confirm what is the expectation regarding dashboard visualisation?

If I compare one instance, lets say "1 hour(s)" then I see both the graphs are saying the same and the position is same (it is spiked), where as in 6 and 7 hours its the second highest spike.

By default, dashboards are sorted in reverse chronological order. You can reorder the list by clicking the column headings. But in the SQL dashboard I do not see it is in order. Is it what you are asking for?

(Ref: https://docs.databricks.com/sql/user/dashboards/index.html#view-and-organize-dashboards)

Hi @Debayan Mukherjee​,

Thanks for your reply.

You are correct in that the values displayed in the graph are the same. (E.g. the spike at "1 hour(s)" has the same value on the y-axis.) I expect the x-axis values to be sorted in the order the SQL query returns them. When I view the visualization in the Query Editor, the x-axis values are sorted correctly, but when I view the same visualization on the dashboard, the x-axis values are sorted incorrectly.

> By default, dashboards are sorted in reverse chronological order. You can reorder the list by clicking the column headings.

I do not believe this quote is not relevant, as it talks about the order of dashboards in the dashboard list.

Wout
Contributor

@Debayan Mukherjee​ @Lindsay Olson​ I believe this is a bug in Databricks SQL. Could you please pass this on to second-level/next-level support?

Anonymous
Not applicable

Absolutely, @Wout Maaskant​ ! Thanks for commenting - the more voices, the better!

Wout
Contributor

It was by going through Databricks Support that we got an answer to this question. I am reposting it here in case others run into the same issue:

---

I researched and found that this is expected behaviour with sorting when added to the dashboard.

Unfortunately, "ORDER BY" is not supported in backend aggregation.

This issue is being caused by new aggregation visualization. In the dashboard, backend aggregation is always used but backend aggregation doesn’t inherit the order by clause specified by the user. The workaround is to use X-axis sort in the visualization editor so the X-axis values are always sorted in the front end.

Although, We have a product feature ticket created to support it. there is no ETA at this moment.

Wout
Contributor

We have further analyzed the visualization problem and found two solutions.

The original visualization consists of 1 series and has aggregation enabled in the UI (but is unused, since the query itself aggregates already).

We found that the following two changes/workarounds solved the problem:

  • Adding a 2nd series that visualizes a constant that uses a window function that repeats the desired ordering: "max(0) OVER (ORDER BY binSortUnit, binSortValue ASC) as constantWindowFunction"
  • Disabling aggregation in the UI: in the visualization edit window, on the "General" tab, click on the three vertical dots next to "Y columns" and click on "Disable aggregations"

The second solution is the easier one, obviously.

disable-aggregations

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!