PIReporter for Batch Production Management populates a historical database stored in SQL Server. In order to access the data stored in this database - a connection to the database needs to be established between the analysis application and the physical data.

The connection to the database depends on several factors:

• How database authorization and authentication is implemented in your installation
• Were is the Database physically stored on the network
• Where is the BIModel Server - (The location where the analysis applications are executing) is physically located on the network
• What database connection technology is used

Database authorization and authentication can be defined (by your database administrator at the SQL Server level) to be carried by Windows (using the standard Domain technology) or by SQL Server itself. The following is a technical explanation of database authentication methods.

When authorization is carried by Windows, the Windows user that is executing the analysis application needs to be authorized to access the database. All analysis applications executed in BIModel Server are executing under the SYSTEM account of the machine where the server is installed (This was done to ensure maximum security). It does, however, offer a challenge when database authorization is carried by Windows across machine boundaries, because by default the SYSTEM account is not authorized to access network resources. If SQL Server and BIModel Server share the same physical machine - Windows authorization is easy to implement and should be easy to implement. If, however, SQL Server is installed on a separate machine from the machine where BIModel Server is installed, and you want to use Windows authorization - you need to authorize the SYSTEM account of the executing application to access the database on the remote machine. Under Windows 2000 and later - the SYSTEM account of a machine is known on the network as an account with the name Domain\Machine - where Domain is the name of the network Domain and Machine is the name of the computer in the network. Please notice that if the network is configured to use the old NT 4 or earlier security model - the SYSTEM account can not gain network privileges and you can not use Windows Authorization to cross machine boundaries

If your installation prefers to use SQL Server authorization, or you can not use Windows authorization because of one of the reasons mentioned above, you need to consider how SQL Server performs it's authorization. Users can be defined in SQL Server, and a password and privileges can be assigned to them. Communication to SQL Server can be performed using several transport methods. The two most popular ones are "Named Pipes" - a Windows interprocess communication method, and "TCP/IP" - the Standard Internet packet based communication layer. Because "Named Pipes" is a Windows resource - it is protected using the same authorization and authentication methods that are performed using "Windows" authorization. TCP/IP on the other hand - is a transport mechanism that does not use Windows authorization and authentication, and thus can be used to connect between machines and circumvent the problems associated with granting the SYSTEM account network privileges. Using TCP/IP - we can pass a user-id and password between machines for SQL Server authorization from a SYSTEM account on a remote machine. The analysis application provided with PIReporter for Batch Production Management take advantage of this capability.

Finally - before discussing the specific PIReporter for Batch Production Management database connection implementation - we need to discuss the database technology used to connect to the data source. There are two standard database methods in Windows - ODBC and ADO/OLEDB. ODBC is the older technology - it is usually easier to setup, but is not as fast and capable as ADO/OLEDB. The analysis models allow you to choose between ODBC and ADO/OLEDB connection. When you select ODBC, you are actually still using ADO to connect, but you do it via an extra layer of software that bridges between ODBC and the analysis model. The sample reports that ship with PIReporter for Batch Production Management use the ODBC connection that is automatically defined for you during setup. However - this connection assumes that the analysis applications and SQL Server are installed on the same machine. If this is not the case(*), you will have to switch to the ADO/OLEDB connection. * - Please notice that for performance reasons, even if the same machine is used for SQL Server and BIModel Server - the ADO/OLEDB connection could be faster.

The Analysis models include several parameters you need to define in order to establish a connection to the history database. By default, the connection parameters assume that you will connect using the ODBC technology using the ODBC Data Source Name BPMTHIST that is automatically set for you during the installation process. This assumes that the applications (and BIModel Server) are installed on the same machine where SQL Server is installed. If, however, this is not the case - you will need to do one of the following:

1. Define the correct connection with the ODBC Data Sources Control Panel Applet and provide the correct Windows authentication privelages over the network.
2. Define the "Connection Type" paramter in the Models to "ADO String" and set the "Computer Network Address", "Database Name", "User ID" and "Password" parameters that will build a TCP/IP SQL authorization connection string.

The Period Comparison model offers date selection based on the Job Start Date (JDJSDATE) field and works with selection windows. You can (and should) define a time window for every period compared.

A selection window is an intersection of date and time coordinates. Think of the time as a two diemnsional grid with the dates on the X axis and the hours of the day on the Y axis. Imagine that your database contains information for the entire year and you want to view all the jobs that ran between July 4 and October 14 during peak work hours (9:00AM to 2:30PM)

In order to define this time window - we set the From Date parameter to July 4th, the To Date parameter to October 14, the Time Window Start parameter to 09:00 and the Time Window End parameter to 14:30.

Sometimes you might want to create a time window that spans over night - for example - you want to see all the jobs that executed between 9:00PM and 5:00AM. In order to achieve a time window that spans overnight you will need to use both Period 1 and Period 2 time windows.

Assume for example that you want to see all the jobs that executed over last night. You will set the parameters as follows:

• Period 1 - Start Date = Yesterday, End Date = Yesterday, Time Window Start = 21:00, Time Window End = End of the Day
• Period 2 - Start Date = Today, End Date = Today, Time Window Start = Start of the Day, Time Window End = 5:00

If you want to check the overnight performace for a range of days you will set the Time Window Start and End of Period 1 and 2 as in the previous example, but you will set the Start Date and End Date of the 2nd Period to one day later than the Start Date and End Date of the 1st Period. (For example if you want all the overnight time periods that started in August you will set the parameters as follows: Period 1 Start Date = Aug 1, End Date = Aug 31, Period 2 Start Date = Aug 2, End Date = Sep 1).