Product Configuration - Physical data model for user selected values

Article
02/28/2013

A constraint-based configurator was shipped with Microsoft
Dynamics AX 2012. In this post, we will provide an overview of the data model
that is used to persist the values that you can select when you configure a
product using the constraint-based configurator.

At first glance, you will notice that there are several
tables involved. If you are not familiar with previous versions of Dynamics AX
and the Product Builder configurator, this might not surprise you. However, if
you are familiar with the Product Builder, you will know that the old
configurator persisted all user selections in one table, namely the
PBATableInstance Table. This implies that retrieving the user selected values
is not as simple as writing a select statement against a single table. In the
following, I will use a report as an example and show you how you can retrieve user
selections in tables.Building a report

The report** that we use as an example displays all the user
selected values that are related to the configurations for a given product configuration
model.

**Disclaimer:
The report is not performance optimized. The purpose of the report is solely to
illustrate traversal of the data structure.

In the report, each element is indented to show the
parent-child relationship between, for example, the HomeTheaterSystem root
component and the “Color” attribute. The “Color” attribute can be assigned
other values, such as “Red”, but in the data on which the report was executed
no such configuration existed.

Note: I have used the Contoso dataset.

Data requirements

The data required to build the report can be broken down
into three parts:

1.
Get the instances of the root component from a
given product configuration model. Here we only want the instances that are
created for product variants and not the ones that are created for configuration
templates. 2.
Get the attributes and their user selected
values for a component instance. 3.
Traverse the subcomponent instances recursively
and repeat part 2.

Let’s take a look at how to retrieve the data requirements to
build the report. The following statements will meet the data requirements and you
can also see the table involved:

Get the instances of the root component from a product configuration model

protected void provideDataForModel(PCProductConfigurationModel _productConfigurationModel)

{

PCVariantConfiguration variantConfiguration;

PCComponentInstance componentInstance;

PCComponentInstanceRootComponent rootComponentInstance;

PCClass rootComponent;

…

// get all configurations made for the given product configuration model

while select componentInstance

// only get instances which belong to a variant configuration, not a configuration template

join TableId from variantConfiguration

where variantConfiguration.RecId == componentInstance.ProductConfiguration

// find the root component instances of the selected model

join TableId from rootComponentInstance

where rootComponentInstance.ComponentInstance == componentInstance.RecId

join rootComponent

where rootComponent.RecId == rootComponentInstance.RootComponentClass

&& rootComponent.RecId == _productConfigurationModel.RootComponentClass

{

// handle data for component

…

}

Get the attributes and their user selected values for a component instance

PCComponentInstanceValue componentInstanceValue;

EcoResAttribute attribute;

EcoResAttributeValue attributeValue;

EcoResValue value;

// get all attribute value assignments related to the component instance

while select value

join TableId from attributeValue

where value.RecId == attributeValue.Value

join TableId from componentInstanceValue

where componentInstanceValue.RecId == attributeValue.InstanceValue

&& componentInstanceValue.ComponentInstance == _componentInstance.RecId

join attribute

where attribute.RecId == attributeValue.Attribute

{

// handle data for attribute and attribute value

…

}

The values as such are sub-typed to model their respective data type.

Traverse the subcomponent instances

PCComponentInstance componentInstance;

PCComponentInstanceSubComponent subComponentInstance;

PCSubComponent subcomponent;

PCClass childComponent;

// get all the subcomponents that have been associated with values

while select component

join TableId from subcomponent

where subcomponent.ParentComponentClass == _parentComponent.RecId

&& subcomponent.ChildComponentClass == childComponent.RecId

join TableId from subComponentInstance

where subComponentInstance.SubComponent == subcomponent.RecId

&& subcomponentInstance.ParentComponentInstance == _componentInstance.RecId

join componentInstance

where componentInstance.RecId == subComponentInstance.ChildComponentInstance

{

// handle data for component

…

}

Implementation of the report

The data in the report is hierarchical in nature because of
the relationship between components and subcomponents.

The report that we build includes the parent-child
relationship between a component and a subcomponent and it also includes the parent-child
relationships between, for example, an
attribute and an attribute value. This data cannot be retrieved from Dynamics
AX using a single query[1]
so we will use a data provider to populate a temporary table. The table has the
following structure:

[1]
This is not completely true because in Dynamics AX 2012, each component is
brought about as a reference to the configured variant. Thus, you could
retrieve all the data and then resolve the parent/child relations once the data
is retrieved.

A quick explanation of why the fields are required:

- ID : this is used because the report framework will not expose the RecId field of the temporary table

Name : this is the name, such as *Color*, that we choose to display for the  
element

Parent : this is a reference to the parent
record ID that helps grouping the SSRS report hierarchically
UniquePath : this is used to distinguish the
attributes that belong to subcomponents of the same type. The path will be
unique since it describes the path from the root component to each element in
the model.

For more information about how to build a simple
hierarchical report in SSRS, go to:

https://technet.microsoft.com/en-us/library/dd255243(v=sql.105).aspx

For details on the implementation, take a look at the
attached X++ project.

Enjoy

PrivateProject_PCSessionValues.xpo

Comments

Anonymous
April 27, 2015
I created a PCM and then attached a price model. Customer is now asking me if I get fetch the price of each option (attribute) set on the PCM. Is that possible?

Share via