Dynamics AX
  RSS Feed  LinkedIn  Twitter
Want to turn you're data into a true asset? Ready to break free from the report factory?
Ready to gain true insights that are action focused for truly data informed decisions?
Want to do all of this across mutliple companies, instances of Dynamics and your other investments?
Hillstar Business Intelligence is the answer then! (www.HillstarBI.com)

Hillstar Business Intelligence for Microsoft Dynamics AX and NAV on Mobile, Desktop, Tablet


Let us prove to you how we can take the complexity out of the schema and truly enable users to answer the needed questions to run your business! Visit Hillstar Business Solutions at: www.HillstarBI.com

Tuesday, June 18, 2013

Webcast of Microsoft Dynamics AX 2012 R2 & Personal BI





Well I hope everyone is doing well this fine Tuesday. We are very close to the summer equinox and that means the longest day of the year! I love summer time, it's full of fun times, water activities as well as opining for the beach!

With that, I wanted to post a link to the recent webcast I lead via MSDynamicsWorld.com. The link to the web cast can be accessed from the following resource.: The Power of Personal BI with Dynamics AX 2012: PowerPivot and Power View



Now there is for sure one point that I need to clarify from this webcast. Specifically the question was asked around what version's of SharePoint & SQL Server are needed in order to enable specific aspects of what we reviewed during the webcast. When looking at this from a Microsoft Office Excel 2013 perspective, then the topic of SharePoint & SQL Server version for that matter in what was shown, is not relevant. Meaning, it does not affect what you can do with Excel 2013 in context to what was covered.



However when we look at enabling PowerView from SharePoint, launched within Microsoft Dynamics AX 2012 R2 then we need to better understand what versions of software do impact what functionality. Thus we have the above pictured diagram from Microsoft. It shows that indeed we do need more than standard versions of MOSS & SQL Server for different levels of functionality, including that last part of the webcast around PowerView & SharePoint.

I hope that this is helpful to people. During the session we have a lot of great questions, and feedback. Keep in mind, this is not edited but is a full capture of the entire webcast itself. Finally, I encourage you to send me any questions around this and just think about how Personal BI can help craft a reporting story for your company! If you would like to know more, you can contact us here.: Sunrise Contact Page.

Well that is all for now. I know that I'm behind a bit in getting some of the previously targeted topics posted. It's a sign of the current times, with being busy. It's good to be busy, and so there will be plenty to cover soon. Til Next Time!


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.


Follow Me @:
   RSS Feed  LinkedIn  Twitter

"Visit the Dynamics AX Community Page today!"

Labels: , , , , , , , , , , , ,

Friday, February 15, 2013

AX 2012 - PowerPivot Date Dimension Query





Today I wanted to spend time building on the topics that I've been covering of late. That is the value of PowerPivot and how Dynamics AX customers to create true Personal BI artifacts with it. A key topic that is important to understand when looking at creating such BI artifacts is around the use of a Date Dimension Table. Find out more about this topic, specific to PowerPivot from the following resource.: TechNet: Dates in PowerPivot



With this in mind there are several ways that this can be achieved. Specifically what we care about is relating some date value at a transaction level data, in order to slice & dice such data by. We could make use of fiscal calendar's, outside date table, or we can create one from a working calendar inside Dynamics AX 2012. The focus of this How-To is exactly around that approach. Making use of the working calendar concepts in Dynamics AX, to have a specific one that generates date values, and that we make use of for our PowerPivot Date Table.



What you see in the above image is the building out of a specific Work Calendar that I'm targeting for use as my Date Table within PowerPivot. After having that built, the next focus is around the project that will contain the elements that help complete this scope. That is what you see with the following image.



Looking at the project, you can see there we have a table, query & job elements. These are the Dynamics AX parts that help us create and expose a Date Table for use with our PowerPivot artifacts. Like most scope, we need to start with a targeted table that will house our date data. That is what we can see in the following image.



From this point, we will then need a Query that will become the basis for an OData feed. This query element, simply exposes the new custom table and it's fields. That is shown in the following image.



