Breaking news from around the world
Get the Bing + MSN extension
Choose your path Increase your proficiency with the Dynamics 365 applications that you already use and learn more about the apps that interest you. Up your game with a learning path tailored to today's Dynamics 365 masterminds and designed to prepare you for industry-recognized Microsoft certifications.
Visit Microsoft Learn
2019 release wave 2 Discover the latest updates and new features to Dynamics 365 planned through March 2020
Release overview guides and videos Release Plan | View virtual launch event
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
I'm in the process of deploying the default cubes to a customer's site and am encountering problems with the data.
In the sales and marketing cube there is a measure - actual revenue gained from opportunity. Looking at the cube properties it would seem this is the sum of the ActualRevenue field within smmOpportunityView.
Unfortunately the records appear multiple times - thus vastly overstating the measure. If I look at the view smmOpportunityActualRevenueView I can see a single line for the opportunity that has been won. However, when this view is then incorporated into smmOpportunityView, it appears multiple times. This seems to be down to the way it is joined to other tables and views - in particular where the address may have changed at some point against the related global address book entry.
I can easily recreate this on the Contoso demo data by creating a new opportunity for Birch Company, creating a quotation and winning that quotation. I can then see a single value in smmOpportunityActualRevenueView but 20 separate lines (each with the full actual revenue amount) in smmOpportunityView.
Also in the sales and marketing cube there is another measure - Sales Order Revenue. This is the sum of the InvoiceRevenue field in the view smmCustInvoiceJourSalesView. This view appears to take the invoiced revenue from CustInvoiceJour (using the amount gross of VAT which is fairly daft in itself given that customers typically are interested in their sales net of VAT) and joins it to the sales order and sales lines to add the item information to the view.
However, in doing this, what it seems to do is to show a line with the full invoice value against each line on the invoice. So if you have an invoice with a total gross value of £1,200 made up of 5 items, each with a net value of £200, the view shows 5 lines at £1,200 thus giving sales order revenue of £6,000 instead of £1,000 (or even £1,200 if we accepted the VAT should be included in the measure).
Are these known issues and how has anyone else managed to deal with them? It is not giving me a lot of faith in the cubes or role centres as a useful tool!
Thanks for letting us know about these problems.
The bug on duplicate rows in smmOpportinityView if the primary address is edited has been reported previously. There should be a patch for it already available, KB3083350. Maybe search LCS for patches related to that view? Or you could contact product support to get it.
The incorrect invoice revenue from smmCustInvoiceJourSalesView has also been reported. Support is apparently working on a patch for that one, but the patch is not yet available. The bug number is 3706767. That patch should show up in LCS once the patch is released. Or if you contact product support and open a support case for this, they should let you know when the patch becomes available.
Hope this helps.
I've had a look at the patch you mentioned. The instructions on the article indicate that an additional modification is needed to the SSAS project beyond simply applying the hotfix. The instructions are as follows:
"And After installing this hotfix, please open SSAS project from AOT and add the following range:
AND (VALIDFROM <= GETDATE()) AND (VALIDTO >= GETDATE()) in the first Where clause on the smmOpportunityView query in the cube data source views.
Then build, deploy, and process Dynamics AX project."
I am struggling to understand what I have to change. I've opened the analysis project in visual studio and opened the data source view in data source view designer. I've then selected smmOpportunityView in the list of tables, right clicked and selected "edit named query".
When I do this I cannot see a where clause in the query definition. Am I looking at the wrong thing?
Actually I don't even see the VALIDFROM and VALIDTO columns in the smmOpportunityView definition, though it could be because they are added by the patch or because my build is rather old.
Do you have those columns?
If the columns are present on your system, try adding the filter statement just before the Union statement in the query.
The columns can be found in the smmOpportunityView on SQL level.
The result is good running reporting cubes.
Changes however will be lost by the next database synchronisation.
I have similar problem with Andrew here on bug 3706767 you mention above and apparently it was solved by LCS on KB3132622, but i can't download the hotfix since it was not a public hotfix. Can you help on this ?
Chris, thanks for reporting this.
Hotfix KB3132622 had been mistakenly deleted off the hotfix share. One of our engineers uploaded it again today, so it should be available again now.
thanks for quick response, the hotfix is available now and i'm working on it on my system to test whether the bug has fixed. Will let you know once confirmed ok.
*Updated post *
the hotfix seems designed for R3 release while ours still on R2 so the database model a bit different and the hotfix unfortunately cannot merge into ours. Do you think it is safe to go upgrade to 2012R3 ? ours are 2012R2CU6.
But again, thanks !
Business Applications communities