Sunday, December 7, 2014

Taking some time off from blogging December 2014

I am taking some time off from blogging in the month of December. I was snowboarding in Vail and I got a little extreme. Maybe I drank too much Mt Dew. I'm not sure. But I ended up judo chopping the edge of the my board with my right forearm and that was that.

So I'll restart my postings in January with the hope of doing two posts per month for the 2015 calendar year.

Happy Holidays!

Monday, November 17, 2014

10 tips for the Microsoft Dynamics AX Technical Conference 2015

Out of all of the conferences and training in a given year, the technical conference is the best one by far. This is just my opinion but I've gone to them year after year and this is by far the best conference event of the year. I'd HIGHLY recommend going.

It is February 2nd-4th, 2015 (Monday-Wednesday) in Seattle, WA. There will be post event training sessions the 5th-7th (Thursday-Saturday) but they have not been announced yet. Attendance is an extra fee for these. NOTE: This is at the Washington State Convention Center in downtown Seattle, not in Bellevue or Redmond campus like past years.

Registration is now open: http://www.microsoft.com/dynamics/AXtechconference/

You don't have to be a technical person to attend. There are functional sessions there as well. And I personally think all functional people should be familiar with the technical aspects of AX just like technical people should be familiar with functional aspects.

Its getting larger and larger every year and has switched venues over time because the sessions get so full. Not sure capacity this year but it may sell out as it gets closer to the event.

To note, that Sunday before is Super Bowl Sunday. There is an MS watch party event if you want to attend that. I was a Bronco's fan in Seahawks territory last year so I opted to not attend the party and go to a brewery instead. Denver got trounced but I got a number of free things from the bar as everyone was very friendly there and felt bad. Seattle is a great city! I'm hoping its not a Broncos/Seahawks rematch. Or do I?

10 tips for attending the MS Dynamics Tech Conference 2015
  1. Register early for the event. You can save money if you book early. Most won't dispute the value of this conference but its a factor of cost vs benefit. Booking early reduces the cost. Plus it sells out so you want to make sure you can go. I believe you can save $300 off registration if you register before December 10th, 2014.
  2. Book the hotel early. They'll fill up quickly and the rates can rise.  
  3. Try to stay in downtown Seattle or as close as you can to the Washington State Convention Center. That's where most people will go out after the events and you never know who you'll run into. The best networking happens outside of the conference and the largest concentration of AX people will be by the convention center. 
  4. Go to the post training events. Last year I went to an R3 retail hands on lab session and it was awesome. At times, you can get one on one time with the product team and you can ask why certain things were done certain ways and provide feedback. Assuming the 2015 sessions will be similar to 2014, you don't want to miss it!
  5. Take great notes during the session. You may remember things that night from the sessions you were in but after the whole week, you'll be hazy about the first day. There is just too much info. If you're in a group, try to use a shared OneNote notebook so you can see what others learned during their sessions. Divide and conquer!!
  6. Pictures are allowed during the sessions but don't post ones that they ask you not to share on social media. Keep those for your notes. The pictures will help give context to notes you may take. I tend to remember things through images so if I read written notes, I may forget details that a picture will bring back.
  7. Pick your schedule ahead of time, but don't be afraid to pull an audible. You may be in a session and they talk about something that peaks your interest you were unaware of (like SQL AlwaysOn for example). If you stay strict to your plan, you could be missing out. Follow your gut. Often the unplanned sessions end up being the most valuable ones.
  8. Bring a list of questions or topics your organization has struggled with. It's easy to lose track of your work tasks with so many shiny, new features being shown to you. The list will help you concentrate on what features and strategies you can employ now or in the future. There will be a number of experts there both presenting and in the crowd who would likely be able to help.
  9. Don't be afraid to talk to people. Networking is important in an community as 'small' as the AX group is. After the sessions, there will be a lot of opinions from technical and functional people alike in which you can learn a lot from. Additionally, be ready to share your own experiences and original opinions. Having people to bounce ideas and thoughts off of helps enhance the sessions' presented content.
  10. Don't party too hard in the evenings. It's tempting and fun I know. Especially with so many other AX enthusiasts in attendance but you'll pay for it in the AM. The sessions go fairly fast with a lot of information. If you feel like you got tackled by a sumo wrestler, the sessions might as well be in a foreign language.
