Since you mention using the same dataset, I have frequently used this solution to reduce memory usage and, incidentally, reduce passwords prompts. Unfortunately, I've never used Analysis Services -- only SQL Server connections -- so it might not work for you. However, if that's the case, you could perhaps consider making an equivalent data set available as a SQL server query, table or view.
Link pivot tables to a single data connection
- From a pivot table, design tab, click Change data source, Choose connection
- Pick a data connection you wish to use
- Repeat for all pivot tables, pointing to the same data connection
- You can then delete all remaining, unused data connections
- Refreshing will ask the password only once, and all linked PivotTables will be updated at once.
Alternative: Regular pivot tables on tabular dataset
Alternatively, you could consider downloading your dataset in a plain table format in a worksheet and create regular (non-connected) pivot tables from that worksheet. To support expanding dataset sizes, the pivot table source should include a large area of blank rows, which will need to be filtered out. A named reference might also help here.
Alternative: VBA Macro
Another alternative would be to create a VBA macro to feed a password and refresh all connections, but I have never tested it. Excel might requires you to save the password in order to perform the refresh without prompts, in which case you'd need to take care to remove it after completion.
Could you clarify 1) are the various pivot tables connected to the same or different data sets 2) is a VBA macro an option 3) Just as a comment if you're not in IT, window authentication may be an option worth pursuing as it places the security responsibility on IT, where it probably belongs, in addition to solving your problem. – mtone – 2015-10-15T15:52:37.193
Yes. 1) Yes, all elements refers to the same dataset. 2) I thought about it...Like a promt to insert user and password, then automatically change the connection properties. I'd like to use this solution as last hope. 3) Yes, you are right but I'd like to use a solution without re-configuring all users pc (since this should be a client oriented solution) – gmeroni – 2015-10-15T17:08:05.620