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