Product Mix Optimization: Excel vs. Power BI Data Binding

These two RASON models solve the same optimization problem — maximizing profit across a product mix, subject to parts-inventory constraints. The math and structure are identical; what differs is where the input data comes from and how it's bound into the model. This page walks through both, explaining what each section does and calling out where the two examples diverge.

The Problem Being Solved

A manufacturer makes several products, each requiring a mix of parts. Each part has a limited inventory, and each product earns a different profit per unit. The model decides how many units of each product to produce — the decision variable — to maximize total profit without using more of any part than is available.

Model Flow

Step Section What it does
1 datasources Imports parts-usage, inventory, and profit tables from Excel or Power BI.
2 data Binds each imported datasource to a RASON data name used later in the model.
3 formulas Pivots the parts-usage table into a matrix, for use in the constraint.
4 variables Defines the decision variable — units to produce, one per product.
5 constraints Limits total parts used per part to the available inventory.
6 objective Maximizes total profit across all products.

Importing Data

Both models import three tables: how many of each part each product requires, how much of each part is in stock, and how much profit each product earns per unit. Every datasource entry needs an indexCols array (the column or columns that identify each row — like "which part" or "which product") and a valueCols array (the actual data values tied to those index columns). The order of indexCols and valueCols must match the order of columns in the source range or query, since RASON reads the shape positionally.

The parts-usage table needs two index columns instead of one, because each row represents a part/product pair rather than a single item — for example, "3 units of Part A go into Product 1."

From Excel

"datasources": {
  "parts_data": {
    "type": "excel",
    "connection": "c:\Users\nicol\.vscode\extensions\frontlinesystems.rason-2026.6.0\out\clientExamples\Data\ProductMixExcel.xlsx",
    "selection": "Sheet1!G2:I12",
    "indexCols": [ "parts", "prods" ],
    "valueCols": [ "qty" ]
  },
  "invent_data": {
    "type": "excel",
    "connection": "c:\Users\nicol\.vscode\extensions\frontlinesystems.rason-2026.6.0\out\clientExamples\Data\ProductMixExcel.xlsx",
    "selection": "Sheet1!N2:O6",
    "indexCols": [ "parts" ],
    "valueCols": [ "inventory" ]
  },
  "profit_data": {
    "type": "excel",
    "connection": "c:\Users\nicol\.vscode\extensions\frontlinesystems.rason-2026.6.0\out\clientExamples\Data\ProductMixExcel.xlsx",
    "selection": "Sheet1!K2:L4",
    "indexCols": [ "prods" ],
    "valueCols": [ "profits" ]
  }
}

For an Excel datasource, connection is a full file path to the workbook, and selection is a sheet name plus a cell range (for example, Sheet1!G2:I12). RASON reads that range as a table, treating the leftmost columns as the index and the rightmost as the value, based on how many entries are listed in indexCols and valueCols.

From Power BI

"datasources": {
  "profits_data": {
    "type": "PowerBI",
    "connection": "pbix=ProductMixPowerBI",
    "selection": "EVALUATE SELECTCOLUMNS('profits', \"Products\",'profits'[Products], \"Profit\",'profits'[Profit])",
    "indexCols": [ "Products" ],
    "valueCols": [ "Profit" ],
    "direction": "import"
  },
  "inventory_data": {
    "type": "PowerBI",
    "connection": "pbix=ProductMixPowerBI",
    "selection": "EVALUATE SELECTCOLUMNS('partsInventory', \"Parts\",'partsInventory'[Parts], \"Available\",'partsInventory'[Available])",
    "indexCols": [ "Parts" ],
    "valueCols": [ "Available" ],
    "direction": "import"
  },
  "parts_data": {
    "type": "PowerBI",
    "connection": "pbix=ProductMixPowerBI",
    "selection": "EVALUATE SELECTCOLUMNS('tableParts', \"Parts\",'tableParts'[Parts], \"Products\",'tableParts'[Products], \"Qty\",'tableParts'[Qty])",
    "indexCols": [ "Parts", "Products" ],
    "valueCols": [ "Qty" ],
    "direction": "import"
  }
}

For a Power BI datasource, connection identifies the open report by name rather than a file path. selection is a DAX query — specifically EVALUATE SELECTCOLUMNS(...), which pulls named columns out of a Power BI table and returns them as rows, the same shape RASON expects from an Excel range. Each Power BI datasource also sets "direction": "import", making explicit that data is flowing from Power BI into the model — the same keyword can be set to "export" when writing results back out, which the Excel examples handle differently (see the Excel export demo on the Optimization page).

Binding Data to RASON Names

A datasources entry only describes how to fetch a table — it doesn't put that data to use in the model. The data section does that, giving each imported table (or a column from it) a short name that the formulas, constraints, and objective sections can reference directly.

"data": {
  "profits": { "binding": "profits_data" },
  "inventory": { "binding": "inventory_data" },
  "parts_data": { "binding": "parts_data" }
}

binding names the datasource being pulled in. valueCol, when present, tells RASON which single column from that datasource's valueCols to expose under this data name — useful when a datasource has more than one value column and only one is needed here.

Shared Logic: Formulas, Variables, Constraints & Objective

From here, both models follow the same four-part logic, just under different names:

Concept Excel example Power BI example
Products dimension prods Products
Parts dimension parts Parts
Pivoted parts-usage matrix piv_parts pivot_parts
Inventory data name invent inventory
Profit data name profit profits
Parts-usage constraint c num_used

Variables

"variables": {
  "x": {
    "dimensions": [ "<products dimension>" ],
    "value": 0,
    "lower": 0,
    "finalValue": [],
    "indexValue": []
  }
}

x is the decision variable: how many units of each product to produce. Setting dimensions to the products dimension tells RASON to create one variable per product, rather than a single scalar. value is the starting value RASON uses before solving; lower sets a floor of 0, since a negative production quantity doesn't make sense. finalValue and indexValue start empty and are filled in by RASON once the model is solved.

Formulas: Reshaping the Data

"formulas": {
  "<pivoted matrix name>": {
    "formula": "PIVOT(<parts-usage data>, { '<products dimension>' }, { '<parts dimension>' })"
  }
}

The imported parts-usage table is a flat list of part/product/quantity rows. To use it in a constraint, it needs to be reshaped into a matrix — parts as rows, products as columns — so it can be multiplied against the production variable. PIVOT does that reshaping: the second argument sets the column dimension, and the third sets the row dimension.

Constraints: Staying Within Inventory

"constraints": {
  "<constraint name>": {
    "dimensions": [ "<parts dimension>" ],
    "formula": "MMULT(<pivoted matrix name>, x) - <inventory data name>",
    "upper": 0,
    "finalValue": [],
    "indexValue": []
  }
}

MMULT performs matrix multiplication between the pivoted parts-usage matrix and the production variable x, producing the total quantity of each part that would be consumed by a given production plan. Subtracting the inventory data name gives, for each part, "amount used minus amount available." Setting upper to 0 tells the solver that this result can never be positive — in other words, usage can't exceed inventory. dimensions set to the parts dimension means one constraint is created per part, each checked independently.

Objective: Maximizing Profit

"objective": {
  "total": {
    "formula": "sumproduct(x, <profit data name>)",
    "type": "maximize",
    "finalValue": []
  }
}

sumproduct multiplies the production quantity of each product by its profit per unit, then adds up the results across all products — giving total profit for a given production plan. Setting type to maximize tells the solver to search for the production plan that makes this total as large as possible, without violating any constraint.