Hi All,
I get into an issue while AX cubes processing. A clean AX installation was performed based on SQL 2016 but SQL version is not an issue since there is another installation based on exactly the same version and it works perfectly.
I tried to change some SSAS parameters according to the following articles:
https://blog.calvett.co.uk/2009/04/05/analysis-server-appears-to-hang/
https://blogs.msdn.microsoft.com/psssql/2007/01/16/processing-appears-to-stall-or-become-sluggish-on-multi-processor-machines-running-analysis-services-2005/
The result was the same - dimensions' full processing always gets stuck on a query when SRSANALYSISENUMS table is used, i.e.:
SELECT [BASEENUM_PurchLineDeliveryPrecision].[ENUMITEMVALUE] AS [BASEENUM_PurchLineDeliveryPrecisionENUMITEMVALUE0_0],[BASEENUM_PurchLineDeliveryPrecision].[ENUMITEMNAME] AS [BASEENUM_PurchLineDeliveryPrecisionENUMITEMNAME0_1]
FROM
(
SELECT A.ENUMITEMVALUE, A.ENUMITEMLABEL AS ENUMITEMNAME FROM [DBO].SRSANALYSISENUMS A WHERE A.ENUMNAME = 'PurchLineDeliveryPrecision' AND A.LANGUAGEID = 'en-us'
)
AS [BASEENUM_PurchLineDeliveryPrecision]
or
SELECT [BASEENUM_NoYes].[ENUMITEMVALUE] AS [BASEENUM_NoYesENUMITEMVALUE0_0],[BASEENUM_NoYes].[ENUMITEMNAME] AS [BASEENUM_NoYesENUMITEMNAME0_1]
FROM
(
SELECT A.ENUMITEMVALUE, A.ENUMITEMLABEL AS ENUMITEMNAME FROM [DBO].SRSANALYSISENUMS A WHERE A.ENUMNAME = 'NoYes' AND A.LANGUAGEID = 'en-us'
)
AS [BASEENUM_NoYes]
or
SELECT [GENERALJOURNALCUBE_DIM].[TRANSACTIONTYPE] AS [GENERALJOURNALCUBE_DIMTRANSACTIONTYPE0_0],[GENERALJOURNALCUBE_DIM].[PERIODCODE] AS [GENERALJOURNALCUBE_DIMPERIODCODE0_1],[GENERALJOURNALCUBE_DIM].[POSTINGLAYER] AS [GENERALJOURNALCUBE_DIMPOSTINGLAYER0_2],[GENERALJOURNALCUBE_DIM].[POSTINGTYPE] AS [GENERALJOURNALCUBE_DIMPOSTINGTYPE0_3],[BASEENUM_LedgerTransType].[ENUMITEMNAME] AS [BASEENUM_LedgerTransTypeENUMITEMNAME1_0],[BASEENUM_LedgerTransType].[ENUMITEMVALUE] AS [BASEENUM_LedgerTransTypeENUMITEMVALUE1_1]
FROM
(
SELECT TRANSACTIONTYPE.ENUMITEMVALUE TRANSACTIONTYPE , PERIODCODE.ENUMITEMVALUE PERIODCODE , POSTINGLAYER.ENUMITEMVALUE POSTINGLAYER , POSTINGTYPE.ENUMITEMVALUE POSTINGTYPE FROM ( SELECT DISTINCT ENUMITEMVALUE FROM [DBO].SRSANALYSISENUMS WHERE ENUMNAME = 'LedgerTransType' ) TRANSACTIONTYPE CROSS JOIN ( SELECT DISTINCT ENUMITEMVALUE FROM [DBO].SRSANALYSISENUMS WHERE ENUMNAME = 'FiscalPeriodType' ) PERIODCODE CROSS JOIN ( SELECT DISTINCT ENUMITEMVALUE FROM [DBO].SRSANALYSISENUMS WHERE ENUMNAME = 'CurrentOperationsTax' ) POSTINGLAYER CROSS JOIN ( SELECT DISTINCT ENUMITEMVALUE FROM [DBO].SRSANALYSISENUMS WHERE ENUMNAME = 'LedgerPostingType' ) POSTINGTYPE
)
AS [GENERALJOURNALCUBE_DIM],
(
SELECT A.ENUMITEMVALUE, A.ENUMITEMLABEL AS ENUMITEMNAME FROM [DBO].SRSANALYSISENUMS A WHERE A.ENUMNAME = 'LedgerTransType' AND A.LANGUAGEID = 'en-us'
)
AS [BASEENUM_LedgerTransType]
WHERE
(
(
[GENERALJOURNALCUBE_DIM].[TRANSACTIONTYPE] = [BASEENUM_LedgerTransType].[ENUMITEMVALUE]
)
)
Execution of the last query returning almost 127k records takes ~500ms.
Some more details:
- This is run in a non-production VM with 2 CPU cores, 8GB RAM containing only SSRS and SSAS components;
- SSAS project gets delpoyed properly;
- Demand forcast cubes get processed without any problems;
- Values tested for ThreadPool \ Process \ MaxThreads : 1, 2, 4, 8, 32, 64, 150;
- When XMLA script for processing dimensions is divided into smaller scripts all dimensions get processed. The thing is that AX database is empty so there is no guarantee it will work when data exists.
Has anybody faced this problem?
