Solved

SSAS Cube Refresh in Excel requires log in for every pivot

Posted on 2016-10-18
3
20 Views
Last Modified: 2016-11-07
I have an Excel workbook with multiple pivot tables all using the same SSAS connection.  When the users refresh the pivots, they have to enter credentials multiple times - once for each pivot. Is there any way around this that does not involve storing credentials in the connection string?
0
Comment
Question by:JLEmlet
  • 3
3 Comments
 
LVL 17

Accepted Solution

by:
xtermie earned 500 total points (awarded by participants)
ID: 41849596
There are some instructions here on how to set up your pivots providing credentials for automatic logon  https://technet.microsoft.com/en-us/library/hh344830(v=office.14).aspx

This section first explains how to create an ODC file to store a connection to SQL Server 2008 Analysis Services. Next, it describes how to use the stored connection to import data from the external source and finally, how to use PivotTable or PivotChart reports to analyze the data with Excel 2010.

The following procedures describe how to create an ODC file to store a connection to SQL Server 2008 Analysis Services to import external data and then configure a PivotTable and PivotChart report.
 To create a PivotTable or PivotChart report that uses an external data source  
1.In Excel, click the Insert tab, and in the Tables group, click the arrow under PivotTable and then click PivotTable. Note that you can also create a PivotChart based on the PivotTable report at the same time. To do so, click PivotChart.
2.On the Create PivotTable window, in the Choose the data that you want to analyze section, click Use an external data source to connect to an external data source, for example SQL Server 2008 Analysis Services. Next, click Choose Connection, where you can use an existing Office Data Connection file (ODC), or click Browse for More and then on the Select Data Source window, click New Source to open the Data Connection Wizard.

The Data Connection Wizard requires the following information:
◦Server or instance name
◦Logon credentials (Integrated Windows authentication or fixed credentials)
◦Database (Relational or OLAP)
◦Table or view for a relational database, or a cube or perspective for OLAP
◦Name and storage location of the data connection file (such as a local or network drive), or a SharePoint Server trusted location (data connection library)
◦Optional description
◦Search keywords
◦Friendly name (name for the data connection file seen in the workbook)

◦If Excel Services will be used with the connection file, one of the following authentication settings must be set:

◾ Windows Authentication   Default authentication method that uses the current user's credentials
◾ SSS   Credentials are saved in a Secure Store Service (SSS) application (requires the ID of the SSS or application)
◾ None   The authentication information is embedded in the connection string and requires the unattended service account

3.On the Data Connection Wizard window, select the data source that you want to connect to from the available list, and then click Next.

4.In the Connect to Database Server section, type the server name in the first box, and select the Log on credentials to use to connect. In this example, select Use Windows Authentication as the logon credentials, and then click Next.

5.In the Select Database and Table section, click the arrow in the Select the database that contains the data that you want section, and then select the database from the list. Next, click Connect to a specific cube or table if you know the specific data that you want to connect to, select the cube or table from the list at the bottom of this window, and then click Next.

6.On the Save Data Connection File and Finish window, type the name and description for this new Data Connection file (ODC), and then click Finish to save. You can also type Search Keywords at the bottom of this window, and select the Always attempt to use this file to refresh data option if it is needed. Click Authentication Settings if you want to specify the kind of authentication that you want for this data connection when the workbook is accessed by using Excel Services, and then click OK. Click Finish on the Data Connection Wizard.

7.Now that you have created a new ODC connection to use for this external data source connection, you are returned to the Create PivotTable window. In the Choose where you want the PivotTable report to be placed section, select one of the following location options:

◦Click the New Worksheet option to place the PivotTable report in a new worksheet starting at the first cell, A1.
◦Click the Existing Worksheet option to place the PivotTable report in an existing worksheet, and then in the Location box, specify the first cell in the range of cells where you want to position it. The default location is in the first worksheet at the first cell, shown as, Sheet1!$A$1.

8.Click OK when you have finished.
0
 
LVL 17

Assisted Solution

by:xtermie
xtermie earned 500 total points (awarded by participants)
ID: 41849597
valuable information on how to log on from Excel here
https://msdn.microsoft.com/en-us/library/gg492136.aspx
0
 
LVL 17

Expert Comment

by:xtermie
ID: 41876855
Valid recommendations and reading material relevant to problem provided to author
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Report Builder 9 30
sql calculate averages 18 25
Slow SQL query 12 21
Countifs but then also countif any value from list 6 14
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now