Configuring a VA server in the Add-In for Office

[Nigel_Pain]

I've been trying to configure an Additional Visual Analytics Server in the Add-In for Office within Excel. However, there's not a lot of explanation in the documentation (it simply says to use the URL for the VA server). Has anyone got this working?

We have separate mid-tier and VA servers, so I'd assumed that the URL would be along the lines of https://midtierserver/SASVisualAnalytics and the port would be 443, but that produces a Connection Failed message: "The server is not valid".

Our software versions are:

Servers: Windows 2012 R2

SAS 9.4 M5

VA 7.4 (non-distributed)

SAS Add-In for Office 7.15 (32-bit)

Clients: Windows 10

We have the latest hotfix for the Add-In (B4P003), which is supposed to address an issue with connecting to VA servers.

Thanks for any clues!

[SASKiwi]

I assume you are referring to the SAS Add-In connection profile to a SAS VA server. You need to put the server name for your SAS VA metadata server (not a URL) like: MyVAMetadataServer.com.

[Nigel_Pain]

Thanks @SASKiwi. I'd used the URL because the help said:

Enter the connection information for the SAS Visual Analytics server. The server name is the URL to the SAS Visual Analytics server.

However, it still doesn't seem to work using the metadata server: maybe I'm using the wrong port? 8561 and 10011 both give the same result. To clarify, we have four servers: Metadata, Office Analytics, VA and Middle Tier.

Regards,

Nigel

[JuanS_OCS]

Hello @Nigel_Pain,

if your connection is https, you might need to import your certificate chain into your SASPrivateJRE certificate store and perhaps even in your Windows certificate store.

Additionally, please check if you have the hotfix mentioned in this SAS note: http://support.sas.com/kb/52/813.html

And, It seems there is another hotfix specific 

In any case, I would like to suggest you to get the logging from the SAS Addon: 

http://support.sas.com/kb/56/743.html

Once you get it, you and us will be able to understand better the problem.

@SASKiwi, I think @Nigel_Pain is right, for connecting to SAS VA servers, the URL to mid tier is the way to go (then  behind scenes it would use the Transport Services, as the Mobile BI app would do).

@Nigel_Pain, as @SASKiwi said, still, possible to connect to the metadata server, though. The port number should be something as 8561 or such.

Connecting to your SAS servers

Connecting to your SAS servers

The SAS Connections window displays your active profile and any SAS Visual Analytics Servers that are available from your session. In the SAS add-in, you can connect to one SAS 9 metadata profile and multiple SAS Visual Analytics servers.

To open the SAS Connections window, click the SAS tab in the Ribbon. In the Tools group, click Tools and select Connections.

In Microsoft Outlook 2007, select SAS Central Options. In the Options dialog box, click the SAS tab, and then click Connections.

Metadata configuration information is shared between the Microsoft Office applications (Excel, Word, PowerPoint, and Outlook) where the SAS add-in is installed. This metadata information is also shared with SAS Enterprise Guide. Any changes that you make to your connection in SAS Add-In for Microsoft Office also appear in Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Microsoft Outlook, and SAS Enterprise Guide.

Selecting your SAS 9 metadata profile

Your SAS 9 metadata profile defines the connection between the SAS Add-In for Microsoft Office and a SAS Metadata Server. Although you can define multiple metadata profiles, only one profile can be active at a time. To create a metadata profile, see Creating a metadata server connection.

To select your active profile:

1. From the drop-down list, select the SAS 9 metadata profile that you want to use. To create a new profile or modify an existing profile, click Manage.
2. After you select the profile from the drop-down list, click Set Active. When a profile is active, the icon appears next to the profile name.
3. Click Close to work in your Microsoft Office document.

If you use the SAS add-in without a profile, you can access a SAS server installed on your local machine only. You cannot define libraries or share SAS resources with other SAS applications.

Defining SAS Visual Analytics servers

All the SAS Visual Analytics reports that you need might not be saved to the server associated with the SAS 9 metadata profile. From the SAS Connections window, you can define connections to any additional SAS Visual Analytics servers that you need to access.

To define a connection to a SAS Visual Analytics server:

