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.

2 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. 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.

3 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 .. 5 SharePoint Folder.

4 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.

5 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. 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.

6 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.

7 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 .

8 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. 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).

9 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.

10 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.