KB6005: How to Create Fact Dependent Security Filters

• Strategy

Some projects may require certain facts to be exempt from security filter qualifications. This document examines a particular scenario that can serve as a model for similar cases.

Three facts are under consideration: Bonus, Sales and Quota. Sales and Quota figures are "public" information: all Employees should be able to see Sales and Quota numbers for themselves and other Employees. An Employee's bonus, however, should hidden from other Employees.

The personnel hierarchy is as shown:

Given the following data in the FT_QUOTA_SALES_BONUS table:

11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS

Employee 1 would be able to see her own Bonus only, but Quota and Sales for the whole region (1):

11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS

Employee 4 would likewise see only his Bonus, but the Quota and Sales for the whole region (2):

11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS

Security filters always apply to related facts. "Related" means that the attribute in the security filter qualification is related to one of the fact's entry-level attributes. In the above model, all three facts have the same entry-level (Employee); therefore, a security filter on Employee would filter all three facts.

To handle this requirement, it is necessary to have secondary fact tables and attributes that are unrelated to the original, primary attributes (which will be used in report templates). Filtering qualifications based on the secondary attributes will apply only to their related facts. The following diagram illustrates how the fact tables may be split, putting the "public" Sales and Quota facts into one table and the "private" Bonus fact into another. Both of the new tables have their own filtering hierarchies.

Also, the EMPLOYEE_ID column in both tables maps onto the primary Employee attribute. Note that this does not constitute a relationship between the reporting and filtering hierarchies. "Employee Quota Sales," "Employee" and "Employee Bonus" are not related to each other; they connect only through the fact tables. It is valid to aggregate by the primary Employee attribute while filtering on an attribute from a different hierarchy.

The secondary fact tables may be physical tables, database views, or logical views defined in Strategy.

Schema mapping details are shown here.

11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS

11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS
11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS	11112001000 1001 1 2 1500 2000 50 1 2 3 1000 1100 80 1 2 4 1100 200 90 11 1 1200 1000 100 1 1 2 1500 2000 -- 12 3 1000 1100 -- 1 2 4 1100 200 90 Employee--RegionEmployee Vice PresidentRegionEmployee Bonus--Region BonusEmployee BonusEmployee Quota Sales--Region Quota SalesEmployee Quota SalesBONUSEmployee Employee BonusFV_BONUSQUOTAEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUSSALESEmployee Employee Quota SalesFV_QUOTA_SALES FT_QUOTA_SALES_BONUS

Since the requirement is to filter Sales and Quota by region, but Bonus by Employee, both qualifications should be in the security filter.

Region Quota Sales in (Employee's region)
AND
Employee Bonus in (Employee)

The Bonus fact has no attribute relationship to Region Quota Sales in the security filter; thus this qualification will be omitted from SQL calculating the Bonus metrics. Likewise, the Employee Bonus attribute is not related to the Sales or Quota facts, whose metrics will be filtered only according to Region Quota Sales.

The metric join types in Report Data Options should be "Outer" to preserve rows for other Employees in the same region.

Comment

0 comments

Details

Knowledge Article

Published:

May 16, 2017

Last Updated:

May 16, 2017