1. CPQ
  2. Configurators
  3. Option Filters

Option Filters 5: combining data

Article last updated:

Many parts of your business are all working to manage your products.  Their information can be useful to your configurator, but often that data is maintained by separate groups and can be found only in "silos" of disconnected information. How can you get their data into your configurator?  Using "Joins" in your option filters and queries can help you gather up data from various sources and combine it in powerful ways.

This is part of a series of help documents on how to use option filters.  For the full list, see the "Related Articles" section below.

Before you begin...


Overview


Defining the problem

When you build the first version of a configurator, you often focus on engineering logic.  This makes sense: your configurator must, above all, present the product options in a way that meets the engineering constraints of the product.  But other parts of your business may also have information that is needed in the configurator. For example, consider the vehicle data we've seen before in the "truth table" discussion: A vehicle configurator must display only the appropriate wheel type options, based on 3 constraints: how the vehicle is used, the type of engine it has, and the region where it is being sold.   

At a high level, we visualize the 3 general constraints like this:

At a detailed level, we can describe the specific constraints like this:


 Vehicle Use Engine Type Sales Region

Smart Car

Sedan

SUV

Truck/ Lorry

4-cyl Engine

6-cyl Engine

Europe

North America

Asia
Wheel
Type

Light

1

1



1


1


1

Standard


1

1


1

1

1

1

1

Heavy Duty



1

1


1

1

1

1

If all three of these sets of data are coming from one source, it's easy to update

  • If the data is coming from a person, it's easy to train that person how they can maintain this table and send any updates to you.  You'll simply paste their updated data into the Epicor CPQ table, and deploy the change.
  • If the data is coming from a system, you can integrate that data with a Epicor CPQ  database table, which can be updated at any time by an integration job.


