AX 2012 - Run Reports without hitting the Production DB

Update: a reader of this post pointed out that this would not work for the setup using log shipping. Using log shipping is for HA, and he is 100% correct. The log shipped db will remain in standby mode and therefore the .net bc would fail in connecting to it. So instead of using log shipping replace that concept either with replication, or mirroring. The other points that people have brought up, including the same reader, is the cost for the license for the SQL server as well as the extra AOS needed. This is true, however if the desire is to offload the processing of reporting resources from the production, transactional db, then the cost of the extra license would have to be used in order to weight the value in which such a setup could bring. With this, still it is possible to have a reporting database that is different than the production transactional db.


I hope everyone had blessed and wonderful holidays! I hope that you got plenty of time with family, friends and loved ones. I wanted to make sure and get one last post in, for the year 2011. Man, what a great year it has been, for the Dynamics Community and Ecosystem.

There are plenty of great wrap up post going on for 2011, as well as some predictive post as well. So, for my 800th post on this blog, and to close out 2011, I wanted to leave you all with a late Christmas gift, if you will. That is, as the title of this post suggest, a way to run AX 2012 Reports, without hitting the Production Database!



What we see in the above image, is the reporting architecture diagram from MSDN. This explains for us, the steps in which a typical, modeled driven solution report executes and fires from menu item execution, to rendering within the AX Rich client that is hosting the report viewer control.

Now lets take this understanding, along with how to setup multiple instances of SSRS on the same server role, and use this understanding to design a solution that will enable us to run all AX reporting from a Reporting Database. This includes both out-of-the-box and custom developed AX-SSRS Reports!.



Important: This approach has not been widely tested. This is not a solution from Microsoft, but something I've been thinking about how to achieve myself. Use this at your own risk, without any warranty or guarantee's.

So, in order to properly understand how we can do this, safely, and securely, and be able to maintain and support such a design, lets revisit a simple architecture that shows the server roles of how a standard AX-SSRS is setup. To help better understand this point, I've created the following diagram in Visio, that illustrates such a standard setup.



With the above, we see a simple architecture that shows off how SSRS and AX work together. It's via the application layer, and how the default .Net Business Connector configuration is used on the SSRS server role, to point SSRS to the right instance of AX.

The AX instance, has within it, the information that connects it to SSRS. This way when the menu item is fired, from the Rich Client, the AOS then uses the information contained within it's setup about Reporting Servers, to understand what SSRS instance to fire.

Since the default .Net Business Connector configuration for SSRS is pointing back to the same AX instance, when the SSRS instance is executing the reporting and firing the Data Extensions to get to the correct Query object and therefore data, then in this standard approach, we are hitting the production database for your instance of AX. Of course, this could be any instance, we are saying production because the goal is to offload the resource needs of processing reports, away from your production database.

Now that we have the established understanding of how AX and SSRS works, along with the understanding of how we can actually place a specific config file, that will point SSRS to a specific instance of our choice of AX, lets move forward with our new design.

To help understand this concept, I've created the following diagram in Visio. This illustrates our new, beta>, reporting architecture configuration for enabling the execution of reports, against a replicated reporting database instance of AX 2012.



What we see here, is our simple reporting setup, and we are taking the information we have from setting up multiple instances of SSRS on the same server role, and pointing SSRS, when processing it's data needs, to interact with a different AOS, that is pointing to a log shipping replicated database of AX 2012. So, with this we still have the ability for all the out-of-the-box reports to be fired, as well as executed in batch, server side, EP, etc. but the actual hit against the production database does not take place. Instead the data is accessed from the replicated, new reporting database.

The critical keys that enable this concept are, (1) having the production instance of your AOS and Reporting Server setup, pointing to your SSRS server role within AX. (2) Changing what AOS the SSRS server hits for processing data, by deploying a custom configuration file. (3) Log Shipping is enabled at on the AX Production database, so that the AX Reporting Database is kept up to date with live data.

Keep in mind, with this setup, that all users interact with your production AOS. They never interact with the reporting instance AOS.

