AX 2012 Excel Add-in - RPC Issue

I hope everyone is doing well, and into the Dynamics World, on this fine Tuesday. We will continue our focus on EP tomorrow, but today, I wanted to spend a little time and write about a specific issue when using the Excel Add-in for Microsoft Dynamics AX 2012. What I'm talking about, can happen in a few different possibilities. Specifically however, and more to the direct error message. If you have Failed records when using the Excel add-in and the error message for each record is very generic, you should then move towards looking at the Application Event Log, where the Excel Add-in is being executed.



Within the Application Log, you will most likely see an RPC Exception 1702 or 1700 or 1704. Now the Excel Add-in is suppose to be connecting and communicating through WCF protocol for the Query Service, and other such services? Why RPC? Well there is a part of the .Net BC still used, from what I've found - needs clarification - with this process.

So how do we reach this error? Well most likely something has changed about a table your trying to access, or a Data, ie: Query or Service, that your trying to consume. For example you might have added a new import staging table to your instance, saw it fine in the Add Data, Add Tables list.



However now that you go to publish the data, from which you have built the excel table with - all records fail? This is a sign, that a Full CIL needs to be executed on the target instance in which the Excel Add-in is pointing to. This has been the fix for such issues, I've come across - every single time!

In short, running into errors with the Excel Add-in, specifically RPC errors in the Application Event log, then perform a full CIL compile on the target instance. Doing so should get you past such issues, and should allow you to proceed using Excel Add-in and your changed or new table - for loading data.

That's all for right now, I will leave you with a link to the past post I did about the Excel add-in.: AX 2012 - Excel Add-in Revisited. We will continue the EP and BI paths, as well as I have a slew of highlights, book reviews, and spotlight post coming soon. Along with a new interview from a Microsoft Executive, to help close out FY12 for Microsoft. So check back soon, and often. Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012 - Excel Add-in Revisited

With the release of Microsoft Dynamics AX 2012, came some great new ways of achieving business requirements, and user experiences. One of those is the Excel Add-in for Microsoft Dynamics AX 2012. I first wrote about this topic, back in June, in which you can find here: Microsoft Dynamics AX 2012 Excel Add-in. This has been a hot post actually, with a lot of comments, and a lot of visits.

It's rightfully so that it should be, being that it's new, and there is some confusion on how to do some of the more complex data sets, within AX, that go beyond just a simple table, which that first post covered for us. With that in mind, I thought I would help clear the air a little bit, in how the Excel Add-in is actually very flexible, and something to watch out for in December from Microsoft on this topic.

First, lets look at the starting point, in which we can start to work with more advanced data sets in AX. This concept, is enabled through the use of document services. This is the way, in which complex data sets in AX are exposed internally and externally of AX 2012. This is true, even for the Excel Add-in.

From the excel point of view, what we are talking about is the Add Data ribbon button, and when the drop down appears clicking on the Add Data, instead of Add Tables.



When clicking on this, for the first time, if you've not setup any Document Data Sources within AX 2012, your selection options will be blank. Now we have talked a little bit about this new concept of Document Data Sources, in the following post.: AX 2012, PowerPivot and a Dash of OData Feeds

In that above post, I covered with you how to enable Excel 2010 PowerPivot to work with OData Feeds from AX 2012. This was enabled through having Query Type of Document Data Sources enabled through the Organization Administration module, Setup, Document Management and finally Document Data Sources.



Here we can also add a document data source, of type service. These services must be document services and extend from the AIF Document Services types. These can not be custom services that do not extend as part of the AIF document services.

With that in mind, we can add a couple of document services, to our list, from within that form, as seen below.



Now that we have these services as document data sources within AX 2012, we can now go back to Excel 2010, within the Excel Add-in and click on our add data, and see our two new services we can make use of.



With this, now we can work with more complex data sets within AX 2012. This also means, that Office 2010, very much is a major part of the user experience in regard to AX 2012. This also means, that some very complex data set needs, can be accomplished through the use of Query Objects, and Document Services that wrap those query objects to expose them internally and externally of AX 2012.

