Analytica User FAQs/Application Integration

How can I integrate Analytica with Microsoft Excel?

There are several ways to integrate your Analytica models to read data from an Excel spreadsheet and to write data to a spreadsheet:

Copy and paste: The most straightforward method is just to copy and paste cells or ranges from Excel into an Analytica edit table, or from an Analytica result table into an Excel worksheet. Copying and pasting of a range in Analytica works much the same as in Excel. If you select a single destination cell when pasting a range, it fills the same size range below and to the right of the destination cell if there's space. If you select a destination range larger than the cell or range you are pasting, it repeats the pasting to fill out the destination range. One advantage of Analytica is that you can rearrange the destination table by pivoting its rows and columns to the form you want.

If you copy a list (1D range) from Excel into an Analytica List (of numbers or labels) that is shorter than the list you are pasting, it will ask if you want to expand the list to accommodate the results. This is a useful way for generating Indexes in Analytica to correspond to row or column headers from an Excel table.

You can copy an entire table from Analytica, including its index headers in rows and columns, using Copy table from the Edit menu. This can copy Arrays with more than two dimensions. When pasted into Excel, it arranges an array with 3 or more dimensions into a single 2D sheet as a series of 2D tables. It shows the title and value of each higher "slicer" dimension(s) above each 2D table.

See Copy and Paste between applications for details.

Functions to read and write spreadsheets: Analytica offers a set of functions to open a spreadsheet, and read and write cells and ranges. These functions can use standard spreadsheet references ("sheet1!A1:Z20") or names of ranges. Functions To Read Excel Worksheets. The webinar Functions for Reading Directly from an Excel File shows how to do this.

OLE linking: OLE is a Microsoft technology for creating "hot links" between documents that propagate changes from Excel cells or ranges to linked variables in Analytica and vice versa. Set up a 2-D table in Analytica having the same number of cells as your Excel source, then Copy/Paste or Copy/Paste link. The advantage of OLE linking is that it propagates automatically, updating a linked document each time you open it with any changes to its source document. The disadvantage is that it is not very robust to changes in index dimensions or the size of ranges, unlike the Analytica functions to read and write spreadsheets. See OLE linking for more.

ODBC access: You can read from and write to spreadsheets using ODBC (a standard protocol for accessing relational databases) and SQL queries with the DbQuery function. The webinar Querying an ODBC relational database goes over this briefly. See Database access for more.

How do I access data in an external Database?

Use DbQuery and related functions (see Database access for details) You'll need Analytica Enterprise or Optimizer.

You can also access multidimensional (or star) data data from an OLAP server, such as Analysis Services using MdxQuery function.

How can I call an external application from my model?

• Analytica offers a set of Functions To Read Excel Worksheets and Functions to Write Data to Excel Worksheets.

• Analytica (Enterprise and Optimizer editions) offers COM Integration to call other applications using the widely supported Microsoft Component Object Model (COM). For example, it lets you integrate with all Microsoft Office applications. (Introduced in Analytica 4.6)

• Use OLE linking for external applications that support it, such as Microsoft Office.

• The built-in function RunConsoleProcess from your model lets Analytica call an external application. It can pass data directly as a parameter or via a file. You can let the external process run in parallel (asynchronously) or have Analytica wait for it to return and get results via a file (blocking).

• ADE provides an API for COM and .NET.