Slow DLP Cube processing performance

book

Article ID: 169561

calendar_today

Updated On:

Products

IT Analytics

Issue/Introduction

The processing job for the Data Loss Prevention cubes is taking over 12+ hours to complete.

Cause

The processing of the Custom Attributes for the different Data Loss Prevention incidents is handled via SQL functions. This results in the results having to be manipulated in the TempDB's memory then transferred. This results in an additional overhead of 20%+.

Environment

IT Analytics for Data Loss Prevention

Resolution

The following steps capture the "SELECT..." portion of the function content and puts it directly into the view being called, rather than calling the function separately.  This will have the same effect of the function call, without the SQL overhead.  If the cube is being processed manually from SQL, you will be able to see row growth progress and know that the cube is processing. 

Please perform a full backup of the IT Analytics Database before proceeding.

  1. Using Microsoft SQL Server Management Studio, Database Engine connection type, expand IT Analytics Database
  2. Expand "Programmability", "Functions", "Table-valued Functions", then right click on the function "fnITAnalytics_DLP_IncidentCustomAttributesFact" and choose  "Script Function as" -> ALTER ToO> New Query Editor Window.
  3. Copy the contents of the function's SQL statement,  beginning at the "SELECT" syntax and ending after the ")" parenthesis before the word "END". 
  4. Right click on the view "vITAnalytics_DLP_IncidentCustomAttributesFact" and choose choose  "Script View as" -> ALTER To> New Query Editor Window.
  5. Comment out the statement "SELECT * from fnITAnalytics_DLP_IncidentCustomAttributesFact()". 
    --SELECT * from fnITAnalytics_DLP_IncidentCustomAttributesFact()
  6. Paste the copied contents (Select statement) from "fnITAnalytics_DLP_IncidentCustomAttributesFact" just above the ending "GO".
    NB.: If there are multiple DLP connection configured, paste the contents from step 3 first to a text editor (such as Notepad++), then replace all occurrences of "INSERT INTO @ret" with "UNION"
  7. Update the line ",(10000000000 * 1) + CUSTOMATTRIBUTESRECORDID as CUSTOMATTRIBUTESRECORDKEY" to be ,"(10000000000 * 1) + CUSTOMATTRIBUTESRECORDID as CUSTOMATTRIBUTEKEY" 
  8. Execute the script to update the view.
  9. Perform same steps against views "vITAnalytics_DLP_DiscoverIncidentCustomAttributesFact", "vITAnalytics_DLP_EndpointIncidentCustomAttributesFact", and "vITAnalytics_DLP_NetworkIncidentCustomAttributesFact",  using their associated functions.