Data Reduction causing application to freeze

[phcaptjim]

I'm having a strange issue with Data Reduction / Section Access.

Before I get to the problem, i want to point out that my data model works fine on a very small scale (less than 50 records).  The issue presents itself when i load the main application which has about 3 million records and specific users can only see specific rows of data.

When I have the CHECKBOX selected for Data Reduction, the application will not open.  It starts taking up all the memory on the server until it crashes.  If I uncheck that box, the application opens up just fine.  Again, this same data model works fine on a small scale.  The main application has 300 users who can see a different rows depending on their AD account.

Has anyone encountered a problem like this?

I would be happy to post my data model / script if you think that would be helpful, but in this case I am fairly sure that is not the problem.

Thanks!

[Oleg_Troyansky]

It sounds like the system is trying to associate your Section Access information with the main data model, and it's running into building a Cartesian Join... Could it be that your Users' data and your data model don't have any identical fields in common? How are you facilitating the data reduction? what fields are supposed to drive it?

[phcaptjim]

Thanks for the reply Oleg.

My rows are limited using the field SECURITYID.

This is the section access:

Section Access;

LOAD

  UPPER(ACCESS) as ACCESS,

     UPPER(NTNAME) as NTNAME,

     UPPER(SECURITYID) as SECURITYID

FROM

Access_Security.qvd

(qvd);

Main section:

Employees:

LOAD SECURITYID,

     EmployeeAD,

     EmployeeKey,

     UserID,

     EmployeeName,

     EmployeeLastName,

     EmployeeFirstName,

     EmployeeTitle,

     EmployeeStartDate,

     ManagerKey,

     ManagerName,

     ManagerLastName,

     ManagerFirstName,

     EmployeeLevel,

     DirectorKey

FROM

Employees.qvd

(qvd);

Security:

LOAD EMP_ID as EmployeeID,

     AD_LOGIN as EmployeeAD,

     SECURITYID

FROM

Security.qvd

(qvd);

EPM:

LOAD UserID,

     MetricID,

     Department,

     Division,

     AppointmentStatus,

     MetricDate,

     NewRegistrationFlag,

     Numerator,

     Denominator,

     EmployeeAD,

     EncounterID

FROM

EPM.qvd

(qvd);

Perhaps I should not use the field SECURITYID in more than one table?  Instead of using the Security table to link between Employees and the fact table (EPM) I could link the two separately.  I'm doing a data reduction based on the what records the users SECURITYID is listed for.  I think for now I will try and eliminate SECURITYID in the Employees table and link that using UserID from the main fact table.  I'm not sure if that will make a difference though.

[Oleg_Troyansky · Accepted solution]

Yes, I think you are doing too much with the field SECURITYID. If you read through the Reference Manual, chapter "Security", they explain in detail how to facilitate data reduction based on section access. In a nutshell, you need to build a simple link table, linking your "security" field (SECURITYID) to a "regular" field, for example EmployeeID. The field EmployeeID, in turn, is part of your regular data model. The "link table" only needs two fields - "SECURITYID" and "EmployeeID", no more.

best,

Oleg Troyansky

[phcaptjim]

Oleg I believe you nailed it.  Let me confirm and I will mark this question as complete.

Here is what I did:

1. I removed the SECURITYID field from the Employees table.

2. I'm only using the Security table to facilitate the section access as Oleg suggested.  SECURITYID only appears in the Security table and that table is linked to the fact table using EmployeeAD.

3. The application now opens without issue.

Thank you!!!