Deserializing Financial Dimensions in Microsoft Dynamics AX 2012

Deserializing Financial Dimensions in Microsoft Dynamics AX 2012

Financial dimensions are stored as a 64bit integer surrogate key in the database. It may be necessary to extract this information to understand the financial dimensions that this surrogate key represents. For example, a custom BI solution, report or integration may require that this surrogate key be de-serialized to reveal the exact financial dimensions it represents. This blog explains some of the common Microsoft Dynamics APIs available and display financial dimension segment from a default dimension surrogate key. A simple job is created to demonstrate this.

In this example, we will be working with the financial dimensions of a customer.

Figure 1: Customer default dimensions

This is stored in the SQL database as a 64 bit integer.

Figure 2: DefaultDimension value

The database schema below represents all the tables and views that are required to de-serialize and display the financial dimensions that this surrogate key represents, however this blog post will demonstrate key Microsoft Dynamics AX API’s that can assist developers.

Figure 3: Data model of dimension framework in AX 2012

 

The following script is created to demonstrate:

Figure 4: Sample AOT Job

 

The following Microsoft API’s are used:

  • The DimensionAttributeValueSetStorage class is used to iterate through all the financial dimensions in the DefaultDimension
  • The DimensionAttributeValue table is used, this stores a link to the VALUE of the financial dimension we want to display, for example a department or cost center number. This is usually linked via a surrogate key, e.g. DimensionAttibuteValue. EntityInstance is a surrogate key to the exact record we wish to display.
  • The DimensionAttribute table is used, this stores information so we can understand the TYPE of financial dimension. This stores the object ID of the view or tabled used as backing entity, e.g. 11765 = DimAttributeOMDepartment
  • The DimensionDefaultingControllerBase class is used to find the backing entity record. The backing entity is usually a view in the AOT, starting with DimAttribute. This view can then be used to display information about the financial dimension. The DimensionAttribute table is used to find the table that contains the financial dimension, the DimensionAttributeValue table is used to find the specific record in that table.
  • The DimensionAttribute record contains a ValueAttibute field, which is the field id in the backing entity view that stores the VALUE of the financial dimension, this is a natural key, for example a department ID (e.g. Department 10)
  • The NameAttribute field on the DimensionAttribute record stores the field in the backing entity view that stores the NAME of the financial dimension, for example “IT Department”.

 

The DimAttribute financial dimension backing entities have a key, value and name field. The following example shows the financial dimension backing entity for departments which are stored in the table OMOperatingUnit

  • Key: This is foreign key to the Operating Unit RecId field (e.g. OMOperatingUnit.recId)
  • Value: This represents the Operating Unit ID (e.g. 110)
  • Name: This represents the Operating unit Name (e.g. “Budget Office”)
Figure 5: DimAttribute View backing entities

Thanks for reading, comment below to let me know if you struggle with something specific in D365 or DynAX!

About Camilo Godines

Camilo Godines has created 2 entries.

Post A Comment

YOUR CAPTCHA HERE

Contact Us

All fields required

First name

Last name

Company

Your Email

Subject

Your Message

×

Meet Us

All fields required

First name

Last name

Company

Your Email

Available Date & Time
AMPM

Your Message

×

Subscribe to our mailing list

* indicates required
 

×
Learn More

Contact Us

All fields required

First name

Last name

Company

Your Email

Subject

Your Message

×

CRM Test Form

×

Influence

All fields required

First name

Last name

Company

Your Email

Proposed Webinar Date & Time
AMPM

Your Message

×

Contact Us

All fields required
×

Contact Us Here! 

* indicates required

×

We want to hear your feedback

∗ indicates required

×

Let us keep you updated!

 ∗ indicates required

×

Meet Us

All fields required

First name

Last name

Company

Your Email

Available Date & Time
AMPM

Your Message

×

Contact Us Here! 

* indicates required

×

Contact Us Here! 

* indicates required

×

Schedule your meeting with us!

* indicates required

×

Schedule your meeting with us!

* indicates required

×

Book Your Time

×

                        RAMPUP SUBSCRIPTION PLANS

                    Choose a plan below, and one of our

              Support Consultants will contact you shortly.

×

                           PACKAGES START AT $4,999

                  Choose a package below, and one of our 

               Support Consultants will contact you shortly.

×

 Performance Review Details

 

Diagnostic Review of DyanmicsAX Server (AOS) & SQL performance and stability.

 

Written summary of findings and recommendations.

 

Up to 15 hours of issue resolution time to target your most immediate performance issues.

 

Review completed by our 100% onshore support team averaging more than 10+ years of DynamicsAX experience.

 

Get the help you need now, with no long-term commitment.

×

Functional/Department Help Pick 3

 

Help with up to 3 functional or department issues such as: invoicing, costing, closing the books, AR, AP, inventory, warehousing, etc.

 

Talk with our 100% onshore functional experts with over 250+ years of combined AX experience to define your issues and find recommended solutions.

 

Up to 20 additional hours of issue resolution time included.

 

Presentation of solutions and next steps from our expert team.

 

Get the help you need now, with no long-term commitment.

 

×

Reporting / BI Help

 

Choose up to 3 standard or custom reports for assistance.

 

Solve typical issues like: Report too slow, does not show what we need, need an additional field/column, etc.

 

Talk with our 100% onshore functional experts with over 250+ years of combined AX experience to troubleshoot report issues and recommend solutions.

 

Up to 20 additional hours of configuration or development time included.

 

Presentation of solutions and next steps from our expert team.

 

Get the help you need now, with no long-term commitment.

×

Please fill in the form to RSVP

 

×