This example demonstrates how to build a Product Mix optimization model that imports its input data from Excel tables. Instead of defining the profit, inventory, and part requirement values directly in the RASON model, the data is read from ProductMixExcel.xlsx using Excel datasources. The model uses indexCols and valueCols to interpret each Excel range as a table, then uses sorted index columns and the PIVOT function to align the imported data correctly for the optimization formulas. This approach makes the model easier to update, since users can change the input data in Excel without rewriting the RASON model.
ProductMixExcel.json
Open by clicking Examples – Optimization – Optimization with Data Binding in RASON Desktop Extension, The ProductMixExcel.xlsx workbook can be opened from C:\ProgramData\Frontline Systems\Examples\ExcelRunner.
{
"modelDescription": "…"
"modelType": "optimization",
"datasources": {
"parts_data": {
"type": "excel",
"connection": "ProductMixExcel.xlsx",
"selection": "Sheet1!G2:I12",
"indexCols": ["parts", "prods"],
"valueCols": ["qty"],
"sortIndexCols": true
},
"invent_data": {
"type": "excel",
"connection": "ProductMixExcel.xlsx",
"selection": "Sheet1!N2:O6",
"indexCols": ["parts"],
"valueCols": ["inventory"]
},
"profit_data": {
"type": "excel",
"connection": "ProductMixExcel.xlsx",
"selection": "Sheet1!K2:L4",
"indexCols": ["prods"],
"valueCols": ["profits"]
}
},
"data": {
"parts2": {
"binding": "parts_data",
"valueCol": "qty",
"comment": "Always select a single valueCol."
},
"invent": {
"binding": "invent_data",
"valueCol": "inventory"
},
"profit": {
"binding": "profit_data",
"valueCol": "profits"
}
},
"formulas": {
"comment": "PIVOT arranges data as matrix with prods across columns and parts across rows.",
"piv_parts": {
"formula": "PIVOT(parts2, { 'prods' }, { 'parts' })"
}
},
"variables": {
"x": {
"dimensions": ["prods"],
"value": 0,
"lower": 0,
"finalValue": [],
"indexValue": []
}
},
"constraints": {
"comment": "Since invent is a table, reference with the [] operator to sort according to the sorted indexCol.",
"c": {
"dimensions": ["parts"],
"formula": "MMULT(piv_parts, x) - invent[]",
"upper": 0,
"finalValue": [],
"indexValue": []
}
},
"objective": {
"comment": "Since profit is a table, reference with the [] operator to sort according to the sorted indexCol.",
"total": {
"formula": "sumproduct(x, profit[])",
"type": "maximize",
"finalValue": []
}
}
}
This RASON model demonstrates how to import Excel data into table objects using indexCols, valueCols, and sorted index columns. The model imports three Excel ranges from ProductMixExcel.xlsx: part requirements, available inventory, and product profits. These imported tables are then bound to RASON data objects and used to define a standard Product Mix optimization model.
The datasources section defines the Excel ranges used by the model. The parts_data datasource imports the quantity of each part required to produce each product from Sheet1!G2:I12. It uses two index columns, parts and prods, and one value column, qty. The sortIndexCols: true property tells RASON to sort the imported table by its index columns, which helps ensure the data is aligned correctly when it is later converted into a matrix. The invent_data datasource imports available inventory by part from Sheet1!N2:O6, while profit_data imports profit by product from Sheet1!K2:L4.
The data section binds each imported datasource to a RASON data object. The parts2 object binds to parts_data and explicitly selects the qty value column. This is important because table bindings should reference a single value column when the model needs a numeric array or matrix. The invent object binds to the available inventory column, and the profit object binds to the product profit column.
The formulas section uses the PIVOT function to reshape the imported part requirement table into a matrix. In this matrix, products appear across the columns and parts appear down the rows:
"piv_parts": { "formula": "PIVOT(parts2, { 'prods' }, { 'parts' })" }
This creates the matrix needed for the inventory constraints.
The variables section defines the decision variable x, indexed by prods. Each value of x represents the number of units to produce for a product. The variable has a lower bound of 0, so the model cannot produce a negative quantity.
The constraints section creates one constraint for each part. The formula:
"MMULT(piv_parts, x) - invent[]"
calculates the amount of each part used by the selected production quantities, then subtracts the available inventory. The upper: 0 setting requires usage to be less than or equal to available inventory. Since invent is a table object, invent[] is used to return its values in the sorted index order.
The objective section maximizes total profit using:
"sumproduct(x, profit[])"
This multiplies the production quantity for each product by its corresponding profit and sums the results. Because profit is also a table object, profit[] is used to return the profit values in sorted index order so they align correctly with the x decision variables.
Next Topic:
See the next topic for more Importing from Excel examples.