Hope the above tips helps people! Looking forward to seeing everyone there!

Thursday, October 23, 2014

AX for Retail POS database size caveat

When deploying the AX for Retail POS solution, there should be a SQL DB installed at the store location. SQL Express can be installed to meet this requirement without adding to the cost of deployment for licensing (since its free!). For the most part, there won't be an issue with this. However, you need to know the issues you'll encounter in a solution so you can anticipate them.

For larger AX implementations, data growth can be an issue at the store DB as retail rollouts or data grows the size of the store side DB.  SQL Express 2012 has a DB limit of 10 GB as of 6/29/2014. 

There are a few options to address this each with their own pros and cons. One way to correct the issue is to buy a license for all SQL Server instances at all of the stores. It's a good solution if you're Scrooge McDuck or Mr Monopoly and don't care about costs. Another option is to limit what stores see what data (e.g. by region). Understand this has a con as well in that not all stores will have visibility to all of the other stores data. Yet another option is to customize the deployments to look at two different databases for the data. This is really tricky and I don't think justifies the cost compared to other alternatives. But its an option! There are other options like a central SQL box and virtualizing the stores DBs referencing that one SQL DB but then you'll still have other concerns like IO, network speeds, etc.

This blog post won't discuss a solution as there are many with each one fitting certain situations. There are even more possibilities out there I haven't even thought about. But hopefully this post will help some people start to think about the limitations proactively and start to brainstorm on their own.

