SharePoint and Qlik Sense - A better way to get List Data? - SharePoint Specific Connector (future)?

[JeffQlik]

Hello -

I am a long time user of SharePoint using Microsoft SSRS / Report Builder for retrieving SharePoint List Data reporting.

I'm moving into using Qlik Sense Enterprise for reporting of SharePoint List Data and am finding differences between Report Builder and Qlik Sense (obviously) in the way data is retrieved.

I'm hoping to find feedback from those with experience on retrieving SharePoint List Data to Qlik Sense that is more efficient / beneficial than the way I am approaching (after many trials).

Screen shots are added to the end...

Side Note:  Many packages have a Connector specific to SharePoint - Perhaps Qlik Sense would consider a SharePoint Connector at some point to resolve Lookup Fields / People Fields and Pagination directly...

Questions: 

• What have others found as a Best Practice to get SharePoint List Data ... including resolution of Lookup Fields and People Fields?
• Is there a method for using $expand with Qlik REST to SharePoint ... and/or ... a formatting to resolve or reference the Lookup value directly?

My research thus far has shown :
(Using Web File Connector with "For Loop" and "URL IS" with $(<loop var) and View Filter has worked the best so far...)

------------------------------------------------------------

• Report Builder:
  • Uses SharePoint defined Connector
  • Resolves SharePoint Lookup Fields and People Fields to value vs. reference ID
  • Retrieves all SharePoint List Items - no user / developer defined Pagination required
• - 
• Qlik Sense REST API:
  • SharePoint Lookup Fields and People Fields are returned as reference IDs requiring querying of the lookup field list to resolve value(s) .. More Connections to get Lookup List(s) ... 10 Lookup fields = 10 more Connections
  • Can use the SharePoint / REST $expand=  for Lookup Fields ... however ... the $expand(ed) resolved value is placed down a level in the JSON tree ... Not knowing how to configure connection request / get data to have all the data at one level
  • All List Data can be retrieved using appropriate Pagination config ( for JSON =  d/__next)
• -
• Qlik Sense Web File:
  • SharePoint Lookup Fields and People Fields are resolved to values vs.  reference IDs - Yay!:
  • Lookup Field / People Field values are prefaced with RefId such as:   10;#Engine
    • Subfield() function easily mitigates RefId in LOAD 
  • List Data returns are limited to List View restrictions (eg. SharePoint magical 5000 List View Limit)
    • Mitigation: 
      • Create a SharePoint List Column for a "viewGroupNumber"
      • Set the viewGroupNumber as follows inside SharePoint Workflow ..   INT([ID]/5000) ... 
      • SharePoint List Items are now divided into Groups of 5000  .... 1, 2, 3, 4 
      • User Qlik Sense "For loop" in conjunction with "URL IS" to execute multiple Web File calls using $() of the Loop variable and a FilterField / FilterValue 

For i = 0 to 30
[UsageMetrics]:
LOAD
ows_ID AS ID,
Num(ows_SizeOfGroupsNumber) AS SizeOfGroupsNumber,
ows_viewGroupNumber AS viewGroupNumber,
ows_Created AS Created,
SubField(ows_Author,'#',2) AS CreatedBy,
ows_SiteLevel AS SiteLevel,
ows_SiteName AS SiteName
FROM [lib://Usage_GlnTrnTlbx_SC_WebFile (xxx_yyyy)]
(URL is [https://<mysite>/_vti_bin/owssvr.dll?Cmd=Display&List={DF0859CE-B9E3-4FA2-9770-1328056D6671}&RowLimit=0&XMLDATA=TRUE&View={D13237A7-22FC-421E-B4C0-B513FC563DE6}&FilterField1=viewGroupNumber&FilterValue1=$(i)],XmlSimple, table is [xml/data/row]);
Next i;

[Ken_T]

This conflicts with the information in post: 
O365 SharePoint Connector not decoding columns correctly
https://community.qlik.com/t5/Connectivity-Data-Prep/O365-SharePoint-Connector-not-decoding-columns-...

and should be updated or corrected.

Qlik Support confirmed this in a support ticket I logged.