visual kpi administrator's guide

Visual KPI Administrator's GuideUpdated for Build: 3.8.6.457
TABLE OF CONTENTS
Introducing Transpara Visual KPI ___________________________________________ 1 Architecture ....................................................................................................................... 2 Visual KPI Components .................................................................................................... 3 Getting Started with Visual KPI ......................................................................................... 4
Working with the Visual KPI Server Manager __________________________________ 5 Open the Visual KPI Server Manager ............................................................................... 5 Navigating in the Visual KPI Server Manager ................................................................... 5
Using the Visual KPI Databases Folder ...................................................................... 6 Using the Visual KPI Services Folder .......................................................................... 7 Using the Visual KPI Web Sites Folder ....................................................................... 9 Using the Visual KPI Server Report .......................................................................... 14
Configuring the Error Logging Level ............................................................................... 16 Configuring the Regional Settings ................................................................................... 17
Using Security with Visual KPI _____________________________________________ 19 Visual KPI Web Site Components ................................................................................... 19 Visual KPI and Active Directory Security ........................................................................ 20 Using Multiple Web Sites to Implement Security............................................................. 20
ACME Manufacturing Example ................................................................................. 21 Steps for Implementing Active Directory Security ........................................................... 22 Configuring Active Directory Security .............................................................................. 23
Granting Permissions to the Virtual Directories ......................................................... 23 Setting Permissions on the Visual KPI Designer Files .............................................. 25
Understanding SQL Server Security ............................................................................... 26 SQL Server Login Account ........................................................................................ 26 SQL Server Security for Interfaces ............................................................................ 26
Troubleshooting Virtual Directory Issues ........................................................................ 27 Installing Visual KPI Interfaces _____________________________________________ 29
Working with the ODBC Interface ................................................................................... 29 Installing the ODBC Interface .................................................................................... 30 Configuring the ODBC Interface ................................................................................ 35 Verifying the ODBC Connection ................................................................................ 36 Testing the ODBC Interface Connection ................................................................... 37
Working with the PI Interface .......................................................................................... 39 Installing the PI Interface ........................................................................................... 39 Configuring the PI Interface ....................................................................................... 43 Verifying the PI Interface Connection ........................................................................ 44 Testing the PI Interface Connection .......................................................................... 44
Working with the SQL Server Interface ........................................................................... 47 Preparing the Stored Procedures for the SQL Server Interface Installation .............. 47 Inserting the Stored Procedures for the SQL Server Interface .................................. 48 Installing the SQL Server Interface ........................................................................... 48 Configuring the SQL Server Interface ....................................................................... 52 Verifying the SQL Server Interface Connection ........................................................ 53 Testing the SQL Server Interface Connection ........................................................... 54
Using the SmartSignal Interface ..................................................................................... 56 Installing the SmartSignal Interface ........................................................................... 57
Introducing Transpara Visual KPI
Configuring the SmartSignal Interface ...................................................................... 61 Verifying the SmartSignal Interface Connection ........................................................ 63 Testing the SmartSignal Interface Connection .......................................................... 64
Using Connect Strings with the Interfaces ...................................................................... 66 Troubleshooting Interface Issues .................................................................................... 66
Using the Visual KPI Alert Server ___________________________________________ 69 Configuring the Visual KPI Alert Server .......................................................................... 69
Visual KPI Alert Server Configuration File ................................................................. 71 Configuring the Visual KPI Alert Server Service ............................................................. 72
Appendix A – Using an SMTP Virtual Server __________________________________ 75 Installing the SMTP Service Components ....................................................................... 75 Configuring the Default SMTP Virtual Server .................................................................. 76 Testing the SMTP Virtual Server ..................................................................................... 79
Appendix B – Using the SQL Demo Database ________________________________ 83 Creating the SQL Server Demo Database ...................................................................... 83 Entering New Data in the SampleData Table ................................................................. 84 Quick Start to Using the ODBC Interface Demo ............................................................. 84
Appendix C – Visual KPI and BlackBerry ____________________________________ 87 BlackBerry Server Configuration FAQ ............................................................................ 87 Blackberry Device Configuration FAQ ............................................................................ 89
Example BlackBerry Configurations .......................................................................... 90 Index __________________________________________________________________ 138H93
1
Transpara’s Visual KPI is a revolutionary application that provides you with access to your real-time business key performance indicators right now, whether you are sitting at your desk or in the field. With Visual KPI, key operating measures are made available to stake-holders at every level of the corporation, delivering a configurable solution for implementing such initiatives as Balanced Scorecard, Six Sigma and Sarbanes-Oxley compliance.
Based on Microsoft SQL Server and ASP.NET 2.0, and leveraging such industry standard technologies as OSIsoft’s proven RtPM platform, this web-based application intelligently determines the screen size available to the requesting user and optimizes the interaction with Graphics, Scorecards, and Trends for the specific device. In addition, the system can be configured either online or offline through an intuitive Excel spreadsheet.
Visual KPI allows you to easily create and securely distribute real-time key performance indicators by simply configuring scorecards and KPIs in Microsoft Excel, or via the Web Editor. You can publish them for instant consumption by anyone in your organization or even on the Internet. By providing ready access to key performance information when and where needed, more informed and accurate decisions can be made, improving business performance and profitability.
e
A R C H I T E C T U R E Transpara’s Visual KPI is based on an architecture that separates the data capture from the way in which this data is organized or queried. This allows a company to change the fundamental way it consumes the data without affecting the data capture method. In fact, companies can add new analyses, or change existing analyses and then apply these analyses not only to new incoming data but all existing data as well.
Transpara’s approach is to keep the underlying source systems pristine and unaffected. Visual KPI lowers the “marginal cost of curiosity” by allowing new questions and data schemas to be applied to existing data without requiring the cube to be re-architected. The software constantly extracts new data from the online systems for a continuous stream of data from business operations.
Visual KPI Administrator's Guide 3
V I S U A L K P I C O M P O N E N T S Visual KPI consists of several components. The following is a brief overview of each of the Visual KPI components.
♦ Visual KPI Server The Visual KPI Server component consists of web pages and services that form a Service Oriented Architecture (SOA) used for accessing the KPI data from an external system. The Visual KPI Server allows you to retrieve real-time data from an external system through your configured Views, Scorecards and KPIs.
♦ Visual KPI Server Manager The Visual KPI Server Manager allows you to install the Visual KPI Server, manage your current installation, and upgrade your current Visual KPI installations. Using the Visual KPI Server Manager you can easily perform side-by-side deployments, allowing you to continue to use your live system while you test your new system.
♦ Visual KPI Website The Visual KPI Website presents varying levels of detail of your KPI data. The Visual KPI Website uses certificate-based authorization of inbound devices, ensuring access by only qualified, known users. Your users can access this data via the Internet using a standard browser, as well as any number of mobile devices such as PDAs, Smart Phones, Blackberry Phones, Tablet PCs, and SharePoint Web Parts.
♦ Visual KPI Database The Visual KPI Database is the storage location for the View, Scorecard, and KPI definitions. The Visual KPI Server retrieves the definition data from the Visual KPI Database, allowing the Visual KPI Server to determine the system and tag from which KPI data is retrieved.
♦ Visual KPI Alert Server The Visual KPI Alert Server notifies users via SMTP (e-mail) when certain events occur. When the Visual KPI Alert Server determines that a limit boundary has been crossed for a KPI, a message is sent to the e-mail address specified for the KPI. Alerting can be defined for each KPI; you need to define the Notification Type and e-mail address in the KPI.
♦ Visual KPI Cache Server The Visual KPI Cache Server caches data, as it is accessed, resulting in a significant increase in performance after it has run the first time. The Visual KPI Cache Server runs as a Windows service.
♦ Visual KPI Interfaces Interfaces allow Visual KPI to communicate with and gather data from external systems. There are a number of interfaces for use with Visual KPI – ODBC, SQL Server, SmartSignal and Excel Services (MOSS 2007 Server as data source), are just a few of the available interfaces.
♦ Visual KPI Designer The Visual KPI Designer (formerly referred to as the Visual KPI Excel Editor) is used to define your KPI data. Because the Visual KPI Designer is in a familiar environment – Microsoft Excel – you can quickly and easily define your KPI data. The Visual KPI Designer is a special spreadsheet that allows you to communicate with your Visual KPI Server, allowing you to retrieve, review and upload your Visual KPI definitions data.
PI
G E T T I N G S T A R T E D W I T H V I S U A L K P I The following is a brief overview of the steps required to start using Visual KPI.
♦ Install Visual KPI Ensure your system meets the minimum requirements. Once you’ve installed the Visual KPI Server, configure the error logging requirements on the server’s components. Refer to the Visual KPI Installation Guide.
♦ Configure Security, if Required Visual KPI supports all forms of standard security implementations used on an application server. This document provides instructions for implementing Active Directory security using multiple Visual KPI web sites.
♦ Install and Configure Visual KPI Interfaces The Visual KPI Interfaces are used to allow the Visual KPI Server to communicate with the external system that contains your KPI data. These interfaces can be easily installed using the Visual KPI Server Manager. Refer to the Visual KPI Installation Guide.
♦ Configure Visual KPI Alert Server If you want to use alerting, you will need to configure the Visual KPI Alert Server to work with your Visual KPI Server.
♦ Define Visual KPI Configuration Data You need to define your Interfaces to specify the external systems from which your real-time data will be retrieved. You can configure Trend Ranges, including the Default Range. You can also specify your own custom logo and alternate text for the logo in the Logo Properties.
♦ Define your KPI Data The Visual KPI Editors are used to define your Views, Scorecards and KPIs. Views and Scorecards allow you to organize your KPI data in a manner that makes sense for your organization. You can configure multiple Scorecards, each of which can include one or more KPIs. A KPI references multiple tags or database values that define the KPI’s target value, range specifications and status.
♦ View Your Data Using the KPI Website The Visual KPI website is made up of several web pages that present varying levels of detail of your KPI data. You can access this data via the Internet, or any number of mobile devices.
3
Working with the Visual KPI Server Manager
The Visual KPI Server Manager allows you to install the Visual KPI Server, manage your current installation, and upgrade your current instances. The Visual KPI Server Manager also allows you to generate a report on all of your Visual KPI Server instances. Because the report is in a single HTML file, it can easily be shared with others.
O P E N T H E V I S U A L K P I S E R V E R M A N A G E R Double-click the Visual KPI Server Manager shortcut to open the Transpara Visual KPI Server
Manager.
N A V I G A T I N G I N T H E V I S U A L K P I S E R V E R M A N A G E R When you open the Visual KPI Server Manager, the left pane contains a hierarchy of objects: Databases, Services, and Web Sites. Each of these components allow you to perform functions specific to Visual KPI.
♦ Open the Transpara Visual KPI Server Manager. You can increase the size of the Visual KPI Server Manager by dragging the lower right corner of the window until the window is an appropriate size.
6 Transpara Corporation
er
The following sections describe the basic capabilities of the Visual KPI Server Manager.
Using the Visual KPI Databases Folder The Databases folder in the Visual KPI Server Manager allows you to review the contents of your Visual KPI databases.
1. Open the Transpara Visual KPI Server Manager.
2. Right-click the Databases folder and select Connect.
The Connect to Server dialog displays.
3. Enter your Database Server info (example: SQLServerName\InstanceName).
4. Select the appropriate security authentication method. If your SQL Server instance is configured to use Integrated Security, select the “Windows authentication” option. If your SQL Server instance is configured to use Mixed mode security, select the “SQL Server authentication using the Login ID and password below” option, and enter your SQL Server login credentials.
5. Click Connect.
If there are no Visual KPI databases installed in the specified SQL Server instance, a message displays indicating "No Visual KPI databases found.” Click OK to close the message dialog and return to the Visual KPI Server Manager.
N avigating in the Visual K
PI Server M anager
If there is a Visual KPI database in the specified SQL Server instance, the Connect to Server dialog closes and the Databases folder expands to show the current Visual KPI databases installed in the specified SQL Server instance.
NOTE: If you installed to a SQL Server 2005 Express database: You will need to enable its browsing capabilities (via the Surface Area Configuration Tool) to see the installed instance names.
6. Select the SQL Server instance. The detail pane displays the Visual KPI database Name, Visual KPI Version number, and the number of Views, Scorecards, KPIs, and Interfaces included the database.
7. If you have multiple database instances into which you have installed Visual KPI, you can connect to each of them using the Visual KPI Server Manager. Repeat the previous two steps to connect to additional SQL Server instances and review their Visual KPI contents.
8. If changes have been made to the contents of the SQL Server instances, while the Visual KPI Server Manager is open, right click the Databases folder, or a specific SQL Server instance folder and select Refresh. The display updates to include the most recent changes.
Using the Visual KPI Services Folder The Visual KPI services, installed on the local machine only, are listed within the Services folder. You can view information on each listed service, as well as control the status of the service. If you need to review/modify Visual KPI services that exist on a different machine, you must run the Visual KPI Server Manager on that machine.
1. Open the Transpara Visual KPI Server Manager.
er
2. Expand the Services folder. Each of the locally installed Services are listed in the hierarchy. The detail pane includes the service’s Name, current Status, Startup Type, Version and Path to the service’s executable.
3. Select one of the services in the list. The detail pane displays the Windows Service information, as well as the configuration information for each service.
PI Server M anager
4. You can modify any of the configuration settings that are not grayed out. Double-click the setting name to select the setting’s value, or click in the setting’s cell, and enter the new value. After making changes, click Apply. The configuration information is saved.
5. You can control the Services by right-clicking and selecting Start, Stop, Restart or Refresh.
Using the Visual KPI Web Sites Folder The Visual KPI web sites, installed on the local machine only, are listed within the Web Sites folder. You can view information on each listed web site, as well as modify the web site’s configuration information. If you need to review/modify Visual KPI web sites that exist on a different machine, you must run the Visual KPI Server Manager on that machine.
er
1. Select the Web Sites folder. The hierarchy displays a list of installed web sites, and the detail pane lists details on each of these web sites. This is the same list of web sites you would see in the Web Sites folder in the IIS Manager. The detail pane displays the web site’s Description, Identifier, current State, Host Header value (if any), assigned IP Address, and Port.
2. Select a specific web site’s folder in the hierarchy. The detail pane displays the installed virtual directories in the hierarchy, as well as the virtual directory’s Name, installation Path, and Version number.
PI Server M anager
3. Expand the web site’s folders to display the web site’s installed components. Select the Visual KPI virtual directory under the installation web site. The detail pane displays virtual directory’s configuration information.
4. You can modify any of the configuration settings that are not grayed out. Double-click the setting name to select the setting’s value, or click in the setting’s cell, and enter the new value. After making changes, click Apply. The configuration information is saved.
er
5. Select the Interfaces folder to view the current list of installed interfaces. The detail pane displays the Interfaces’ Name, installation Path, and Version.
6. Select an interface to review/modify its configuration settings. You can modify any of the configuration settings that are not grayed out. Double-click the setting name to select the setting’s value, or click in the setting’s cell, and enter the new value. After making changes, click Apply. The configuration information is saved.
7. You can review general information for the WebEditor and WebService by selecting them. You cannot make any changes to either of these.
8. Right-click any of the installed Visual KPI virtual directories under the installation web site and select Browse. The browser opens and displays the associated Visual KPI web site.
PI Server M anager
If the web site does not display, refer to Troubleshooting Virtual Directory Issues.
er
Using the Visual KPI Server Report The Visual KPI Server Report provides information on each of the Visual KPI instances installed on the machine. You can generate a status report of your current Visual KPI instances by selecting Generate Report from the Action menu. An HTML file is created and saved to the local drive, in the same folder as the Visual KPI Server Manager executable, and opened in your browser.
The Visual KPI Server Report includes statistics that specify the number of Visual KPI Instances installed on the machine, the total number of Views, Scorecards, and KPIs, combined from all of the locally installed instances. Additionally, the Statistics indicate the total number Interfaces installed on the machine, by interface type.
When the Visual KPI Server Report file is opened in a browser, click a plus sign (+) to expand a specific installed instance. Alternatively, click the “[expand all]” link, to expand everything within the Web Site section.
1. From the Action menu, select Generate Report. The browser opens and displays the HTML file that contains the Visual KPI Server Report.
The Web Site section’s initial view includes basic information regarding the Visual KPI web sites installed, as well as the Default web site.
2. Click the plus sign (+) for a specific installed instance of the Visual KPI Server to review more details for that instance.
PI Server M anager
Blue text that is underlined is a hyperlink. Click the link to open the associated web site.
NOTE: If the additional info isn’t displayed, you may need to respond to an Internet Explorer prompt at the top of the HTML page. In order to review all of the data in the HTML file, from within your browser, you will need to allow the Blocked Content.
3. Continue by clicking the plus signs (+) for each section of information you want to review.
The more sections expanded, the more detail that is displayed.
4. The Visual KPI Server Report is a single file, and can be easily shared with others, by sending a copy of the HTML file to anyone who needs to review the data.
el
C O N F I G U R I N G T H E E R R O R L O G G I N G L E V E L The Visual KPI Server logs events to the Transpara event log in the Windows Event Journal. The level of detail included in the event log is dependent upon the LogLevel setting for the Visual KPI Server and its components. The following describes the LogLevel options:
ErrorsOnly: Only errors are logged
Moderate: Logging ON with some logging (only public functions)
Full: Logging ON with complete logging (public and private functions)
During the Visual KPI Server installation, a “Transpara” log in the Windows Event Journal is created. The server components each have a unique “Source”, making it easier to sort through the Transpara event log. The following is a list of the different Sources that can be found in the Transpara event log.
♦ Visual KPI Server 1001-2000 ♦ Visual KPI PI Interface 2001-3000 ♦ Visual KPI SQL Server Interface 3001-4000 ♦ Visual KPI Alert Server 4001-5000 ♦ Visual KPI ODBC Interface 5001-6000 ♦ Visual KPI Cache Server 8001-9000
You can change the level of detail included in the Event Journal by editing the individual Visual KPI Server components in the Visual KPI Server Manager.
It is important to note that the LogLevel setting flows downward. This means that if you change the setting on the VisualKPI virtual directory, all sub-virtual directories settings also change. The exception being that if a sub-virtual directory has already been modified from its default setting, then it will not be updated by the change. For example, say the LogLevel setting on the VisualKPI virtual directory has been set to Moderate. Then, say the LogLevel setting for the WebEditor virtual directory is set to ErrorsOnly. Finally, the LogLevel setting on the VisualKPI virtual directory is changed to Full. The LogLevel setting on the WebEditor virtual directory will stay set to ErrorsOnly.
1. Open the Visual KPI Server Manager.
2. Select the component on which you want to change the LogLevel setting.
C onfiguring the R
egional Settings
3. In the LogLevel setting, click the drop-down arrow and select the LogLevel appropriate for your implementation.
4. Repeat the previous two steps for each component that requires a change to the LogLevel setting.
C O N F I G U R I N G T H E R E G I O N A L S E T T I N G S The Visual KPI Server’s Regional Setting specifies how the Visual KPI Server will communicate with other machines. When you specify a Regional Setting, the Text, Numbers, Dates and Times used within the Visual KPI Server’s communications will adhere to the formats specified within Visual KPI Server’s Regional Options settings.
1. Within the Visual KPI Server Manager, select a web site.
2. In the Visual KPI Site Settings section, select the appropriate Regional Setting.
3. Click Apply.
4. Repeat these steps for each web site on which you want to change the Regional Settings.
5. On the Visual KPI Server, from within the Control Panel, select Regional and Language Options.
6. In the Regional and Language Options dialog, select the Advanced tab.
7. In the Default user account settings area, select the Apply all settings to the current user account and to the default user profile check box.
8. Click OK to close the Regional and Language Options dialog.
The Visual KPI Server is configured for using Regional Settings.
Using Security with Visual KPI
The Visual KPI Server runs under the context of a web site in IIS. When you install the Visual KPI Server, it automatically creates the main Transpara web site that includes the Visual KPI web site and Visual KPI’s virtual directories. You then install any Visual KPI Interfaces that you want to run, either on the Visual KPI Server or another machine. If you need to limit access to the web site or Editors — limit who can view your KPI data, limit who can modify your KPI configurations — then you will want to implement some form of security.
This section provides information on how to implement a specific security scheme, one that includes multiple Visual KPI web sites and uses Active Directory (AD) security. There are other security implementations, as Visual KPI supports all forms of standard security implementations used on an application server.
V I S U A L K P I W E B S I T E C O M P O N E N T S This section describes the structure of the Transpara web site and its associated virtual directories, using default names. With the exception of the WebEditor and WebService virtual directories, the names shown in this section can be changed during the installation of the Visual KPI Server. By default, Transpara is the main web site under which a Visual KPI Instance is installed. However, you can install a new Visual KPI instance into any existing web site.
A new Visual KPI Server installation consists of three virtual directories under the main Transpara web site: VisualKPI, WebEditor and WebService. An additional virtual directory is created for each Visual KPI Interface installed to the same instance. The Transpara web site and its associated virtual directories are each mapped to a folder within the Visual KPI installation folder. Visual KPI Server’s default installation folder is C:\Program Files\Transpara\Visual KPI, under which is a sub-folder for each instance of Visual KPI installed on the machine.
The following table shows the relationships between the Visual KPI components, the related IIS components and application folders (using the default names).
Visual KPI Component IIS Component Application Folder
Main web site Transpara web site …\Visual KPI\Root
Visual KPI web site VisualKPI virtual directory …\Visual KPI\
Visual KPI Designer WebService virtual directory …\Visual KPI\WebService
PI Interface PIServer virtual directory …\Visual KPI\Interfaces\PIServer
ODBC Interface ODBC virtual directory …\Visual KPI\Interfaces\ODBC
SQL Interface SQLServer virtual directory …\Visual KPI\Interfaces\SQLServer
SmartSignal Interface SmartSignal virtual directory …\Visual KPI\Interfaces\SmartSignal
Vi su
al K
PI a
nd A
ct iv
e D
ire ct
or y
Se cu
rit y
V I S U A L K P I A N D A C T I V E D I R E C T O R Y S E C U R I T Y Active Directory can be used to secure the Visual KPI Web Site, as well as the Visual KPI Editors. Using AD, you can decide who can see and/or change your Visual KPI data. The following is an overview of the processes involved in implementing AD Security on a single Visual KPI Web Site.
1. Enable Security by:
a. Turning OFF anonymous access on the Web Site, and any Virtual Directory that you wish to secure.
b. Removing the user “Everyone” from the Permissions on the Web Site and Virtual Directories.
c. Adding AD Users and/or User Groups with Read & Execute permissions to the Web Site.
2. There are three types of users of any given instance of Visual KPI:
a. Read-only users of the Web Site
b. Read/write users of the Web Editor
c. Read/write users of the Visual KPI Designer
3. Grant Permissions to AD Groups for the Web Site and Virtual Directories.
a. Web Site: User/User Group must have Read & Execute permissions on the Web Site.
b. Web Editor: User/User Group must have Read & Execute permissions on the Virtual Directory called WebEditor.
c. Visual KPI Designer: User/User Group must have Read & Execute permissions on the Virtual Directory called WebService, plus Read & Execute and Write on the ExcelEditor folder on the client machine.
4. Errors are presented to any user without appropriate permissions to the Web Site or Virtual Directories.
a. Web Site: A simple 403 error page is shown.
b. Web Editor: A simple error page is shown.
c. Visual KPI Designer: A modal dialog box is shown telling the user they are forbidden to access the necessary web services.
U S I N G M U L T I P L E W E B S I T E S T O I M P L E M E N T S E C U R I T Y If you have only a single set of users who all need the same access to all of your KPI data, then implementing AD security on your web site is easy enough. But, what if you have multiple user groups, each of which needs access to different sets of KPI data, and/or different levels of access (read only, edit)? How can you assign different levels of access to each of the different user groups? Well, you can’t if you only have a single Visual KPI Web Site. Visual KPI supports the use of multiple Visual KPI Web Sites, allowing you to have a finer level of control over who will have what type of access to your various KPI data.
U sing M
plem ent Security
The following is an example implementation of AD security with multiple Visual KPI web sites.
ACME Manufacturing Example Let’s look at an example where multiple Visual KPI web sites would be required to implement AD security. ACME Manufacturing has two plants, each of which has it’s own set of KPIs. ACME wants to have multiple levels of access to the KPI data, based on an employee’s job title. Some users will only need to view the KPI data on the Web Site, while others will need to use one of the Editors to edit the KPI configuration data. Additionally, ACME wants to limit KPI visibility based on an employee’s location.
The following table shows the different groups of ACME users, across the top row. In the first column is the list of required access levels, broken down by location. VKPI
Admins Corp Execs
Plant 1 Mgr
Plant 1 Supv
Plant 1 Tech
Plant 2 Mgr
Plant 2 Supv
Plant 2 Tech
Adam Bill Charlie Dave Ed Frank George Harry Plant 1 Web Site
Y Y Y Y Y
Plant 1 Web Editor
Plant 2 Web Editor
ACME will need six AD User Groups:
One for each Web Site (Plant 1 Web Site, Plant 2 Web Site) One for each plant for editing the KPI Configuration data (Plant 1 VKPI Edit, Plant 2 VKPI Edit) One for the VKPI Admins One for the Corporate Executives
ACME’s AD User Group membership would look like this:
AD Group name User Name
VKPI Admins Adam
Corp Execs Bill
Plant 1 VKPI Edit Dave
Plant 2 Web Site Frank, George, Harry
Plant 2 VKPI Edit George
pl em
en tin
g A
ct iv
e D
ire ct
or y
Se cu
rit y
The Read & Execute permissions would be assigned to the Virtual Directories as follows:
Virtual Directory Name AD Group Name
Plant1WebSite Plant 1 Web Site, VKPI Admins, Corp Execs
Plant1WebEditor Plant 1 VKPI Edit, VKPI Admins
Plant1WebService Plant 1 VKPI Edit, VKPI Admins
Plant2WebSite Plant 2 Web Site, VKPI Admins, Corp Execs
Plant2WebEditor Plant 2 VKPI Edit, VKPI Admins
Plant2WebService Plant 2 VKPI Edit, VKPI Admins
ACME will need to create a separate Visual KPI database for each of the Visual KPI Web Sites. By organizing its data to reflect its Web Sites and Users, ACME will have a secure Visual KPI system running on multiple Web Sites. Only those users who have been given explicit permissions can access ACME’s Visual KPI data.
S T E P S F O R I M P L E M E N T I N G A C T I V E D I R E C T O R Y S E C U R I T Y Now that you have a better understanding of how multiple Visual KPI web sites used in conjunction with AD can provide you with a secure environment, you can begin the implementation process. The following outlines the steps required to implement the Active Directory Web Site security scheme described in the previous section.
♦ Review your KPI data and determine which users need what type of access.
♦ Create a table that includes your User Groups and the Type of Access needed.
♦ Install a new instance of the Visual KPI Server for each web site you need. When you install the Visual KPI Server using the Visual KPI Server Manager, both the web site and database are created. For instruction on installing the Visual KPI Server using the Visual KPI Server Manager, refer to Visual KPI Installation Guide.
♦ Configure the Visual KPI Alert Server for each web site on which you want to use the Alert Server. (See the Using the Visual KPI Alert Server section for more information.)
♦ Install and configure the Visual KPI Interfaces that you need to use on each web site. (See the Visual KPI Installation Guide for more information.)
♦ For each web site, assign the appropriate permissions to each AD User Group for each of the Virtual Directories.
♦ Configure each of your web sites, using one of the Editors. (See the Visual KPI User’s Guide for more information.)
♦ Using either of the Visual KPI Editors, create your Visual KPI data: Views, Scorecards, KPIs. (See the Visual KPI User’s Guide for more information.)
♦ Start using Visual KPI to track your real-time key performance indicators, from your desk or from out in the field. (See the Visual KPI User’s Guide for more information.)
C onfiguring A
ctive D irectory Security
C O N F I G U R I N G A C T I V E D I R E C T O R Y S E C U R I T Y Once you have created your Visual KPI database and web site, and installed the interfaces, you are ready to configure the security settings. You will configure the permissions on the web site and the Editors’ virtual directories to allow access to a specific set of AD accounts. You can limit who will have access to each of these, by assigning different permissions.
Granting Permissions to the Virtual Directories Before you begin, prepare your list of AD Groups and/or AD Users who need access to the Visual KPI web site, Visual KPI Designer and Web Editor. For each Visual KPI web site you have created, perform the following steps on the Visual KPI web site, WebEditor, and WebService virtual directories.
1. In the IIS Manager window, right-click the virtual directory and select Properties. The Properties dialog displays.
2. In the Directory Security tab, click the Authentication and access control Edit button. The Authentication Methods dialog displays.
3. Ensure that Enable anonymous access is NOT selected and that Integrated Windows authentication IS selected. Click OK to return to the Properties dialog.
4. Click OK to return to the IIS Manager window.
5. Right-click the virtual directory and select Permissions. The virtual directory’s Security dialog displays.
y
6. Click the Advanced button. The Advanced Security Settings dialog displays.
7. Select the first check box (“Allow inheritable permissions from the parent to propagate to this object and child objects. Include these with entries explicitly defined here.”) to remove the check mark. The Security dialog displays.
8. Click Copy. All groups and users that were assigned by inheritance remain in the Permission entries list.
9. Click the Add button. The Select Users, Computers, or Groups dialog displays.
10. In the Enter the object names to select: area, enter the names of the User Groups and/or Users that require access to the virtual directory, along with “Network Service” and “Administrators”. Click the Check Names button.
C onfiguring A
ctive D irectory Security
Remember, for users and user groups who require access to the:
♦ VisualKPI web site, assign Read & Execute permissions on the Web Site.
♦ Web Editor, assign Read & Execute permissions on the virtual directory called WebEditor.
♦ Visual KPI Designer, assign Read & Execute permissions on the virtual directory called WebService.
You may be prompted to provide your credentials for accessing AD information from the domain. Additionally, you may have to change the location for the Administrators group; you are adding the local Administrators group.
11. Click the OK button to return to the Security dialog.
12. Remove all remaining users and user groups, unless they require access to the virtual directory.
13. Assign Full Control to the user or user group that will be in charge of administering the IIS components (Administrators is the default). At least one user or user group requires full control to administer the components in IIS.
14. For the remaining users/user groups that you added, select the user/user group and ensure it has the Read & Execute permission. When the Read & Execute permission is selected, the List Folders Contents and Read permissions are automatically selected.
15. Click OK to return to the IIS Manager window.
16. Repeat the above steps for each of the virtual directories under the Transpara web site.
The Visual KPI Web Site is now complete.
Setting Permissions on the Visual KPI Designer Files For each client machine on which the Visual KPI Client application was installed (the Visual KPI Designer), you need to ensure that the appropriate users and/or user groups have the required access to the Visual KPI Designer folder.
1. Log onto the client machine as a user with local administrator privileges.
2. Open Windows Explorer and navigate to the ExcelEditor folder (default location is C:\Program Files\Transpara\Visual KPI\Visual KPI Designer).
3. Right-click the \Visual KPI Designer folder and select Properties. The Properties dialog displays.
4. Select the Security tab. Add the users and/or user groups who will need to access the Visual KPI Designer.
ity
5. For each user and/or user group that was added in the previous step, assign the Read & Execute and the Write permissions. The permissions are also assigned to each of the files within the folder.
6. Click OK to return to Windows Explorer. Close Windows Explorer.
U N D E R S T A N D I N G S Q L S E R V E R S E C U R I T Y The Visual KPI web service manages the communications with the Visual KPI database. During the installation, the Visual KPI web service and the Visual KPI database are configured to allow for this communication to occur in a secure manner. The web service is assigned to one of the application pools also created during the installation. The application pool’s Identity is set to Predefined and uses the NT AUTHORITY\NETWORK SERVICE account. In the Visual KPI database, a SQL Server Login and VisualKPI database User are created for the NETWORK SERVICE account.
NOTE: If you choose to use an application pool Identity other than a built-in account, the account must be added to the IIS_WPG local users group. If not, you may see the “server not available” message when attempting to access the database. Additionally, if the user account password has a timeout defined, the interface will become unavailable after the password’s timeout period.
SQL Server Login Account A different account can be used for the Application Pool’s Identity, but it must adhere to the following requirements.
The account must be mapped to a SQL Login account, with permission to connect to the VisualKPI database.
The Login account should be a member of the database role: db_VisualKPIRole. The db_owner database role will also work, but is not necessary.
The db_VisualKPIRole has permission to run all of the Visual KPI stored procedures, but it will not have permissions on any other SQL Server object such as a table or a view.
If the Login account is not mapped to the db_VisualKPIRole, it must be granted the Execute permission on all of the non-system stored procedures in the Visual KPI database.
SQL Server Security for Interfaces SQL Server can be configured to support one of two types of Authentication modes: Windows or SQL Server and Windows. Windows authentication requires a Windows domain account to connect to the SQL Server. SQL Server and Windows authentication allows either a Windows domain account or a SQL Server account to connect to SQL Server. You need to know which authentication method your SQL Server is configured to use.
Using Windows Authentication If your SQL Server and/or SmartSignal interface is connecting to a remote SQL Server that uses Windows authentication, you need to ensure that the domain account used in the Application Pool has the proper access to the remote SQL Server. Verify the following are set correctly:
The interface’s Application Pool has its Identity set to a Windows domain account.
Troubleshooting Virtual D irectory Issues
The interface’s connect string uses Integrated Security. Example: "Server=<servername>[\<instancename>];Database=<databasename>; Trusted_Connection=yes;"
A SQL Server Login account exists that maps to the domain user used as the application pool’s Identity, or to a domain group that includes this domain user. And, this SQL Server Login account has: permission to connect to the database. been granted access to the appropriate access to the Stored Procedures and/or Tables.
Using SQL Server and Windows Authentication If your SQL Server and/or SmartSignal interface is connecting to a remote SQL Server that uses SQL Server and Windows authentication, you need to ensure that the SQL account specified in the Connect String has the proper access to the remote SQL Server. Verify the following are set correctly:
The connect string specifies a SQL Server Login account
The interface’s connect string does not use Integrated Security. Example: "Server=<servername>[\<instancename>];Database=<databasename>;Trusted_C onnection=no;UID=<userid>;PWD=<password>;"
The SQL Server Login account has: permission to connect to the database. been granted access to the appropriate access to the Stored Procedures and/or Tables.
T R O U B L E S H O O T I N G V I R T U A L D I R E C T O R Y I S S U E S If you receive an error page when trying to verify the connection, verify the following issues have been addressed.
Verify virtual directory properties are set properly.
In the IIS Manager window, right-click the virtual directory, and select Properties. Verify the following:
♦ In the Virtual Directory tab, verify the Local path is directed at the correct folder (example: C:\Program Files\Transpara\VisualKPI\VisualKPI3.1\Interfaces\ODBC).
♦ In the Virtual Directory tab, verify the Application pool is set to one of the following application pool names: Application Pool ID Components Assigned
TAP[VisualKPIInstanceName] Visual KPI web site, Visual KPI Web Editor TAP[VisualKPIInstanceName]Int Visual KPI interfaces TAP[VisualKPIInstanceName]WS Visual KPI Web Service
♦ In the Directory Security tab, click the Authentication and access control Edit button. The Authentication Methods dialog displays. Ensure that Enable anonymous access is NOT selected and that Integrated Windows authentication IS selected. Click OK to return to the Properties dialog.
♦ In the ASP.NET tab, click the Edit Configuration button. In the ASP.NET Configuration Settings General tab, ensure that you have the correct Connection Parameters and Application Settings parameters.
Verify ASP.NET 2.0 is set to Allowed.
In the IIS Manager window, select the Web Service Extensions folder. The Web Service Extensions list displays in the right-hand side pane of the IIS window. Right-click the ASP.NET v2.0.x Web Service Extension, if it does NOT have a Status of Allowed, and select Allow. The ASP.NET v2.0.x Web Service Extension must be allowed.
Set Logging to 100 and check contents of the Transpara Event log.
Change the Error Logging Level for the virtual directory. Refer to Configuring the Error Logging Level on page 16 for details. Attempt to open the web page for the virtual directory again. If the there is an error on the page, open the Administrative Tools window, and double-click the Event Viewer icon. In the Event Viewer window, select the Transpara event log. Review the Transpara event log for errors that have a source of Visual KPI Server.
If you are still receiving an error, contact Transpara Support.
4
Installing Visual KPI Interfaces
Visual KPI Interfaces allow the Visual KPI Server to communicate with and retrieve data from non-Visual KPI systems, such as a PI Server, a SQL database, or a SmartSignal system. In order to access these systems, you must install an appropriate interface and define the system using one of the Visual KPI Editors.
Visual KPI supports the following interfaces:
♦ ECHO ♦ Proficy Historian OLEDB
♦ PI ♦ Wonderware Historian ODBC Interface
A Visual KPI Interface defines how to retrieve the required data from the non-Visual KPI system. The system definition defines how to access the non-Visual KPI system, and is specified in the Interfaces configuration (Refer to the Visual KPI User’s Guide). If the system is external to the Visual KPI Server, credentials may be required to access the system. You can specify the credentials when defining the Interfaces.
W O R K I N G W I T H T H E O D B C I N T E R F A C E The ODBC interface is made up of a web service and two SQL scripts (GetCurrentData.sql and GetTrendData.sql). The SQL scripts each use four fields, two of which are used in your KPIs: LookUp (this is the tag name) and Value (this is the value associated with the tag name). The other two fields, SampleData (the table name) and lupd (the date/time the row was last updated) are used to find the correct value. These are generic field names.
When displaying KPI values, the VisualKPI ODBC interface reads the tag name associated with a specific KPI column (such as Value, Target, Low, etc.) and then searches the SampleData table for a row that has the same name in the LookUp column. When the interface finds the correct row, it retrieves the Value in the same row and returns that value to Visual KPI.
When displaying a KPI’s trend, the VisualKPI ODBC interface reads the tag name associated with a specific KPI column (such as Value, Target, Low, etc.) and then searches the SampleData table for each row that has the same name in the LookUp column and where the lupd (timestamp) falls within the correct time range. Time range is based on the current Trend Range in Visual KPI. When the interface has completed its search, it retrieves the Value and lupd (timestamp) in the same row and returns these to Visual KPI, which in turn, draws the trend graph.
In order to use the Visual KPI ODBC interface, you need to specify the connection parameters for your database and modify the SQL Scripts to reference the actual field names for SampleData, LookUp, Value, and lupd. The ODBC Interface installation using the Visual KPI Server Manager allows you to make these changes as part of the installation. Should you need to modify the
e
connection parameters after the interface has been installed, you can use the Visual KPI Server Manager to make the changes; refer to Configuring the ODBC Interface.
If you want to test the ODBC interface before you connect it to your live database, you can create and work with the SQL Demo database.
Installing the ODBC Interface The ODBC Interface should be installed and configured using the Visual KPI Server Manager. The ODBC Interface can be installed on the Visual KPI Server, or on any machine that is available to the Visual KPI Server via HTTP. If the interface is being installed on a machine other than the Visual KPI Server, you need to first install the Visual KPI Server Manager.
During the interface install, use the Back button to move to a previous page in the installation wizard, or use the Cancel button to cancel the installation process.
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they are displayed here. Expand the web site in which you want to install the interface. Continue expanding the web site’s hierarchy until Interfaces is selected.
2. Right-click Interfaces and select Install New Visual KPI Interface | ODBC Interface. The Interface Setup Wizard opens.
3. Click Next to continue. The License Agreement page displays.
W orking w
Interface
4. Select the I accept the terms of the license agreement option and click Next to continue. The ODBC Interface Name page displays.
5. Accept the Visual KPI ODBC Interface Name, or change it to meet your requirements. The Visual KPI ODBC Interface Name is used to set the defaults for the Installation Path and Virtual Directory Name during this installation. Click Next to continue. The Installation Path page displays.
6. Accept the default installation path, or change it to meet your requirements. Click Next to continue. The Virtual Directory Properties page displays.
e
7. Accept the default Virtual Directory Name, or change it to meet your requirements. The ODBC Virtual Directory Name is the same name that will be used when the interface is defined in the Visual KPI Editor. Click Next to continue. The Connection String page displays.
8. Update the Connect String to meet your requirements. You can use the drop-down list to select sample connect strings, which you can then modify. Refer to Using Connect Strings with the Interfaces for example connect strings. Click Next to continue. The Get Current Data page displays.
W orking w
Interface
9. Update the Get Current Data Query to reflect the actual field names for your system. Refer to Working with the ODBC Interface for detailed information on the fields used. Click Next to continue. The Get Trend Data page displays.
10. Update the Get Trend Data Query to reflect the actual field names for your system. Refer to Working with the ODBC Interface for detailed information on the fields used. Click Next to continue. The Ready to Install page displays.
11. Click Next to install the interface. The Finish page displays.
e
12. Click Finish to close the Setup Wizard. The newly installed interface displays in the Interfaces folder.
13. Select the newly installed interface in the left pane. The detail pane displays the interface’s configuration information that was entered during the installation.
The installation of the ODBC interface is now complete.
W orking w
Interface
Configuring the ODBC Interface If you need to change the configuration settings for your ODBC Interface after it has already been installed, you can use the Visual KPI Server Manager.
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they are displayed here. Expand the web site that includes the interface to be configured.
2. Under the Interfaces folder, select the ODBC interface to be configured. The detail pane displays the interface’s current configuration.
Mousing over a setting displays a tooltip with the current configuration for that setting, if the value is not already displayed in its entirety.
You can modify any of the configuration settings that are not grayed out.
To make changes to LogName and ConnectString, double-click the setting name to select the setting’s value, or click in the setting’s cell, and enter the new value. Refer to Using Connect Strings with the Interfaces for example connect strings.
e
To make changes to GetCurrentDataQuery or GetTrendDataQuery, click in the cell and enter the new query, or click the down arrow to open a larger text entry field. Press the Enter key to start a new line. Press Ctrl + Enter to close the field and accept the text you have entered.
Refer to Working with the ODBC Interface for detailed information on the fields used in these queries.
To make changes to the LogLevel, select an appropriate level from the list. You can select ErrorsOnly, Moderate or Full. Refer to Configuring the Error Logging Level for details on logging levels.
3. After making any required changes, click Apply. The configuration information is saved. Refer to Verifying the ODBC Connection to verify the connection is working correctly.
Verifying the ODBC Connection Once you have installed and configured the ODBC interface, you should verify the interface is able to connect to the database. You can use the Visual KPI Server Manager to verify the connection.
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they will be displayed here. Expand the web site that includes the interface to be verified.
2. Right-click the ODBC interface and select Verify Connection. A message displays indicating the connection was successful, or if the connection failed, a message displays indicating the failure:
W orking w
3. Click OK in the message.
4. If there were any errors, make the required changes to the Connect String and/or Queries and repeat the verification. Otherwise, refer to the Using Connect Strings with the Interfaces section for tips on correcting the configuration.
NOTE: It is important to note that verifying the connection does not ensure proper security has been implemented. To ensure you can access the data within the database, you should test the ODBC connection.
Testing the ODBC Interface Connection Once you have verified the ODBC interface connection, you should test the connection to ensure you can access the data within the database. You can use the Visual KPI Server Manager to test the connection.
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they will be displayed here. Expand the web site that includes the interface to be tested.
2. Right-click the ODBC interface and select Browse. Your browser opens and displays the ODBC Interface’s Web Service page.
3. Click the Test link.
4. In the sLookupValue field, enter a valid lookup name and click the Invoke button. A new instance of your browser opens.
W orking w
ith the PI Interface
5. Look for the “<NewDataSet xmlns="">” entry. Within this entry are the results of the test. If the connection and security settings are valid, a value is returned. Otherwise, an error is displayed as the value.
6. If there are any errors, make the required changes to the Connect String and/or Queries and repeat the test. Otherwise, refer to the Using Connect Strings with the Interfaces section for tips on correcting the configuration.
W O R K I N G W I T H T H E P I I N T E R F A C E The VisualKPI PI interface is a web service that communicates with OSIsoft’s PI-SDK to get data from a PI Server. The PI interface must be installed on a machine that includes OSIsoft’s PI-SDK.
Installing the PI Interface The PI Interface can be installed and configured, using the Visual KPI Server Manager, on the Visual KPI Server, or on any machine that is available to the Visual KPI Server via HTTP. If the interface is being installed on a machine other than the Visual KPI Server, you need to first install the Visual KPI Server Manager.
W or
ki ng
w ith
th e
PI In
te rf
ac e
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they are displayed here. Expand the web site in which you want to install interfaces. Continue expanding the web site’s hierarchy until Interfaces is selected.
2. Right-click Interfaces and select Install New Visual KPI Interface | PI Interface. The Interface Setup Wizard opens.
W orking w
4. Select the I accept the terms of the license agreement option and click Next to continue. The PI Interface Name page displays.
5. Accept the Visual KPI PI Interface Name, or change it to meet your requirements. The Visual KPI PI Interface Name is used to set the defaults for the Installation Path and Virtual Directory Name during this installation. Click Next to continue. The Installation Path page displays.
W or
ki ng
w ith
th e
PI In
te rf
ac e
7. Accept the default Virtual Directory Name, or change it to meet your requirements. The Virtual Directory Name is the same name that will be used when the interface is defined in the Visual KPI Editor. Click Next to continue. The PI Server Settings page displays.
8. Enter the PI Server Name. In the Security area, select the Verify connection before proceeding
check box if you want the security credentials to be verified before moving on in the install. Select the appropriate security option: PI Trust; PI Login; or Windows Security. If PI Login is selected, then enter the PI User Name, and the PI user’s Password. Click Next to continue. The Ready to Install page displays.
W orking w
10. Click Finish to close the Setup Wizard. The newly installed interface displays in the Interfaces folder.
The installation of the PI interface is now complete.
Configuring the PI Interface If you need to change the configuration settings for your PI Interface after it has already been installed, you can use the Visual KPI Server Manager.
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they will be displayed here. Expand the web site that includes the interface to be configured.
2. Under the Interfaces folder, select the PI interface. The detail pane displays the interface’s current configuration. You can modify any of the configuration settings that are not grayed out.
W or
ki ng
w ith
th e
PI In
te rf
ac e
To make changes to LogName, PIPassword, PIServer, or PIUser, double-click the setting name to select the setting’s value, or click in the setting’s cell, and enter the new value.
3. After making changes, click Apply. The configuration information is saved. Refer to Verifying the PI Interface Connection to verify the connection is working correctly.
Verifying the PI Interface Connection Once you have installed and configured the PI Interface, you should verify the connection is working properly. You can use the Visual KPI Server Manager to verify the connection.
2. Right-click the PI interface and select Verify Connection. A message displays indicating the connection was successful, or if the connection failed, a message displays indicating the failure:
4. If there were any errors, make the required changes to the configuration settings and repeat the verification.
Testing the PI Interface Connection Once you have verified the PI interface connection, you should test the connection to ensure you can access the data within the database. You can use the Visual KPI Server Manager to test the connection.
W orking w
2. Right-click the PI interface and select Browse. Your browser opens and displays the PI Interface’s Web Service page.
W or
ki ng
w ith
th e
PI In
te rf
ac e
4. In the sLookupValue field, enter a valid lookup name (example: sinusoid) and click the Invoke button. A new instance of your browser opens.
6. If there are any errors, make the required changes to the PI Settings and repeat the test.
W orking w
ith the SQ L Server Interface
W O R K I N G W I T H T H E S Q L S E R V E R I N T E R F A C E The VisualKPI SQL Server interface is a web service that communicates with a SQL Server database. The SQL Server interface works much the same as the ODBC interface. However, the SQL Server interface requires two new stored procedures to be inserted into the target database: usp_VisualKPI_GetCurrentData and usp_VisualKPI_GetTrendData.
The stored procedures use four fields, two of which are used in your KPIs: LookUp (this is the tag name) and Value (this is the value associated with the tag name). The other two fields, SampleData (the table name) and lupd (the date/time the row was last updated) are used to find the correct value. These are generic field names.
When displaying KPI values, the Visual KPI SQL Server interface reads the tag name associated with a specific KPI column (such as Value, Target, Low, etc.) and then searches the SampleData table for a row that has the same name in the LookUp column. When the interface finds the correct row, it retrieves the Value in the same row and returns that value to Visual KPI.
When displaying a KPI’s trend, the Visual KPI SQL Server interface reads the tag name associated with a specific KPI column (such as Value, Target, Low, etc.) and then searches the SampleData table for each row that has the same name in the LookUp column and where the lupd (timestamp) falls within the correct time range. Time range is based on the current Trend Range in Visual KPI. When the interface has completed its search, it retrieves the Value and lupd (timestamp) in the same row and returns these to Visual KPI, which in turn, draws the trend graph.
In order to use the Visual KPI SQL Server interface, you need to specify the connection parameters for your database and modify the StoredProcedures.sql SQL Script to use the actual field names for SampleData, LookUp, Value, and lupd. You can specify the connection string as part of the interface’s installation, when using the Visual KPI Server Manager. However, prior to installing the SQL Server Interface, you need to update the field names, in the StoredProcedures.sql SQL Script, to reference the actual field names in your database and execute the StoredProcedures.sql SQL script in the target database.
Should you need to modify the connection parameters after the interface has been installed, you can use the Visual KPI Server Manager to make the changes; refer to Configuring the SQL Server Interface.
If you want to test the SQL Server interface before you connect it to your live database, you can create and work with the SQL Demo database, then reference the SQL Demo database during the SQL Server interface installation.
Preparing the Stored Procedures for the SQL Server Interface Installation 1. Open Windows Explorer and navigate to the folder containing the extracted files for the SQL
Server Interface. Example: C:\Visual KPI 3.1.7.46 - Server\Visual KPI\Server\Interfaces\SQL Server\bin\SQL Scripts
2. Open for edit the StoredProcedures.sql file using Notepad.
3. Locate each instance of SampleData and replace it with the name of the table that contains the data you want to access using the SQL Server interface.
4. Locate each instance of LookUp and replace with the name of the column that contains the name of the KPI.
5. Locate each instance of Value and replace with the name of the column that contains value associated with the KPI.
W or
ki ng
w ith
th e
SQ L
Se rv
er In
te rf
ac e
6. Locate each instance of LUPD and replace it with the name of the column that contains the date and time that a record was last updated.
7. Save and close the SQL script file. The updated SQL Script will be used during the SQL Server Interface installation, when installing using the Visual KPI Server Manager.
Inserting the Stored Procedures for the SQL Server Interface 1. Update the StoredProcedures.sql SQL Script, as described in Preparing the Stored Procedures
for the SQL Server Interface Installation.
2. Open the SQL script, StoredProcedures.sql, in the instance of SQL Server in which the target database resides.
3. In the Available Databases list, select the database in which you want to create the stored procedures.
4. Click the ! Execute button.
5. Two new stored procedures are created in the selected database: usp_VisualKPI_GetCurrentData and usp_VisualKPI_GetTrendData.
Installing the SQL Server Interface The SQL Server Interface should be installed and configured using the Visual KPI Server Manager. The SQL Server Interface can be installed on the Visual KPI Server, or on any machine that is available to the Visual KPI Server via HTTP. If the interface is being installed on a machine other than the Visual KPI Server, you need to first install the Visual KPI Server Manager.
NOTE: Prior to installing the SQL Server Interface, you need to update the field names in the StoredProcedures.sql SQL Script to reference the actual field names in your database, then run StoredProcedures.sql in the target database.
W orking w
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they will be displayed here. Expand the web site in which you want to install interfaces. Continue expanding the web site’s hierarchy until Interfaces is selected.
2. Right-click Interfaces and select Install New Visual KPI Interface | SQL Server Interface. The Interface Setup Wizard opens.
W or
ki ng
w ith
th e
SQ L
Se rv
er In
te rf
ac e
4. Select the I accept the terms of the license agreement option and click Next to continue. The SQL Server Interface Name page displays.
5. Accept the Visual KPI SQL Server Interface Name, or change it to meet your requirements. The Visual KPI SQL Server Interface Name is used to set the defaults for the Installation Path and Virtual Directory Name during this installation. Click Next to continue. The Installation Path page displays.
W orking w
7. Accept the default Virtual Directory Name, or change it to meet your requirements. The SQL Server Virtual Directory Name is the same name that will be used when the interface is defined in the Visual KPI Editor. Click Next to continue. The Connection String page displays.
8. Update the default Connect String to meet your requirements. You can use the drop-down list to select sample connect strings, which you can then modify. Refer to Using Connect Strings with the Interfaces for example connect strings. Click Next to continue. The Ready to Install page displays.
W or
ki ng
w ith
th e
SQ L
Se rv
er In
te rf
ac e
10. Click Finish to close the Setup Wizard. The newly installed interface displays as part of the Interfaces folder.
The installation of the SQL Server interface is now complete.
Configuring the SQL Server Interface If you need to change the configuration settings for your SQL Server Interface after it has already been installed, you can use the Visual KPI Server Manager.
W orking w
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they are displayed here. Expand the web site that includes the interface to be configured.
2. Under the Interfaces folder, select the SQL Server interface to be configured. The detail pane displays the interface’s current configuration.
To make changes to LogName, ConnectString, GetCurrentDataStoredProcedure, GetTrendDataStoredProcedure, or ServerName, double-click the setting name to select the setting’s value, or click in the setting’s cell, and enter the new value. Refer to Using Connect Strings with the Interfaces for example connect strings.
3. After making changes, click Apply. The configuration information is saved. Refer to Verifying the SQL Server Interface Connection to verify the connection is working correctly.
Verifying the SQL Server Interface Connection Once you have installed and configured the SQL Server interface, you should verify the interface is able to connect to the database. You can use the Visual KPI Server Manager to verify the connection.
W or
ki ng
w ith
th e
SQ L
Se rv
er In
te rf
ac e
2. Right-click the SQL Server interface and select Verify Connection. A message displays indicating the connection was successful, or if the connection failed, a message displays indicating the failure:
4. If there were any errors, make the required changes to the Connect String and/or Queries and repeat the verification. Otherwise, refer to the Using Connect Strings with the Interfaces section for tips on correcting the configuration.
NOTE: It is important to note that verifying the connection does not ensure proper security has been implemented. To ensure you can access the data within the database, you should test the SQL Server connection.
Testing the SQL Server Interface Connection Once you have verified the SQL Server interface connection, you should test the connection to ensure you can access the data within the database. You can use the Visual KPI Server Manager to test the connection.
W orking w
2. Right-click the SQL Server interface and select Browse. Your browser opens and displays the SQL Server Interface’s Web Service page.
ce
4. In the lookup field, enter a valid lookup name and click the Invoke button. A new instance of your browser opens.
6. If there are any errors, make the required changes to the Connect String and/or Queries and repeat the test. Otherwise, refer to the Using Connect Strings with the Interfaces section for tips on correcting the configuration.
U S I N G T H E S M A R T S I G N A L I N T E R F A C E The VisualKPI SmartSignal interface is a web service that communicates with SmartSignal’s eCM product to access data in a watch list table. The SmartSignal interface uses a query to retrieve data from the WatchList table in the SmartSignal database.
U sing the Sm
artSignal Interface
Installing the SmartSignal Interface The SmartSignal Interface can be installed and configured using the Visual KPI Server Manager. The SmartSignal Interface can be installed on the Visual KPI Server, or on any machine that is available to the Visual KPI Server via HTTP. If the interface is being installed on a machine other than the Visual KPI Server, you need to first install the Visual KPI Server Manager.
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they will be displayed here. Expand the web site in which you want to install interfaces. Continue expanding the web site’s hierarchy until Interfaces is selected.
2. Right-click Interfaces and select Install New Visual KPI Interface | SmartSignal Interface. The Interface Setup Wizard opens.
ce
4. Select the I accept the terms of the license agreement option and click Next to continue. The SmartSignal Interface Name page displays.
5. Accept the Visual KPI SmartSignal Interface Name, or change it to meet your requirements. The SmartSignal Interface Name is used to set the defaults for the Installation Path and Virtual Directory Name during this installation. Click Next to continue. The Installation Path page displays.
U sing the Sm
artSignal Interface
7. Accept the default Virtual Directory Name, or change it to meet your requirements. The SmartSignal Virtual Directory Name is the same name that will be used when the interface is defined in the Visual KPI Editor. Click Next to continue. The Database Server page displays.
8. In the Database Server list, enter or select the database server and instance that contains the SmartSignal database.
9. Enter the SmartSignal database name in the Database Name field.
10. Specify the SQL Server authentication credentials required to connect to the SmartSignal database. Click Next to continue. The Ready to Install page displays.
ce
12. Click Finish to close the Setup Wizard. The newly installed interface displays as part of the Interfaces virtual directory.
13. Select the newly installed interface in the left pane. The detail pane displays the interface’s configuration information.
U sing the Sm
The installation of the SmartSignal interface is now complete.
Configuring the SmartSignal Interface If you need to change the configuration settings for your SmartSignal interface after it has already been installed, you can use the Visual KPI Server Manager.
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on your system, they will be displayed here. Expand the web site that includes the interface to be configured.
2. Under the Interfaces folder, select the SmartSignal interface. The detail pane displays the interface’s current configuration.
ce
To make changes to LogName, ConnectString, or SmartSignalServer, double-click the setting name to select the setting’s value, or click in the setting’s cell, and enter the new value. Refer to Using Connect Strings with the Interfaces for example connect strings.
To make changes to Query, click in the cell and enter the new query, or click the down arrow to open a larger text entry field. Press the Enter key to start a new line. Press Ctrl + Enter to close the field and accept the text you have entered.
3. After making any required changes, click Apply. The configuration information is saved. To verify the connection is working correctly, refer to Verifying the SmartSignal Interface Connection.
U sing the Sm
artSignal Interface
Verifying the SmartSignal Interface Connection Once you have installed and configured the SmartSignal interface, you should verify the interface is able to connect to the database. You can use the Visual KPI Server Manager to verify the connection.
1. Open the Visual KPI Server Manager. Expand the Web Sites folder. If you have multiple web sites defined on the local machine system, they will be displayed here. Expand the web site that includes the interface to be verified.
2. Right-click the SmartSignal interface and select Verify Connection. A message displays indicating the connection was successful, or if the connection failed, a message displays indicating the failure:
4. If there were any errors, make the required changes to the Connect String and/or Query and repeat the verification. Otherwise, refer to the Using Connect Strings with the Interfaces section for tips on correcting the configuration.
NOTE: It is important to note that verifying the connection does not ensure proper security has been implemented. To ensure you can access the data within the database, you should test the SmartSignal connection.
ce
Testing the SmartSignal Interface Connection Once you have verified the SmartSignal interface connection, you should test the connection to ensure you can access the data within the database. You can use the Visual KPI Server Manager to test the connection.
2. Right-click the SmartSignal interface and select Browse. Your browser opens and displays the SmartSignal Interface’s Web Service page.
U sing the Sm
artSignal Interface
4. In the sLookupValue field, enter a valid lookup name and click the Invoke button. A new instance of your browser opens.
6. If there are any errors, make the required changes to the Connect String and/or Query and repeat the test. Otherwise, refer to the Using Connect Strings with the Interfaces section for tips on correcting the configuration.
U si
ng C
on ne
ct S
tr in
gs w
ith th
e In
te rf
ac es
U S I N G C O N N E C T S T R I N G S W I T H T H E I N T E R F A C E S The format for connect strings varies, depending on the target database. You

visual kpi administrator's guide

Documents