I observed that Many developers are using Power BI with Dynamics Ax 2012 R3 by using Direct SQL server connection on Dynamics Ax database. Which is not good approach? This will expose Dynamics Ax 2012 database outside Dynamics Ax 2012 security. Especially when you expose or deploy Power BI on public URL.
With entity Store, Microsoft provide near to real time database access to Power BI.
In this approach, you can extract data to external database by Designing entities and data extraction from Dynamics ax tables and populate that staging entities and then to database. And this process can schedule into Dynamics Ax.
This approach is faster and more secure in the sense
- Only required data available for Query, Unnecessary fields and table already removed.
- No extra overhead on OLTP Dynamics Ax 2012 Database.
- You can create more indexes on target database to faster fetch. As compare to indexes on Ax tables which creates performance overhead on Dynamics Ax.
- Instead of Creating complex data mart, cubes and complex queries. We can create simple table structure and build dashboard on them.
- Certainly external database can share and access on Internet or reporting is much more secure instead expose Dynamics Ax 2012 database for reporting.
For reference you can explore following links.
https://blogs.msdn.microsoft.com/intel/archives/185
http://download.microsoft.com/download/2/4/2/242608B8-7F6A-4A59-8AAF-545EBE5166BA/Entity%20store%20Microsoft%20Dynamics%20AX%202012%20R3.pdf
Microsoft release the Entity Store for Dynamics Ax 2012 R3 back in May 2016.
But somehow I decide to explore it. So today I decide to Install Entity store on my local VM. And My local vm had Dynamics Ax 2012 R3 RTM. No other Dynamics Ax 2012 Update Installed on machine. Entity stored is already part of Cu 11 for Dynamics Ax 2012 R3.
So First step is download and install KB. You can find It from following link
https://mbs2.microsoft.com/Knowledgebase/KBDisplay.aspx?scid=kb;EN-US;3147499
I have to enter my email address and Microsoft send download link for patch.
I recommend to explore the following link for Installation of any update for Dynamics.
https://technet.microsoft.com/en-us/library/hh335183.aspx
For this article I just extract the self-extract file.
Extraction result in folder something like
This file is again extracted exe. On its extraction it creates two folders
And again these two folder contains two self-extracted exe files..
On extracting these exes, one update for Model store and second for Kernel update.
Model Store Update:
So First I decide to install update for Model store.
Before that I stopped the AOS service. Otherwise update process takes unnecessary time.
So I Click on update Ax exe in Application folder.
Click on Next
Accept and Continue
Select you model store and Click on next.
On this steps you find conflict then first resolve them. In my clean installation there will be no conflict. Press next.
Next
Click on Install.
And finally Update is successful.
Kernel Update.
Now I decide to install what ever exe at Kernel folder.
So I go the extracted folder and run the update ax exe there
Next
Accept and Continue
Next
Click on Install
Finally it also installed successfully.
Post Installation update challenges:
After installation when I start AOS service and open Ax Client. I found check list, with different options for example database synchronization, compilation and full cil Generation.
In my case, this check list did not perform anything.
So closed the Ax client and in Windows command prompt, I run following command. Remember, run Windows command prompt as run as administrator.
AXUTIL set /noinstallmode
Now open Dynamics Ax client and perform following steps.
- Synchronize the database.
- Full compile and resolve any error.
- Generate Full CIL.
Interestingly I got errors. And errors are related to Payroll some symmentic dll. Each line shows error 99999.
I used very strange trick. Just add new empty line and add empty comment //. Save it and compile and as result error resolved. I resolved all these error 99999 by same way.
More details I follow my own link.
http://tech.alirazazaidi.com/errors-in-payroll-classes-symmetry-dynamics-ax-2012-r3/
After that, I found Entity store menus in Import export framework.
DNS and Entity store connection.
Now open Windows control panel, Administrator tools.
Select ODBC Driver 11 or it depends on installation.
Enter Name of User dsn.
Set default database.
Now test the connection.
Now go back to Dynamics Ax client and click on entity store. Select on User DNS. And set User DSN in last step. And click on test.
.