Now there is still a concept we have not addressed, and one that is critical to the success of this design. That is code promotion, or change management. Why does this matter? Well simply put, it's because the model store that represents the application now lives as a part of the production database. This then gets replicated when any changes take place.

If we are following the guidelines given to us by Microsoft, for promotion of code to a production instance of AX, via Model Store Files, then we should be fine with this solution. The reason this is the case, is because in doing this, we perform a full compile and full CIL compile within the QA / Test environment. in doing this, and then correctly draining all users from production and shutting down all AOS(es), within our instance. We can then safely move the entire Model Store File, into our production database for AX. Further, we can do the same to our log shipping, reporting database of AX as well.

This means, that before we did this action, we would need to turn off log shipping, while we are importing the model store files into production, and also the reporting database, which is a replica of production. After this was completed, and before enabling users the right to get back connected to AX, enable log shipping again, and your solution is back on-line, with any new changes.

Further to this point, if as part of that new reports were brought in as part of the model store file move, you would deploy the reports from the AX production instance. You would not deploy them, via the reporting instance.

So, with the above information, we have a possible solution that could enable the use of Log Shipping, and some configuration tweaks, that would allow our Production Database to not be affected by Reporting resource needs. Instead the reports are executed against a replicated database. This, in theory then, would work for all reports, including batch scheduled, server side and EP. I do welcome any feedback on this concept from the community. If you feel I'm overlooking something, or have failed to bring to light some critical point, please share and I will update this post.

Well that's it for me for this year. Thanks everyone for reading my blog, and supporting me in this effort. The reason I write is because of you, so thanks and have a blessed and safe Happy New Year! May 2012 be the best year for each and every one of you! Till next year!

Important: This approach has not been widely tested. This is not a solution from Microsoft, but something I've been thinking about how to achieve myself. Use this at your own risk, without any warranty or guarantee's.


Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

Merry Christmas!

I just wanted to take the time, and wish Everyone a Merry Christmas! I will be back writing some next week, and so will wish you all a Happy New Year then.

May your Christmas be blessed, and Happy Holidays! Thank you for reading my blog, and always giving me challenges that make this social exploration of Dynamics knowledge so fun.

For an early Christmas present, if you will, the following is a blast from the past. First up we have: Twas the night before implementation

Finally, if you dare, When squirrels attack! - A True Christmas Tale



Merry Christmas!

Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 - Items & InventItemSetupSupplyType Table

In past post, I've covered the usage of services for performing operations external to AX, as well as internal.

Some examples that have done recently, were focused around Creating Products in AX 2012, via the EcoResProductService.



As well as creating the new Trade Agreements, via PricePriceDiscJournalService.



The point that I've tried to get across in these examples, is to show how this can be done within AX 2012, as well as the same concepts and constructs can be used externally of AX 2012. This is the right move, specifically if your looking beyond AX 2012, and beyond AX7 even.

With this concept, you can also Release Products to a specific legal entity, which is covered pretty well, via a C# example, found at the following resource.: Product-item data management services.

So again, we see how both X++ and C# can use these constructs, Document Services, to create products, as well as release them to a specific legal entity. With this said, there is something missing, for this process.

Specifically, if we look to have our migrated products, that have been released, to show up on the Purchase Order Lines, Item Id field drop down. If we go to do this, and we created products, and released them for use within a specific legal entity, via the services described in the above references, then our items would not show up for use on Purchase Order lines. They can be entered, but do not show up.



The reason this is the case, is because in AX 2012, the Purchase Order Line, Item Id datasource field, has a new lookup() form it uses, called InventItemIdLookupPurchase. This form, has a datasource, that includes the new table: InventItemSetupSupplyType. [InventItemSetupSupplyType Table [AX 2012]]

This new table, contains sourcing information, related to the Item that is being released to a specific legal entity. When this release takes place, the AX Rich Client UI, will fire the EcoResProductReleaseManager object that has logic which creates a default entry into the InventItemSetupSupplyType table, with a Default Order Type of Purchase.

