Restore parallel cube processing

Products

• Information Centric Analytics
• Data Loss Prevention Core Package

Issue/Introduction

As documented in Broadcom KB article 175826, Microsoft released a security update for SQL Server that adversely affected parallel cube processing, which is the default processing method used for the RiskFabric cube. As a workaround, Broadcom recommended splitting the cube processing step into distinct steps for the cube measures and dimensions, each run as sequential processing tasks.

Microsoft has since released a fix to resolve the underlying issue in SQL Server. This article provides a procedure to restore cube processing to a single, parallel processing task.

Environment

Version : 6.x

Component : Microsoft SQL Server Analysis Services

Resolution

This procedure will restore parallel cube processing in environments in which sequential processing was implemented per Broadcom KB article 175826. This procedure should be followed only after all servers hosting Microsoft SQL Server (MSSQL) and SQL Server Analysis Services (SSAS) have been patched to the appropriate cumulative update (CU). Per Microsoft document KB4589170, a fix for this issue was initially released in the following updates:

• SQL Server 2016: CU14 (13.0.5830.85)
• SQL Server 2017: CU23 (14.0.3381.3)
• SQL Server 2019: CU9 (15.0.4102.2)

NOTE: Once the appropriate CU (or greater) has been applied, the setting CoordinatorSafeJobUnblocking needs to be set to 0 in the msmdsrv.ini file on the SSAS server.

Assumption: It is assumed that the steps in the related article below have been applied to the RiskFabric Processing job.

• Open SQL Server Management Studio (SSMS)
• Connect to the SQL Server hosting the Risk Fabric Processing job. Also connect to the server hosting SQL Analysis Services.
• Backup the existing RiskFabric Processing job. Expand SQL Server Agent, then Jobs. Right Click on RiskFabric Processing job, mouse over Script Job as, Create To File. Enter a location and name the file.

• Expand the Analysis Server database connection, then databases, RiskFabric, Cubes.
• Right click on the Risk Fabric cube and select Process.
• In the resulting window, right click on the Risk Fabric cube and select Add Related Dimensions. Your screen should look like this:

• Notice that all the dimension added are automatically highlighted, right click on any highlighted dimension and select Process Full. Now your screen should look like this:

• Click on the word Script, then click on the Cancel button. The resulting screen should look like this:

• Inside this window, press Ctrl+A to select all, and Ctrl+C to copy the contents to the clipboard.
• Return to the previously expanded SQL Server Agent, Jobs folder and double click on the RiskFabric Processing job to edit it. Then click on steps.
• Between the step named CustomComplianceData and Process Risk Models steps, you should see two steps (the names may vary based on implementation), both steps will be of the type SQL Server Analysis Services Command. Highlight the first step, which will refer to dimension processing:

• Delete this step.
• Edit the Risk Fabric cube processing step
• Press the Select All button, then Delete (on the keyboard).
• Paste the contents of the clipboard in the Command window. The resulting window should look similar to this:

• Press Ok to close the Job Step Properties and Ok a second time to close the Job Properties dialog box.
• Run the RiskFabric Processing job and ensure the updated step completes successfully.
• The job has been restored to parallel processing.

Additional Information

RiskFabric Processing hangs during cube processing

Broadcom KB article 175826: https://knowledge.broadcom.com/external/article?articleId=175826
(Link available as of May 12, 2023)

The following builds of SQL Server were affected:

• SQL Server 2016: all cumulative updates for SP2 from CU7 (13.0.5337.0) through CU13 (13.0.5820.21)
• SQL Server 2017: all cumulative updates from CU15 (14.0.3162.1) through CU22 (14.0.3356.20)
• SQL Server 2019: all cumulative updates through CU8 (15.0.4073.23)

SQL Server 2022 is not affected by this issue.