After having our custom table targeted, next we can move towards creating X++ code that will fill this table. This X++ code, in this current write up, it housed and executed from within a Job element. This could easily be adapted to have parameters and execute as a batch job, or fired from a menu item within an instance of Dynamics AX. This is the tie between the working calendar & date data we created, and the Query element that will expose the data it generates.

With that, lets look at the X++ code. We have two sections of this job really. The first section is pictured below, and it shows the declaration section of the job variables, as well as a local method. For those that don't understand local methods for Dynamics AX, please review the following resource.: MSDN: Local Functions. Even though that is stated for Dynamics AX 2009, this still is a valid concept for AX 2012.



What we see in the local method, or function, is the creation reading of a date value and based on that date value, we are returning the calendar year quarter that the date belongs to. Having this, we can now move to the second half of the job. That is seen in the following image.



The above, we see the majority of the business logic behind the creation of our target date table data. This job will clear the custom table, and then proceed to fill it with information based on the working calendar data we created. You can see several calls that will give us specific information like: DayName, Week, MonthName as well as our local function Qtr().

Having complete this, and executing the job the majority of what we need to do within Dynamics AX is completed. Since we are targeting this for use as an OData feed, the next step for us is to target the query element within the project and publish it as a Document Data Source. That is what we see highlighted in the below screen shot.



Once we have done this, we are now able to launch into a PowerPivot design space to add the new query as a date table for our Personal BI needs. As we can see in the following image, our new query appears as a valid OData feed.



I'm adding this as a new data source to an existing PowerPivot artifact that is focused on looking as Sales Data. After adding the new data source to the PowerPivot design space, we can see the two query elements from the AOT, as shown below.



Next step is to join our two query elements within the design space of PowerPivot. What we are doing here is stating that the sunSalesLineData query is the fact table, and the sunDateTimeTable query is a dimension table, linked to the fact table. We are linking these two tables via the sunSalesLineData.SalesLine_ConfirmedDlv = sunDateTimeTable.sunDateDimTable_TransDate. Creating this relation allows us to make use of the fields within the targeted Date Table, to slice & dice our sales data with. We can see this relation in the following image.



Now that we have our relationship established and our PowerPivot workbook updated, lets take a look at the current Month value. I derived the "month" value used in the current workbook by making use of a DAX expression within PowerPivot. It's a computed column, with the following expression.: '=MONTH([SalesLine_ConfirmedDlv])' This gives us a numerical value that represents the month from the SalesLine_ConfirmedDlv date value. You can see this as a horizontal slicer in the following screen shot.



This might be fine for some reports, but the goal of a Date Table within PowerPivot is to add more flexibility and friendliness to the date values which we filter and slice by. Now that we have the date table we can target as dimensions for slicing our facts from the sales data with, we can replace the integer month value, with friendly month names. We can see that with the following image.



Because we built out our date table within Dynamics AX and expose it as a Query element, we have flexibility for control of the dates. As well as we could make use of targeted languages, labels and more. As you can see in the following image, we have the ability to make use of any of the fields now, to slice and filter by.







With the above we have a powerful, yet simple example of enabling a date table built from Dynamics AX. Further, by example, how with little effort such scope can start to add value and use for our personal BI development needs. Further I will add, we could then make the choice to expose any number of fiscal or working calendar data in such fashion. Building from the above knowledge, you can see where this Date query can be added as part of the design as a dimension table for many, many PowerPivot artifacts.

There are plenty of detail specifics that could be considered in the design of this, but I hope that you see the value and ease we have with empowering the creation of useful and value added BI artifacts with Dynamics AX & PowrPivot. That is all I have for this post. I will be back next week to continue focusing on the helping put your companies data to work for you! If your interested in what else can be done with BI Semantic Model as well as BI in general, make sure to contact my team at Sunrise, to see how we can help you today! Sunrise Technologies Site. Till Next Time!

P.S.: I will be at the MVP Summit all next week at Microsoft HQ. I do plan however to get at least two post up next week. Make sure to check back soon and often!

Follow Me @:
   RSS Feed  LinkedIn  Twitter

"Visit the Dynamics AX Community Page today!"

Labels: , , , , , , , , , ,

Wednesday, January 23, 2013

AX 2012, SQL Server 2012 & NLB Reporting Services





