We have a SharePoint Online account and local data in a SQL Server instance. The data from the SQL Server is available as an OData feed in SharePoint and can be worked with in Excel Power Query.
We had hoped to use an angularjs UI, hosted in SharePoint Online, to provide some extra functionality on top of that data, but we are running into problems when trying to access the data "directly". I'm not sure if the cause is the authentication, the OData feed functionality or the lack of SharePoint Online capabilities.
Hence the following questions:
- Is the data in a Power BI OData feed accessible to a SharePoint hosted, client side UI?
- Does the SharePoint REST API provide access to a Power BI Datasource in some way?
- Or do we need to (and can we) use BCS to create an External Data list, synchronised with a local SQL Server instance.
- Are there specific subscription requirements to make this possible (e.g. if BCS is needed, that requires a plan 2 subscription, I think?)
Or, to summarize:
- What is a good way to provide a simple UI that reads and updates some on-premise data and is fully integrated with SharePoint Online (preferrably using REST + client-side Javacript)?
BTW, according to this page, including on-premise SQL in SharePoint Online is impossible: http://office.microsoft.com/en-us/office365-sharepoint-online-enterprise-help/make-an-external-list-from-a-sql-azure-table-with-business-connectivity-services-and-secure-store-HA102933008.aspx
"NOTE: You cannot use on-premise data sources, such as .NET Type,
with SharePoint Online.
In addition, you cannot use a SQL Server data source that is on-premise
with SharePoint Online."