I have a hierarchy setup A->B->C->D->E within Tableau. And we have a Sort Order column in the database at the lowest level of hierarchy (E). When the hierarchy is completely drilled down and the lowest level dimension is sorted using the Sort Order column, everything works fine. However, when I drill up one level (or two, three level, etc), Tableau sorts the dimensions in alphabetical order. To get around this problem, I have to sort each dimension (A, B, C, D, E) using the Sort Order column which perfectly meets my requirements.
However, since the extract has 14 million rows and we expect it to still grow in the near future, the Sorting of the visualization takes forever; every time we change a filter/parameter, Tableau spends a lot of time computing the visualization (sorting) which is a problem for business users. I have attached a screenshot of the Performance Recording that I did for the Tableau file.
Is there any better way to implement sorting within hierarchies without this huge performance lag?