I've had to make another username because I am literally unable to get to the login page, it constantly pushes me to the "Create New User" page.
The PowerBI part I'm not having troubles with, it's the Dynamics part.
For example, as I understand it:
In AX 2012 and earlier, it was fairly common to access the production AX databases directly with tools such as SSMS/SSIS/SSRS in order to pull data into a data warehouse, and this could be accomplished without having significant AX expertise (as you were dealing with a SQL database like most any other). Now, unless I am mistaken, direct database access is not allowed.
As I understand it, the newly recommended approach to this is to use the new BYOD functionality (which can be easily confused with Entity Store, but that is different). However, at least two shortcomings that I am currently struggling with are:
a) BYOD entity exports are not capable of passing deletes through when doing incremental updates
b) If you are refactoring existing SSIS/SSRS work that was directly accessing specific tables, how does a non-expert determine what entities correspond to specific underlying AX tables? For that, I did somehow stumble across a reference to sometyhing that sounds useful here:
mbs.microsoft.com/.../axtechrefrep
Dynamics 365 for Operations version 1611 with platform update 3 (Nov 2016)​
The following reports provide details about technical objects available in Dynamics 365 for Operations version 1611 with platform update 3. Get more information about that release​.​
Data entities report
Aggregate data entities report​
Aggregate measurements report​
SQL Server Reporting Services reports report
License codes and configuration keys report
Workflow types report
Key performance indicators (KPIs) report
Tables report
The "Data Entities Report" looks useful, I was able to get to that page yesterday and eventually ended up downloading a spreadsheet containing data like this:
Name Public PublicCollectionName StagingTable EntityCategory TableGroup Field_Name Field_Binding
CustCustomerEntity Yes Customers CustCustomerStaging Master Miscellaneous OnHoldStatus CustTable(CustTable).Blocked
CustCustomerEntity Yes Customers CustCustomerStaging Master Miscellaneous CommissionCustomerGroupId CustTable(CustTable).CommissionGroup
CustCustomerEntity Yes Customers CustCustomerStaging Master Miscellaneous ItemCustomerGroupId CustTable(CustTable).CustItemGroupId
CustCustomerEntity Yes Customers CustCustomerStaging Master Miscellaneous WarehouseId CustTable(CustTable).InventLocation
CustCustomerEntity Yes Customers CustCustomerStaging Master Miscellaneous SiteId CustTable(CustTable).InventSiteId
CustCustomerEntity Yes Customers CustCustomerStaging Master Miscellaneous ChargesGroupId CustTable(CustTable).MarkupGroup
CustCustomerEntity Yes Customers CustCustomerStaging Master Miscellaneous IsExcludedFromCollectionFeeCalculation CustTable(CustTable).CustExcludeCollectionFee
CustCustomerEntity Yes Customers CustCustomerStaging Master Miscellaneous IsExcludedFromInterestChargeCalculation CustTable(CustTable).CustExcludeInterestCharges
Unfortunately, due to my login issues I can't get to that page any more.
But it looks encouraging. Does anyone know if there is a metadata table stored within AX that one could examine to find the cross reference between Entities and Tables? At least that would solve my problem (b) above, so would then I think only be left with the unfortunate requirement to do a full export of all data every time you want to have a completely up to date set of the AX data in your warehouse.
In my opinion Microsoft is doing itself a bit of a disservice. When someone new to the platform is trying to learn how to do reporting against Dynamics, there is a LOT of legacy information out there on "how to do <x>" that is now out of date, and there are also a lot of arguably half-true presentations from Microsoft themselves promising how straightforward all of this integration now is, when the reality seems to be something else entirely.
Having an always up-to-date, comprehensive and authoritative "Data Integration Options for Microsoft Dynamics" seems like a complete no-brainer solution to me, but then perhaps that would make some of these current shortcoming too visible?
