Analytica User FAQs/Application Integration

How can I integrate Analytica with Microsoft Excel?

Copy and paste: The simplest method is to copy and paste cells or ranges from Excel into an Analytica edit table, or from an Analytica result table into an Excel worksheet. Copy and paste of a range in Analytica works much the same as in Excel. If you select a single destination cell when pasting a range, it will try to fill the range below and to the right 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 rows and columns to the arrangement 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 will copy Arrays with more than two dimensions. When pasted into Excel, it shows the title and values for the row and column headers, and higher slicer dimensions.

Functions to read and write spreadsheets: Analytica Enterprise and above 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 covers this topic.

OLE linking: You can for a hot link that will propagate any changes from Excel to Analytica. 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.

ODBC access: You can read from and write to spreadsheets using ODBC and SQL queries with the DbQuery function. The webinar Querying an ODBC relational database goes over this briefly.

How do I access data in an external Database?

Use DbQuery and related functions in Chapter 21 of the Analytica User Guide. You'll need Analytica Enterprise.

To query data from an OLAP server, such as Analysis Services, you'll need Analytica 4.0 Enterprise and will use the MdxQuery function.

How can I call an external application from my model

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

• Use the built-in function RunConsoleProcess from your model. It provides a general facility to call an external application, pass it data directly or via a file. You can let the external process run in parallel or have Analytica wait for it to return and get results via a file.

• ADE provides an API for COM and .NET. Analytica does not currently provide a direct COM, .NET or Java object interface.