Using SharePoint as a data source for Modern Reporting

1 Using SharePoint as a data source for Modern Reporting White paper SharePoint Business Apps | 2 Copyright The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication. This white paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED, OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in, or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation.

2 Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property. 2019 Microsoft Corporation. All rights reserved. June 2019 Microsoft, Azure, Office, Office 365, Excel, Word, PowerPoint, OneNote, OneDrive, SharePoint , Skype for Business, Teams, Planner, Flow, Graph, Yammer, and Delve are trademarks of the Microsoft group of companies. The exclusion of a specific trademark herein shall not be constituted as a waiver of Microsoft s rights. All other trademarks are property of their respective owners. SharePoint Business Apps | 3 Table of Contents Executive Summary .. 4 data Sources .. 4 SharePoint Sources for Power Query.

3 5 SharePoint Folder .. 5 OData .. 5 SharePoint List and SharePoint Online List .. 5 SharePoint Sources for Paginated Reports .. 6 Reporting Directly on a SharePoint List .. 6 Caching data with SQL Server Integration Services .. 6 Using Power Query .. 7 SharePoint Folder .. 7 Extracting File Metadata .. 8 Extracting File Contents .. 10 Combining File Contents .. 10 OData .. 12 SharePoint Lists and Online Lists .. 14 FieldValuesAsText .. 16 Working with the Power BI Service .. 17 Publishing .. 17 Automatic Refresh .. 17 Recap and Recommendations .. 19 SharePoint Business Apps | 4 Executive Summary SharePoint is a great organizational data store. SharePoint lists offer a quick and easy way for users to store and share millions of items, while document libraries have become the industry standard for document management, supplying secure, reliable storage. SharePoint libraries supply the backbone for document storage in all of Office 365.

4 When you add documents to Microsoft Teams, or Yammer, they re stored in a document library. In addition to lists, documents can also contain valuable data . For example, where one user may create a list to store data in rows and columns, another user may choose to do so in Excel, and then save the Excel file to a document library. Both containers hold valuable data , and as with most data , there will be a need to report on it. Once the Reporting needs go beyond the features available in a SharePoint view, a different Reporting platform is necessary. There are several different platforms and technologies for working with SharePoint data , such as Power Query, Excel, Power BI, SQL Server Reporting Services and SQL Server Integration Services. Your choice of Reporting platform depends on the report type, data , storage, and your skill level. With a wide variety of choices available, it is important to understand the strengths and weaknesses of different approaches to choose the right tool for the job.

5 This white paper examines the different platforms and approaches that can be used to report on SharePoint data and explains the procedures. data Sources There are currently three primary tools available to access SharePoint data for Reporting purposes, and each one serves a different purpose. Traditional business intelligence solutions employ a process called extract, transform and load (ETL). This process extracts the data from its source environment, transforms it to support the end use, (filtering, column removal, data augmentation) and finally load and optimize storage for Reporting . This process cuts Reporting load on the source system and improves security and usability. Power Query is the Modern ETL tool and is built into Power BI (the service), Power BI Desktop, Excel, and is now available online through Power BI Dataflows and Microsoft Flow. Power Query can extract content from a SharePoint list and cache it into a tabular data model for Reporting purposes.

6 Using Power Query Online, data can be cached in a Power BI Dataflow. Report Builder is the tool for building paginated reports for SQL Server Reporting Services (SSRS), Power BI Report Server, and the Power BI service (currently in preview). Report builder can connect directly to SharePoint list data and report on it in place without the need for an ETL tool. Doing this however places a significant load on SharePoint and is not recommended for large amounts of list data . To build a paginated report with a large amount of list data , it is possible to use SQL Server Integration Services (SSIS) to move the data into a data warehouse and report on it from there. SQL Server Integration Services is an on-premises enterprise ETL platform that is available as a part of SQL Server. SSIS has an OData driver, which can be used to connect to SharePoint list data and move it to other SharePoint Business Apps | 5 repositories such as SQL Server or SQL data warehouse.

7 Reports can then be run from the destination source . SharePoint Sources for Power Query Power Query is an ETL tool that can connect to SharePoint data via several different connectors. Once connected, it uses a step-based approach to transform the data into the required shape and load it into a data model for Reporting purposes. In the case of Power Query Online, the data is loaded into a Power BI Dataflow for further modifications and reuse. There are 4 connectors available in Power Query for connecting to SharePoint data : SharePoint Folder, OData, SharePoint List and SharePoint Online List. SharePoint Folder The SharePoint folder connects to a SharePoint site and returns all the documents contained by that site in all libraries and folders. This connector can be used to report on documents and their metadata. It can also be used to retrieve document contents. This is useful for connecting to Excel or CSV files stored in SharePoint or OneDrive.

8 The native Excel and text connector connect only to the file system, but by Using the SharePoint folder connector Excel and CSV data stored in SharePoint can be easily refreshed by the Power BI service. If documents have a common schema, they can be combined into a single table. This can be useful in a logging scenario, when new files are added to a folder periodically. OData Any SharePoint list since SharePoint 2010 can be expressed as OData. To do so, we use the REST endpoint by appending /_vti_ to the end of a site URL. This URL can be used as a starting point within Power Query to access list-based data . SharePoint List and SharePoint Online List The SharePoint list and SharePoint Online list connectors are identical and can be treated as one (and will be referred to henceforth as SharePoint List). The only fundamental difference between the two is the method of authentication. One authenticates to SharePoint Online, and the other to an on-premises SharePoint .

9 The SharePoint List connector is like the OData Connector in that it connects to the root of the SharePoint site and allows the report designer to import from one or more SharePoint lists. However, the type and quantity of data returned by this connector is significantly richer than that returned by the more generic OData connector. The SharePoint list container returns more metadata about each item, and it provides helpers for use with complex SharePoint data types. The full breadth of these helpers is beyond the scope of this paper, but it is detailed in Using Power BI with Complex SharePoint Field Types. One example of the differences between the two connectors can be seen below. Both examples are querying the same SharePoint list. OData connector results SharePoint Business Apps | 6 SharePoint List connector results SharePoint Sources for Paginated Reports SQL Server Reporting Services (SSRS) has been in use since 2004, and it has traditionally been the platform for what is now referred to as paginated reports.

10 Paginated reports are operational in nature and tend to be pixel and page perfect. They are often also referred to as RDL reports. Paginated reports can be rendered by SSRS, Power BI Reporting Server (PBIRS) and now by the Power BI service itself. Paginated reports are connected directly to a SharePoint list, and the reports are run directly against the SharePoint list. The SharePoint connection for paginated reports is based on the legacy SOAP based web services, which has several implications, but the most important is authentication. Paginated reports are unable to connect SharePoint Online data because they are unable to authenticate against Azure Active Directory. This restricts the data sources to SharePoint on-premises. Reporting Directly on a SharePoint List In almost any Reporting environment, it is considered a bad practice to report directly against operational data . It is much more efficient to abstract the data away from its source into some form of cache, typically a data warehouse or a data mart.