I realize I might be asked for a code sample considering the complexity, but I'll start with the following. I have a scenario where I have Providers (provider_key) and Groups (group_key), where providers belong to groups.
(1) Row reduction (not column) is based on both keys, that is, a provider can only see his provider name and info and measures associated to him. In some cases, certain providers are set up to see ALL providers in their group (they might supervisors or lead, let's say).
(2) My existing Fact table is at a grain where values exists for both of these keys. Up to this point, I have security and row reduction working the way I want it to. Now I've been asked to add another fact table but it only has values for group_key, the less granular grain.
(3) Pretend MEASURE-1 is at the provider AND group grain, and MEASURE-2 is at the group grain only.
The 2 ways I've tried to implement this have me in a pickle, so to speak....
--> If I use the value <ANY> for the provider_key in the fact table for the rows with MEASURE-2, and add a single row in the provider dimension to represent the <ANY> value (where all other attributes of the dimension are null) to tie to those fact table rows, row reduction works when I log in as various users (good), however if you select a provider attribute, then any chart with both MEASURE-1 and MEASURE-2 changes to only show MEASURE-1 (bad).
-->If I use the value <ANY> for the provider_key in the fact table for the rows with MEASURE-2, and add a duplicate set of provider dimension rows where the provider_key value is replaced with <ANY> to tie to the applicable fact table rows, row reduction does not work when I log in as various users (bad), however if you select a provider attribute, then any chart with both MEASURE-1 and MEASURE-2 remains unchanged (good).
I would love to hear if anyone has had to deal with this and if there's a way to make it happen. At this point I'm thinking I cannot make the best of both worlds occur. Thanks.
