In my previous post, I have explained sharing SQL Server Data source connections in SharePoint 2013. In this post we can use SQL Analysis service connection. We can create Analysis service connection as shown the steps below,
In Excel, Data tab, click on From Other Sources and select From Analysis Services option.
In Connect to Analysis services, in the Server name box, we have to specify the SQL Server name to get the SQL Server data. Here Iam entering as “.” to connect the local. We have to specify the Log on credentials in the credentials section. To use Windows Authentication, we have to select Use Windows Authentication or to choose SQL Server authentication Select SQL Server Authentication and have to enter the credentials. Click on Next.
“In the Select the database that contains the data that you want” list; we have to select the database that we want to use. If we don’t want to select any database or if there is no databases, uncheck Connect to a specific table.
On Save Data Connection and File and Finish page, we have to enter File Name, Description, Friendly Name, and Search Keywords boxes. In the Authentication settings, we can choose the type of authentication. Click on finish.
Connection will be saved in default location. Once we save the connection, we can upload the connection file Data connections library as shown in my previous post.