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:
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:
(
(
[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.
Update (02 March 2011)
The binding between VENDTABLE and EMPTABLE you can see in DataSource view. If you look into relationship one of the relationship look like following:
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 |
| –edit: | 2/March/2011 |