With this said, there are some issues, and area's to watch out for. Basically, there are some super normalized data sets, like customers, vendors, etc. To help address those needs, Microsoft will be releasing in December a resource kit, that will contain artifacts that help enable the use of the Excel Add-in, for the master data, that has some super normalization aspects to it, that make it a little harder to work with from Excel. The basic concept will still be used that you see above here, that will be enabled with staging tables that the process will still use document services to enable.

I hope this helps further explain the value of the Microsoft Dynamics AX 2012 Excel Add-in and how it can be used right now with even more complex data sets, than just simple tables. You enable this through the use of Document Services. Also, it's very important to understand that Microsoft will be releasing that resource kit to help out with some of the super normalized master data elements. Finally, you can create your own document services, that wrap your own custom data sets, or query objects, that in turn can be consumed and made use of in a bi-directional nature through Excel. This is a huge benefit to customers of AX 2012, and something that needs to be considered when thinking about designing and developing scope for customer.

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

"Visit the Dynamics AX Community Page today!"

Microsoft Dynamics AX 2012 Excel Add-in

With the release of Microsoft Dynamics AX 2012, the lines between the Rich Client, the Portal as well as Microsoft Office are going to be blurred. This is out-of-the-box ability, that replaces a lot of older ways for doing things.

One such topic, is the old Microsoft Excel Export / Import Wizard for Microsoft Dynamics AX. You can see this, in the Microsoft Dynamics AX 2009 menu system, here.:


With the release of Microsoft Dynamics AX 2012, this no longer exists! For some, I'm sure this will be a little shock, however this is a good thing.

The older Excel Import, honestly was very slow and if you had any records of any size, then you just really would rather create X++ scripts to import data.

Now, with Microsoft Dynamics AX 2012, and the new Excel Add-in for Microsoft Dynamics AX, scripts are a thing of the past. Let me say, this new process is very fast, and is designed so that a developer or architect is really not needed to import a lot of data, into AX.

To help show this process, I have created the following walk through that shoudl give an idea of what it takes to work with the new Dynamics AX Add-in for Excel, and get data into Microsoft Dynamics AX 2012 using this new, and great tool!

First, you have to launch Microsoft Office Excel 2010, and of course have the Excel Add-in installed. I'm going to assume this is already in place, and lets say, for the sake of keeping this simple, that I need to add some Delivery Mode data.

This data, lives in the DlvMode table within the Microsoft Dynamics AX AOT. So to get started, lets click on the Connection button, from the ribbon, and make sure we are in the company account and instance we care about.


(Server specific information, blanked out on purpose.)

We see, that we have the ability to override here, and point to what AOS instance we want to connect too, as well as within that what company entity.

Now moving forward from here, lets go and add the DlvMode table, by clicking on the add data, then add table.



Once we select to Add tables, then we can select the DlvMode table, and actually use the Filter field at the bottom, so we only see that table to select. Also notice the fact, that the process created a new worksheet, in this workbook, just for our DlvMode AX table.



Here we can add and remove fields, using the Field Chooser, on the ribbon. Here I've added a few fields, just to give you an idea.



Now moving forward, I will only have the key field Mode of Delivery as it is required, and then Description field.



I then took and added new rows, which gave me three records within the table, of the Excel worksheet. From here, I added my three new DlvModes.

At this point, we now have the data we need, and want to get this into Microsoft Dynamics AX 2012 instance. With this, we simply take and Publish Data, then Publish All. In doing this, we could have selected only one record to publish, but for now lets just publish all of them.

With doing this, we see the following worksheet that was generated, that shows the status of the process.



Having this, we can see that everything went great, and was able to upload the data. Also, we now see, on the DlvMode worksheet, that our new data, and the existing data that was already in the DlvMode table, now appears.



Going into Microsoft Dynamics AX 2012, we can still hit Ctrl+D, and it will open the new Developer workspace. Here we can expand the AOT, and go to our DlvMode table. Right click, and table browse, and see that our data we just created, exists within Microsoft Dynamics AX 2012.



So with the release of Microsoft Dynamics AX 2012, a lot fo great new things come as part of it. More native Office integration, with very little effort, and very little technical knowledge needed.

