Should I use ODC on report connection?


  • 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


    jeudi 3 mai 2012 10:26

Toutes les réponses

  • 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:


    Challen Fu

    TechNet Community Support

    mercredi 9 mai 2012 07:40
  • Hi Challen Fu,

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



    mercredi 9 mai 2012 14:45
  • 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

    jeudi 10 mai 2012 01:53
  • 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.


    lundi 21 mai 2012 20:37
  • 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


    mardi 22 mai 2012 08:57
  • 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.


    mardi 22 mai 2012 14:02
  • 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. 



    mercredi 23 mai 2012 09:38