Insight (Global) Spreadsheet Server (GSS / ISS)

Author:  Eric Vasbinder

ERPs Applicable:

• Vista

Overview

Support Status:  Connection to Vista Supported by Viewpoint (PRODUCT SUPPORTED BY THIRD PARTY)

Integration Method:  ODBC Direct Database

Connection Methods:

• VRL CLOUDs (Trimble Construction One / Viewpoint One):  TLS Database Endpoint (TLS VPN) (RECOMMENDED) or an IPSEC VPN

• Legacy RDP Clouds (VFC and VEC RDP Published Apps):  Direct TLS network connection, hosted in same network as Vista

Deployment Model:  

• VRL CLOUDs (Trimble Construction One / Viewpoint One):  Locally hosted on customer workstations

• Legacy RDP Clouds (VFC and VEC RDP Published Apps):  Viewpoint hosted on same network as Vista database

Hosted:   NO (VRL Customers) / YES (RDP Customers)

Additional Charge:   VRL Customers:  Not directly, but either TLS Database Endpoint (TLS VPN) or IPSEC VPN required /  VEC RDP Customers:  No (Required tool for financial reporting)

Product Use Case

Insight Software's Spreadsheet Server simplifies the process to set up financial reports and SQL queries into Vista, through the use of an add-on to the familiar Microsoft Excel program.

Similar Products

This product is similar in operation to the reports that can be created using PowerBI, Prophix, and others.

ERP Connection Overview

As a client-side app, ISS/GSS can either be hosted with Viewpoint on our terminal servers or hosted on the customer's own local workstations.  Which method is used depends on whether the customer is using Vista Remote Link (VRL) to connect to Vista in the cloud.  If the customer is using VRL, then all client-side applications remain hosted locally on the customers workstations.  If the customer is conversely using our RDP-based solutions (e.g. Viewpoint For Cloud (VFC) or RDP Published apps in VEC), then we need to host ISS along with Microsoft Excel.

NOTE:  VRL IS OUR DEFAULT AND PREFERRED CLOUD CONSUMPTION METHOD

RDP HOSTING IS FOR LEGACY VFC and VEC / RDP CUSTOMERS ONLY

THUS, MOST ALL NEW IMPLEMENTATIONS OF SPREADSHEET SERVER IN OUR CLOUD ARE HOSTED ON CUSTOMER WORKSTATIONS

Deployment Methodology

VRL Clouds (Trimble Construction One (TC1) / Viewpoint One (VP1))

In this case, the customer has no RDP capabilities, as with VRL, those are not needed; all client-side apps are run locally, on the customers own local workstations.  In this case, Microsoft Office (Excel) runs on the local workstation, and ISS is installed locally as well.  For ISS to be able to talk to the Vista database in this situation, a VPN will need to be set up between the customer's environment and the Viewpoint network.  Now that the TLS Database Endpoint (TLS VPN) is available, we recommend that approach to allow this method.  However, assuming that the TLS Database Endpoint (TLS VPN) does not work, say for example if you need direct file system access over SMB to allow an automated server to server import file process, you can also still use the IPSEC VPN method. 

Steps:             

1.  (CUSTOMER IT and VIEWPOINT)  Set up VPN:  

   1. TLS Database Endpoint (TLS VPN) (RECOMMENDED):  TLS Database Endpoint 

   2. IPSEC VPNs:  How do I set up an IPSEC VPN to access my Vista database directly?

2.  (CUSTOMER)  Create Dedicated SQL Account(s):   Please use the instructions at this link to create an account for Spreadsheet Server:  I need a dedicated SQL account for my integration to Vista in your cloud. How do I set that up?

   1. NOTE:  the permissions needed (e.g. tables and queries) to run reports often vary by customer.  Please make certain you review the permissions of the account that is used to run spreadsheet server today, then copy those Vista permissions to the new SQL Service account(s) that are needed.

      1. For example, if you have two users that access ISS on-premise today, you should create SQL accounts for each user to use with ISS.  Then copy their permissions to those new SQL accounts.

   2. IMPORTANT:  For security reasons you should use a separate SQL account for each end user that needs to run ISS; each human running ISS should have their own, dedicated ISS SQL account in Vista.

NOTE:  YOU MAY, FOR EASE OF MANAGEMENT, CREATE A SHARED, READ ONLY ACCOUNT FOR SPREADSHEET SERVER USE.  HOWEVER, THIS SHOULD ONLY BE DONE IF THE ACCOUNTS CREATED ARE READ ONLY.  IF THE ACCOUNTS ALLOW FOR WRITE ACCESS, USERS SHOULD THEN HAVE SEPARATE ACCOUNTS.

