Analytica Docs - User contributions [en]

RunConsoleProcess

2011-11-08T19:55:25Z

J bryan: /* Description */

[[What's new in Analytica 4.0?]] >
[[Category:Doc Status C]] 

== Description ==

'''RunConsoleProcess''' lets an Analytica model run a ''console process'' -- that is, any Windows program. The program may be very simple with no graphical user interface that takes input from ''stdIn'' and writing output to ''stdOut'' -- or it may interact with the user directly. '''RunConsoleProcess''' gives the path and name of the application in the '''program''' parameter. It can feed input to the program via command line parameters in '''cmdLine''', or as input data given as text to the '''stdIn''' parameter, which is piped to the ''StdIn'' input channel of the console process. Normally, when the process completes, '''RunConsoleProcess''' will return a result (as text) any information the program writes to ''stdOut''. Analytica can also send data to a console process via a data files created with '''WriteTextFile''' or receive a data file created by the process using '''ReadTextFile'''.

==Declaration ==

'''RunConsoleProcess'''(
'''program''': Atomic Text,
'''cmdLine''': Optional Atomic Text,
'''stdIn''': Optional Atomic Text,
'''block''': Optional Atomic Boolean, /* default TRUE */
'''curDir''': Optional Atomic Text, /* default process directory */
'''priority''': Optional Atomic Number, /* default 0 = normal, same as Ana */
'''showErr''': Optional Numeric) /* defaults to 1=err msg */

'''RunConsoleProcess''' offers considerable flexibility through a number of other optional parameters:

'''block''': By default (or if you set '''block''' to True or 1), '''RunConsoleProcess''' ''blocks'' -- that is, Analytica waits until the console process terminates and returns a result before it resumes execution. While blocked, Analytica still notices Windows events: If you press ''ctrl-break'' (or ''ctrl-.'') before the process terminates, it kills the process, and ends further computation by Analytica -- just like what it does when Analytica is computing without another process.

If you pass False (0) to '''block''', '''RunConsoleProcess''' will not wait for the process to terminate: It immediately returns an empty text to Analytica. Analytica and the spawned process both continue running concurrently until they each terminate. If you press ''ctrl-.break'' (or ''ctrl-.''), it interrupts any computations by Analytica, but has no effect on the spawned process. An unblocked process is independent, and may continue running even after you exit Analytica. Unblocked processes are useful when you want to send data to another application for display, such as a special graphing package or GIS, or for saving selected results. It is hard to get any results or status back to Analytica from an unblocked process. It is usually best to use a blocked process if you need results back.

The '''program''' and '''cmdLine''' parameters are separated, rather than lumped together as one parameter, to protect against a common type of virus attack.

'''curDir''': Any relative directory path specified in the program parameter is interpreted relative to Analytica's '''CurrentDataDirectory'''. '''curDir''' specifies the directory the spawned process should use as its default directory to read and write files. If '''curDir''' is not specified, it uses its own directory as its default .

'''priority''' defines the priority that Windows should give the spawned process relative to the Analytica process. The default (0) runs the new process at the same priority as the Analytica program. A value of –1 or -2 lowers the priority, allowing other programs more of the CPU. A value of +1 or +2 raises the priority, dedicating more of the CPU to the process.

'''showErr''': By default, in blocking mode, if the process writes anything to '''stdErr''', Analytica will display it as an error message when the process terminates. If ''showErr''=2 it shows any text in '''stdErr''' as a warning message. If ''showErr''=0, and always in non-blocking mode, it ignores anything in '''stdErr'''.

'''Errors''': Analytica will give an error message if '''RunConsoleProcess''' cannot find or launch the specified program.

'''RunConsoleProcess''' fully supports Intelligent Arrays: If any parameter is passed an array, it will run a separate process for each element of the array. It runs multiple blocking processes in sequence, one after another. It runs multiple non-blocking processes concurrently.

== Examples ==

=== VB Script ===

Suppose the file '''HelloWorld.vbs''' is in your model directory and contains:

WScript.Echo "Hello World"

Your call to RunConsoleProcess might look like:

RunConsoleProcess( "C:\Windows\System32\CScript.exe",
"CScript /Nologo HelloWorld.vbs" )

The first parameter is the program (usually an EXE file) to be launched. You don't need to worry about quoting any spaces in the path name. The second parameter is the command line as it might appear on a command prompt. This expression will evaluate to the string "Hello World".

If you need to send data to the '''StdIn''' of the process, include an optional parameter StdIn:
RunConsoleProcess( "C:\Windows\System32\CScript.exe",
"CScript /Nologo HelloWorld.vbs",
StdIn: MyDataToSend )

where MyDataToSend evaluates to a text.

=== Batch File ===

In this example, a batch file named DoIt.bat is in the directory C:\Try. Also in that directory is a file named "data.log". The batch file, DoIt.bat contains the following:

# DoIt.bat -- dump the log
Type data.log

This batch file assumes it is run from the directory C:\Try. The call is:

RunConsoleProcess( "C:\Windows\System32\Cmd.exe",
"Cmd /C DoIt.bat",
CurDir: "C:\Try" )

or you can run it directly:

RunConsoleProcess( "DoIt.bat", "DoIt.bat", CurDir: "C:\Try" )

=== Reading Data from a URL ===

To read the contents of a web page given its URL, you can use:
RunConsoleProcess( "[[media:ReadURL.exe | ReadURL.exe]]", "ReadURL " & url )
where url is a text string as would appear in the address bar of your browser. You can download the [[media:ReadURL.exe | ReadURL.exe]] program by clicking on the link and saving. The first parameter to RunConsoleProcess may need to be set to the full path where you placed [[media:ReadURL.exe | ReadURL.exe]] unless you put it in your [[CurrentDataDirectory]].

A step-by-step example using [[media:ReadURL.exe | ReadURL.exe]] is given here: [[Retrieving Content From the Web]]. This example includes the source code for [[media:ReadURL.exe | ReadURL.exe]], and develops an example that obtains historical stock price data from the Yahoo finance web site.

= See Also =

* Webinar: [http://analyticaonline.com/WebinarArchive/2007-10-18-Calling-External-Applications.wmv Calling-External-Applications.wmv] (requires Windows Media Player)
* [[CurrentDataDirectory]]
* [[WriteTextFile]], [[ReadTextFile]]

Beta Tester Page

2011-10-31T21:29:53Z

J bryan: /* Beta 4.4 downloads */

[[Analytica 4.4]] is now in beta testing. As an Analytica user with active support, we invite you to become a beta tester. Send bugs and feedback to [mailto:support@lumina.com support@lumina.com].

= Becoming a Beta Tester =

* [[Beta Tester Page/Becoming a Beta Tester|Becoming a Beta Tester]], includes:
** How to become a beta tester
** Why become a beta tester
** Responsibilities of a beta tester
** Contributing to the Analytica Wiki
* [[Beta Tester Page/Submitting a Bug Report|Submitting a Bug Report]]

= What's New in 4.4? =

* Enhancements listed at [[What's new in Analytica 4.4?]]
* Changes from beta-build to beta-build are enumerated in [[Analytica 4.4 beta Release Notes|release notes]].

= Beta 4.4 downloads =

The current release is 4.4.0.13. The latest beta-build installers can be downloaded from:

* [http://AnalyticaOnline.com/ANA4.4/AnaSetup4_4_0_13.exe Analytica 32-bit]
* [http://AnalyticaOnline.com/ANA4.4/Ana64Setup4_4_0_13.exe Analytica 64-bit]
* [http://AnalyticaOnline.com/ANA4.4/AdeSetup4_4_0_13.exe ADE 32-bit]
* [http://AnalyticaOnline.com/ANA4.4/Ade64Setup4_4_0_13.exe ADE 64-bit]



= Beta Testing Licenses =

To get the beta software you need a beta-tester license. As a beta tester, you will normally have:
* An individual license for Analytica 4.2 or 4.3 already installed on your computer
* Active support
* An internet connection
In that case, the installer should automatically obtain the necessary beta license and you need to do nothing more (a feature that itself is being beta-tested).

If you are set up with a centrally-managed license for Analytica 4.2 or 4.3 (e.g., a floating license), the installer should still be able to obtain a beta license for you automatically, assuming you have active support and an internet connection.

If you use a floating license with active support and want to install a 4.4 beta license on the RLM server for easy use by anyone in your organization, please contact us via support@lumina.com.



== [[Analytica 4.4 beta Release Notes| Release Notes]] ==

See [[Analytica 4.4 beta Release Notes| release notes]] for what's changed in each beta release. It lists new features and fixes to significant problems.

== Known issues ==

* See the [[Pending Analytica 4.4 Issues]] page for known issues in the current beta build.
* See [[Analytica 4.4 beta Release Notes]] for issues that present in early beta bulids, but since fixed.

= FAQs: Frequently Asked Questions =

See [[Analytica User FAQs]] for frequently asked questions not specific to 4.4 or to beta.

= Other =
== User Group and Special 4.4 Webinars ==

Check the [[Analytica User Group]] page for information on webinars covering new 4.4 features and other topic of general interest to the Analytica community.

Consider being a presenter at a webinar yourself!

Analytica 4.4 beta Release Notes/Changes in 4.4.0.12

2011-10-13T21:45:24Z

J bryan: /* Preferences */

[[Analytica 4.4 beta Release Notes|<< Analytica 4.4 beta Release Notes]]

This page lists fixes in beta build 4.4.0.12 since the previous [[Analytica 4.4 beta Release Notes/Changes in 4.4.0.9|beta build 4.4.0.9]]. For major enhancements since 4.3, see [[What's new in Analytica 4.4?]].

= Clipboard (Copy/Paste) =

* Removed PNG type with transparent background from clipboard (experiment in built 4.4.0.9). Windows GDI+ does not handle transparent backgrounds well, many things didn't look good, so we include the background color always.

* Fixed a case where garbage cell data sometimes pasted from Excel.

* Fixed a problem where node colors were altered when they were pasted.

* copy/pasting of several esoteric data types from one variable to an edit table in the same Analytica works better. Things like handles, references, LP/QP terms, pictures, comparison terms.

* Improved awareness of when quotes should be omitted or included when pasting textual values between Analytica views.

* Copy diagram image (or node images) would not capture the image with identifiers when the '''Show by identifiers''' was set.

= Diagrams =

* Fixed cases where balloons would persist despite having left the window.

* Dragging of nodes interrupted by balloon hover.

* Removed the anachronism "Set Diagram Size" from the right-mouse context menu.

* Restored cross hatching of input or alias nodes when the original is cut but still on clipboard.

* The initial diagram window for a new model started a bit too far left, so that the left boundary was outside the desk window.

= Engine =

* Fixed a potential memory leak that could occur when a stack overflow condition occurs.

* Improved robustness of stack overflow handling, including fixing cases that could cause crashes.

* The [[Iterate]] function now accepts named parameters.

= Example Models =

* Updated example models that still had the anachronistic PICT format (from the old Mac days) to use modern PNG image formats. PICT is the one format that ACP does not support, so this allows these models to display their images when uploaded to ACP.

* The Newton Raphson example model was updated to use the [[Iterate]] function, rather than the [[Dynamic]] function.

= Excel Spreadsheet Functions =

Unfortunately, Excel 2010 has introduced numerous bugs into its COM API support, which impact the Analytica Spreadsheet functions. This build contains numerous enhancements to work around these Excel bugs to make the spreadsheet functions more robust.

* When saving a workbook via [[SpreadsheetSave]], Excel 2010 would set its visibility flag to hidden. Then, when you loaded it into Excel, it would appear you were staring at a blank spreadsheet (but it would open fine in Open Office, and other Spreadsheet functions would read the data as if it were there). From Excel, you would need to go to the '''View''' Tab and select '''Unhide''' to see anything. [[SpreadsheetSave]] now momentarily sets the visibility flag to unhidden before saving.

* Another Excel 2010 but is that when the visibility flag is toggled, a background EXCEL.EXE process fails to terminate when all references to a workbook are released. This would cause the Excel.exe process to linger on Task manager even after you closed your Analytica model. As a work around, when Analytica releases a workbook, it checks whether the spreadsheet is being viewed from an Excel graphical user interface. If not, it closes it, which then allows the Excel.exe process to terminate as it should.

* If a workbook instance is exogenously terminated after [[SpreadsheetOpen]] has established a connection to it, errors (like [[Error Messages/42645|The object invoked has disconnected from its clients]] or [[Error Messages/42645|The RPC server is unavailable]]) would occur the next time you tried to evaluate a spreadsheet function on the workbook. Exogenous termination could be the result of a bug in Excel 2010 that terminates the workbook when it is closed by the user from the graphical user interface even when an external application holds a reference to it, or when the Excel.exe background process is killed from task manager. Now it can also occur as a result of the previous bullet if two different Analytica processes are simultaneously using the same Excel workbook. Now, when this connection is broken, when you attempt to evaluate a spreadsheet function it will attempt to re-establish a connection, which usually means launching an instance of Excel again in the background.

= Expression Assist =

* Autocomplete would linger, often after backspacing over the first character of an identifier, and act as if first character of the identifier still existed.

* Parameter help for [[Iterate]]

* Parameter help for [[Var..Do]] and related declarations improved.

= File Saving/Loading =

* When a legacy (4.3 or earlier) model is loaded into 4.4, the "do not use ClearType fonts" flag should become set. This causes the same fonts to be used for node titles as in 4.3, so that work breaks occur at the same places. In one model submitted, this flag did not get set as it should have, so we fixed this case. We do recommend that you switch over to ClearType fonts ('''Diagram→Set Diagram Style...''' from the top-level model diagram) -- it looks better and several improved functionalities are only available when ClearType fonts are on -- but you will likely need to adjust some nodes due to altered work wrapping.

* The images of diagrams produced by '''File→Export...''' were often quite ugly, with splotchy fonts and lines. These now look much better.

* When an identifier collision occurs with a UDF when adding a module, and one UDF is renamed, it would lose its parameters attribute.

= Object Window =

* When editing a definition (or any other text field) before any other change has been made since the model was last saved, the Save option would be disabled, even though you've made edits in the field being edited.

= Preferences =

* The '''Use Excel date origin''' preference was using the wrong date offset in the previous 4.4 beta builds. Note: When loading a model from a previous build with '''Use Excel date origin''' set, you must un-check and re-check the preference box for the changes to take hold.

= Printing =

* Stray buttons and stray rectangles were visible on an Object Window's Print Preview.

* Several problems fixed with printout when a scaling percentage was used.

* Fixed an intermittent crash when previewing an Object Window.

= Typescript =

* The time required to print stuff to the typescript window is dramatically faster.

= Windows Management =

* Minimizing of windows was not functioning in 4.4.0.9

= ADE =

* The ADE installer now adds a folder to the Start Menu with shortcuts to the manuals, to AdeTest, and to the register component (i.e., <code>ADE.EXE /RegServer</code>).

* The AdeTest application is now configured to compile a 32-bit executable or 64-bit executable, and the 32-bit or 64-bit AdeTest.exe is included with the corresponding installer. This allows the '''New ADEW.CAEngine''' button to work when you install 32-bit ADE on a 64-bit Windows platform.

* CAObject::ResultTable with CAObject::ResultType=3 (PDF) picks up KDE Smoothness settings when the variable is set to use [[Kernel Density Smoothing]].

* When running ADE on web servers, some security issues may arise. For example, should the use of [[RunConsoleProcess]] be allowed at all? If anonymous end-users are uploading their own models, then maybe you'll want to disable this. Perhaps you'd like to make it impossible to call DbWrite from a variable that contains an input node, so as to prevent a malicious SQL-injection attack. Security can be configured on a function-by-function basis by modifying the Analytica.ini file. Comments present in that file provide the details.

* Uninstall of Analytica 32-bit removed the TypeLib registry settings for Analytica 64-bit.

Analytica 4.4 beta Release Notes/Changes in 4.4.0.12

2011-10-13T21:44:52Z

J bryan: /* Preferences */

[[Analytica 4.4 beta Release Notes|<< Analytica 4.4 beta Release Notes]]

This page lists fixes in beta build 4.4.0.12 since the previous [[Analytica 4.4 beta Release Notes/Changes in 4.4.0.9|beta build 4.4.0.9]]. For major enhancements since 4.3, see [[What's new in Analytica 4.4?]].

= Clipboard (Copy/Paste) =

* Removed PNG type with transparent background from clipboard (experiment in built 4.4.0.9). Windows GDI+ does not handle transparent backgrounds well, many things didn't look good, so we include the background color always.

* Fixed a case where garbage cell data sometimes pasted from Excel.

* Fixed a problem where node colors were altered when they were pasted.

* copy/pasting of several esoteric data types from one variable to an edit table in the same Analytica works better. Things like handles, references, LP/QP terms, pictures, comparison terms.

* Improved awareness of when quotes should be omitted or included when pasting textual values between Analytica views.

* Copy diagram image (or node images) would not capture the image with identifiers when the '''Show by identifiers''' was set.

= Diagrams =

* Fixed cases where balloons would persist despite having left the window.

* Dragging of nodes interrupted by balloon hover.

* Removed the anachronism "Set Diagram Size" from the right-mouse context menu.

* Restored cross hatching of input or alias nodes when the original is cut but still on clipboard.

* The initial diagram window for a new model started a bit too far left, so that the left boundary was outside the desk window.

= Engine =

* Fixed a potential memory leak that could occur when a stack overflow condition occurs.

* Improved robustness of stack overflow handling, including fixing cases that could cause crashes.

* The [[Iterate]] function now accepts named parameters.

= Example Models =

* Updated example models that still had the anachronistic PICT format (from the old Mac days) to use modern PNG image formats. PICT is the one format that ACP does not support, so this allows these models to display their images when uploaded to ACP.

* The Newton Raphson example model was updated to use the [[Iterate]] function, rather than the [[Dynamic]] function.

= Excel Spreadsheet Functions =

Unfortunately, Excel 2010 has introduced numerous bugs into its COM API support, which impact the Analytica Spreadsheet functions. This build contains numerous enhancements to work around these Excel bugs to make the spreadsheet functions more robust.

* When saving a workbook via [[SpreadsheetSave]], Excel 2010 would set its visibility flag to hidden. Then, when you loaded it into Excel, it would appear you were staring at a blank spreadsheet (but it would open fine in Open Office, and other Spreadsheet functions would read the data as if it were there). From Excel, you would need to go to the '''View''' Tab and select '''Unhide''' to see anything. [[SpreadsheetSave]] now momentarily sets the visibility flag to unhidden before saving.

* Another Excel 2010 but is that when the visibility flag is toggled, a background EXCEL.EXE process fails to terminate when all references to a workbook are released. This would cause the Excel.exe process to linger on Task manager even after you closed your Analytica model. As a work around, when Analytica releases a workbook, it checks whether the spreadsheet is being viewed from an Excel graphical user interface. If not, it closes it, which then allows the Excel.exe process to terminate as it should.

* If a workbook instance is exogenously terminated after [[SpreadsheetOpen]] has established a connection to it, errors (like [[Error Messages/42645|The object invoked has disconnected from its clients]] or [[Error Messages/42645|The RPC server is unavailable]]) would occur the next time you tried to evaluate a spreadsheet function on the workbook. Exogenous termination could be the result of a bug in Excel 2010 that terminates the workbook when it is closed by the user from the graphical user interface even when an external application holds a reference to it, or when the Excel.exe background process is killed from task manager. Now it can also occur as a result of the previous bullet if two different Analytica processes are simultaneously using the same Excel workbook. Now, when this connection is broken, when you attempt to evaluate a spreadsheet function it will attempt to re-establish a connection, which usually means launching an instance of Excel again in the background.

= Expression Assist =

* Autocomplete would linger, often after backspacing over the first character of an identifier, and act as if first character of the identifier still existed.

* Parameter help for [[Iterate]]

* Parameter help for [[Var..Do]] and related declarations improved.

= File Saving/Loading =

* When a legacy (4.3 or earlier) model is loaded into 4.4, the "do not use ClearType fonts" flag should become set. This causes the same fonts to be used for node titles as in 4.3, so that work breaks occur at the same places. In one model submitted, this flag did not get set as it should have, so we fixed this case. We do recommend that you switch over to ClearType fonts ('''Diagram→Set Diagram Style...''' from the top-level model diagram) -- it looks better and several improved functionalities are only available when ClearType fonts are on -- but you will likely need to adjust some nodes due to altered work wrapping.

* The images of diagrams produced by '''File→Export...''' were often quite ugly, with splotchy fonts and lines. These now look much better.

* When an identifier collision occurs with a UDF when adding a module, and one UDF is renamed, it would lose its parameters attribute.

= Object Window =

* When editing a definition (or any other text field) before any other change has been made since the model was last saved, the Save option would be disabled, even though you've made edits in the field being edited.

= Preferences =

* The '''Use Excel date origin''' preference was using the wrong date offset in the previous 4.4 beta builds.
When loading a model from a previous build with '''Use Excel date origin''' set, you must un-check and re-check the preference box for the changes to take hold.

= Printing =

* Stray buttons and stray rectangles were visible on an Object Window's Print Preview.

* Several problems fixed with printout when a scaling percentage was used.

* Fixed an intermittent crash when previewing an Object Window.

= Typescript =

* The time required to print stuff to the typescript window is dramatically faster.

= Windows Management =

* Minimizing of windows was not functioning in 4.4.0.9

= ADE =

* The ADE installer now adds a folder to the Start Menu with shortcuts to the manuals, to AdeTest, and to the register component (i.e., <code>ADE.EXE /RegServer</code>).

* The AdeTest application is now configured to compile a 32-bit executable or 64-bit executable, and the 32-bit or 64-bit AdeTest.exe is included with the corresponding installer. This allows the '''New ADEW.CAEngine''' button to work when you install 32-bit ADE on a 64-bit Windows platform.

* CAObject::ResultTable with CAObject::ResultType=3 (PDF) picks up KDE Smoothness settings when the variable is set to use [[Kernel Density Smoothing]].

* When running ADE on web servers, some security issues may arise. For example, should the use of [[RunConsoleProcess]] be allowed at all? If anonymous end-users are uploading their own models, then maybe you'll want to disable this. Perhaps you'd like to make it impossible to call DbWrite from a variable that contains an input node, so as to prevent a malicious SQL-injection attack. Security can be configured on a function-by-function basis by modifying the Analytica.ini file. Comments present in that file provide the details.

* Uninstall of Analytica 32-bit removed the TypeLib registry settings for Analytica 64-bit.

DbQuery

2011-10-05T02:07:54Z

J bryan: /* ODBC to read Excel data */

[[Category:Database Functions]]
[[Category:Doc Status C]] 

''(Requires Analyica Enterprise or Power Player)''

= DbQuery( connectionString, sql'', key, datesAsText'' ) =

Used to define an index Variable. The definition of the index should contain only one DbQuery() statement. «ConnectionString» specifies a data source (e.g., 'DSN=MyDatabase'). The «sql» parameter defines an SQL query.

When placed as the definition of an index Variable, [[DbQuery]]() will be evaluated as soon as the definition is complete. When it is evaluated, the actual query is performed. The resulting result table is cached in the ''Att_recordSet'' attribute of the variable (or local index) containing the call to [[DbQuery]], which is then subsequently accessed by [[DbTable]]() or [[DbLabels]]().

[[DbQuery]]() returns a sequence 1..n, where n is the number of records (rows) in the result table. ([[What's new in Analytica 4.2?|''new to 4.2'']]): Alternatively, you can use one column from the result set as the index labels by specifying the name of the column in the optional «key» parameter.

[[DbQuery]]() should appear only once in a definition, and if it is embedded in an expression, the expression must return a list with n elements.

[[DbQuery]]() processes the sql statement in read-only mode, so that the data source cannot be altered as a result of executing this statement. To alter the data source, use [[DbWrite]]().

== New to 4.0 ==

In Analytica 4.0 (but not earlier releases), a local index can hold the record set. This means you can create a user-defined function that bundles several database query steps. For example:

Function DbFullTable(connectionStr, sql: text)
Definition:
[[Index]] Rows := [[DbQuery]](connectionStr, sql);
[[Index]] Cols := [[DbLabels]](Rows);
[[DbTable]](Rows, Cols)

This user-defined function returns the full relational table in a single call, using local indexes, Rows and Cols.

== Date and Time data ==

''New to [[What's new in Analytica 4.2?|Analytica 4.2.3]]''

Date and time values retreived from the database are returned as text. Beginning with release 4.2.3, you can also opt to have date or time data types returned as date-time numbers. A date-time number in Analytica encodes a point in time as the number of days elapsed since the date origin (usually Jan 1, 1904). Date-time numbers respond to the Date style Number Format.

The system variable, ''Sys_DbDatesAsText'' contains a global preference for the handling of dates by [[DbQuery]] or [[DbWrite]]. When this system variable is 0 (false), dates are returned as text. When set to 1, dates are returned as date-time numbers. For backward compatibility with pre-4.2.3 releases, it is 0 by default.

Individual calls to [[DbQuery]] can override the system default by specifying the optional «datesAsText» parameter.

= Connection String Syntax =

Comprehensive references for connection string syntax across different database services are at [http://www.connectionstrings.com www.connectionStrings.com].

== Using a configured DSN ==

The ''connectionString'' parameter specifies the data source that you will be querying. The most common syntax for the connection string is:
:"DSN=«name»"
where ''«name»'' is the name of a system or user data source that you have previously configured in the '''Data Sources (ODBC)''' wizard (accessable in Windows via
:Start → Programs → Administrative Tools → Data Sources (ODBC).

You can augment a DSN-based connection string with additional keyword values. These are specific to the database driver you are using, and the names of these additional keywords must be found from other documentation. For example, some databases require a login and password, which can be added to the connection string as:
:"DSN=MyDatabase; UID=John; PWD=Lightning"
Some databases require two logins, one for the server, and another for the DBMS. In this case, both can be specified:
:"DSN=MyDatabase; UID=John; PWD=Lightning; UIDDBMS=JQR; PWDDBMS=Thunder"

There are a couple of advantages to using a DSN to specify your data source. First, the wizard in Windows makes it relatively easy to select the appropriate driver and configure the parameters used to access the data source. Second, the ''connectionString'' parameter in your model remains very simple. And third, the model is not reliant on the precise location of the database, so that if someone else uses your model on a different machine, the connection string can remain valid. On the downside, it does mean that whenever you share your model with someone else, they must configure a DSN before using it.

== Directly accessing the database file ==

You can also access your database directly, without configuring a DSN. To do this, at a minimum you need to specify the ODBC driver name and the database location in the connection string.

The driver name can be discovered by evaluating
:[[SqlDriverInfo]]("")
from within Analytica.

Many drivers use the keyword DBQ for specifying the file path to the database. Some database drivers (examples are Microsoft Access and Excel) store the database in a single file, so the path specifies the actual file. Other drivers (e.g., Paradox, Text driver) store their database in many files in a single directory, in which case DBQ locates the directory. Here are some example of connection strings:
:"Driver=Microsoft Access Driver (*.mdb); DBQ=C:\MyData\MyDatabase.mdb"
:"Driver=Microsoft Excel Driver (*.xls); DBQ=" & [[CurrentModelDirectory]]() & "\SourceData.xls"
:"Driver=Microsoft Paradox Driver (*.db); DBQ=C:\MyData\MyDatabaseDirectory"
:"Driver=Microsoft Text Driver (*.txt; *.csv); DBQ="C:\MyData\MyCsvTables"

Note that when specifying the location, you must use absolute path names. The ODBC drivers do not know anything about Analytica's current directories. You may find [[CurrentModelDirectory]] and [[CurrentDataDirectory]] functions useful for computing full path names when your data source is in the same directory as your model, or a subdirectory.

= SQL Syntax =

Databases contain tables, each table having a name. The simplest and most common SQL statement is one that retrieve all the data from a given table:
:"SELECT * FROM ''tableName''"
Many databases also allow you to define queries, which may join several underlying tables to create a new computed table results. The names of such predefined queries can usually be used in place of ''tableName'' as well.

The SELECT statement is SQL's workhorse for reading data. You can expand on this simple form by selecting only a subset of the columns, selecting only a subset of the rows using a ''search condition'', joining multiple tables, sorting the selected rows, eliminating duplicate rows, or grouping (aggregating) the rows. The following shows the general syntax used by most SQL databases, where brackets surround optional parts:
SELECT column_list
[ FROM list_of_tables ]
[ WHERE search_condition ]
[ ORDER BY column_list [ASC|DESC] ]
[ GROUP BY group_by_expression ]
[ HAVING group_search_conditon ]

If you are creating a complex query, involving the joining of multiple tables and complex WHERE or GROUP BY criteria, a convenient way to do this by using an SQL designer application. Graphical user interfaces that help you create your SQL queries can be found in Microsoft Access, in a program called Microsoft Query, in Microsoft SQL Server management studio, etc.

You can often bring entire tables into Analytica and "join" them from within Analytica, using Analytica expressions. However, if you are dealing with huge databases, then use of more sophisticated SQL can be used to reduce the amount of data that needs to be transferred and held in Analytica's memory.

Note that DbQuery cannot be used to modify the database -- it executes its SQL in a read-only model. Other SQL statements can be used to modify a database -- creating new tables, adding or removing data to/from tables, etc. To utilize these SQL statements, you must use [[DbWrite]].

== ODBC to read Excel data ==

ODBC can be used to read data from Excel spreadsheets, at least in a rudimentary way. However, if you want to read from Excel spreadsheets, we recommend using the [[Functions To Read Excel Worksheets|Spreadsheet Access functions]] instead, which are similar in result, but can also be used from Analytica 64-bit (The JET database drivers, required for ODBC queries into Excel, are only available for use by 32-bit processs). Nevertheless, understanding how to access spreadsheets through ODBC can be informative, so a description is provided here.

When using ODBC from Excel, data that you might read should be organized as a table, where the first row of the table contains column labels. If you have a single table on a worksheet, you can use:
: "Select * from [Sheet1$]".
The Excel ODBC driver wants the the name of the sheet to be followed by a dollar sign, and because of this, you must surround the sheet name and dollar sign with square brackets.

If you name a region in your workbook, you can read the contents of that region using:
: "Select * from [RegionName]"
In this case, you don't have to specify a worksheet name, and you don't include the trailing dollar sign. The first row of your region needs to have column labels. If the first row does not have column labels, it will omit the first row from the result and return placeholder labels like ["F1","F2","F3"].

You can query a cell range using, e.g.:
: "Select * from [Sheet1$D17:F55]"
Again, the first row (in this example, D17:F17), of the range needs to have labels. I do not know of a way of reading a transposed table (i.e., where the labels are row headers, and the fields for each record are in the same column). If the block of data don't have the labels for the first row, as long as it doesn't start in row 1, you can always start your query from one row previous. E.g., if you want cells F9:H12, then query F8:H12.

One downside with using ODBC to query an Excel workbook is that the ODBC driver does not seem to pick up on changes made from Excel itself while Analytica and Excel are both open. If you change something in Excel, you may need to exit Analytica, then save the Excel file with the change, then re-enter Analytica. It appears that the Excel ODBC driver locks the file in certain ways and caches certain things so that changes are not always picked up once the driver has been loaded. When you load the file into Excel once the workbook has already been queried, it seems to convert to a read-only mode a soon as you attempt to save.

On the upside, there are advantages to using ODBC for reading data from Excel over OLE linking. Especially if you use a one table per sheet organization, then the size of the table adapts very gracefully with ODBC when the amount of data in Excel changes.

= Programs with ODBC support =

ODBC drivers exist for the following programs (this is only a partial list), which means if you need to exchange data between these applications and Analytica, the use of ODBC may be an option.

* SQL Server
* Microsoft Access
* Oracle
* MySQL
* IBM DB2
* Sybase
* Paradox
* Microsoft Excel
* Text files -- csv, fixed-width delimited, others.
* FoxPro

* AS/400 (iSeries)
* Caché
* Filemaker
* Firebird
* Informix
* Ingres
* Interbase
* Lotus Notes
* Mimer SQL
* Pervasive
* Postgre SQL
* Progress
* SQLite
* Teradata

These support the OLE DB interface (Analytica uses straight ODBC) and thus may be usable (I'm not totally sure):
* Microsoft Project
* Lightbase
* SQLBase
* VistaDB
* Active Directory

= 64-bit ODBC Drivers =

To use ODBC from Analytica 64-bit, you must have 64-bit versions of the ODBC driver for your database. Microsoft SQL Server, Oracle, MySQL and most other major commercial databases provide 64-bit ODBC drivers, and can be used with Analytica 64-bit.

The Microsoft JET drivers (for Microsoft Access, Microsoft Excel and CSV file queries) require Microsoft Office 2010 or later, and you must install the x64 edition of Office. Prior to the Office 2010 release, 64-bit drivers were not available. If you install Office 2010 x64, then you can query Access, Excel, or flat files directly via ODBC.

Without MS Access x64 installed (e.g., if you have only Office 2003 or 2007), you won't be able to query an Access database directly. There is a method, however, of querying a Microsoft Access database from Analytica 64-bit by configuring the free Microsoft SQL Server express driver to act as a proxy to the Access database. The steps for doing this are detailed at [[Querying Access database from Analytica 64]]. Again, this is now unnecessary if you have MS Access 2010 x64.

= Library =

Database Functions

= Additional Help =

* Follow a step-by-step mini-tutorial to get your first DbQuery working with a Microsoft Access database here: [[DbQuery/Step-by-Step querying Microsoft Access]]

* An Analytica User Group webinar on the use of DbQuery to read data in relational databases can be viewed at: [http://AnalyticaOnline/WebinarArchive/2008-02-07-Using-ODBC-Queries.wmv Using-ODBC-Queries.wmv].

* A bug has been identified in 4.0 (fixed in 4.1) that causes the following message to appear incorrectly in some circumstances:
First arg, <rowIndex>, to DBLabels must be defined with a DBQuery( ) statement
The bug only occurs when the node holding the DbQuery is an index node that has just been defined. If you use a Variable node, rather than an index node, the problem does not occur, or if a model is saved and reloaded the problem does not occur. If you change the index to a variable node, you must force the definition to be re-evaluated, such as by inserting a space in the definition. If you encounter this problem when it should not be appearing, our recommendation is to save and reload the model, which will correct the problem.

= See Also =

* [[DbTable]]
* [[DbLabels]]
* [[DbWrite]]
* [[SqlDriverInfo]] -- Note: [[SqlDriverInfo]]("") returns list of drivers
* [[OpenExcelFile]], [[WorksheetCell]], [[WorksheetRange]] -- alternative functions for reading from Excel sheets (Ana 4.1+)
* [[ReadCsvFile]]
* [[ReadTextFile]]
* [[MdxQuery]] -- for querying OLAP services
* [[Querying Access database from Analytica 64]]

Logical Operators

2011-01-05T00:41:09Z

J bryan: /* Or */

[[category:Operators]]
[[Category:Doc Status C]] 

Logical operators are used to combine boolean assertions. Analytica uses the values True=1 and False=0.

When applied to values other than 0 or 1, other parameter values are interpreted as follows:
* a '''non-zero numeric''' is treated as true
* '''NaN''' is treated as ambiguous (could be 0 or 1)
* '''Null''' results in a warning, with a Null result.
* Any '''other''' data type results in an error.

Note: The value special Undefined is considered deprecated in Analytica 4.0. Null is now used exclusively for a non-existent value from within Analytica expressions.

[[INF, NAN, Null, and Undefined]]
= And =

A And B

True if both A and B are true. False if either A or B is false.

In this table, x signifies a numeric value that is not equal to zero (and is not NaN).
{| border="1"
! And !!   0   !! x !! NaN !! Null
|-
! 0 || 0 || 0 || 0 || Null
|-
! x || 0 || 1 || NaN || Null
|-
! NaN || 0 || NaN || NaN || Null
|-
! Null || Null || Null || Null || Null
|}

= Or =

A or B

True if A or B or both are true.

In this table, x signifies a numeric value that is not equal to zero (and is not NaN).
{| border="1"
! Or !!   0   !!   x   !! NaN !! Null
|-
! 0 || 0 || 1 || NaN || Null
|-
! x || 1 || 1 || 1 || Null
|-
! NaN || NaN || 1 || NaN || Null
|-
! Null || Null || Null || Null || Null
|}

= Not =

{| border="1"
! Not !!   0   !!   x   !! NaN !! Null
|-
! || 1 || 0 || NaN || Null
|}

Concat

2010-12-22T20:53:37Z

J bryan: /* Examples */

[[category:Array Functions]]
[[Category:Doc Status D]] 

= Concat(A,B'',I,J,K'') =

Concatenates lists or arrays.

When A and B are 1-D arrays,
Concat(A,B)
returns a list (1-D array) consisting of their elements. This form is often used to concatentate two indexes to obtain the elements for a third index.

When A and B are 1-D arrays with a common index

Concat([A],[B])
returns a 2-D array with two columns. Notice square brackets surrounding the variables. If only two parameters are used, column index is .K.

When A and B are arbitrary arrays, where A has index I and B has index J, then
Concat(A,B,I,J,K)
concatenates (i.e., joins) arrays A and B, with the new result indexed by K. You must provide an index K whose length is the sum of the lengths of I and J. Often the index K is obtained using the first form of concatenate.

(new to 4.1) You can omit the K parameter:
Concat(A,B,I,J)
when you do so, the function creates a new local index named K for the result.

= Library =

Array functions

= Examples =

Index In1 := ['a','b','c']

Concat( In1, ['z'] ) &rarry ['a','b','c','z']

index I := [1, 2];
index J := ['a', 'b'];
index K := concat(J, 'c');
var A := Array( I, J, 1 );
var B := Array( I, 2 );
Concat( A, [B], J, , K)

Result:
a b c
1| [1, 1, 2
2| 1, 1, 2]

= See Also =

* [[ConcatRows]]
* [[ConcatN]]

Sort

2010-12-21T00:21:51Z

J bryan: /* Decreasing sort order */

[[Category:Transforming functions]]
[[Category:Sorting Functions]]
''New to Analytica 4.2''

= Sort(A,I) =

Sorts an array ''A'' in ascending order along index ''I''. The result has the same indexes as the original array, but the elements are re-ordered.

== Comparison to [[SortIndex]] ==

[[Sort]] is equivalent to:
:A[I=[[SortIndex]](A,I)]

In several respects, [[SortIndex]] is more general than [[Sort]], but in some cases [[Sort]] is more convenient. [[SortIndex]] provides you with the sorted-order, which can then be used to re-index more than one array. Also, if you are sorting a multi-dimensional array, re-ordering all columns based on the sort-order of one particular "key" column, then you will need to use [[SortIndex]]. When [[SortIndex]](A,I) is applied to a multi-dimensional array, every column is sorted independently.

== Multi-key sorting ==

Like [[SortIndex]], [[Sort]] can be used to perform multi-key sorting. With [[Sort]], this is most convenient when the key columns are ordered from left-to-right along the ''keyIndex''. With a multi-key sort, the first column of the array (along the ''keyIndex'') determines the sort order, unless there are ties, in which case the second column breaks the tie. If there is still a tie, then the third column breaks the tie, and so forth. The syntax for using a multi-key sort is:
:[[Sort]](A,I,keyIndex)

Note that this syntax provides a convenient way to sort a 2-D array, using the first column to determine the sort order for remaining columns.

== Descending sort order ==

The optional parameter ''descending:true'' reverses the sort order, e.g.:
:[[Sort]](A,I,descending:true)

== Case Sensitivity ==

[[Sort]] compares text values in a case-sensitive fashion, with upper case letter coming before lower-case letters in the sort-order. For example, "Zebra" comes before "apple". The optional ''caseInsensitive:true'' parameter causes comparisons to occur in a case-insensitive fashion:
:[[Sort]](A,I,caseInsensitive:true)

= Library =

Array functions

= Examples =

Suppose
{|border="0"
!     A → ||
{| border="1"
! !!colspan="3" | J
|-
!rowspan="5" | I
| 3 || 2 || 1
|-
| 1 || 3 || 2
|-
| 2 || 1 || 2
|-
| 1 || 2 || 1
|-
| 2 || 1 || 2
|}
|}

{|border="0"
!     [[Sort]](A,I) → ||
{| border="1"
! !!colspan="3" | J
|-
!rowspan="5" | I
| 1 || 1 || 1
|-
| 1 || 1 || 1
|-
| 2 || 2 || 2
|-
| 2 || 2 || 2
|-
| 3 || 3 || 2
|}
|}

{|border="0"
!     [[Sort]](A,I,keyIndex:J) → ||
{| border="1"
! !!colspan="3" | J
|-
!rowspan="5" | I
| 1 || 2 || 1
|-
| 1 || 3 || 2
|-
| 2 || 1 || 2
|-
| 2 || 1 || 2
|-
| 3 || 2 || 1
|}
|}

= See Also =

* [[SortIndex]]
* [[Rank]]

Cdf and Pdf Functions

2010-11-24T19:06:39Z

J bryan: /* Detailed Description */

[[Category:Statistical Functions]]
[[Category:Doc Status C]] 
[[What's new in Analytica 4.0?]] >

===PDF(X) and CDF(X)===
(New to 4.0)
These functions generate histograms for a sample for quantity X. They are similar to the methods used to generate uncertainty views of sample values, PDF or CDF, for graphs or tables. But, as functions, they return results as arrays available for further processing, display, or export. They can also generate histograms for data with indexes other than Run, the default for uncertain samples. PDF generates a mass function or density function according to whether X is discrete or continuous. CDF generates a cumulative mass or cumulative distribution function. They can also generate Histograms

==== Simple Usage ====

A common use is to generate the PDF or CDF table of an uncertain variable X, generated as a random sample, e.g.:

PDF(X)
CDF(X)

Here the distribution, Ch1, contains uncertainty, and is therefore has a sample indexed by Run.

==== Histograms ====

You can also use PDF and CDF to generate histograms of data that is not uncertain, i.e. indexed by something other than '''Run'''. For example, to generate a histogram of Y over index J, use:

PDF(Y, J)

==== Parameters====

'''PDF''' and '''CDF''' have one required parameter:
:'''X''': The sample data points, indexed by I
and several optional parameters:
:'''i''': The index over which they generate the histogram. By default this is Run -- i.e. a Monte Carlo sample -- but you can also specify another index to generate a histogram over another dimension.
:'''w''': The sample weights. Can be used to weight each sample point differently. Defaults to system variable '''SampleWeights'''.
:'''discrete''': Set true or false to force [[#Is the distribution discrete or continuous? | discrete or continuous]] treatment.
:'''binMethod''': Selects the histogramming method used. Otherwise it uses the system default set in the '''Uncertainty setup''' dialog from the '''Result''' menu. Options are:
:: 0 "equal-X": Equal steps along the X axis (values of X).
:: 1 "equal-sample-P": Equal numbers of sample values in each step.
:: 2 "equal-weighted-P": Equal sum of weights of samples, weighted by '''w'''.

:'''samplesPerStep''': An integer specifying the number of samples per bin. Otherwise it uses the system default set in the '''Uncertainty setup''' dialog from the '''Result''' menu.
:'''domain''': Name of a variable whose Domain attribute should be used (see below)>

;[[Syntax]]: PDF(x: [I]; I: IndexType=Run; w: NonNegative[I]=SampleWeighting; discrete: optional boolean; binMethod, samplesPerStep: optional positive; domain: Unevaluated = x)

;[[Syntax]]: CDF(x: [I]; I: IndexType=Run; w: NonNegative [I]=SampleWeighting; discrete: Optional Boolean; binMethod, samplesPerStep: Optional Positive; domain: Unevaluated = x)

==== Is the distribution discrete or continuous? ====

'''PDF(X)''' generate a probability mass function or density function according to whether it thinks '''X''' is discrete or continuous. '''CDF(x)''' does the same, generating a cumulative mass or cumulative probability function. If '''X''' contains text values it knows '''X''' must be discrete. If '''X''' contains numbers with few or no identical values, it guesses continuous. If '''X''' contains numbers with many identical values, it guesses discrete.

Usually, they guess correctly. But, sometimes, such as with discrete distributions over a wide range of integers, it may be ambiguous. In such cases, there are two ways to make sure it does what you want:

* If '''X''' is a variable, you can set its '''Domain''' attribute as:
** '''Continuous'''
** '''Discrete Numeric''', '''Categorical''', '''List of Numbers''', '''List of Labels''', or '''Index''' -- all of which it treats a discrete.
** '''Automatic''' is the default, meaning Analytica guesses.

* If '''X''' is an expression, set the optional parameter '''Discrete''' to '''PDF''' or '''CDF''' as True or False

If '''X''' contains text values, i.e. categorical data, you may want to control the order of the categories, e.g. ["Low", "Medium", "High"]. You can do this by specifying the its Domain as a List of Labels with these values, or as an '''Index''', referring to an Index using them. Alternatively, you can provide a list of labels to the optional '''Domain''' parameter of '''PDF''' or '''CDF'''. If '''X''' is an expression rather than a variable, this is your only choice.

=== Weighted Data ===

Normally, each point in a data set or sample carries equal weight. However, in some situations data or sample points may have unequal weights. When the running index is Run (i.e., the case of variables with uncertainty), the SampleWeighting system variable provides the default weighting (which itself defaults to equally weighted points). The default weighting can be provided explicity using the w parameter, for example:

PDF( Total_revenue, w: (SalesByRegion<ProjectedSales)[Region='East Coast'] )

This expression computes the posterior probability of total revenue given that the east coast sales are less than projected, which is accomplished by providing a zero weight for all points not consistent with the assumption.

== Detailed Description ==

''Text below needs editing''
PDF and CDF behave differently depending on whether the domain of x is discrete or continuous. PDF and CDF determine whether the domain is discrete or continuous as follows. If x contains non-numerics, then the domain is discrete. Otherwise, if the optional discrete parameter is specified, its value (true=discrete, false=continuous) is used. Otherwise, if the domain parameter contains a variable identifier, the domain attribute for that variable is consulted. (The user of PDF/CDF would seldom, if ever, explicitly specify the domain parameter, but if the first parameter to PDF/CDF is a variable identifier, then the domain parameter will pick that up). If the domain attribute is set to Continuous, then a continuous domain is used. If it is set to Discrete (numeric or categorical), if the domain is an explicit list or list of labels, or if it is set to an Index, then a discrete domain is used. Otherwise (i.e., the domain attribute is
automatic", or the domain parameter is not a variable identifier, PDF uses some heuristics to "guess" whether x is discrete numeric or continuous. The heuristics judge such things as whether the value in x appear to be regular integer multiples (as would occur from a discrete distribution such as Poisson or Binomial).

When PDF/CDF uses a discrete domain, the domain parameter contains a variable identifier, and the domain attribute of that variable contains an explicit list of values, or an index with explicit values, then those values are used, in that order, as the domain of PDF/CDF. If no such domain declaration is available, then the set of unique values in x are used as the domain. If a variable with an explicit domain was found, that variable serves as the index of possible values. If so such domain variable was utilized, a local magic "magic" local index named "PossibleValues" is used. The result is indexed either by this domain index or the local "PossibleValues" index. The value in each cell of the array is the relative frequency of occurrence of that value.

When PDF uses a continuous domain, the result will be indexed by "Step" and "DensityIndex" (plus any abstracted indexes in the parameters). Step is a "magic" local index with the name "Step". DensityIndex is a system variable index containing two elements, ["X", "Y"]. The "X" column of the result contains the centroid for each "bin" of the histogram, while the "Y" column contains the density estimate for that bin.

To construct a continuous PDF/CDF, the algorithm must partition the set of reals into bins. The key operation is determining where to place these bins (or, more accurately, the boundaries between these bins). There are three algorithms that may be employed for doing this: EqualX (binMethod=0), Equal Weighted Prob (binMethod=1) and Equal Sample Prob (binMethod=2). EqualX divides the range of value occurring in X into equal sided intervals. The Equal Weighted P selects bins with variable sizes so that each bin contains the same amount of weighted probability mass. The Equal Sample P binMethod selects bins with variable sizes so that roughly the same number of points fall into each bin. (With a constant weighting, Equal Weighted P and Equal Sample P should be identical, up to numeric round-off effects). The samplesPerStep controls how finely partitioned the histogram is, specifying how many points, on average, should land in each bin. These controls can be supplied explicitly via optional parameters to PDF or CDF, or if they aren't specified, PDF will obtain them from the settings specified on the Uncertainty Settings dialog. If x (i.e., domain) is a variable identifier, then the local settings for that variable are used, otherwise, the local settings for the variable whose definition contains the call to PDF is used. If that is not set, then the global settings are used. Analytica defaults to an EqualX binMethod for PDF, and an EqualP method for CDFs.

Once the bins are selected, the density estimate is just the ratio of the proportion of points in the bin divided by the bin's width.