Dynamics Community Forum Thread Details

This looks like an error in either in a dimension for the analysis services project or in a calculated measure from the project.

These can be a bit of challenge to debug. Tech support might be a good option on this one.

But if you want to try yourself, first step would be to confirm you have a ledger currency set for EVERY company account in your database including the default 'dat' company. The analysis currency dimension that shows up in the error output is for currency conversion and there have been prior reports of it not working correctly if there is a company account without a ledger currency set.

Once you've checked for companies without a currency, then its time to debug the analysis services database.

Open the Dynamics AX analysis services project. You'll find in AOT under projects -> Analysis services projects.

The dimension 'Analysis currency' shows up in the error messages, so find that dimension in the project and open it.

On the right hand side of the editor for the dimension it will show you the schema for the tables used by the dimension, which for this dimension is a query named BIAnalysisCurrency. If you right on it and look in the property window it has a Query property that shows you what query is run to fill the data.

For me it has a value of

SELECT [BIANALYSISCURRENCY].* FROM ( SELECT CAST([DBO].[BICURRENCYVIEW].[CURRENCYCODE] AS nvarchar ( 5 )) AS [CURRENCYCODE] , CAST([DBO].[BICURRENCYVIEW].[ISOCURRENCYCODE] AS nvarchar ( 5 )) AS [ISOCURRENCYCODE] , CAST([DBO].[BICURRENCYVIEW].[CURRENCYNAME] AS nvarchar ( 60 )) AS [CURRENCYNAME] , CAST([DBO].[BICURRENCYVIEW].[SYMBOL] AS nvarchar ( 5 )) AS [SYMBOL] , CAST( CASE WHEN [DBO].[BICURRENCYVIEW].[CURRENCYCODE] = ( SELECT CASE WHEN ((SELECT COUNT(*) FROM [DBO].[SYSTEMPARAMETERS] WHERE PARTITION > 0 AND [SYSTEMCURRENCYCODE] != '') > 0) THEN (SELECT TOP 1 [SYSTEMCURRENCYCODE] FROM [DBO].[SYSTEMPARAMETERS] WHERE PARTITION > 0 AND [SYSTEMCURRENCYCODE] != '' ) ELSE (SELECT TOP 1 [ACCOUNTINGCURRENCY] FROM [DBO].[BICOMPANYVIEW] WHERE PARTITION > 0 AND [ACCOUNTINGCURRENCY] != '') END ) THEN (SELECT 1) ELSE (SELECT 0) END AS NVARCHAR ( 5 )) AS [ISSYSTEMCURRENCY] FROM [DBO].[BICURRENCYVIEW] WHERE PARTITION > 0 AND CURRENCYCODE IN (SELECT [FROMCURRENCYCODE] FROM ( SELECT [FROMCURRENCYCODE], COUNT(DISTINCT [TOCURRENCYCODE]) AS [TOCURRENCYCODECOUNT] FROM [DBO].[BIEXCHANGERATEVIEW] WHERE PARTITION > 0 AND [TOCURRENCYCODE] IN ( SELECT DISTINCT [ACCOUNTINGCURRENCY] FROM [DBO].[LEDGER] WHERE PARTITION > 0 ) GROUP BY [FROMCURRENCYCODE] ) AS [CURRENCYCODECOUNTS] WHERE [TOCURRENCYCODECOUNT] > 0 AND [TOCURRENCYCODECOUNT] = (SELECT COUNT(DISTINCT [ACCOUNTINGCURRENCY]) FROM [DBO].[LEDGER] WHERE PARTITION > 0) ) UNION SELECT DISTINCT CAST(N'Local' AS nvarchar( 5 )) AS [Local 1] , CAST(N'Local' AS nvarchar( 5 )) AS [Local 2] , CAST(N'Local' AS nvarchar( 60 )) AS [Local 3] , CAST(N'Local' AS nvarchar( 5 )) AS [Local 4] , CAST(N'Local' AS NVARCHAR( 5 )) AS [Local 5] ) AS [BIANALYSISCURRENCY]

If someone has attempted to customize your project, you could see a different query.

start by running that query and seeing if you get the divide by zero error. I don't see a divide actually in the query but perhaps there is a divide in one of the views it references?

If the query fails then its a matter of figuring out which of the views it selects from has the division and then figuring out what it divides by and then fixing the data to not be zero.

If the query works, then open sql management studio and connect to analysis services. Find the dynamics ax initial database. expand the dimension list. for each dimension, select the dimension node, right click and process. Click Ok on the process dialog. If you ever get a divide by zero reported, that dimension is where we debug next. In that case go back to the analysis services project, find the dimension, see what query it uses and get the query property value. run that query in sql to reproduce the error and then look into the column that is used as the divisor.

If all the dimensions work, then start process the cube files one at a time until you hit the one with the error. If its in a measure group, you probably get a decent error message identifying what measure could not be computed, but if its in a calculated measure, analysis services really doesn't tell you much of anything. In that case start deleting calculated measures that use division one at a time, and redeploy and reprocess the cube after each deletion until it succeeds. There is a context menu on the cube file with a process command which should run deploy and process for you. Once the broken calculation is identified, reopen the project from AOT to get it back to the original state and either delete the broken calculation or fix the formula for it. After doing, add the project back into AOT and redeploy it (or you can deploy it out of the designer if you prefer).

Calculated measures are defined as mdx queries in the cube file. They'll be shown on the calculations tab. There are a number of them with divisions. My guess is there will be one where the author forgot to check for null or zero before doing a divide.

If coded correctly you should see something like this formula.

CREATE MEMBER CURRENTCUBE.[Measures].[Cumulative % of the total quantity]

IIF(

IsEmpty([Measures].[Total quantity]) OR [Measures].[Total quantity]=0,

NULL,

[Measures].[Cumulative quantity] / [Measures].[Total quantity]),

FORMAT_STRING = "Percent",

VISIBLE = 1;

The IIF statement means if the condition that the total quantity is null or zero is true, return NULL otherwise do the division and return it.

If you see a calculated measure definition where somebody did the divide without putting inside an IIF statement and checking for null or 0 value, then that one is likely to be the one that fails.

If you do find an incorrectly coded calculation, please do report it so that it can be fixed for everybody else who has the same calculation defined.

hope this helps,

--Lance

"OLE DB error: OLE DB or ODBC error: Divide by zero error encountered.; 22012." while processing cubes

Helpful resources

Quick Links

November Spotlight Star - Khushbu Rajvi

Forum Structure Changes Complete!

Dynamics 365 Community Platform update – Oct 28

Leaderboard

Featured topics

Product updates