I have a document where I am doing data reduction based on section access and no strict exclusion.
I have a mixed bag of users who have access to all data or manager level access of sales rep access.
The sales data reduces and shows perfectly for all users, however, I also have non-sales data that everyone should be able to see but for some reason it is getting filtered out of the app sicne it is in a table that is connected with invoice lines.
Any thoughts on how I could stop this from happening?
Hi, I had a similar problem this week..... here's what I've found out.
Section access works like a wild fire, print out your table diagram and set fire to one corner and you'll see what I mean. It starts from the table where the initial connection is and spread through all your tables where there is a connection.
If you have a single fact table then you need a column to link to a new table to hold the combinations. If you are using more than one field it will need to be designed in a similar way to a link table.
For example: North, South and National
2 people, one working in the North and One in the South the new table would look like this
So if you reduce on North, South or National the right people are left behind. Problem is by only linking on a standard field it won't link to all your rows and this is what you're experiencing. Now you could expand the example above like you would a link table to bring in all the different combinations of the various keys and your Reduction field would be more generic; Levels or Job Titles for example. Like a link table you'd end up with something quite big but narrow enough to not cause too many problems.
So in this example Level 1 would have access to everything, Level 2 would have access to all products but only SalesID 1 and Level 3 would have access to all products but only SalesID 2
My problem was a bit different but here it is........
If you have more than one fact table (like I have) then you have to put in links to stop the reduction. For example I reduce on location (like the example above) but as the reduction spreads through each table via the links it comes to a reference table (A) which has a hierarchy. Issue I had is that the parents didn't have links into the previous table (B) where reduction was spreading from and were removed. This caused an issue with a treeview selection box.
What I did was place lines of "Dummy" link data (only two fields) in the table (B) before the problem with reference to the affected Parent ID's (From table A) and also a Key I knew was already present in table B. This meant that as the reduction moved from table B to table A it found a link to the Parent ID's in table A and the data remained.
Of course you have to be careful and really think about how your data model works