AX 2012 - From PowerPivot to Tabular Model

Today I wanted to continue the focus around some of the great options that now come as part of the stack offering for Microsoft Dynamics AX customers. This builds on the BI Semantic Model, going from the perspective of personal BI through to needed scale offered via organizational BI concepts.



What I'm speaking about specifically to you today, is the ability to target a PowerPivot BI Artifact and use it as the basis for creating a scalable, tabular model for SQL Server 2012. This knowledge, builds on that which I've covered in depth for Personal BI. This recently includes my last post Dynamics AX 2012 R2 - OData Improvements. Further, this is a prime example that I explained at a high level, with the post about Understanding the natural flow of the BI Semantic Model.



What we see above is the final outcome of a focus around PowerPivot, OData and a Dynamics AX Query Element. This query element brings together Sales data, for a specific set of customer groups. You can see in the following screen shot, the targeted query element from the AOT.



What would prompt you to take this PowerPivot artifact and move it along from the Personal, Team and into the Organizational BI area is the need to scale. That is the focus when we talk about organizational BI artifacts. These are BI needs that need to scale beyond the capability of what Personal & Team BI aspects can offer us. Having created this personal BI artifact in PowerPivot, we do not have to start from scratch. In fact, we can actually use the work completed in the design space of the PowerPivot workbook, to become the basis of an Analysis Service Tabular Model. This starts with SQL Server Data Tools, the replacement of BIDS for SQL Server 2012.



Having launched SSDT, we can navigate to creating a new project type of Analysis Services > Import from PowerPivot. This is what we see in the below screen shot.



In doing this action, we are then taking to a screen in which we must set a target work space database instance. For the sake of time, I'm doing all of this on an AX 2012 R2 demo image that you can download from Microsoft PartnerSource. This is why, as seen in the screen shot below, I'm targeting a localhost\Tabular instance of SSAS.



Having set our target work space server, we can then move forward with selecting a PowerPivot workbook. At this point there are some very specific security design considerations you need to keep in mind. Specifically when the target is a remote server vs. a local server. To find out more about this topic, please review the following resource.: Impersonation (SSAS Tabular). The goal of this article is to show off the concepts, in which such details will be discussed at length, in a later post.



What we see in the above screen shot, is the selection of our PowerPivot personal BI artifact that we created in earlier post. Having this selected, our tabular workspace server is able to read in the meta-data as well as data contained within the workbook. We can see this reflected in the following image.



Now that we have our tabular model created from the effort put forth in the creation of the personal BI artifact, we can begin modifying and creating needed elements to complete our scaled out BI needs. What important to keep in mind, is that any computing measures that where created in the PowerPivot artifact, are now in our tabular model. Further, the connection via an OData feed to our Query Element is in tact. You can see this from the screen shot below.



Having this understood, and not worrying about security design concerns at this point, we can proceed with deploying our tabular model to our workspace server for Analysis Services. That is what we see, in the outcome below.



With the above example, we can see how quickly the BI Semantic Model can easily take the efforts of a Personal BI design and turn that into a truly scalable state as a tabular model. This does require SQL Server 2012 as well as Dynamics AX 2012. With this however, we can now process our OData feed from within the Tabular model, and work off it for scalable artifacts that can help complete a companies BI story. Further this takes advantage of Dynamics AX, specifically with the use of Query Elements & Document Data Sources.

That's all for this post, but check back soon as I continue to dive further into taking full advantage of Dynamics AX, in helping put your companies data to work for you! I will continue to build on the concepts that I have been writing about, plus more including coverage of the upcoming Convergence 2013! Till Next Time!
Follow Me @:
       

"Visit the Dynamics AX Community Page today!"

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 @:
       

"Visit the Dynamics AX Community Page today!"

Microsoft Dynamics AX 2012 System Requirements - Updated

Welcome to November everyone! It seems this year is really flying by, and what a year so far. It is one of the largest product release cycle years from Microsoft in the history of the company. This includes Microsoft Dynamics AX 2012 R2, SQL Server 2012, Office 2013, SharePoint 2013 & Windows Server 2012 as well as Windows 8.

With this in mind, recently Microsoft updated the System requirements for Microsoft Dynamics AX 2012. You can download the latest system requirements from the following location.: Microsoft Dynamics AX 2012 System Requirements



Above we see a screen shot from the system requirements section on supported server OSes. This now includes and list Windows Server 2012.



Similar to the inclusion of Windows Server 2012, as well as SQL Server 2012 bits for server side possible scenario's, we now have listed as Windows 8 being a valid, tested and certified client OS for Microsoft Dynamics AX 2012.



You will also notice in reading the system requirements that Microsoft Office 2013 is supported, but only for Microsoft Dynamics AX 2012 R2. On this note, if you do wish to take full advantage of AX 2012 R2 new BI capabilities - in full - then an update is coming in Spring 2013 for SQL Server 2012. Further you will need Office 2013 and SharePoint 2013 to make full use of all the now native features, like PowerView for example.

