A popular business scenario for integrating MS Excel with a SQL Server database involves using MS Excel as a flexible front-end "calculation engine" to perform complex business functions that would otherwise be more expensive and less flexible to build into SQL Server (Goal Seek and Solver, as two examples).  This is a cost-effective means to provide very flexible and highly interactive method for users to manipulate large sets of data stored in a SQL Server database — while taking full advantage of powerful MS Excel functionality that is familiar to most power users.

ODBCODBC or Webservices is used to connect MS Excel to one or more SQL Server database(s).  Once connected, SQLqueries are written to the database to extract data from specific columns and tables.

A great example of using MS Excel as a SQL Server calculation engine is the MS Excel Goal Seek functionality.   Goal Seek is part of a suite of commands sometimes called "what-if analysis".  When you know the desired result of a single formula but not the input value, you can use the Goal Seek feature.  When goal seeking, Microsoft Excel varies the value in one specific cell until a formula that's dependent on that cell returns the result you expect.   In the example below, Goal Seek is used to determine the appropriate interest rate in cell B3 for the target payment value in B4 of $900.

The Goal Seek command uses a simple algorithm and, as a result, may have problems converging on a solution if the function is not linear.   Because the iteration process begins with guesses around the source cell, if Goal Seek is having problems converging on a solution, changing the value in the source cell may help. Alternatively, you can use Microsoft Excel Solver Add-in for a more powerful alternative to Goal Seek for SQL Data Integration.

The Excel Solver function helps determine the optimal values of specific cells in a spreadsheet.  Excel Solver is commonly used to determine the minimum or maximum range of values to satisfy specific business objectives, for example:

• How can a large drug company determine the monthly product mix at their Indianapolis plant that maximizes corporate profitability?>
• If Microsoft produces Xbox consoles at three locations, how can they minimize the cost of meeting demand for Xbox consoles?
• What price for Xbox consoles and games will maximize profit from Xbox sales?
• A large manufacturing company would like to undertake 20 strategic R&D initiatives that will tie up money and skilled programmers for the next five years. They do not have enough resources to undertake all 20 projects. Which projects should they undertake?
• How do bookmakers find the best set of "ratings" for NFL teams to set accurate point spreads?
• How should I allocate my retirement portfolio among high-tech stocks, value stocks, bonds, cash, and gold?

The Excel Solver optimization model has three components: the target cell, the changing cells, and the constraints. The target cell represents the objective or goal. We want to either minimize or maximize the target cell. In the example of a drug company's product mix, the plant manager would presumably want to maximize the profitability of the plant during each month. The cell that measures profitability would be the target cell. The target cells for each situation described at the beginning of the article are listed in the following table. Keep in mind that in some situations, you might have multiple target cells. For example, Microsoft might have a secondary goal to maximize Xbox market share.

Changing cells are the spreadsheet cells that we can change or adjust to optimize the target cell. In the drug company example, the plant manager can adjust the amount produced for each product during a month. The cells in which these amounts are recorded are the changing cells in this model.

Constraints are restrictions you place on the changing cells. In our product mix example, the product mix can't use more of any available resource (for example, raw material and labor) than the amount of the available resource. Also, we should not produce more of a product than people are willing to buy. In most Solver models, there is an implicit constraint that all changing cells must be non-negative. Remember that a Solver model does not need not have any constraints.