11 Replies Latest reply on Dec 6, 2010 4:38 PM by VCampus-METCO

    Builders' Café Webinar Series - PI OLEDB Enterprise 2010

    andreas

      The recording of the Builders' Café Webinar Series - PI OLEDB Enterprise 2010 has been posted at the auditorium.

        • Re: Builders' Café Webinar Series - PI OLEDB Enterprise 2010
          VCampus-METCO

          Andreas,

           

          Have you any experience in using the PI-OLEDB Enterprise driver referenced as a trusted Data Connection in SharePoint 2007. I would like to create a Excel Services report which references an Office Data Connection stored in the SharePoint database. I build the report fine and it brings back the rows of data from AF, but as soon as I publish the report to SharePoint and try to open using IE, it complains that table queries do not work with Excel Services. Any advice would be appreciated on how to use AF within Excel Services.

           

          Simon

            • Re: Builders' Café Webinar Series - PI OLEDB Enterprise 2010
              hanyong

              Hi Simon,

               

              I think this is because of feature limitation in Excel Services, as listed here. If you refer to the list, we can see that table queries is one of them. One way to workaround this is to use pivot table that flattens to a table to display your result that is retrieved from Data Connections mentioned in this url

               

              Hope this helps.

                • Re: Builders' Café Webinar Series - PI OLEDB Enterprise 2010
                  VCampus-METCO

                  Han,

                   

                  Yes after I made the post I realised that query tables were not allowed so I switched to a pivot table. I now have a spreadsheet that works perfectly and references an ODC file in a SharePoint data connection library, which in turn has teh connection string and SQL statement to get data from AF. I have said to use windows authentication in Excel and also in the AF OLEDBENT connection string. Unfortunately when I try and view the report in the browser and then try and refresh the connections I get an error "The data sources may be unreachable, may not be responding, or may have denied you access".   As far as I know I have full access to everything so I really do not know where the problem is or how to debug. Nothing is in the PI Messge Log, nor in the PIOLEDB.LOG file so this is something internally within SharePoint.  Things are just too complicated these days!

                   

                  Any ideas? I find it hard to believe that this is not the first time that this is the first time this has been tried.

                    • Re: Builders' Café Webinar Series - PI OLEDB Enterprise 2010
                      Ahmad Fattahi

                      Do you have any other way of trying the connection from the same box? My goal here is to see if the problem lies in your piece or is just a lack of authentication.

                        • Re: Builders' Café Webinar Series - PI OLEDB Enterprise 2010
                          VCampus-METCO

                          Ahmad,

                           

                          Thanks for your reply.

                           

                          I can use the same ODC file from within Excel itself and the query returns data to me as expected. I can also run queries from SQL Commander. It only breaks when I try and use the ODC file from the same published excel spreadsheet to the portal in explorer mode then try to do a refresh so it must be a permissions thing. I have set in the ODC file to use Windows Authentication and my connection string to AF also uses the Integrated Security Option. I do not get any messages in the PI Log indicating that there is a connection attempt from the OLEDB driver, so it could be that something in SharePoint itself is preventing the query from being executed. Do you know if there is any way to debug SharePoint to see where the problem is?

                           

                          OSI must have done this kind of thing before - it is on the demos that they show but there does not appear to be a step by step description available. Maybe because this is a ShsrePoint problem and not PI.