Situation: The organization has three regions. The East Region has sales of $100, the West has sales of $200, and the South has sales of $300. If I create an analysis containing all three regions, include a rank of their sales, and run the analysis as a user with access to all regions, I see South ranked #1, West #2, and East #3.
But consider that each regional manager can see only their own data (row-level security implemented in the RPD).
Problem: When any regional manager runs the analysis, he sees a rank of 1 for his region's sales. I want that manager to see his region's "correct" ranking value. Because of the row level security, there is only one region available to be ranked when the query runs, therefore each region sees a rank of 1. I want the East regional manager to see a '3' in his rank column, and West to see '2'. I want the managers to see their "correct" ranking value.
I am open to ANY method, no matter how complex.
I think your only option would be to precompute your ranks and store them, using your ETL, or use sql in an opaque view to create the equivalent, naturally the former performs better than the latter.