3 พฤษภาคม 2555 10:26
I am only connecting a Database (only 3 tables) and generating 10 reports from that, I developed the report in below steps:
1. open up PowerPivot for Excel add in from Data source select SQL
2. use DAX or Query
3. then set SSS ID data refresh
the question is should I use ODC on data base connection? where should I save the connections on SharePoint? and if I got Claim based authentication it will cause issue when I run auto refresh?
9 พฤษภาคม 2555 7:40ผู้ดูแล
I am not sure what's your meaning of ODC, if you want to import the data from SQL Server, just select the correct datasource type from the PowerPivot windows, there is no need to save the connections on SharePoint, the connect string is saved in the PowerPivot file, if you want to comfigure the auto refresh, in Credentials, specify an account used to run the data refresh job. The account must have Contribute permissions on the workbook so that it can open the workbook to refresh its data. It must be a Windows domain user account. In many cases, this account must also have read permissions on the external data sources used during data refresh. Specifically, if you originally imported the data using the Use Windows Authentication option, then the connection string is built to use the Windows credentials of the current user. If the current user is the data refresh account, that account must have read permissions on the external data source in order for data refresh to succeed.
For more information about refreshment, please see: http://msdn.microsoft.com/en-us/library/ee210651.aspx
TechNet Community Support
9 พฤษภาคม 2555 14:45
Hi Challen Fu,
Thank you very much for the answer what I mean from "ODC" Microsoft Office Data Connection (.odc) the connection string file.
10 พฤษภาคม 2555 1:53ผู้ดูแลJust like I mentioned in the first reply, if you want to import the data from SQL Server, just select the SQL Server datasource type from the PowerPivot windows is OK.
TechNet Community Support
21 พฤษภาคม 2555 20:37Yes, you can. you can save the ODC file in "data connection", which is content type. If you dont have it so far in your sharepoint, you can create one.
- แก้ไขโดย Derek Dai 21 พฤษภาคม 2555 20:38
22 พฤษภาคม 2555 8:57
Thanks but I am facing issue if I put the connection files in ODC or Data feed library because I am using Claims Authentication all permission are set for the auto refresh account holders.
Any work around or tricks?
22 พฤษภาคม 2555 14:02
I am not totally following you.
Generally, you have a workbook either in "Powerpivot Gallery" or "Powerpivot Workbooks", in which you can set the auto-refresh.
ODC file, uploading to the "data connection" is a shortcut, which points to your real excel workbook.
23 พฤษภาคม 2555 9:38
I got the workbook in "Powerpivot Gallery" with data source from SQL and SharePoint list as data feed,
Data source using SQL: Connection string are connected and saved within the workbook and refresh successfully using SSIS.
Data source using SharePoint list as data feed : Connection string are Data feed are saved in Data feed library refresh fails using SSIS.