All-in-all, the ability to run and take advantage of the latest Database, Client and Server OSes for Microsoft Dynamics AX 2012 is now here. Further, taking more advantage of the latest Office, and SharePoint technologies does mean an upgrade to those packages for 2013 versions. It's good to see Microsoft continue to invest in Microsoft Dynamics AX, and allowing those that have made the choice to do so, continue to take advantage of the latest and greatest technologies that makes up the stack in which AX resides on.

That's all for this post, check back soon as more to come! Till Next Time!
Follow Me @:
    

"Visit the Dynamics AX Community Page today!"

AX 2012, SQL Server 2012 & AlwaysOn - Issue with AOS Install

I wanted to spend a little time today talking about a specific setup issue I recently encountered with a client in setting up Microsoft Dynamics AX 2012 with SQL Server 2012 AlwaysOn technology. The focus around SQL Server 2012, AlwaysOn technology is around enabling an active-active high availability options for AX customers. You can find out more about this, here.: AX 2012 - New High Availability Options with SQL Server 2012



This is a very interesting issue actually, one that does not have much public coverage yet it would seem. Actually not sure what the combination of setup & settings that causes such a state, however this is an issue directly related to setting up an AOS that points to the Virtual Name of a Synchronous SQL Server 2012 - AlwaysOn Active-Active node.



What we run into is, the AOS reports as if the install took place. However in looking at the setup log, you will see something similar to the following.:

"Granting AOS account access to database [DatabaseName] on server [Virtual SQL Server Name.]

*******************************

Component installation task stopped due to an error.

*******************************

The network path was not found.

*******************************

System.IO.IOException: The network path was not found.

S260FinishedInfo"

Now, the above is not the full error message at all, but it is the keywords that most people would search on, to reach this post - and therefore reach the solution!. When faced with this scenario, it would seem the issue is the installation program running into an issue with the resolution of the Virtual SQL Server 2012 name, to the current primary node of the AlwaysOn Group for the Dynamics AX 2012 Database.

The path around this issue, at least at the time of this writing is to target the node name of the primary SQL Server 2012 instance, for the AX 2012 database. This targeting should be as part of the install process when selecting which SQL Server, and corresponding database in which the AOS should connect to. In doing this, you can then have a successful install of your new AOS.

After having a successful run at installing your new Microsoft Dynamics AX 2012 AOS, you can now use the server configuration utility, specify the virtual SQL Server 2012 name, restart the AOS and everything should be working correctly.

There is a community post about a similar issue to this, in error reported from the installation process. You can find that here.: AOS installation issue (Remote Registry service). As the name states, this was directly related to the Remote Registry Service of the targeted SQL Server box not running. This could be the issue your facing, if your not trying to use AlwaysOn for SQL Server 2012, and get the above type errors.

Finally, let me add a recently updated Microsoft TechNet article on supported SQL Server topology for AX 2012.: (TechNet) SQL Server topology [AX 2012]. Here we can see the supported scenario's, in which the Synchronous for SQL Server 2012 is one of those. I do also welcome any further feedback, and specific details if anyone has anything else to add to this topic. At the very least you can use the above to be able to easily get past such installation woes, but still use the desired SQL Server 2012 topology.

Well that's all I have for this post, check back soon as a whole lot more to come, including more focuses around Putting your data to work for you while driving towards Creating Systems of Engagement with Microsoft Dynamics AX 2012! Till 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 @:
    

"Visit the Dynamics AX Community Page today!"

Management Reporter 2012 Rollup 2 Released

Well we have some exciting news to report on, in that Microsoft has just released Management Report 2012 Rollup2! You can find a direct download, via PartnerSource or CustomerSource access from the following resource page.: Management Reporter for Microsoft Dynamics ERP Installs and Service Packs Overview



The biggest reason I'm excited about this release, beyond the 70+ bugs that are fixed, is the tested and official support for Management Reporter 2012 databases running on SQL Server 2012!

Up until this release it was not possible to install Management Reporter 2012 on SQL Server 2012. For more information about Management Reporter 2012, you can use my recently published post cover the great content on the topic located at InformationSource: InformationSource: A wealth of Management Reporter Knowledge



To get more insight into the value SQL Server 2012 brings to customers who are investing into Microsoft Dynamics AX, you can use the following link to see past post I've done on this topic as well.: Past AX 2012 & SQL Server 2012 post.

Finally, there is one more interesting point to be made with this latest Rollup for Management Reporter 2012. As reported in the following, New Management Reporter 2012 update available post customers & partners should be aware that this is the last release that will support the Dynamics AX 2012 INTFx data provider. There is a new setup option that came with MR2012 that allows for the setup and use of a Datawarehouse concept, vs. direct connecting to the AX 2012 transactional DB.

This new option should be considered as proceeding with the use of the Datawarehouse concept for MR 2012 with AX 2012 allow for amazing performance boost. Further this option should be considered and migration plans created, as beyond this release the Datawarehouse approach will be the *ONLY* option supported.

Well that's it for today's updates. I hope you find them useful, and helping you get the most out of your Dynamics investment. Till Next Time!

Follow Me @:
    

"Visit the Dynamics AX Community Page today!"