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.
- Using Microsoft SQL Server Management Studio, Database Engine connection type, expand IT Analytics Database
- 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.
- Copy the contents of the function's SQL statement, beginning at the "SELECT" syntax and ending after the ")" parenthesis before the word "END".
- Right click on the view "vITAnalytics_DLP_IncidentCustomAttributesFact" and choose choose "Script View as" -> ALTER To> New Query Editor Window.
- Comment out the statement "SELECT * from fnITAnalytics_DLP_IncidentCustomAttributesFact()".
--SELECT * from fnITAnalytics_DLP_IncidentCustomAttributesFact()
- 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"
- Update the line ",(10000000000 * 1) + CUSTOMATTRIBUTESRECORDID as CUSTOMATTRIBUTESRECORDKEY" to be ,"(10000000000 * 1) + CUSTOMATTRIBUTESRECORDID as CUSTOMATTRIBUTEKEY"
- Execute the script to update the view.
- Perform same steps against views "vITAnalytics_DLP_DiscoverIncidentCustomAttributesFact", "vITAnalytics_DLP_EndpointIncidentCustomAttributesFact", and "vITAnalytics_DLP_NetworkIncidentCustomAttributesFact", using their associated functions.