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 Summary from Data Warehouse model offers date selection based on the Job Start Date (JDJSDATE) field and works with selection windows.

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).

Measure selection parameters allow you to investigate specific steps and jobs based on "extreme" situations. For example - if you notice that there are performance problems in a specific date/time that you trace to large I/O requests and you want to see what jobs and steps were active at this time - you can set the "Minimum Job I/O Count) to a large number and see only the steps that belong to jobs that took more than the I/O count you specified.

It is however important to understand how measure selection works and how summary for grouping levels works in this model. The measure selection is performed using a SQL query against the database. The results that are returned for the processing by the model code are the steps that meet the criteria you specify. Summarization is then performed only on these steps that were selected. It is important to point this out because of the following condition that could happen:

Assume that you set the Minimum Job I/O Count to 40,000 and also set the Minimum Step I/O Count to 500 - if you now run a report that groups information by the job you might see I/O Count values in the job level that sometimes are less than 40,000 - the reason is that some of the steps that belong to the job might have less than 500 I/O Count, and because they are not summarized by the report - the summary level that you see is less than the 40,000 you specified.

The same kinds of issues can happen if you combine measure selection of different measures (like I/O Count and CPU Time).

In other words - when using measure selection - it is important to understand that the selection process is separate from the summary process, and as such - a discrepancy in the results could happen. In most cases it is recommended to limit your reports to only one measure selection parameter - this will make the summarization results are easier to understand.

Day Segments allow you to divide the day into custom segments used for summarization purposes. The Job Start Date (JDJSDATE) field is used to calculate the day segment a record belongs to.

To use day segments in your reports one of your "Summary By" parameter fields has to be set to "Job Start Date - Day Segment".

The Summary from Data Warehouse model allows you to divide the day to 3 different segments - they are defined by setting the Segment 1 - Start, Segment 1 - End and Sengment 1 - Name parameters for the first segment, Segment 2 - Start, Segment 2 - End and Segment 2 - Name for the 2nd Segment and Other Segment - Name for the name of the 3rd segment (all the entries that do not fall in the first or the second segment).

Please notice that unlike date and time window selection the day segment parameters are not used for record selection - they are only used to divide the selected data into the correct time slots. Also - if there is an overlap between the first and second segments, records that fall in the overlap will be counted only for the first segment and not in the second segment. (Imagine that Segment 1 is from 8:00am to 05:00pm and Segment 2 is from 04:00pm to 11:00pm - records that fall into the time of 04:30pm for example, will be counted as part of Day Segment 1 and will not be counted as part of Day Segment 2).

The output created by the report will be sorted by the day segments based on the order of segment definition, not by the segment's name or time span. For example - if the first segment is between 7:00pm to 9:00pm and is called "Last part of the day", and the second segment is between 7:00am and 9:00am and is called "First part of the day" - the output will display the "Last part of the day" segment data before the "First part of the day" segment data.

Special functionality has been added to segments that span midnight. Imagine that you have a night time window defined as anything between 09:00pm to 05:00am on the next morning. If you define a segment with Start parameter of 21:00 and an End parameter of 05:00 the model will know that the segment spans midnight and even entries that start after midnight will be displayed as belonging to the segment of the previous date. For example - assume we defined a segment with these start and end parameters called "Night". Jobs that started on July 15 at 02:00 am will be reported as part of the "Night of July 14".