We have following requirement.
1. we have two groups accessing the same reports (A and B).
2. When user from A view the report, he should only see the data from A. Similar for B, he should only see data from B.
3. We also provide ad-hoc query creation in production through Query Designer.
We thought of creating authorization relevant object and include it in query. But the problem is, if they dont include the authorization object in the query, then data will not be filtered and we will get data security issue. We dont have any problem with Canned queries (deliverd by developers). but while creating ad-hoc query, they might remove the authorization relevant field. Hence, this type of authorization may not work for us.
1. While loading the data split the data and load into seperate cubes. That is Cube-A and Cube-B.
2. Apply info provider level security to Cube-A and Cube-B (based on PFCG security roles)
3. Create a multiprovider on top of Cube-A and Cube-B
4. Expose this mutliprovider for ad-hoc and other quries.
But i am not sure about following things.
1. Lets say A tries to access the report created on this multiprovider.
2. As the multiprovider splits the query to Cube-A and Cube-B, will he gets authorization failed error on Cube-B? Or whether multiprovider just ignores data from Cube-B and shows only Cube-A data?
3. Or, will it ignore the authorization at Cube-B and shows all the data?
4. if this model works from authorization perspective, what are the drawbacks we have with this approach.
Please let me know your thoughts on this design and also suggest if you have any other solution.