-------------------------------------------------------------------------------------------
Here are some more technical details around limitations for those interested:
  1. Constrained to a single CPU (in 2012, this limitation has been changed to "The lesser of one socket or four cores", so multi-threading is possible
  2. 1 GB RAM (Same in 2008/2012)
  3. 4 GB database size (raised to 10GB in SQL 2008 R2 and SQL 2012)
The 4 GB database size limit applies only to data files and not to log files. However, there are no limits to the number of databases that can be attached to the server

However, as mentioned in the comments and above, the database size limit was raised to 10GB in 2008 R2 and 2012. Also, this 10GB limit only applies to relational data, and Filestream data does not count towards this limit (http://msdn.microsoft.com/en-us/library/bb895334.aspx).

Friday, October 17, 2014

How To: Remove Collaboration Workspaces created from AX 2012 Enterprise Portal (EP)

AX has the ability to create collaboration workspaces for various records in AX (e.g. project, opportunity) automatically from within records in AX and have them available on a SharePoint workspace site.

The workspaces will show up in the workplace home page setup location in Figure 2 below using the template also in that configuration. You can get to the settings by going to System Administration > Setup > Enterprise Portal > ‘Collaboration workspace settings’.

That’s great that they’re there and they’re very useful, but once those workspaces are no longer needed, how can we get rid of them?
 
Figure 1 – The SharePoint AX EP site with collaboration workspaces added
Figure 2 – Legal entity settings for collaboration workspace system generation
While there is no ‘correct’ way to do this, keep in mind that the idea of this is to make AX managed SharePoint subsites for collaboration. That being said, the sites used were intended to be created, linked, and deleted from within AX. It doesn’t mean that they have to be.
To SharePoint administrators, they might go into SharePoint designer, open the link where the collaboration site is (Figure 3), right click on the site, and select ‘Delete’ (Figure 4). This works but it will create an inconsistency with data between AX and what actually exists. This will yield an HTTP 404 Not Found error when users attempt to get to the specific information that doesn’t exist (Figure 5).
The above would work but you SHOULD go into and update the individual records’ information that refer to this URL. That way, it the system won’t indicate that a site doesn’t exist.
Alternately, the site can be either deleted or ‘unlinked from within AX. Unlinking is nice as it will keep the subsite available for repurposes or archival reasons. For archives though, I’d still keep it… Removing the workspaces is as easy as the below. The bullet points I made should explain how it all ties together.
Navigate to Sales and Marketing > Common > Opportunities > All Opportunities, select the opportunity with the collaboration workspace. On the ‘General’ tab in the ribbon, click the drop down for ‘Collaboration workspaces’. There are four options (Figure 6):
·         Create collaboration workspace
o   Creates a collab workspace for the record.
o   This is a ground up workspace using the template specified in the
 
·         Link collaboration workspace
o   Allows a user to link an existing collab workspace to that AX record
 
·         Delete collaboration workspace
o   Deletes the entire collaboration workspace from SharePoint
o   The site will not show up in the EP list in Figure 1
o   If another record in AX is also using that collab workspace and the user attempts to delete the workspace, AX will only unlink the collab workspace from that record, not delete it
o   The Internal/External URL fields will become blank
 
·         Unlink collaboration workspace
o   Does not delete the collab workspace from SharePoint but removes the association with that specific record
o   The site will still show up in the EP list in Figure 1
o   If the workspace is linked to other records in AX, the association will still exist for those other records
o   The Internal/External URL fields will become blank
Some extra side notes:
Each object, in this case an opportunity can only have one collaboration workspace URL associated with it: an internal and external.
As hinted above in the bullet points, multiple entities in the system can be linked to a single collaboration workspace.


Figure 3 – Opening the workspace where the collaboration workspace is located
Figure 4 – Deleting the SharePoint Collaboration Workspace subsite in SharePoint Designer
Figure 5 – The data in AX will still show the collaboration workspace but the site does not exist (HTTP 404 Not Found error)

Figure 6 – Opportunities record in AX with the options for collaboration workspaces

Monday, September 29, 2014

AX 2012 Issue: Missing module in Dynamics AX module navigation

Issue:
An end user seemed to be missing the entire Accounts Receivable module (Figure 1 below) in AX 2012.

Thought process:
My first thought was that the issue was security: if the user doesn't have permissions to any of the menu items under that module, the module won't show up. The issue is that the user was a power user and should obviously have access to almost all objects under AR. 

The giveaway:
A key indicator that the issue was specific to a single user; other users with the same role were able to to see it. That means it must be something else outside of security.

Solution:
The solution was that the module was unintentionally hidden. Make it visible (or hide it!) by going to File > 'View' > 'Modules' > Uncheck/Check the appropriate module(s) (Figure 2 below). When they are checked, they are visible. If they are unchecked, they are not visible. Once rechecking the AR module, we can see the module is back in place (Figure 3 below)

Note: This is a great way to hide modules that users may not need. It can simplify the modules list for end users who really only need to see specific things. For example, a retail power user may need to see customers, workers, etc. Since those example objects also exist in the AR, sales and marketing, and HR, the user will see the AR, Sales&Marketing, HR, and Retail modules. Since the user can get to everything they need in the Retail module, they may not want to see those other three modules. By hiding the other modules on a user by user basis, it will simplify their view of the system and seem less cluttered. 

Figure 1 - Missing Accounts Receivable module in AX 2012 navigation

Figure 2 - The accounts receivable module is unchecked, meaning that it is hidden.

Figure 3 - The Accounts Receivable module is now visible to the user

Tuesday, September 9, 2014

Dynamics AX 2012 R3 - Complete List of Out Of The Box Workflows

Microsoft Dynamics AX 2012 R3 comes with 75 workflow types in the base product. Identifying and leveraging these base workflow types can be highly beneficial to adding value to customers' implementations. The key is knowing what they are while analyzing a businesses needs to devise the best solution AX can offer with minimal modifications.

Just to clarify, these are the workflow types. There can be multiple workflows setups using one or more of the below workflows.

For total transparency, I used the code below to extract the list from the AOT. I hardcoded the 'en-us' but this can be set for whatever language you want.

static void daxWorkflowAnalyzer(Args _args)
{
    #AOT
    #Properties
    TreeNodeIterator          templateNodes;
    TreeNode                      templateNode;
    SysDictWorkflowType template;
 
    templateNodes = TreeNode::findNode(#WorkflowTypesPath).AOTiterator();
    templateNode = templateNodes.next();
 
    while (templateNode)
    {      
        info(strFmt("%1 - %2", SysLabel::labelId2String2(findProperty(templateNode.AOTgetProperties(), #PropertyLabel), 'en-us'),
                               SysLabel::labelId2String2(findProperty(templateNode.AOTgetProperties(), #PropertyHelpText), 'en-us')));
     
        templateNode = templateNodes.next();
    }
}

WORKFLOW DESCRIPTION
Advanced ledger entry workflow Use this type to create approval workflows for advance ledger entries
Bank reconciliation journal approval workflow Use this type to create approval workflows for Bank Reconciliation journals
Budget account entry workflow Use this type to create review workflows for budget account entries
Budget plan workflows Use this type to create and review workflows for budget plans
Budget register entry workflow Use this type to create review workflows for budget register entries
Case management workflow Use this type to create a workflow for cases
Catalog import product approval Use this type to create review workflows for imported catalog products
Catalog import approval Use this type to create review workflows for imported catalogs
Customer bank remittance workflow Use this type to create approval workflows for customer bank remittance journals
Customer draw bill of exchange workflow Use this type to create approval workflows for customer draw bill of exchange journals
Customer free text invoice workflow Use this type to create approval workflows for free text invoices
Customer payment workflow Use this type to create approval workflows for customer payment journals
Customer protest bill of exchange workflow Use this type to create approval workflows for customer protest bill of exchange journals
Customer recurring invoice workflow Use this type to create approval workflows for recurring invoices
Customer redraw bill of exchange workflow Use this type to create approval workflows for customer redraw bill of exchange journals
Customer settle bill of exchange workflow Use this type to create approval workflows for customer settle bill of exchange journals
Document handling Workflow type for document handling
Human resources actions workflow Use this type to create workflows for personnel actions, such as to create new positions or to modify positions
Worker actions hire Workflows for worker actions of the Hire type
Worker actions terminate Workflows for worker actions of the Terminate worker type
Worker actions transfer Workflows for worker actions of the Transfer worker type
Signing limits workflow Use this template to create a workflow for review of signing limit requests
Time and attendance days total workflow Use this type to create approval workflows for time and attendance days total records
Time and attendance journal registration workflow Use this type to create approval workflows for time and attendance journal registration records
Ledger allocations journal workflow Use this type to create approval workflows for allocation journals
Ledger daily journal workflow Use this type to create approval workflows for daily journals
Ledger eliminations journal workflow Use this type to create approval workflows for eliminations journal
Ledger fixed assets budget journal workflow Use this type to create approval workflows for fixed asset budget journals
Ledger post fixed assets journal workflow Use this type to create approval workflows for post fixed asset journals
Customer rebate workflow Customer rebate workflow type
Review original budget workflow Use this type to review an original budget
Review budget revision workflow Use this type to review a budget revision
Review project invoice proposals Use this type to review project invoice proposals
Review project quotations Use this type to review project quotations
Purchase agreement approval workflow Use this type to create approval workflows for purchase agreements
Commitments approval Use this template to create approval workflows for commitment documents
Delivery due date notification workflow Use this type to create delivery due date notification workflows for unconfirmed product receipts
Invoice received notification workflow Use this type to create invoice received notification workflows for unconfirmed product receipts
Product receipt failed notification workflow Use this type to create notifications workflows for failed product receipts
Unconfirmed product receipt rejection notification workflow Use this type to create notification workflows for rejected unconfirmed product receipts
Purchase order line workflow Use this type to create review- and approval workflows for purchase order lines
Purchase requisition line review Use this type to create review workflows for purchase requisition lines 
Purchase requisition review Use this type to create review workflows for purchase requisitions 
Purchase order workflow Use this type to create review- and approval workflows for purchase orders
Retail catalog approval workflow Retail catalog approval workflow type
Trade allowance workflow Trade allowance workflow type
Vendor rebate approval Vendor rebate workflow approval type
Freight Invoice Approval Workflow to support invoice approval
Cash advance request Use this type to create approval workflows for cash advance requests
Dispute management Use this type to create workflows for credit card dispute management
Expense report auto posting Use this type to create document automatic posting workflows for expense reports
Expense report Use this type to create document approvals for expense reports
Expense line item auto posting Use this type to create line item automatic posting workflows for expense reports
Expense line item Use this type to create line item approvals for expense reports
Travel requisition Use this type to create approval workflows for travel requisitions
VAT tax recovery Use this type to create approval workflows for VAT tax recovery
Review timesheet workflow Use this type to review a timesheet
Review timesheet line workflow Use this type to review a timesheet line
User request workflow Use this type to create user provisioning workflows for user requests
Inactivate user request workflow Use this type to create user inactivation workflows for inactivate user requests
Vendor bank remittance journal workflow Use this type to create approval workflows for vendor bank remittance journals
Vendor disbursement journal workflow Use this type to create approval workflows for vendor disbursement journals
Vendor draw promissory note journal workflow Use this type to create approval workflows for vendor draw promissory note journals
Vendor invoice approval journal workflow Use this type to create approval workflows for vendor invoice approval journals
Vendor invoice journal workflow Use this type to create approval workflows for vendor invoice recording journals
Vendor invoice register journal workflow Use this type to create approval workflows for vendor invoice register journals
Vendor invoice workflow Use this type to create review workflows for vendor invoices
Vendor invoice line workflow Use this type to create review workflows for vendor invoice lines
Vendor redraw promissory note journal workflow Use this type to create approval workflows for vendor redraw promissory note journals
Vendor category application workflow Use this type to create approval workflows for vendor category application requests
Vendor category justification workflow Use this type to create approval workflows for vendor category justification requests
Vendor add application workflow Use this type to create approval workflows for vendor add application requests
Vendor add justification workflow Use this type to create approval workflows for vendor add justification requests
Vendor status change request workflow Use this type to create approval workflows for vendor status change requests
Vendor settle promissory note journal workflow Use this type to create approval workflows for vendor settle promissory note journals

Sunday, August 10, 2014

Business Intelligence Components Part 2: Multidimensional aggregation and allocation

In part two of the 'Business Intelligence Components' series, we'll look at the 'Multidimensional aggregation and allocation' component. For more information about this series and its context, see Part I of the series Overview and the 'Master' BI Components list

Multidimensional aggregation and allocation
This means processing data from many different data sources in a memory efficient way.

Examples of 'multidimensional aggregations' are OLAP (online analytical processing) cubes or 'multidimensional data analysis'. Basically, taking several different data sources (e.g. cities, sales transactions, date of creation, items) and displaying these in a single analytical view which gives the users a view of the information and how they all relate to each other. Depending on the application where the cubes are being leveraged, the users may be able to drill into certain parts of the data (e.g. city) and see even more detailed information around the selected data (e.g. which stores in that drilled into city are selling how much).

Depending on how complex the above links are to display the information from the tables, memory consumption can be quite complicated. It's one thing to process the data accurately. It's another thing to process it quickly and efficiently. Therefore, it is required to allocate the multidimensional information into strategic blocks of memory so it can be equally as strategically removed. While people want all the data in the world, no one wants to wait for it.

To look at these two concepts together, think of a Rubik's cube where each axis represents a different data source. There are nine boxes on each side of the cube. Now picture a fixed multidimensional array variable where we'd declare something to the tune of  int   multiDimArray [9] [9] [9]. The 9s represent the number of blocks on each side of the cube. Think of these as values in the tables. Fixed arrays are more efficient memory wise than dynamic arrays.

I know I skimmed over a bunch of details above but again this is a high level look at these concepts. If I explain in more detail than the above, I'm defeating the whole purpose of these posts.

PART I:      Overview and the 'Master' BI Components list
PART II:    Multidimensional aggregation and allocation
PART III:   Denormalization, tagging and standardization
PART IV:   Realtime reporting with analytical alert
PART V:     A method of interfacing with unstructured data sources
PART VI:   Group consolidation, budgeting and rolling forecasts
PART VII:  Statistical inference and probabilistic simulation
PART VIII: Key performance indicators optimization
PART IX:    Version control and process management
PART X:     Open item management

Monday, August 4, 2014

Business Intelligence Components Part 1: Overview and the 'Master' BI Components list

Business Intelligence (BI) is a hot topic in today's business landscape. Everyone wants it but not everyone knows what they need. In describing a business' needs and analyzing capabilities of existing BI solutions, people often refer to a list of generic classifications of BI components.

The interesting thing about this is that someone (or organization) had compiled a list of BI components in which everyone seems to refer to in all of their BI articles. It's also found on Wikipedia (http://en.wikipedia.org/wiki/Business_intelligence). Whether all these articles are all referring to this source for their information or not is irrelevant. The important thing is this list is out there and is used verbatim and in order in many locations.

Everyone seems to be describing BI as being something along the lines of "made up of an increasing number of components. These include the following" and then including the below list from Wikipedia http://en.wikipedia.org/wiki/Business_intelligence:
  • Multidimensional aggregation and allocation
  • Denormalization, tagging and standardization
  • Realtime reporting with analytical alert
  • A method of interfacing with unstructured data sources
  • Group consolidation, budgeting and rolling forecasts
  • Statistical inference and probabilistic simulation
  • Key performance indicators optimization
  • Version control and process management
  • Open item management

  • Funny thing is no one actually talks about what these are or why their important. They just rely on Wikipedia's BI knowledge to provide generalized talking points. ~95% of the articles mention the components in fancy wording. The other discussions I found on the specific topics got so far under the hood that most people would give up reading after paragraph one.

    The following series of blog posts is meant to provide a high level look at what these components of BI actually mean in layman's terms. I originally had these in one GIANT post but it was getting ridiculous so I made this a ten part series with this being part 1.

    PART I:      Overview and the 'Master' BI Components list
    PART II:    Multidimensional aggregation and allocation
    PART III:   Denormalization, tagging and standardization
    PART IV:   Realtime reporting with analytical alert
    PART V:     A method of interfacing with unstructured data sources
    PART VI:   Group consolidation, budgeting and rolling forecasts
    PART VII:  Statistical inference and probabilistic simulation
    PART VIII: Key performance indicators optimization
    PART IX:    Version control and process management
    PART X:     Open item management

    Monday, July 28, 2014

    AX 2012 Max Buffer Size setting vs AX 2009 and before

    'MaxBufferSize' is a system value for the Dynamics AX that may need to be adjusted to allow large queries or variables to be used. The adjustment becomes especially true when dealing with import/export of data in a single variable (like loading a large XML into an XML variable). It's primary use is to throw an error when a file or query falls outside of the 'expected max' value. This prevents the system from being brought to its knees from an unexpected scenario or file or query 'much' larger than expected. Very large report queries can draw an error triggered from this value as well.

    In Dynamics versions AX 2009 and before, this setting was hidden in the depths of the registry. I streamlined changing this value via a job I posted in the below blog post: 'Insufficient Memory to Run Script - MaxBufferSize'. This was helpful for environments where changing registry values or restricted access was an issue. http://daxdude.blogspot.com/2013/02/insufficient-memory-to-run-script.html 
     
    I think the reason it was hidden was to prevent people from nonchalantly changing the maximum buffer size to something like 500MB in a matter of seconds. Navigating and changing a value in the registry makes people think a little harder about the consequences of changing a value. 
     
    Now in AX 2012, this parameter is available for everyone to use in the AX Server Configuration Utility. Users can change this value without crawling around in the registry but be very careful with changing this value from the default. Depending on application, it can be very useful to change this value quickly and may have little to no impact seen. In other applications, this can potentially take your system down to its knees by allowing a user to run something like a large query that will be 'too' large for the anticipated system load.

     
     

    Friday, July 18, 2014

    Dynamics AX - How to make a table an optional parameter in a method

    I couldn't remember how to make a table value option for a method. I've done it a ton before but just couldn't recall at the time. Tried doing a few searches and didn't find anything so thought it would be a good post.

    Dynamics AX can allow parameters to be optional for a given method. This is accomplished by assigning a parameter to an appropriate value at the top of the method (see below example). If there is no assignment, the parameter is required. The one rule is that all optional parameters must be after the mandatory ones. It is not allowed to have a mandatory following an optional one.

    When calling the method, you can see which parameters are mandatory and which are optional as the optional values will show up with a bracket '[' in them in the intellisense.

    All that being said, below is how you would accomplish making a table optional with a null value in its place. Simple assign the functions parameter to a value of 'null'.

    public void processInbound(Table1 _mandoatoryTableParam, Table2 _optionalTableParam = null)


    For additional information about certain data types being null values, see below:

    Null Values for Data Types [AX 2012]
    http://msdn.microsoft.com/en-us/library/aa846236.aspx
    Type
    Value treated as null
    Date 1900-01-01
    Enum Element with its value set to 0.
    Integer 0
    Real 0.0
    String An empty string
    Time 00:00:00
    Utcdatetime Any value with its date portion as 1900-01-01 is treated as null, regardless of the time portion value. Therefore the value 1900-01-01T22:33:44 is treated as null.
    NoteNote
    Any utcDateTime value with its date portion as 1900-01-01 is displayed as blank by the X++ print statement. Only the value 1900-01-01T00:00:00 is displayed as blank by the Global::info method. That is the value from the DateTimeUtil::MinValue method.

    Friday, July 4, 2014

    AX 2012 Maximum Export to Excel and Performance Data Issues


    One of the most valuable features of AX to an organization is its capability to get the right information to the right people at the right time (e.g. ad hoc reporting). A major way to accomplish this is to leverage AX's tight integration Excel for virtually limitless reporting capabilities across multiple data sources.

    In AX 2012, there is a way to export data to Excel from an AX grid, with possible filters and user form preferences (e.g. MorphX field changes). The shortcut to this has changed over time (ctrl+e vs Ctrl+t) but there may also be an actual Excel Export button on the form. While this feature is incredibly useful, there are a few downsides to this:
    • it does require the AX client's full attention while exporting   
      • User's AX client will be unusable 
    • File generation can take a significant amount of time (sometimes >45 mins for large data set)
    • There is a limit to the number of rows that can be exported
      • CRM to Excel is 10k lines
      • AX to Excel is 65k lines (I believe)
    Depending on the organization, these limitations can be incredibly debilitating. Some limitations can be configurable but others cannot.

    So how would we overcome these limitations? We can address them point by point:
    1. AX client usage -> Move the processing to the server freeing up the user's AX instance to do other tasks  
    2. File Generation time -> Processing on the server should be faster than on the client so file will be ready sooner  
    3. Row Limit -> Create and save an excel file via X++ rather than using AX's export feature
    Unfortunately, we can't leverage this solution for exporting using a keyboard shortcut (e.g. Ctrl+t) but we can add it when exporting using a 'Export to Excel' button. As examples, there are a number of these buttons in base AX already. The key is to change the functionality behind these buttons and utilize the code to alleviate the issues detailed above.

    To accomplish this, we need to:
    • Override the clicked method on the 'Export to Excel' button (or add one to the form)
    • Remove the super() and paste in the code below (includes the file location field which is required)
    • Create a class and action menu item which will loop over a data source and form/usage data of the grid to create a file in Excel which will reflect exactly what the user sees within AX. This should extend run base batch.

    With the above, the user can get a prompt asking if and where they would like to create a file with the specific data to Excel. This will utilize base AX's batch functionality so the user can either run it manually at that exact moment or have it run it batch for a specific time. The later of this option allows the user to continue with their daily functions.
     
    The code is not too complex and shouldn't take too long to assemble including testing. There are a few gotchas in the code like creating the fields in the Excel to the exporting user's usage data preferences, but a pretty fun overall mod. Hopefully the above will give some people a direction to take to extend AX in a very practical application.

    Saturday, June 21, 2014

    AX 2012 issue with menu item button not showing selected record in new form

    I recently hit an issue where a lines record with a menu item button was not opening an associated form with the selected record. Normally, adding the menu item button to the form and tieing it to the data source would open the new form showing the record that I had selected on the previous grid.

    The ultimate goal was to have a number of lines available in a details grid, select a record, and click a button which would open a new form and show an editable XML for data manipulation. A feature like this is absolutely imperative for interfaces. I've written similar functionality into existing interfaces but this one was tripping me up for some reason.

    This is like AX Development 101 stuff. For some reason, there were things on this form preventing it from opening as it should. The normal methods were running short and I didn't want to resort to the menu item parameters via X++ yet. There had to be a way to do this some other way since it was not complex.

    In  poking around a bit more, I set the 'CopyCallerQuery' property to 'Yes' instead of 'Auto' and the issue was resolved. See below for the menuItemButton having this property set to Yes.

    I've never had to set this parameter before so I think the Auto was automatically triggering to 'Yes' but in this case, reverting to 'No'. Again, I've never encountered this before so I'm not sure why it would revert to 'No'. I did not investigate as to why this was happening, but did feel it important to post about. Hope it helps someone out. Even just one and this post is justified. #TigerBlood #Winning

    UPDATE (3hrs after posting): Figured out the issue. Just tired so I didn't think of it at the time. I was calling a form which had that selected record from the caller form as the primary data source. There were no table relations as it was the same record. If the primary table on the second form is a different table with relations in place, the form would be fine without the parameter set below. 



    Sunday, June 1, 2014

    Retailing in the modern world Pt III - The careful balance in maximizing profits

    The goal of a business is to make profit. You need to increase profit in order to grow or sustain a business. You can increase profit by lowering costs and/or increasing revenue. How do you do this? That can literally be the million dollar question. It can also be the question standing between your business and prosperity. The answer can vary from industry to industry and the key to finding it depends on number of factors ranging from timing to personalities to ideologies. The answer will also change as the retail landscape continues to evolve at an ever increasing rate. 

    When determining what path to take, remember that there is a delicate balance between reducing costs and increasing revenue. As an example, If you reduce costs by using cheaper parts, you may be making an inferior product. This may result in fewer purchases and more returns thus lower revenue. On the other hand spending too much money on something, like a mobile app for your company, can increase your costs and thus lower your profits.  
    How do you know where to skimp and where to splurge?
    As stated in previous posts in this series, the answer is different from channel to channel and industry to industry. What may work for the restaurant industry may not work for the wine industry. What works for a textile producer may not work for a clothing manufacturer. From a consultants perspective, how will you know how to advise your clients' positioning for optimal revenue growth? 

    The key here is to really know your client's business intimately. Even if you know their industry, do you know your client's secret sauce? Do those industry secrets change the way various sales channels should be leverage? More than likely, its a resounding yes. Truly understanding an organization is a key component to turning a good strategy into a great one.

    The next series of posts will analyze the various retail channels mentioned in Pt 1 of the Retailing in the modern world series: http://daxdude.blogspot.com/2014/05/retailing-in-modern-world-pt-i-business.html

    Wednesday, May 28, 2014

    Retailing in the modern world Pt II - Omni-channel vs Multi-channel buzz words

    It is a big buzz word to say 'omni-channel retail' in today's world but what does that mean? Is it the same as multi-channel? No. Then what is the difference? Depends on interpretation.

        Multi-channel retail – involves both the sales and supply chain aspects of a retail channel independent of any other channel
        Omni-channel retail – same definition as multi-channel but involves integrating the various channels to create a seamless, 360 degree experience for the customer. For example, it is creating a wish list on the website and continuing your order from any other channel.
    Really, omni-channel is an evolved iteration of multi-channel that was created as time went by and systems didn’t have to be disjointed solutions. When technologies like mobile first came out, they were completely separate worlds from existing retail channels that relied heavily on integrations and imports/exports. There were commonly large lags and discrepancies in data due to physical and data limitations of the time. Now that the concepts have been around for a while now and limitations reduced, solutions for that retail channel can be improved with interfaces reduced and optimized if existent at all.
    Omni-channel is also a bit of a sales buzz word. Elements of the 360 view existed in systems that were dubbed multi-channel at the time. When things improve significantly, it helps to have a new name or something to distinguish it as new and an improvement. For instance, omni-channel sounds better than multi-channel. Xbox 360 is better than Xbox. PS4 is better than PS2.
    However you define and view it, it all boils down to improving a seamless customer experience to assure a consistently exceptional experience. How do you best accomplish this? Have a centralized location for your ‘One version of the truth’.
    What are the aspects of the seamless experience?
    ·         Branding
    ·         Pricing, Discounts, Shipping, and Taxes
    ·         Channel Management & Publishing
    ·         Catalog Management Enrichment & Publishing
    ·         Customer Management
    ·         Merchandising
    ·         EOD & Financials
    ·         Order Management & Fulfillment
    ·         BI/Reporting
    ·         Payment Processing
    ·         Inventory & Replenishment
    As an example of a fully integrated omni-channel situation, if a customer has special pricing for an item at 9am on a specific day, they should be able to engage any retail channel at 9am and see consistent pricing across the board. It doesn’t seem like a big deal until you are encounter a situation where it isn’t working. These incongruences between systems is where some internet discount sites get their ‘hacker’ pricing or discounts. When I see these, it always makes me as a consumer second guess if I’m truly getting the best price from the same company.