Product Configuration - Physical data model for user selected values

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?