"Connexion successful" mean "the network connection is operational but without any guaranty that the current user can "Read" the source data.
First, check your sa_capp account has "Read" right on the source database. For Microsoft SQL Server Analysis Services, add sa_capp account in a Role with "Read" authorization.
For Live query connexion, you don't need to schedule refresh.