3.  (CUSTOMER AND INSIGHT SOFTWARE) Set up ISS to talk to Vista:  Update the settings for ISS to point to the new location for Vista's database.  If you have those, you can follow the instructions in the third party's help documentation to add a new ODBC connection to enable the product to speak to the new Vista server.  If you have issues with those instructions, our friends at Insight Software can help you create the new connection.  Here is a link with a high level description of what needs to happen:  https://www.tutorialgateway.org/create-odbc-connection/

   1.  You will need to open the Application Configurator and locate the "Connections" area.

   2.  Once there, locate the Viewpoint connection.

   3.  Change the server specified to point to the new IP address of the Vista server in the cloud.

   4.  Test and save the connection.

4.  (CUSTOMER IT)  Open the Firewall Ports:  Ensure that you have opened the following ports in your firewall for the ISS ODBC connection to and from Vista:

   1.  TCP port 1433 inbound and outbound (this is the actual DB connection)

   2.  UDP Port 1434 inbound and outbound (this is the SQL Server Browser service)

   3.  Here are some detailed instructions on how to test that connection:  I need to check my VPN to the Vista Database? How do I do that? Is there a firewall blocking me?

5.  (CUSTOMER AND INSIGHT SOFTWARE) Obtain the ISS configuration files:  ISS uses configuration files to tell itself how to interpret Vista's data schema.  These files are normally stored by customers in a special directory on the on-premise Vista server, in the Viewpoint Repository folder.  To work with your new deployment model in our cloud, you'll need to download these files from your on-premise location, and then store them in an area accessible to your end-user's client workstations.

   1. NOTE:  A shared OneDrive, Google Drive, Box, DropBox, etc. folder is good to use for this purpose and can be a good substitute to a network file share.  Keep in mind though that you need to set up the file synch tool (e.g. OneDrive) to store the ENTIRE content of the file on the local workstation (i.e. mark it for Keep Local, or Download Always, etc.)

      1. If you are using a file sync solution such as OneDrive, Google drive, box, or dropbox, you will need to ensure that the pathname given to Insight Software Spreadsheet Server refers to the same directory on each local workstation to which the GL templates are synchronized.   To do so, you will create a local mapped drive redirect, using the "subst" command, which will map the unique pathname that a specific user may have for the shared folder to the same drive letter on each workstation.  For example,"S:\" for "Spreadsheet Server".

      2. In other words, if the user "Jane" is synching the shared OneDrive folder for Spreadsheet Server to her local folder, C:\Users\jane\OneDrive\SharedFolders\Spreadsheet Server, she should have the "subst" command entered into her registry to automatically map that directory to the "S:\" drive. 

         1.  To set up this mapping to be fully automated please use the steps under the automatic mapping section of the following FAQ article:  https://sites.google.com/trimble.com/vista-cloud-faq/home/rdp-explained/map-local-directory-drive-letter-rdp-redirect

Just to reiterate, once ISS is configured to talk to Vista, you need to also ensure that your common, shared reports location for ISS reports is moved.  Many customers use the Vista server's "Viewpoint Repository" folder, or a mapped drive on the Vista database directly to share ISS reports with staff members.  This method will NOT work with Vista in the VRL Cloud.  The reason being is that no mapped drives are available with VRL and direct access to the Viewpoint repository is not possible.  Instead, we highly recommend that you set up a separate shared folder to use as the main storage location for ISS reports.  

This shared reports folder can be an on-premise Windows file share, or a more complicated option that does not require a file share is to use a common, synchronized folder through a cloud synchronization service installed on your local workstations (e.g. Box, Dropbox, OneDrive, or NextCloud), combined with a drive mapping to keep that file set to always have the same path name.

NOTE:  Once you have the above information and files, please reach out to Insight Software for further assistance.

Using ISS in RDP Clouds (VFC / VEC RDP)

In this case, the customer needs to pay Microsoft for Microsoft Office 365 E3 or above licensing and have Viewpoint host Office.  We will then also need to host the ISS add-on to Excel in our environment so it can still see the Vista database and integrate with Excel.

tl;dr:  Customers who need to integrate ISS with Vista need to either have ISS installed locally (VRL Clouds such as TC1 and VP1), connecting over either the TLS Database Endpoint (TLS VPN) or their site to site IPSEC VPN tunnel to the Trimble Viewpoint ERP Cloud, or in RARE cases customers can host ISS with us (RDP Cloud Legacy customers, such as VEC RDP and VFC, ONLY).  If the customer is using VRL Cloud, they'll need to move the shared GL templates and reports for ISS to another, still accessible location (e.g. a OneDrive Shared directory), and off of the Vista server.  On VRL, dedicated SQL account(s) will also need to be made for each user who needs ISS access.  Firewall ports will also need to be opened (UDP port 1434 and TCP port 1433).  ISS configuration files like Viewpoint.xml will need to be moved and stored in a location accessible to all user workstations.