1. Under the Additional Visual Analytics Servers heading, click Add. The Add a Server dialog box appears.
2. Enter the connection information for the SAS Visual Analytics server. The server name is the URL to the SAS Visual Analytics server. Contact your SAS administrator for this URL.
3. (Optional) Select the Save password checkbox.
   • If you save your password and the authentication information is correct, the SAS add-in tries to connect to the server without prompting you for additional information.
   • If you do not save your password, you are prompted for your password when the SAS add-in tries to connect to the server.
4. Click OK. This SAS Visual Analytics server should now appear in the Additional Visual Analytics Servers box.

If you are using a SAS Visual Analytics server as your metadata server, do not define the same server in the Additional Visual Analytics Servers box.

Connecting to a SAS Visual Analytics server

The SAS add-in does not connect to the SAS Visual Analytics server as soon as you define it in the SAS Connections window. You can create the connection by selecting the server name and clicking Connect. You can also wait and have the SAS add-in create the connection when you try to access SAS content on that server, such as when you try to open a SAS Visual Analytics report.

These icons in the SAS Connections window help you determine the status of your connections:

• - The SAS add-in successfully connected to the server.
• - More information (such as your password) is needed to connect to the server.
• - The SAS add-in cannot connect to the server. Reasons that you might see this icon are that your password is invalid or the SAS add-in could not find the specified server.

[Nigel_Pain]

Hi @JuanS_OCS 

Thanks for your suggestions.

The certificates are all sorted on our servers. I'm not authorised to open certificate stores on my laptop so can't check that.

The hotfix you refer to doesn't cover our version of the Add-In (7.15) so has presumably been incorporated into it. I've got the latest hotfix for 7.15 though, and this is supposed to address an issue with connecting to a VA server.

The application log doesn't give any clues:

2018-09-12 11:18:50,798 [VSTA_Main] INFO  SAS.EC.GTLTransport.GTLLogOn.ServerManager [(null)] - *server name redacted* changed its ConnectionStatus to: ConnectionInProgress
2018-09-12 11:18:50,911 [VSTA_Main] INFO  SAS.EC.GTLTransport.GTLLogOn.ServerManager [(null)] - *server name redacted* changed its ConnectionStatus to: Error

I can get a normal connection using a profile (connecting via the metadata on port 8561) but that's different to adding an Additional Visual Analytics Server.

Regards,

Nigel

[SASKiwi]

Just wondering why you are not using SAS Management Console for VA server configuration. That is the usual tool to do this. 

[Nigel_Pain]

Sorry, my subject is probably misleading. I'm not configuring VA, that's all done, and in use. I'm trying to configure AMO so that I can connect to VA from Excel:

This is more as an exploration of what's possible than anything else.

[SASKiwi]

OK. We also connect to SAS VA from the SAS Add-In but we just use the usual connect to metadata server method and that works fine for what we want to do. For example to run VA reports and have the results come back to Excel.

What are you trying to do in VA from Excel that is not possible via a metadata connection?

[Nigel_Pain]

I'm not trying to do anything in particular. Like I say, I was just exploring what was there. I'm the platform admin for our site, so I like to investigate functionality to see if it's of any special use to the users.

Thanks for your input, anyway.

Nigel

[JuanS_OCS · Accepted solution]

Hello @Nigel_Pain,

this should not take that much time, to be honest, because it is simple. It is strange.

I agree with @SASKiwi: you have 2 ways to connect to SAS VA resources (including explorations)

1. Connecting to the VA in its middle tier (TransportServices), hence you use the URL, and if it is a https connection, you might need http://support.sas.com/kb/61/148.html
2. Connecting to VA to its SAS metadata server (8561), and the metadata will take care of providing the middle tier info to the SAS Addon. 

If you cannot move forward, you might need someone to have a quick remote connection to figure out the exact problem. I would like to advise you to contact SAS Technical Support if it takes you too long.

[Nigel_Pain]

Many thanks to you and @SASKiwi for your help. It's not vital, as you'll have gathered. I'll do some more digging and if necessary speak to Tech Support.
All the best,
Nigel