Should I use ODC on report connection?

Question

Hi All,

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?

Many thanks

---

erkindunya

Answer 1

Hi erkindunya,

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

Thanks,

---

Challen Fu

TechNet Community Support

Answer 2

Hi Challen Fu,

Thank you very much for the answer what I mean from "ODC" Microsoft Office Data Connection (.odc) the connection string file.

Thanks

---

erkindunya

Answer 3

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.

---

Challen Fu

TechNet Community Support

Answer 4

Yes, 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

Answer 5

Hi Derek,

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?

many thanks

---

erkindunya

Answer 6

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.

---

Derek

Answer 7

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. 

Regards

---

erkindunya