I have a similar problem.
My fact table is limited on a field called USERID. Data reduction works well with my current setup, however I have added some summary records to my fact table that I identify using a new field called RecordType. The detail rows are RecordType = 'D' and the summary records are 'S'. The summary records have a NULL value in the USERID column so I can summarize the facts by manager and organization so each employee can see the overall averages and totals for their manager and organization.
ACCESS, NTNAME, USERID
USERID, MANAGERKEY, DIRECTORKEY, DEPT, DIVISION, NUMERATOR, DENOMINATOR, RECORDTYPE
The problem I have is the Summary records are being eliminated by the data reduction. Is there a way to keep these records in the data set with a blank or NULL USERID column?
I have found it very difficult to find any information on keeping summary data when using section access.
Any help would be greatly appreciated!
As per your section access table admin user can able to see all the data.
1) if you put * in Reduction field then the admin user can able to view all the values listed in the section access table (example1.qvw)
2) if you put "null" then the admin user can able to view all the values in that particular field (including the values not listed in the section access table). Only in the case where the reduction field doesn't have any null value (example2.qvw).
In your case City has null value. So the values X, Y and Null will be visible for the ADMIN user (example3.qvw)
If you want the admin users to see only CODE C data. Then remove the ADMIN user entry with * in the section access table (example4.qvw)
Do you want the summary records can be viewed by all the users?
If yes create a link table wuth two fields USERID and RECORDTYPE
Now All users can see summary records
I have attached a file for reference Section Access_Consolidated.qvw.
Thanks for the response Prabhu.
I love your example but is the synthetic key going to be an issue with this current data model? Below is the data model for my application, it's a little more involved than the example I posted but the concept is the same. EPMFacts stores my details and summary records with the RecordType column used to distinguish between the two. SECURITYID is the field that links to the Section Access table for each user from the Security table. I could add two records for each user in the security table (one for RecordType 'S' and one for 'D' but then a synthetic key would be created between the two tables.
Do you know how I should handle this?
(I have no idea why the image shows the calendar table twice.....that is odd)
This could be solved by using "generic keys". I have done it for a simple scenario. Modify this as per your requirement.
Create a new table with two fields GEN_SECURITYID, SECURITYID
and instead of SECURITYID in the fact table use GEN_SECURITYID. Change the GEN_SECURITYID for the summary records as ANY.
The section access table will look like this
* INLINE [
ACCESS, USERID, SECURITYID
USER, A, 1
USER, B, 2
USER, C, 3
ADMIN, X, *
Now any user will be able to see the summary record. (Refer the attachment for more)
Generic Key_Section Access.qvw 153.0 K