MGDC for SharePoint FAQ: How to Run a PoC without Pulling Your Entire Tenant
Overview When getting started with SharePoint data in Microsoft Graph Data Connect (MGDC) for SharePoint, many teams want to validate scenarios - such as reporting or analytics before committing to a full production deployment. A common first instinct is to pull a complete dataset from a production tenant. While this delivers the most comprehensive view of SharePoint usage, it also: Requires broad administrative authorization Consumes the most Azure compute and storage resources Increases MGDC extraction and processing costs Adds complexity to early experimentation Fortunately, MGDC for SharePoint provides multiple ways to run low‑cost experiments or proof‑of‑concept (POC) deployments using partial or scoped datasets. This guide presents these options using a uniform comparison model, helping you choose the right approach based on: Cost Representativeness of production behavior Implementation effort Dataset completeness Supported datasets Option 1: Use a Dev or Test Tenant Description Use an existing development or test tenant (or create a new trial tenant) to enable MGDC and run initial experiments. Pros Smaller datasets reduce MGDC and Azure costs Easier to obtain administrative permissions Lower operational impact Cons May not reflect production‑scale usage patterns Some SharePoint features or integrations may be missing Requires simulated user activity to generate meaningful data Trial tenants are time‑limited Learn More Microsoft 365 Trial Options Azure Trial Options Option 2: Start with the SharePoint Sites Dataset Description The Sites dataset is typically the smallest MGDC dataset for SharePoint and provides tenant‑wide metadata for all site collections. Pros Lower cost compared to Files or Permissions datasets Provides organization‑wide coverage Minimal MGDC configuration beyond standard onboarding Small dataset can be handled directly by a variety of analysis tools Cons Does not include permission or file details Limited insight compared to full datasets Learn More How can I estimate my Azure bill? Updated! Gather a detailed dataset on SharePoint Sites Option 3: Sample a Limited Number of Rows Description Some MGDC SharePoint datasets support returning only a subset of rows in query results. This is supported across the top 5 SharePoint datasets in MGDC (Sites, Permissions, Groups, Files and File Actions). Pros Minimal and predictable extraction cost Enables rapid schema inspection Provides total dataset row count in request metadata Cons Rows are not returned in a predictable order Sample is not randomized. It is not reproducible and could be biased Results should not be used to draw tenant‑level conclusions Learn More How can I sample or estimate the number of objects in a dataset? Option 4: Filter by SiteId Description Because SharePoint data is partitioned by site collection, MGDC filtering allows you to extract data from a single site or a small group of representative sites. This supports Sites, Permissions, Groups, Files and File Actions datasets. Pros Enables realistic workload simulation Reduces total extraction volume Simplifies downstream reporting Cons May introduce sampling bias Not suitable for tenant‑wide reporting Learn More How can I filter rows on a dataset? Option 5: Filter by TemplateId Description Instead of selecting individual sites, filter by site template to isolate specific workloads. For example, you could filter for OneDrives or SharePoint Embedded. Pros Consistent dataset scope Useful for workload‑specific analysis Cons Limited dataset support (supported only for Sites, Files and File Actions) May not reflect cross‑workload usage patterns Learn More How can I filter rows on a dataset? Option 6: Use Delta State Datasets Description Delta datasets allow you to retrieve only changes since your last data transfer for supported SharePoint State datasets. Pros Enables recurring analytics with lower extraction costs Supports daily or weekly trend analysis Reduces data movement after initial ingestion Cons Requires an initial full dataset pull Adds complexity to downstream merge processing Learn More How can I use Delta State Datasets? How do I process Deltas? Summary MGDC for SharePoint provides multiple approaches to extract targeted subsets of tenant data, allowing teams to: Run proof‑of‑concept deployments Validate analytics pipelines Test governance or migration scenarios Estimate ongoing MGDC and Azure costs By selecting the right combination of dataset scope, filtering strategy, sampling method or delta tracking, you can balance cost, representativeness, and implementation effort before scaling to a full production deployment. For additional guidance on MGDC for SharePoint, visit SharePoint Data in MGDC.MGDC for SharePoint FAQ: How to flatten datasets for SQL or Fabric
When you get your data from Microsoft Graph Data Connect (MGDC), you will typically get that data as a collection of JSON objects in an Azure Data Lake Storage (ADLS) Gen2 storage account. For those handling large datasets, it might be useful to move the data to a SQL Server or to OneLake (lakehouse). In those cases, you might need to flatten the datasets. This post describes how to do that. If you’re not familiar with MGDC for SharePoint, start with https://aka.ms/SharePointData. 1. Flattening Most of the MGDC for SharePoint datasets come with nested objects. That means that a certain object has other objects inside it. For instance, if you have a SharePoint Groups object, it might have multiple Group Members inside. If you have a SharePoint Permissions object, you could have many Permissions Recipients (also known as Sharees). For each SharePoint File object, you will have a single Author object inside. When you convert the datasets from JSON to other formats, it is possible that these other formats require (or perform better) if you don’t have any objects inside objects. To overcome that, you can turn those child objects into properties of the parent object. For instance, instead of having the File object with an Author object inside, you can have multiple author-related columns. For instance, you could have Author.Name and Author.Email as properties of the flattened File object. 2. Nested Objects You can get the full list of SharePoint datasets in MGDC at https://aka.ms/SharePointDatasets. Here is a table with a list of objects and their nested objects: Object How many? Primary Key Nested Object How many? Add to Primary Key Sites 1 per Site Id RootWeb 1 per Site Sites 1 per Site Id StorageMetrics 1 per Site Sites 1 per Site Id SensitivityLabelInfo 1 per Site Sites 1 per Site Id Owner 1 per Site Sites 1 per Site Id SecondaryContact 1 per Site Groups 1 per Group SiteId + GroupId Owner 1 per Group Groups 1 per Group SiteId + GroupId Members 1 per Member COALESCE(AADObjectId, Email, Name) Permissions 1 per Permission SiteId + ScopeId + RoleDefintion + LinkId SharedWithCount 1 per Recipient Type Type Permissions 1 per Permission SiteId + ScopeId + RoleDefintion + LinkId SharedWith 1 per Recipient or Sharee COALESCE(AADObjectId, Email, Name) Files 1 per File SiteId + WebId + ListId + ItemId Author 1 per File Files 1 per File SiteId + WebId + ListId + ItemId ModifiedBy 1 per File When you flatten a dataset and there is an object with multiple objects inside (like Group Members or Permission Recipients), the number of rows will increase. You also need to add to primary key to keep it unique. Also note that the File Actions, Sync Health and Sync Errors datasets do not have any nested objects. 3. One Object per Parent When the nested object has only one instance, things are simple. As we described for the Author nested object inside the File object, you promote the properties of the nested object to be properties of the parent object. This is because the Author is defined as the user that initially created the file. There is always one and only one Author. This can happen even happen multiple times for the same object. The File also has a ModifiedBy property. That is the single user that last changed the file. In that case, there is also only one ModifiedBy per File. The Site object also includes several properties in this style, like RootWeb, StorageMetrics, SensitivityLabelInfo, Owner and SecondaryContact. Note that, in the context of the Site object, there is only one owner. Actually two, but that second one is tracked in a separate object called SecondaryContact which is effectively the secondary owner. 4. Multiple Objects per Parent The SharePoint Permissions dataset has a special condition that might create trouble for flattening. There are two sets of nested objects with multiple objects each: SharedWith and SharedWithCount. SharedWith has the list of Recipients and SharedWithCount has a list of Recipient Types. If you just let the tools flatten it, you will end up a cross join of the two. As an example, if you have 4 recipients in an object and 2 types of recipients (internal users and external users, for instance) you will end up with 20 objects in the flattened dataset instead of the expected 10 objects (one per recipient). To avoid this, in this specific condition, I would recommend just excluding the SharedWithCount column from the object before flattening. 5. Conclusion I hope this clarifies how you can flatten the MGDC for SharePoint datasets, particularly SharePoint Permissions dataset. For further details about the MGDC for SharePoint, https://aka.ms/SharePointData.MGDC for SharePoint FAQ: Why does the file count not match?
I am frequently asked why the number of files in the Sites dataset does not match the count of the Files dataset. It is true that they sometimes don't. Here are the counts we are talking about: Sites: SELECT Id as SiteId, StorageMetrics.TotalFileCount AS FilesInSite FROM Sites Files: SELECT SiteId, COUNT(*) AS FilesInSite FROM Files GROUP BY SiteId The main reasons for the discrepancy are: The Files dataset is collected weekly, while the Sites dataset is collected daily, making it difficult to capture the exact same state. The Files dataset includes only items inside Document Libraries, whereas the Sites dataset counts all files, including those in other list types. The Files dataset does not include pages (files ending in .ASPX), while the Sites dataset counts all files. The Files dataset does not include items in the primary and secondary recycle bins, whereas the Sites dataset counts all files. Here are a few examples: A new site was created, and a few files were uploaded to a document library in this new site. Two days later, you get the SharePoint Sites dataset and find the new site with the right count of files. However, you cannot find these files in the SharePoint Files dataset. This is because the Files dataset may take one week to refresh. Wait a week and try pulling the Files dataset again. A team site was created with a few lists, where some of the items have file attachments. The SharePoint Files dataset does not show these file attachments. This is because the SharePoint Files dataset will only show files in document libraries. You deleted files from a document library in a SharePoint site a few weeks ago. Now the count of files in the SharePoint Sites is smaller than the count of files in the SharePoint Files dataset. That is because files in the recycle bins are excluded from the Files dataset. Once these deleted files go through the first and second level recycle bins, the count in the Sites dataset will also reflect that reduction.
