When Business Rules are defined in the Modeler there are Filter parameters that define the selections a user makes when running the rule. These parameters are used within the SQL stored procedures or scripts to limit the slice of the database the SQL commands should be executed against. Following is the method used to make these parameters available within SQL.

Callisto calls stored procedures with by executing an “exec” command in SQL. For SQL rules that consist of a SQL script, Callisto first defines the script as a temporary stored procedure and then call the procedure in the same way using an “exec” command in SQL. Before calling the “exec” command it first creates a temporary table named “#Temp_ParameterValues”. This table has the following structure:

  • [nvarchar](255) NOT NULL

  • [MemberId] [bigint] NULL

  • [StringValue] [nvarchar](max) NULL

For each Filter parameter defined in the Modeler this table is populated with the members selected by the user for that parameter. Each record consists of:

  • ParameterName – value from the Filter column in the Modeler identifying which parameter the entry is for. There can be multiple records with the same ParameterName when multiple members are selected by the user.

  • MemberId – internal identifier of the member selected by the user. This is the value used to identify records in the Fact tables and it is the MemberId value in the DS tables.

  • StringValue – null

In addition, to the Filter parameter values the table contains 2 additional records:

  • ParameterName – Model

    • MemberId – null

    • StringValue – name fo the Model the request is for

  • ParameterName – Userid

    • MemberId – null

    • StringValue – userid of the user who made the request. All records updated in the Fact tables by the rule should be identified with this value in the Userid field.

When requests are made to run Business Rules from Excel using the Data Manager or an Excel macro the filter parameters are checked to insure all combinations of Scenario members and other members are unlocked and the user has write access. Any members the user does not have write access to or that are locked are not included in the parameter members that are part of the request from the client to the server.

Requests to run Business Rules from Excel are handled by the NT Service. To get detailed information on the requests received from the client and the SQL statements executed to run your rule on the server you can increase the “tracelevel” setting in the Callisto.txt configuration file in the NT_Service folder. Trace information is logged to the file CallistoService.log in the same folder. Typically the tracelevel is set to 1 which logs a message on each successful submission and any errors that occur. By increasing the tracelevel to 10 you can obtain detail information on each request. (Caution: You should remember to always set the tracelevel back to 1 or 0 after you finish your investigation to prevent the log file from reaching an extremely large size very quickly.)

When developing Business Rules in the Modeler rules can be run while a rule is checked out for changes. These requests take place inside of security and the parameter members are NOT checked for write access or for locked data. Requests to run business rules as part of developing and testing business rules in the Modeler are executed immediately and do NOT use the NT Service. Messages returned when an error is encountered in SQL are returned at the bottom on the screen in the Modeler.