Looks to me like the Query Plan Analyzer incorrectly labels some joins as "many" when they should be "one". Also, it seems to analyze the SQL constructed by Sisense, not the database's query plan. Is that correct, or am I missing something? (My goal with this post is to check my understanding of Sisense and the Query Plan Analyzer, and to point out potential issues.)
1) Incorrect "many"
See the attached "QPA-graph2.png". Looks like it's saying "Sum(Table1.Field1) Cross Join Sum(Table2.Field2) yields >1 rows". But the sums are not being sliced or grouped; it's just a sum of every row in the table. How can that possibly yield >1 rows?
2) Sisense SQL vs database query plan
See attached "QPA order of operations.png". Looks like it's saying that the plan is to:
[1] Select a row from the Table1 (Episode dim table). (1 row.)
[2] Join Table2 (Dates) to Table3 (Patient/Episode fact table) on DateID. (80mil rows.)
[3] Join the results of [1] and [2]. (1 row.)
It would be more efficient to:
[1] Select a row from the Table1 (Episode dim). (1 row.)
[2] Join [1] to Table3 (fact) on EpisodeID. (1 row.)
[3] Join [2] to Table2 (dates) on DateID. (1 row.)
That way, it doesn't need to load all the Table2 and Table3 rows. The query planner knows that it can safely rearrange the joins this way, and it has cardinality estimates so it can see which order will result in less work.