Today I wanted to take the time, and give light to a specific topic, Network Load Balancing Reporting Services with AX 2012 & SQL Server 2012. As I have done in the past, to help shed light on this most important topic, I invited Matt Tortorice AX System Engineer for McWane, Inc. to give us some insights on this topic.



Matt, along with McWane, is currently implementing Microsoft Dynamics AX 2012 for some of their divisions. Recently, I worked with Matt on the topic of clustering for reporting services, and he is here to help give valuable, customer based insight into the process.


Matt Tortorice LinkedIn Profile | @mtortorice


After following the “Configure a Report Server on a Network Load Balancing Cluster” article on TechNet, and generating my machine key from aspnetresources/machineKey I was able to apply the new Machine Key value to both of our load balanced Report Servers web.config file. In doing this, we ended up with a configuration that looks similar to what is pictured below.:



When first launching into the instance of Microsoft Dynamics AX 2012, that had three AOSes, users had no issues executing report. Users, that is, that where connected to the first AOS that has an Reporting Services configuration associated with it that is.

Those Users trying to run an SSRS Report when connected to the any other AOS instances (ax102, ax103) would get the following error message.: “The default Report Server Configuration ID could not be found in the SRSServers table.” This error message was presented in an infolog, similar to the one pictured below.:



To solve this problem, the answer was to create a Reporting Server Configuration for each AOS in the cluster by setting the Application Object Server Name to each AOS instance. In other words, 3 identical Server configurations except for the AOS Server name. Configuration ID 1 points to AX101, ID 2 points to AX102 and ID3 points to AX103 as seen below:



Once there was a Report Configuration for each AOS instance all of the reports SSRS Reports would generate when a client was connected to any AOS. All reports where able to execute, no matter what AOS a user was logged into. Further each of the report server configurations where pointing to the NLB, or Network Load Balancing server name.

Having done this, we where able to take full advantage of SQL Server 2012, with a scaled out, load balanced reporting servers that better meets are reporting needs. This will further enable, as needed, future scale as our users demand more, and more reports from our Microsoft Dynamics AX 2012 instance.


I would like to thank Matt for taking the time to share with us, about this topic. Giving us insight into the trenches of Microsoft Dynamics AX 2012 and how the stack is helping him realize real value for his company needs.

Further, the following is more information about Matt and his blog, where he plans to continue his journey, and helping all of us along the way.:

With a Background in .Net Development, SQL Server Management and Microsoft Server Administration I began my unexpected adventure in Dynamics AX in late 2012 when I was tasked with setting up 3 Dynamics AX 2012 CU3 Single Server Environments using SQL Server 2012, and a 9 Server Production installation of Dynamics AX with 3 AOS Servers, 2 SharePoint EP Servers, 1 SharePoint Search Server, 2 SQL Server 2012 Servers in a High Availability Group and 1 Help/FileServer. I will continue to blog about my experiences and answer any questions I get at http://blog.thedeprecated.com

That's all for this post, check back soon as I will continue the focus of valued added content. We will dive further into the Dynamics based world, with more coverage of BI, Reporting, Services and more! Till Next Time!
Follow Me @:
   RSS Feed  LinkedIn  Twitter

"Visit the Dynamics AX Community Page today!"

Labels: , , , , , , , , , , , ,

Wednesday, November 28, 2012

AX 2012 BI Basics - Dual Datasets for single Report Data Provider





I hope everyone had a wonderful Thanksgiving! I must say for me, it was great being with family and friends, however I was terribly sick and still fighting this cold. With that I'm finally getting back into the swing of things and ready to get some great value added content back on the blog.

With this I wanted to focus back on some BI Basics with Microsoft Dynamics AX 2012. The focus for today's post is around delivering two Datasets via a ReportDataProvider class. The idea, is that we have a need for a header and details section of a report, in which the data will be delivered via a single Report Data Provider.



One of the best examples of this, that really is easy to understand and explain this concept via is the WMSPickingListReportDP class. This class has two methods that have the decoration of SRSReportDataSetAttribute as part of it's make up. One if for the header, as seen in the image above. The next is for it's details, as seen in the image below.



