emeadaxsupport: Measure 'Total purchases' in Accounts payable cubes for dimension 'Vendor Master' shows values Uknown

[Blog bot]

Источник: http://blogs.msdn.com/b/emeadaxsuppo...es-uknown.aspx
==============

Some time ago I run into problem that measure ‘Total purchases’ for dimensions ‘Vendor Master’ did not work. There was only values for attribute Uknown. So I was not able to see what was sum of vendor purchases for each vendor although I had bunch of Purchase Orders which were posted and invoiced.

First step to troubleshoot this issue was to find out what exactly query is hidden under measure ‘Total purchases’ To do it in Business Intelligence Development Studio when you look into Source Property of ‘Total Purchases’ measure:



You can that values are coming from VendTransTotalPurchase.AmountMST.

When you go to data source view Dynamics AX you will that VendTransTotalPurchase it is named query and you will be able to see query behind.

If you wan to change this query to get data for Vendor account number I got following:

SELECT     VENDTRANS.ACCOUNTNUM,  sum(AMOUNTMST), VENDTRANS.DATAAREAID, VENDTABLE.NAME
FROM         dbo.VENDTRANS, dbo.VENDTABLE
WHERE     (INVOICE '') and VENDTRANS.ACCOUNTNUM = VENDTABLE.ACCOUNTNUM and VENDTRANS.DATAAREAID = VENDTABLE.DATAAREAID
group by VENDTRANS.ACCOUNTNUM, VENDTRANS.DATAAREAID, VENDTABLE.NAME
order by VENDTRANS.DATAAREAID
If I run this query I could see correct values for each vendor, but still I could not see it in ‘Vendor master’. So my next step was to get closer look on Vendor Master dimension.

I found that when processing dimension Vendors master attribute Vendors (key) there is very long and very complex query which I will not copy here, but the most important was where expression which looked like:

WHERE
  (
   
  (
   [VENDTABLE].[DATAAREAID]   =  [EMPLTABLE].[DATAAREAID]
  )
     AND 
  (
   [VENDTABLE].[MAINCONTACTID]   =  [EMPLTABLE].[EMPLID]
  )

  )
The I looked into my VENDTABLE and I saw that indeed the field MAINCONTACTID was not set up.

In summary to solve problem I needed in Dynamics AX Client for each Vendor update field Main Contact on General tab (to assign employee to vendor – it can be dummy empl) after that processing Vendor master dimension and Accounts payable cube is needed. It worked !!!

--author: Czesława Lagowska --editor: Czesława Langowska --date: 1/March/2011




Источник: http://blogs.msdn.com/b/emeadaxsuppo...es-uknown.aspx