Forum Discussion
Object Level Security that is measure dependent
I appreciate the suggestion of a shadow/hidden table, but please provide more details on how to implement. I'm also providing more specifics about what the model design and desired reporting output for two different user groups.
All Measures (calculated table for all explicit measures)
- Invoiced Tons comes from Invoice fact table
- Inventory Tons comes from Inventory fact table
Product dimension (many attributes but limiting to 4 for this example)
- Product category
- Product subcategory
- Reporting grade
- Material number
- This column should be visible for Sales users for Invoiced Tons but be hidden for Inventory Tons
- All other users can see this column with Invoiced and Inventory Tons.
Table relationships:
- Invoice material number = Product material number
- Inventory material number = Product material number
Example #1 for Sales user to see the first three Product fields:
Example #2 - Sales user should not see Material Number for Inventory Tons. Ideally the gray portion would be blank so the same report works for all users instead of completely hidden/access denied that would create an error on the report.
Example #3 - Non-Sales user can see all product levels:
Thanks for any suggestions!
- lbendlin2 years agoSuper User
use two copies of the Product Dimension table. Join the one with the visible materials column to the invoices and the one with then hidden materials column to the inventory
- JKarrick2 years agoFrequent Visitor
If inventory is joined to the version of the Product dimension with the material column hidden, wouldn't that hide material from all users? Most users should see inventory by material. It's only sales reps that need to have material hidden.
- lbendlin2 years agoSuper User
If you have sufficiently diverse audiences then you better create separate reports.