Both are very similar in deed, and both return variables that are declared and filled within the calling of the processReport() method of this Report Data Provider class. This in turn allows the report design, to have a linked set of Temporary tables, one for header data that is returned and used for rendering, and the other is for the details beyond the scope of the given header data that is filled for a specific WMSPicingListReport.

What this shows, is the power and flexibility of the AX-SSRS framework when used and designed correctly. I would, also in this post like to take a step back, and lets review then the following when considering the design of such reporting artifacts for an instance of AX 2012.



Above we see a diagram that shows the concepts of the reporting framework for Microsoft Dynamics AX 2012. The idea, is that you always start with the data, this includes inside or outside of AX 2012. Further moving from there, going to the report design, the report elements, and finally the execution of the report, and security to govern it.

The following is a list of the task needed, in order when you start to design and creating AX-SSRS BI Artifacts.:
  • Define Data Source (Query / Code, OLAP, Other DS)
  • Define Ranges (Query or Code)
  • Create a Reporting Project
  • Create a Dataset bound to Data Source
  • Create Report design
  • Publish report
  • Create Menu item for launching
  • Define Security Elements
It's important to keep this focus in front of you when designing and working within the AX-SSRS framework. If you do then you can be assured you will get the most out of your reporting investment.

That's all for this post, but check back soon as I will have more to come. Till Next Time!

Update: I wanted to make sure and back reference a post I did, that covers the core concepts of the AX-SSRS reporting process. You can find that post here.: AX 2012 - AX-SSRS Report Design Concepts. As the name suggests, this post covers well the core concepts, and will help refresh anyone that needs it on the process of reporting for AX 2012.
Follow Me @:
RSS Feed  LinkedIn  Twitter

"Visit the Dynamics AX Community Page today!"

Labels: , , , , , , , , , , , , ,

Thursday, October 18, 2012

The value of a BI Product outside of Microsoft Dynamics AX 2012





With the a lot of focus this week around Business Intelligence, including being my entire session load, I keep getting asked the same question.: "Why are there so many BI products in the expo hall?" Another flavor of this is "Which BI Products are good candidates to look deeper into."



Before I give my public answer to this, let me first start with this. When you purchase and invest into Microsoft Dynamics AX 2012 you are also getting a BI Solution. It true then, when I state that BI is no longer a separate product or project. With the focus around the BI Semantic model, you can achieve Personal, Team & Organisational BI artifact creation without having any other tool.



You can create compelling executive dashboards, as well as other role tailored user experiences, enabled via SharePoint, Office & SQL Server technologies. This can be viewed without having to access the Rich Client of AX 2012 as well. So having a statement that a "CEO never uses AX" is not valid. Just because they don't make use of the rich client, to enter transactions, does not mean they do not use AX. In fact Role Centers should be front and center for the User Experience modeling for you AX investment.

With that said, there still is a valid reason for adding another BI enabling product to your total solution. First thing we have to focus on is the value such a product brings. If it brings relevant, pre-built, focused artifacts that fit into your business model and needs - then that is functional value. Further, just as important as the functional value, is the fact that whatever product this may be - it should never compete with the Microsoft Dynamics AX BI solution.

This means it should compliment the stack of SQL Server+SharePoint+Office. The artifacts it generates should be easily adding as web parts, so that the targeted Role Tailored user experiences are enabled - not replaced.

Microsoft is committed to the continued development and improvement of having this focus, Role Tailored User Experiences - as part of the out-of-the-box offering. So to answer in short: If the product compliments the stack that represent Dynamics AX, and add value for your company, then it's a possible good candidate for adding to your overall BI story your creating for your company.

That's all for this post. Make sure to join me at 4:00 PM Today, in room 604 for the round table discussion on "Understanding What is Available in Standard AX to Meet your BI and Reporting Needs." This will be an open discussion format, meant to engage the audience and have the audience guide and create the conversation.

Make sure to check back soon and often! Till Next Time!
Follow Me @:
RSS Feed  LinkedIn  Twitter

"Visit the Dynamics AX Community Page today!"

Labels: , , , , , , , , , , , ,


Copyright 2005-2011, J. Brandon George - All rights Reserved