Using SharePoint as a data source for Modern …
[Pages:19]Using SharePoint as a data source for Modern Reporting
White paper
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. 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. v1.0 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 | 2
Table of Contents
Executive Summary .............................................................................................................................................. 4 Data Sources...........................................................................................................................................................4
SharePoint Sources for Power Query .............................................................................................................................................. 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 | 3
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. 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.
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. 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 | 4
repositories such as SQL Server or SQL Data warehouse. 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.
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 listdata.svc REST endpoint by appending /_vti_bin/listdata.svc 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.
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 | 5
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. 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. This is the process of ETL, defined above.
There are a whole host of reasons for doing this, including security, usability, and flexibility, the chief reason among them is performance. Using the same database for reporting that people are accessing to get work done puts added strain on that database, leading to a bad experience for everyone. Warehousing the data fixes this problem.
This situation is particularly important in SharePoint. Data platforms like SQL Server are designed for high volume data queries and retrieval and perform significantly better than SharePoint when querying large amounts of data.
Given that the SSRS connector connects directly to SharePoint data, it should only be used in cases where the data volume is small, and the need for up to date data is high. If paginated reports are needed for large volumes of SharePoint data, it is much better to load that data into a data warehouse/mart first, and report on it from there.
Caching Data with SQL Server Integration Services
SQL Server Integration Services (SSIS) is Microsoft's on-premises ETL platform. It comes with SQL Server and supplies a rich variety of capabilities for connecting to and transforming data.
SSIS does not have a native connector for SharePoint, but it does have an OData connector. As mentioned above, SharePoint lists can be expressed as OData feeds. Using the OData connector, data can be extracted from the SharePoint list, transformed, and combined with other data as required, and then loaded into a data store more suitable for reporting like SQL Server.
SharePoint Business Apps | 6
The OData connector for SQL server supports several authentication mechanisms including Windows authentication, and Azure Active Directory. This means that although SSIS is an on premises product, it can connect to SharePoint lists either on-premises or online in Office 365.
Using Power Query
SharePoint Folder
The SharePoint folder connector should be used whenever it is necessary to report on file storage in SharePoint, or to extract data from files stored in SharePoint. Although there are native connectors for many different file-based data types (XLSX, CSV, PDF) in Power Query, those connectors expect to find the files stored in a file system. The SharePoint folder is as a gateway to those files, and in fact, it will load the file connector for the relevant file type. To access file-based information, use the Get Data button in either Excel or Power BI Desktop, and choose SharePoint Folder.
Next, enter in the URL of the site, not the URL of a folder as the title might show.
SharePoint Business Apps | 7
The options on the next screen are Combine and Edit or Edit.
Combine and Edit will extract the contents of all files and merge them into a single table. This action may be appropriate if all files are of the same format, and they all have the same schema. However, it is more likely that some additional filtering and transformations will be required before any such combinations. Therefore, the Edit button should be used in most cases.
Extracting File Metadata
The data available here is the file-based metadata for each file. If the goal is to report on the files in the site, a couple of additional steps should be performed. First, the Content column contains the binary file content of each file. This data is for metadata reporting and can be removed. Right click on the column header and select Remove.
SharePoint Business Apps | 8
................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- open excel spreadsheet from command line
- turning text documents into excel spreadsheets
- power automate pdf to excel
- routeone utilities 400 utilities 400
- excel spreadsheet remittance file processing automate
- convert to csv spreadsheet
- automatically add new emails to spreadsheet in excel
- how to create pipe delimited files in excel
- using sharepoint as a data source for modern
Related searches
- using a tens unit for erectile dysfunction
- personal loan using car as collateral
- advantages of using computers as media pdf
- loans using property as collateral
- using effect as a verb
- change data source in powerpoint
- using while as a conjunction
- using amazon as a distributor
- using such as in a sentence
- odbc data source file location
- using python as a calculator
- another for word for as a result