When creating products and releasing them through the services, specifically for the InventItemService, [InventItemService Class [AX 2012]], it makes use of the AxdItem Query object in AX 2012, in order to create records via.



Since this is the case, and we can see from above that the InventItemSetupSupplyType table does not exists as a datasource, then when creating records via this design concept, will not satisfy the requirement of adding entries into the new table, of InventItemSetupSupplyType.

Therefore, there is a small disconnect between the UI release process, and the document service approach for creating and releasing items. This is a simple fix however, either invoke similar code, found in the EcoResProductReleaseManager class for the createInventItemSetupSupplyType() method. Or the other option, could be to add the datasource, to the AxdItem query, for the InventItemSetupSupplyType table, and regenerate the InventItemService, document service, so it will reflect the new entity within it's WSDL.

Depending on the scope of what your doing, should guide you on which choice you make here. If it's throw away, migration code, the simple, fast option of just invoking the X++ code that lives within the EcoResproductReleaseManager class would be advised.

However, if your doing a longer, value add modification, or integration, then, looking to modifying the InventItemService, via the AxdQuery object should be considered.

Well I know that's a little deep dive, on something very specific, however such deep dives are warranted at times. I believe, it's important to understand this, from a process point of view, but also see the value in showing choice. The ability to solve a need, there is multiple ways. Depending on where the most value is, should guide your choice, for the design and the development of answering a specific scope need.

That's all for now, but check back soon as a whole lot more to come. Till next time!

Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 - AX-SSRS Report Design Concepts

Recently I have been focusing on the BI and reporting aspects that come as part of Microsoft Dynamics AX 2012. There is a lot of great resources for this topic, and it's a topic that drives a lot of value, when speaking about AX 2012.

Since this is the case, I wanted to continue my focus on reporting, with this blog post, focused on the two categories, if you will, of types of AX-SSRS reports. These are Model Driven Solutions and Code Driven Solutions.


Model Driven Solution Diagram

First up, lets talk about the model driven solution, in terms of AX-SSRS reports. When speaking about the model driven approach to AX-SSRS report development, the focus is around the use of Query objects, from the AOT, to model your report with. The idea, with such reports, in that very little X++ code is needed to enable them.

This path, should be favored as much as possible, as it's the least amount of TCO in terms of report development. With this approach, and with report design in general, we have the report itself, and surrounding it is the design, data and control aspects of the report.

When talking in terms of the model driven approach, the design, as you can see from above is created within Visual Studio 2010. Further, the data aspect, is represented via a Query object, and a good example of the control for such a designed report is ranges.

Keep in mind, when thinking about such design patterns, that the Query object is meant to be a re-usable API in AX 2012. The same query object that powers a AX-SSRS report design, could also enable a PowerPivot report, or could be used to help define and deliver Contextual BI elements, say for an InfoPart.

Moveing forward with this thought process, lets look to the next category, for AX-SSRS report design. This is the Code Driven Solution.


Code Driven Solution Diagram

With this concept, we have our basic design elements, or constructs for reference. It's the design, data and control points. Looking at the above diagram and comparing the code driven solution vs. the model driven, we see that at the heart of the design, is still Visual Studio 2010.

Beyond that though, these are much different than the Model driven solution, and the use of queries. The idea for this concept, is when code is needed to create the data behind a report. When this is the case, the data, for the above is represented by RDP, or Report Data Provider. This is a new class type for AX 2012, that enables the ability to design more complex reports, when the need to "mash" data from different sources arises.

Moving along with this concept, we also see that for the control, we have a Data Contract. This too is new to AX 2012, and we have seen some of these, in my coverage of services in AX 2012..

The data contract, when used in a design pattern that is laid out in the above, is meant to act as a simple class, that ties extended data types, that represent the data contract properties, to the report design itself. Enabling, therefore, the correct formatting of data, based on underlying table and data type designs.

