Dynamics AX OLAP cubes shows data only till 2008 year

Some time ago we run into following problem. When we browsed Accounts Receivable Cube for Total Sales dimension we could see data only till 2008 year, the data for years: 2009, 2010 and 2011 where just simple not displayed.
The first idea was to try solution from following post: http://blogs.msdn.com/b/emeadaxsupport/archive/2011/01/07/olap-time-dimension-does-not-contain-dates-for-year-2011.aspx but it did not help. Then we double check that in OLTP database we for sure have needed data from i.e. 2010 year

The query which is run to compute Total Sales is based on named query CustTransTotalSales. The measure Total Sales has relation with Time dimension using DUEDATE field. So to double check that query returns data for year 2010 we tried to run following query:

SELECT ACCOUNTNUM, VOUCHER, INVOICE, AMOUNTMST, CURRENCYCODE, TRANSTYPE, APPROVED, DIMENSION, DIMENSION2_, DIMENSION3_, DIMENSION4_, PAYMMODE, DATAAREAID, RECVERSION, RECID, CAST(FLOOR(CAST(DUEDATE AS FLOAT)) AS DATETIME) AS DUEDATE, CAST(FLOOR(CAST(LASTSETTLEDATE AS FLOAT)) AS DATETIME) AS LASTSETTLEDATE, CAST(FLOOR(CAST(CLOSED AS FLOAT)) AS DATETIME) AS CLOSED, CAST(FLOOR(CAST(TRANSDATE AS FLOAT)) AS DATETIME) AS TRANSDATE, CAST(FLOOR(CAST(DOCUMENTDATE AS FLOAT)) AS DATETIME) AS DOCUMENTDATE, CAST(FLOOR(CAST(LASTEXCHADJ AS FLOAT)) AS DATETIME) AS LASTEXCHADJ, CAST(FLOOR(CAST(MODIFIEDDATETIME AS FLOAT)) AS DATETIME) AS MODIFIEDDATETIME, CAST(FLOOR(CAST(CREATEDDATETIME AS FLOAT)) AS DATETIME) AS CREATEDDATETIME, CAST(FLOOR(CAST(PAYMMANLACKDATE AS FLOAT)) AS DATETIME) AS PAYMMANLACKDATE FROM dbo.CUSTTRANS WHERE (INVOICE <> ”) and DUEDATE between ‘1/1/2010′ and ’12/31/2010’ 

Query returned values so we needed to look further what is wrong

With help of Analysis Services EE we saw that [Measures].[Total Sales] is changed/altered by a Scope with following expression:

This = [Master Company Reporting Currency].[Currency].[Local] * (Measures.[Master end of day rate], StrToMember("[Currency].[Currency].&["+[Company].[Currency code].CurrentMember.Name+"]")) / (Measures.[Master end of day rate], LinkMember([Master Company Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])); 

The [Measures].[Master end of day rate] is used internally by above expression is null for the 2010 year and will cause that all other measure will be null for 2010. Measure is based on following named query:

BIMASTERCOMPANYEXCHANGERATES.ENDOFDAYRATE To check execute following SQL Query: SELECT D.ID AS EXCHANGERATECOMPANY, V.CURRENCYCODE, V.DATEKEY, V.ENDOFDAYRATE FROM dbo.DATAAREA AS D CROSS JOIN (SELECT EXCHANGERATECOMPANY, CURRENCYCODE, DATEKEY, ENDOFDAYRATE FROM dbo.BIEXCHANGERATES WHERE (UPPER(EXCHANGERATECOMPANY) = (SELECT TOP (1) UPPER(EXCHANGERATECOMPANY) AS EXPR1 FROM dbo.BICONFIGURATION AS B)) AND (UPPER(CURRENCYCODE) IN (SELECT CASE WHEN UPPER(CURRENCYCODE) IS NULL THEN ” ELSE UPPER(CURRENCYCODE) END AS CURRENCYCODE FROM dbo.COMPANYINFO))) AS V 

If you check following query:

Select Max(DateKey) from BIEXCHANGERATES Where CurrencyCode = ‘EUR’ 

You will see that the last value is for year 2008

So to fix the problem we needed:

1. Go to Administration > Setup > Business analysis > OLAP > BI generation options (if you get error please look into post: http://blogs.msdn.com/b/emeadaxsupport/archive/2011/02/07/error-when-accessing-form-bi-generation-options.aspx)

2. Go to Time dimension tab Make sure that Ending time is set up till the end of this year or further and click OK

3. Go to Administration > Setup > Business analysis > OLAP > Setup Exchange Rates and run the process

After this when you run Select

Max(DateKey) from BIEXCHANGERATES Where CurrencyCode = ‘EUR’ 

You should get the ending date from 3rd step Process cubes

Now you can process cubes and data for remaining years will appear in cube.

author:	Czesława Langowska
editor:	Czesława Langowska
date:	07/Feb/2011

Tags