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

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

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.

 

 

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. Note 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.

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.

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. 

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