The error "Connection to server has been lost. All data sources have been disconnected" is displayed in Analysis Office at any time when using the tool.
SAP Analysis for Microsoft Office
This is not a bug in the tool. The error "Connection to server has been lost. All data sources have been disconnected" is always displayed when the communication between Analysis Office and the backend is broken.
Client-server application needs a working network connection, unless the client and the server are located in the same host PC.
Analysis for Office, as any other "client application" that uses client-server architecture, uses a so called "network connection" to communicate with the backend server. The network connection can be either hard wired connection with a Local Area Network (LAN) cable connecting the client PC and the server using a real hardware network adapter or it can be a virtual connection also known as VPN, using internet and a virtual network adapter in the PC (refer to this Wikipedia article to learn more about VPN).
According to the Wikipedia article:
A virtual private network (VPN) is a mechanism for creating a secure connection between a computing device and a computer network, or between two networks, using an insecure communication medium such as the public Internet. A VPN can extend a private network (one that disallows or restricts public access), enabling users to send and receive data across public networks as if their devices were directly connected to the private network.
A host-to-network configuration is analogous to connecting a computer to a local area network. This type provides access to an enterprise network, such as an intranet. This may be employed for remote workers who need access to private resources, or to enable a mobile worker to access important tools without exposing them to the public Internet.
The above statement means that if the server is in a private network and/or uses TCP/RFC protocol (such as for BW systems, either direct or initiated via a LIVE connection) and the user is not in the same network, there must be some network connection established between those 2 networks, typically using VPN. In contrast, if the server is http based (like BOE or SAC) and is hosted in a public network, it is possible to simply use internet to connect to the server, thus there is no need of any intermediate VPN. Also it is important to know that a particular port number plays no role in this scenario, but simply the protocol used by the client-server.
The client application is not typically aware of the underlying network type, may it be cabled (LAN) or VPN. The client simply consumes the network provided by the PC and it is the responsibility of the user / admin to make sure that the client can successfully reach the server via whatever the network established in the organization. Some troubleshooting tips / tools can be found in the SAP Note 3008665.
As described above, this is not a bug of Analysis Office, so it is not something that can be fixed in the product. The recommendation is to contact the network team or basis team to identify why the connection is being terminated.
SAP Note 3008665 - Analysis Office Error: Partner not reached
SAP Note 486688 - Schedule VPN connection to SAP network
ID-114200, communication, rfc, connection, broken, broke, failed, disconnected, disconnecting , KBA , BI-RA-AO-XLA , Excel Addin , How To