As a part of the technical support team I have helped customers create data source connections to databases anywhere from MS Excel, MS Access, SQL Server, Oracle, and DB2 to even some legacy databases such as FoxPro. In Support, we say that as long as a database has a valid ODBC/JDBC driver available, iDashboards should be able to connect to it as a data source. Recently, we have received many requests from customers who either want to integrate iDashboards with Sharepoint or want to create a SharePoint List data source.
Traditionally Support has proven out that integration with SharePoint can be accomplished in one of three methods:
1] Using a Named user account:
iDashboards is embedded into a SharePoint Page Viewer Web Part. When, the user logs into SharePoint, a login screen to iDashboards pops up. If the user has an account in iDashboards, then he/she can login similar to logging in directly with the iDashboards URL.
2] Using a Guest User Account:
A guest user license needs to be purchased and configured first. Then, iDashboards is embedded into a SharePoint Page Viewer Web Part using the special guest user URL. This allows any number of users logged into SharePoint to view a set of embedded charts/dashboards that the guest user has access to.
3] Integrating Tomcat with IIS:
SharePoint uses IIS as a front-end webserver and since it is not a J2EE compliant application server, it is required to integrate Tomcat (our recommended webserver) with IIS. This integration will allow a user logging into SharePoint to automatically log into iDashboards (again embedded in a Page Viewer Web Part) and can access dashboards which that logged-in user has access to. This assumes that the user exists in Active Directory or other directory services and also exists as a named user in iDashboards.
SharePoint lists have always been a challenge when it comes to pulling data for creating charts in iDashboards. Especially since the SharePoint data resides in a set of tables which cannot be termed as a relational database. Since iDashboards is a java based application, Support has always recommended JDBC drivers to connect to relational databases such as MS SQL Server, Oracle, MySQL etc. So at first it was just logical to explore the same option when it came to MS SharePoint Lists. We tested a third party driver which showed a lot of promise initially. But soon it was clear that establishing a basic connection to SharePoint data was just half the battle won, the actual issue was being able to use this data effectively within the iDashboards application.
The major limitation from iDashboard’s point of view was the inability to apply SQL functions on the derived SharePoint data for the purpose of data aggregations/manipulations. With the JDBC driver, all iDashboards could do was to show the SharePoint List data AS-IS in charts, with the inability to calculate totals, counts, averages, minimum and maximum values etc. This threw us off big time.
With our preferred option out the window (at least for the time being), we then found an alternative solution in the form of ‘AxioWorks SQList’ (http://www.axioworks.com/) service. The SQList service acts as a middleware between an installed MS SQL Server database (2000 – 2008) and a SharePoint list(s). Once installed, the SQList service provides a tool called the ‘SQList Manager’ where the entire configuration is handled. After it is configured, the service then dumps out the data from SharePoint Lists into tables created by SQList service in one or more specified SQL Server database(s). A data source to the MS SQL Server database can then be created in iDashboards to derive the SharePoint List data in the form of charts.
SharePoint is an excellent tool utilized by many organizations for sharing corporate data. The iDashboards and SharePoint integration has been tested and proven out by many customers. This integration continues to provide value. Now with AxioWorks SQList, SharePoint list are as easy as a JDBC connection.
Khuteja Abdul-Technical Support Engineer, iDashboards