AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 07.02.2011, 16:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,631 / 848 (80) +++++++
Регистрация: 28.10.2006
emeadaxsupport: Dynamics AX OLAP cubes shows data only till 2008 year
Источник: http://blogs.msdn.com/b/emeadaxsuppo...2008-year.aspx
==============

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/emeadaxsuppo...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/emeadaxsuppo...n-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




Источник: http://blogs.msdn.com/b/emeadaxsuppo...2008-year.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
daxdilip: Whats New in Dynamics AX 2012 (A brief extract from the recently held Tech Conf.) Blog bot DAX Blogs 7 31.01.2011 12:35
semanticax: Dynamics AX 2009 Installation - Application Blog bot DAX Blogs 0 22.12.2010 08:11
emeadaxsupport: List of fixes that improve performance of certain features in Dynamics AX 2009 Blog bot DAX Blogs 0 13.10.2009 19:06
gatesasbait: Dynamics AX 2009 SSRS and SSAS Integration Tips Blog bot DAX Blogs 3 09.07.2009 13:07
axStart: Microsoft Dynamics AX 2009 Hot Topics Web Seminar Series Blog bot DAX Blogs 0 06.08.2008 12:05

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 05:54.