OData Query Utility for Dynamics AX 2012

Well I hope everyone is doing well, and finding yourself very productive in this Exciting Week for Microsoft Dynamics. Of course, I'm talking in reference to the now released Microsoft Dynamics AX 2012 R2.

While that for sure deserve's a lot of attention, there is something I wanted to give to the community at large, as my Christmas present if you will to everyone. What I'm talking about is an Open Source Project I created called.: AX-OData Queries



As the above image implies, it's the gift that keeps on giving! That is, this open source project has ready to use, a Utility Wizard, and context action that makes understanding which Queries in a Dynamics AX 2012 instance, are valid candidates to become PowerPivot OData Feed sources!



Further as part of this project, there is a Library component that I started, in which I have placed a customer aging query, in the standard folder location. You can read more, by visiting the project's home page.

Further, important, is the documentation, so the following is a direct link to that.: AX-OData Queries Project Documentation. Make sure to read the Read Me, and then following along from there. This includes from Install, through to How To Use Guide.



The above image is the Dynamics AX 2012 project that currently comprises as v1.0.0 of the OData Query Utility scope. This again, includes a Wizard as well as an Context menu, that lives under the Add-in's section.



Like all projects, this needs revision and refinement. This is an Open Source project, and so I do encourage you to get involved as you can. Help make this better! Further, the changes and impacts of AX 2012 R2 has not be tested against this. So there is plenty of chances to make this a much better set of tools, and queries for common data sets.



As-is, you can download the Utility as an XPO file, and import it into a target Dynamics AX 2012 RTM or Feature Pack instance. Then you can either run the Wizard, or you can execute the Context Menu for a specific single query element. The goal, is to help enable the continued theme of creating a System of Engagement with Microsoft Dynamics AX. This specific will help on the Personal BI landscape, in helping faster enable use of PowerPivot, via OData Feeds.

Well that's all for now! (Early) Merry Christmas! As updates happen, of course I will take the time and let everyone know. Please visit the project, download, test, enjoy, contribute! Till Next Time!

Update: There was a slight feature added this afternoon. One that I thought was important to make the first grid, in which you filter the searching of queries by layers. We have added a new field called "Number of Queries".



Now I admit the speed in which the utilElements table can be read, which is a key to the majority of this, is not at the level I like. This is one of the area's that will be focused on next.
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

MSDW: Article on Self Service BI and Dynamics

Well as promised, I have a new article I wrote for MSDynamicsWorld.com, which can be found here.: Self Service BI - Where the Rubber Met the Road in 2010 for Microsoft Dynamics

In this article, I try to cover how we have arrived at where we are today, why it's important, a high level of technology involved, and a real aassement and hopefully push for the concept of self service BI.

I try to capture the Vision Microsoft has with self service BI, that goes beyond Dynamics itself. And the fact that Microsoft Dynamics AX, and those companies that invest in it, can make use of this vision.

I hope you enjoy it, and many thanks to Jason and the MSDynamicsWorld.com team for allowing me to write for them.

Check back soon as more to come!

"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!"

Dynamics BI: BI for the Masses

Recently Microsoft made the announcement that Office PerformancePoint Server will soon become PerformancePoint Services for SharePoint Server.

The move can best be described by the recent statment from Microsoft on their Partner Program entry about BI: (Link Here)
"The Microsoft business intelligence (BI) strategy is to use the tools found in Microsoft Office SharePoint Server and Microsoft Office Excel —and the scalable Microsoft SQL Server business intelligence platform —to deliver BI to anyone in any organization. This strategy enables customers to deploy complete BI solutions through existing investments in these three key Microsoft products."

So when have been talking about Office being more and more the front end for Dynamics AX for years now. And with some of the confusion that has surronded the BI road map and approach in the past, this move seems to be the best move forward.

Now we do have a clear direction on 'BI for the masses'. And this direction has been bulding up for the past several years, and it started with the release of Dynamics AX 4.0.

With this new path, we can now really start to focus on using SQL Server's BI offerings with OLAP mixed with SharePoint and PerformancePoint Services for Dashboards, scorecards, KPI, and make Excel the front end for view and using this combined information to make Office Business Applications and Custom BI reports for customers. We also use this platform to enable our customers to create ad-hoc BI reports with much ease --- once all of this is in place.

The move can start to happen now though with SQL Server's OLAP, and Dynamics AX 2009 and Office 2007. You can already create great BI reports and dashboards inside Excel with today's technology that will build up and allow you to add to that the PerformancePoint Services offerings that will also be delivered via SharePoint.

Now I did mention something else in there, OBA or Office Business Applications. This concept was introduced with VSTO in Visual Studio 2005 and Office 2007 and the offering really expanded with the release of .Net 3.5 and Visual Studio 2008.


Image source from the AXStart blog located here: AXStart Blog Post

So OBA's exists and can be bought or custom made by partners and customers. These are Office centered applications, code in managed code for offering up libraries, and full applications that achieve a business process.

As you can see in the image above, these can span multiple LOB Apps, or Line of Business Applications even beyond Dynamics ERP / CRM.

To find out more about OBA, I recomend going to the MSDN article found here: Building Office Business Applications

So are we starting to see what 'BI for the masses' is going to be? It will span possibly multiple LOB Apps, including having Dynamics AX at the heart of that, with SQL Server and ShaerPoint, and other Office Services at the stars for the delivery, shaping and mining of the needed information, using Excel as the front end for the presentation of such information.

This is an exciting move forward, because everywhere I go, everyone loves to use Excel as their choice of reporting tool actually. And this BI for the masses simply builds on that desire and drive and keeps Excel as the front end to a very flexible, scalable and desireable BI platform that fits in the smallest of companies to the top of the fortune 500.

If you would like information on BI for Dynamics AX and the Dynamics Platform, feel free to contact us at Sunrise: Sunrise Contact us link. Make sure to tell them I sent you!

Also check back soon as I continue down the BI path, and also bring in some exciting information and interview about the Recent BPM / SOA conference and it's topic on Dynmaics AX. I also have been contacted about a soon to be release Dynamics AX 2009 book that I will be reviewing, and on top of that more SQL Server... so I have a lot coming out.

See you then!

Update:
I wanted to also make sure and point you to the Offical Microsoft BI home on the web. That can be found here: Microsoft.com/BI. Check it out. A lot of good information can be found there.


"Visit the Dynamics AX Community Page today!"

Calling an Excel Macro from X++

All, recently I needed to know how to call an Excel Macro from X++. This is not something that is widely published anywhere on the web, and something I had to figure out.

It's actually not that hard, and the best help was looking at how I had done it before in .Net.

So here is the code example:


SysExcelApplication ExcelApp;
SysExcelWorkbooks WorkBooks;
SysExcelWorkbook WorkBook;
SysExcelWorksheet Worksheet;
;


ExcelApp = SysExcelApplication::construct();
ExcelApp.displayAlerts(true);
ExcelApp.visible(true);

ExcelApp.workbooks().open("SomeExcelFile.xls");

Workbook = ExcelApp.workbooks().item(1);
Worksheet = Workbook.worksheets().itemFromName("WorkSheetName");

ExcelApp.comObject().Run("SomeMacro"); // The Call to run a Macro


Of course there is a lot more you can do, but basically any Excel based class has a .comObject() at which you can further extend and make Excel Interop COM calls via, that go beyond what is coded for in the SysExcel Class framework.

Check back soon!

"Visit the Dynamics AX Community Page today!"