With these two concepts in mind, we can start to move forward with report design. A big focus, and understanding point needs to be seen here. That is, you don't see the mention of BIDS, or Business Intelligence Development Studio. That is the standard SSRS design piece. The reason this is the case, is that VS2010 and a special project type are used to enable the creation of AX-SSRS reports. BIDS is actually hosted within VS2010, at least the design space is, so once in the VS2010 project, it's very familiar process for those use to using BIDS. However BIDS, and standard SSRS reports are not meant to be used in delivery data, with AX 2012.

To help you further, with learning these concepts, there are a couple of resources I would like to point you to. First, the Microsoft Dynamics AX BI team blog: Dynamics BI Team Blog. This is a resource I have highlighted before, and one I want to make sure everyone is aware of and makes use of.

Also, the same team, behind that blog, creates YouTube videos, at the following YouTube channel: Dynamics AX BI YouTube Channel. Here again, we have another great resource, in which the main topics of this post are discussed more fully, and taking to a deeper level. Some really great video's out there, so I highly recommend reviewing them, as you can.

Finally, Microsoft recently published the Whats New: Reporting for Developers in AX 2012. This is a really great resource, espically for those that have done AX-SSRS report development in AX 2009. All the concepts contained within that page, need to be fully understood, so that you can take full advantage of all the great improvements Microsoft has given us with this release, around report development.

Well that's all for this post, check back soon though as there is a whole lot more to come.

Till next time!

Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 - Upgrade Questions Answered

Recently, I took part in a community effort, in order to drive awareness, as well as social connections for the Dynamics Community. This experiment, was focused around the Microsoft Community Site, hosted a set of questions around AX 2012 Upgrades.

Several people took part in this, for submitting questions, and giving answers. The following link, will guide you to this first iteration of such an effort: Microsoft Dynamics Community: Dynamics AX 2012 Upgrade Questions……Answered!

An Example, from this post, can be seen here:
"Community Question #1

First of all I want to say I love the initiative! Great to see that Microsoft is working pro-active! My question is related to the licenses. There will be different type of roles which will play a part in the license value for a given customer. Image I have a lot of project manager which will be functional roles. Are there any limitations regarding availability of different legal entities? With other words, can a functional user use/see all the legal entities deployed in an AX2012 application?

What about custom security roles made by the partners? How will they fit into the license layout..."

And the answer I provided:
"First, lets address the fact that AX 2012 License model has changed a lot actually. It's for sure a much simpler approach, and closer to how Microsoft license it's other products for use. The best source, to help understand the new license model, in full can be found from the following PDF, white paper guide: Download Whitepaper

With this in mind, it's very clear that the mappings of named user types, to menu functions, and therefore roles will have to be updated. For example, currently, if you basically touch anything with a journal, for updating say, then your user will be marked as an Enterprise Named User License. This is not the vision Microsoft had for this, so there is a planned revisit to get the menu functions, and therefore ultimate mapping of users to Named User Types updated.

With that knowledge in hand, lets move to the more direct question, is the ability to cross legal entities for performing work. This is clearly stated as a goal, if a user is meant to cross legal entities they will be considered an Enterprise Named User. That make sense, in that, if your crossing legal entities, you are no longer a Functional or Task user, because you are truly doing functions at the enterprise level.

Finally, lets address your question around custom roles and security. In terms of reference to the Named User types, when custom menu functions, and business logic is created, either via an ISV, or the customer itself, the Microsoft Named User license count is not affected. If you look, for example, and create a custom menu item, which is how the Named User counts are tallied and linked, you will notice that None is set, instead of Functional, Task, Enterprise, etc. To the license point for ISV there is a different license model that ISV's can use, that for a customer will have to maintain that either through their VAR, or through a direct relationship with the ISV who is providing you service.

One note, I will make about this, is around AX in the cloud. When this is the approach, and the Partner Hosted model is in place, you pay by month, per user. The named user license still can come into play, however you only upcharge, if you will, when you add another user. So for example, if your implementing AX, via the cloud, and only need 5 users at first to get the implementation going, that's all you will be billed for, in the months those five users access your instance. When the next month rolls around, and say you add a cost accountant, for working in your AX implementation. Then this will add a sixth user license count and from that month forward is when you will pay the difference added to your bill for that use."