That's all for now, but this is very exciting stuff, and we are just getting starte within the great new release.

Till next time!

Update: I wanted to make sure an update this post, so that you would have the latest information about the Excel Add-in, in case you hit this post, from a search engine. Make sure and check out the revisited post for the Excel Add-in located here: AX 2012 - Excel Add-in Revisited



Visit Hillstar Business Intelligence (www.HillstarBI.com) in order to truly unlock your data trapped in your Microsoft Dynamics investment. With our value driven business intelligence strategy Hillstar help you transform into a data informed company.


"Visit the Dynamics AX Community Page today!"

More on the Future of BI with Microsoft, Dynamics AX and Project Gemini

In the past I mentioned Project Gemini, which will have a large impact on new features, and abilities for doing BI reporting, analytics in regard to Microsoft stack, including Dynamics AX.

The following is a great video that really goes into project Gemini, and covers what Gemini is.
Ch. 9 Project Gemini Video

From the details of the Video:
"Need to make timely business decisions without having to use complicated and sluggish analytical applications? Love to use Excel? Project Gemini is an Excel 2010 add-in that allows you to create powerful analyses by quickly manipulating millions of rows of data into a single Excel workbook and utilize Microsoft Office 2010 to share and collaborate on your insights with your team."

And..
"For all you spreadsheet jockeys out there, this should prove extraordinarliy useful to you when you need to crunch and analyze lots of data effeciently. Wow. 1,000,000 rows in Excel without causing a hang."

So this whole 'BI for the Masses' will start to have some real meaning behind the term, once Gemini becomes a reality.

Another great resource on this, can be found at the following link.:
Project "Gemini": Build powerful analytical applications



"You can combine native Excel 2010 functionality with Gemini’s in-memory engine to allow users to interactively explore and perform calculations on large data sets. In addition, you can easily streamline the process of integrating data from multiple sources – including corporate databases, spreadsheets, reports, and data feeds.

Share and collaborate with confidence by easily publishing your analysis to SharePoint 2010 and have other users enjoy the same slicer and fast-query capabilities when working on your Excel Services reports."

So now, with Office 2010, and the next release of Dynamics AX 2011, Analytics and Ad-hoc ability to really offer BI to masses will have a dramtic face lift and new offerings. This includes collaboration making use of SharePoint, which is already a big part of the Dynamics AX platform.

This is going to add to, the already existing abilities of BI options with Dynamics AX, and will add to also the ability to talior Role Centers and add meaningful data points and reports / abilities to those users that need those Dashboard or Role Centers.

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




"Visit the Dynamics AX Community Page today!"

Dynamics AX Add-on: Atlas XL

Recently I wrote about the possible reporting options that exist inside Dynamics AX 2009.

Dynamics AX 2009 - Reporting Possibilites

I did however leave one area not covered, which is important, and very useful. That area is third party applications or packages for Microsoft Office, specifically Excel add-ins.

One that I have seen used, and works great in Dynamics AX 2009 is Atlas XL developed by Globe Software Pty Ltd in Perth, Australia.





This has an integration with Dynamics AX 2009, that makes use of the .Net business connector to expose the full data model, including business logic, X++ based calculated fields.

Once the server peice is setup and the Excel Add-in is installed and configured for end users, the usage is pretty easy. Super user can create their own Ad-hoc reports, pivot tables, etc. and access company data, they have security rights too, as this takes on the security rights of the user at hand for access to Dynamics AX objects inside the instance.

There is other Excel Add-in's that exists as well, but this is one I have seen in action, and one that works with nearly all AX versions, not just 2009. I have seen this fully working though, in 2009, making it a proven product.

If your interested in Atlas XL, then feel free to contact Sunrise Technologies, Inc. as we can get this for you, set it up for your instance and get your using it.

Other than third part Excel Add-in's, it is possible to create custom OBA or Office Business Applications and Add-in's for office products like Excel. Sunrise also has the knolwedge and skillset to do this for you, if needed.

Well that all for now, I wanted to make sure and give a complete picture for reporting options with a Dynamics AX 2009 instance.

Check back soon!




"Visit the Dynamics AX Community Page today!"