Like many organizations, my client had noticed the loads of awesome competitive advantage and Business Intelligence that SharePoint offers. The problem, however, is that SharePoint and Dynamics AX are both complicated by themselves. When you have to deal with two complicated systems and make them play nice, things become exponentially harder. After many months of failed attempts, my client contacted me with one goal: could I get SharePoint 2013 and Dynamics AX integrated for reporting purposes. I got them integrated, but my client remarked about how much money and time it would have saved his organization if someone wrote something that talked about how to integrate these two awesome products for reporting. He told me it would be great if some AX Architect took the time to write from his experience on how to get AX reporting working in SharePoint integrated mode.
And presto.. An idea was born. USING THE LATEST and GREATEST SOFTWARE,
we will take Windows SERVER 2012R2, Dynamics AX 2012R3,SQl Server 2014, and SharePoint 2013 SP1, and we will get AX working in SharePoint Integrated Mode.
Disclaimer: After having setup and fixed a number of implementations, I can’t guarantee that you will share my opinions of how to setup a BI reporting infrastructure with Dynamics AX. These techniques are my own after all. Furthermore, every organization is different. I often have to change strategies depending on the implementation – there no such thing as an out of the box solution for all with an ERP system. But everyone needs a baseline – a working case. And if you follow this series, you will get that and understand how to install a SharePoint BI infrastructure for Dynamics AX, something very few organizations have…
First, Create a DNS Entry as we will make a separate web application dedicated to SharePoint Dynamics AX Reporting
You do this by going into DNS manager and creating an entry like any other host record. What we are doing is making sure this can be found for later when we connect AX to reporting.
Second, Create a Web Application in SharePoint 2013 using the new host record
This part is absolutely essential. Web Applications give you two advantages: First, you can isolate and control resources which is very important on any reporting installation, not just AX. Second, you can adjust security settings for the complicated implementations where the documentation becomes next to useless. See, what I’ve done here is simply map my web application back to the host header. I have given it a nice friendly name that identifies why it is there, so that the infrastructure guys will be able to easily recognize it and let me know when something strange happens. IMPORTANT UNDOCUMENTED TIP: be sure to NOT allow anonymous authentication or AX will have trouble later.
Remember: you can easily add an a web application through the Manage Web Applications option in Central Administration.
If you go through the settings, notice all the security configurations that allow us to handle all the different patterns that different companies will present in security configurations. This is important: to save yourself a lot of stress, I recommend creating a web application here. Give it a friendly name, but make sure that you run the application pool under the business connector account, which is called bus_prod in this case and don’t let SharePoint change the password when you add it as a managed account.
Finally, notice how in a moment of true power, I’ve been able to add several service applications that are not even possible in SSRS native mode. I have Excel Services plus some nice Usage and Health Data Collection to help me manage and keep my AX SharePoint Reporting infrastructure healthy(because it will be very busy)
And we have our web application but there is still more to be done. Powerview and Powerpivot both need to be installed also before we install the Dynamics AX SSRS extensions.
So, part 1 was simple but extremely important. We learned of a concept that I feel is essential, which is the need to manage AX functionality by Web Application. We also learned some other things, such as the importance of naming and what not for event logs later on. We are also starting with a very minimalist setup in that we are running 5 Service Applications – in other words, we are going to add some service applications, but we aren’t going to be running a bunch of things that we don’t need. We want a central location to manage our BI infrastructure for ease of maintenance, but we also want the super duper benefits of SharePoint Business Intelligence. We are getting there.