I would like to thank Andy, and the team at Microsoft for allowing me to take part in this effort. I look forward to such future engagements, as I believe this really helps connect the community. Using the power of social media, to get real world questions, answers and the sharing of knowledge.

If you have a burning question, feel free to leave me a comment on my blog, email me, and of course let Andy at the Microsoft Community Know. It could help shape the next topic, for when we do such an effort in the future.

That's all for now, but check back soon as more to come! Till Next time!

Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

Spotlight: Dive into IDMF for AX 2012

From time to time I like to spotlight some great content that is being posted out there in the big wild social media landscape that is the Dynamics Ecosystem.

Today I thought I would spotlight, AXWonders.blogspot.com. This blog is ran by a fellow Sunriser, Eduardo Arias. He is a Senior Technical Architect like myself, and has a passion for AX. I recommend you bookmark, and subscribe to his blog feeds.

What I wanted to specifically spotlight was his recent dive in IDMF for AX 2012. I've talked a good bit about the value this has in the past, specifically around AX 2009 and the fact that this was being updated for AX 2012. Well last week the news came out that it has been release, which you can find more about this on InformationSource.



From the post: "There has been a lot of talk about the Intelligent Data Management Framework (IDMF) in AX 2012, and for a good reason! This is because the Intelligent Data Management Framework allows the system administrators optimize the performance of Microsoft Dynamics AX installations, which is something that we all should be concerned about when implementing AX 2012."

I wanted to make sure and tell Eduardo Arias thanks for sharing with the rest of us, and giving us some highlights from the recent release of IDMF for AX 2012! Keep it up!

That's all for now, but check back soon as more to come!

Till next time!

Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 - Hiding a form control without code

With Microsoft Dynamics AX 2012, the whole concept of Model more and code less is in full affect. The idea behind modeling, is that you cut down on the need to create custom code, to achieve needs. This is a great overall concept, and we are seeing this in Workflows, and well as Security.



So taking this to heart, and running with it, lets say you have a request to hide a form control, based on some context. Lets pick a functional one, in that we don't want users, who have read only access to a form, to see a specific form control.

In the past, real simple steps could be taking to do this, and a popular way of handling such a request, would be to (a) Make sure the control had AutoDeclaration set to yes, and (b) override the init() method of the form, and work with the forms .visible() property. This would be based on either, say the company, or a security key in the pre-AX 2012 days.

This however has changed with AX 2012, and we can achieve this need, without any code actually. How you may ask? Well check out the following Microsoft resource: Security Permissions Properties for a Form [AX 2012]

"To see a particular control on a form, the user must have a permission to the control that is at least as strong as the permission the control requires.
For example, suppose a control has its NeededPermission property set to Update. A user who has only Read permission does not see the control on the form. But the control is visible to another user who has Update or Delete permission to the control."

So lets explain this a little bit then, shall we? To start, we have a need Permissions node that lives under the form objects now.



With this, we see, as mentioned in the above resource, the Read, Update, Create and Delete Permission nodes. Each of this are in order of weakest to strongest security. In that, if a user has a privilege that enables them to have Read access to the form, and then control had the needed permission of Update, the user Would not see the control on the form. Since this is the desired scope, then we understand how we can relate privilege's to NeededPermission.

When looking under these nodes, you would not drag or drop controls under each. Nor do you create new elements, under the Controls node. If, we want to take a control, and have it's NeededPermission to be set as Update, then we must go to that Control, open the properties, and set the NeededPermission value as so.



On saving of this property change, we can now see that under the Update node of the Forms Permissions, contained within the Controls section, we see now our 'Control2' has appeared.



Now through proper security setup, when users only have Read level permissions to our form, they will not see the Control2, form control. So we have used security modeling aspects of AX 2012, and not code, to enable the visibility of a control on a form. Pretty powerful huh?

Well that's all for this post, I hope this helps someone out. Keep checking back as we continue to dive more and more into AX 2012.

Till next time!

Follow Me @:
    

"Visit the Dynamics AX Community Page today!"