But what if this information is coming from different sources?  Let's say you have 3 different tables from 3 different departments.  When you first gather that data, it may not even match (different groups using different descriptions) and some tables don't refer to all options (below, notice that Marketing doesn't tell us anything about heavy duty wheels).

Example Data from Engineering
(the type of vehicle constrains the wheel choices as shown in the table)


 Vehicle Use

Smart Car

Sedan

SUV

Truck/ Lorry
Wheel
Type

Light

1

1



Standard


1

1


Heavy Duty



1

1

 
Example Data from Product Safety 
(Small engines can't turn big wheels: the engine will overheat.)
(Big engines can't have small wheels: the wheels will overtorque and shred.)


 Engine Type

4-cyl Engine

6-cyl Engine
Type of wheel mechanism

L

1


S

1

1

HD


1

Example Data from Marketing
(small wheels aren't profitable to sell in North America)


 Sales Region

Europe

North America

Asia
Choice_Of_Wheel

03: Lt

1


1

42: Std

1

1

1


Implementing a solution

How does a business analyst solve this problem?  Here's one suggested process. 


 Task
Example
1

Find common ground by agreeing to consistent IDs.

In order to combine the data, you'll need to coordinate with each of the groups to find one common way to describe things across groups.  Usually, companies have a "single source of truth" in which all options must be stored, and their descriptions are agreed to. This is usually an ERP or engineering system, and the official options are usually IDs: number codes that any person or any computer system speaking any language can use.   

If you can find no "single source of truth", don't worry.  Just create one.  List all the various ways each group describes the options, and in a quick meeting build consensus around one.  Or use everyone's ideas to build a brand new way that incorporates the best of everyone's needs.


We have 3 completely separate departmental descriptions of wheel options.  So, we replaced them with one that's standard and company-wide.  How?  In this example, we found that every group honored the ERP's Names and ID of the options, so we agreed to use those:

ID    Name
W01   Light
W02   Standard
W03   Heavy Duty

Using the ERP names and IDs also ensures that our configurator is speaking the technical language of how components and options are described throughout the company.  This helps ensure easier integrations with other tools the company might be building in the future.
2

Clarify assumptions.

Now that everyone speaks the same language, resolve any missing data.  If a source of information doesn't give information about all the possible choices, you can ask for an explicit or implicit answer.

  • explicit answers would require any group to "fill in the gaps" in their data tables for any missing data.
  • implicit answers means that instead of filling in the gaps, they explain what a missing row means with a business rule, and you use that rule in your query.  This can be useful if a group is adding information that is helpful, but not required, for your work. 



For example, if you have thousands of rows of data, and want to augment each row with a media image to make it easier for your user to select the right choice, the media team may not be able to create thousands of images.  Just accept a rule, such as "if an image is missing, show a placeholder image instead".  Simple rules like this you can implement in your query, rather than creating a table with thousands of rows.


We asked the Marketing team about the missing heavy-duty wheels: where can they be sold?  They agreed it was best to be explicit, and not implicit, in their description of where wheels can be sold.  They added the missing row to their table.

In our example, completing these two steps makes our data look like this:

ERP Names and Descriptions
Vehicle Use (per Engineering)
Engine Type (per Product Safety)
Sales Region (per Marketing)
ID

Smart Car

Sedan

SUV

Truck / Lorry

 ID

4-cyl Engine

6-cyl Engine

 ID

Europe

North America

Asia
Wheel
Type

W01

 Light W01

1

1



W01

1


W01

1


1

W02

 Standard W02


1

1


W02

1

1

 W02

1

1

1

W03

 Heavy Duty W03



1

1

 W03


1

 W03

1

1

1

Notice how every group has the same ID in their table.  This is key (sorry for the pun).  By sharing the same ID across various tables, you will be able to combine them accurately and reliably.



 Task
Example
3

Store the separate data tables in Epicor CPQ.

Depending on how this data is maintained, you can either copy/paste the data into local tables, or upload/integrate it into database tables.


We'll create 4 local tables.

  1. WheelType
  2. WheelType-by Vehicle Use
  3. WheelType-by Engine Type
  4. WheelType-by Sales Region

(Note how in these last 2 tables, we have the "WheelType" and "Legacy...Code..." columns as a courtesy to the people maintaining this table.  It helps them make the transition from their own legacy codes to the new ones. Those columns we must be sure we don't use in our query logic.  Within a couple update cycles, your users will be familiar with the new columns and these transitional ones are no longer needed.)
4

Determine which data table is the "backbone" of your query.

When you join data in multiple tables, it's best to think about which table has data that is most important.  Specifically, which table has rows that you always want to see.  Is there a table which provides the "master" list of rows?  If a row appears or disappears from this table, then it will appear or disappear from your results, even if that same ID appears in another table.


We chose the ERP table WheelType as our backbone, or primary table.  If a product is added or removed from that table, we want that change to guide our configurator options.  All the other tables are secondary: if a row is added or removed to them, we don't necessarily want to show that change before the option is officially listed in our ERP.
5

Create your option filter.

Use a source of "query" and connect your primary table.  The goal here is to simply confirm that all the rows you expect to appear are here.  You can always add logic to make fewer rows appear... but (based on the simple SQL we're using here) you can't make more rows appear.  It doesn't matter if the labels or descriptions are not correct, or even visible.  We're just counting the rows.


We created an option filter with this query:

And used it for the select options of a field:

Which appears in the UI:

We confirm that we see the maximum number of wheel choices here.  We don't care about the label or description yet.
6

Add a secondary table, using the best way to join it to the query.

Usually, the other tables you want to join to the main table are adding useful columns of information which you can use for filtering, or your users can see on-screen as a label, description, or image.  Therefore, usually a left join is best.  You can learn more about the various join types in a query, and they are easy to experiment with.  But a "left join" means "everything from the main table, with any rows from this table if those rows match something which already exists in the main table."

We updated our option filter with a left join to "WheelType-by Vehicle Use", using the matching ID columns to join the tables together.

Which adds new elements for our select options:

Did you notice that "ID" is listed twice?  That's because the join of the two tables gives you visibility to all the columns.  If two columns in two tables have the same name, you'll need to be sure to select the correct one.
7

Test your join, to ensure proper operation.


 Run your configurator: if you added one of the new columns to the value, label, description, or image, you'll see that column's data appear in your select control.
8

Repeat the previous 2 steps for each table you are joining.

We're joining three tables to our main table.  So here are the three additional joins, to make a total of four, after we've added and tested each one:
9

You've built a query.  Test how it handles changes in the data.

Try adding rows or deleting rows from your tables.  Does the query populate the select list as you expected?


Here, by joining each of our ancillary tables with a left join, we know that missing data from one of those tables will not cause the row to disappear.  Furthermore, any extra data (any rows with an ID that's not in our primary ERP table "WheelType") will not appear prematurely, before they appear in our primary table.
10

Add any filter logic to your query, based on business rules.

You've confirmed your query is working well.  Now you can add whatever business rules you need.  


For this example, we can add the truth table logic seen in this walkthrough.  

The sky's the limit!  You can add on more tables as you grow.  For example

  1. Start with one table: your ERP data of part numbers.
  2. Join to this a second table from your translations team, giving each part number a label and description in another language. 
  3. Join to this a third table from your marketing team, giving each part number an image to appear in the select list.


Your company has many sources of truth.  Coordinate them, so they play nicely, and gain the benefits from all of them.

Related articles



Was this article helpful?