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
PersonID, Reduction
1,North
2,South
1,National
2,National
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 as an example:
OneFactTable:
Reduction , SalesID, ProductID, SalesAmount, ProductDesc
1X , 1 ,X, 100 ,
2X , 2 ,X, 200 ,
XA , X, A , , ProductA
XB , X, B , , ProductB
ReductionTable:
Level, Reduction
1,1X
1,2X
1,XA
1,XB
2,1X
2,XA
2,XB
3,2X
3,XA
3,XB
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
