Share via


AX OLAP数据立方体只显示到2008年为止的数据

前段时间我们碰到了个问题。当我们浏览应收帐款数据立方体中的总销售额维度时,我们只能看到2008年为止的数据,而2009,2010和2011的数据没有显示。

首先想到的是尝试以下博文中的解决方案:

https://blogs.technet.com/b/axchina/archive/2011/01/26/olap-2011.aspx 

但是并没有帮助。随后我们再次进入OLTP数据库确认了需要的数据是存在的(例如2010年的)

用来计算总销售额的查询是基于命名查询CustTransTotalSales的。总销售额度量和时间维度通过DUEDATE域关联。因此,为了再次确认查询会返回2010年的数据,我们尝试运行以下查询:

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'

该查询返回了数据。因此我们需要进一步研究哪里出错了。

在分析服务EE工程师的帮助下,我们看到[Measures].[Total Sales]的范围被以下表达式改动了:

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]));

上述表达式中用到的[Measures].[Master end of day rate]在2010年为空,这会造成2010年的所有其它度量为空。度量是基于如下命名查询的:

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

如果您查看如下查询:

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

您会发现最后的值是2008年的

因此要想解决该问题,您需要:

1.导航到系统管理-〉设置-〉商务分析-〉OLAP -〉BI生成选项(如果您碰到错误,请参阅博文https://blogs.technet.com/b/axchina/archive/2011/02/08/bi.aspx )

2.进入时间维度标签,并确认将终止时间设成今年末或更远的将来,点击OK

3. 导航到系统管理-〉设置-〉商务分析-〉OLAP -〉设置汇率,运行处理

在此之后当您运行

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

您应该可以得到步骤2中设置的终止时间。

现在您可以处理数据立方体,然后剩余年份的数据就会在数据立方体中显示了。

原文地址:

https://blogs.msdn.com/b/emeadaxsupport/archive/2011/02/07/dynamics-ax-olap-cubes-shows-data-only-till-2008-year.aspx