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.
Depreciation of 'Shared with us' on Group Sites
In leu of the latest update to the document library on groups sites showing 'In channels' and 'In site library' sections, the 'Shared with us' menu item that allowed you to locate any resources shared with the group centrally has been removed. After contacting Microsoft support, they indicated that this feature is being depreciated. There has been no admin notifications, timeline, or indication of how users are expected to locate resources shared with the group from the group context. My question is what next? It cant be logical to just drop a feature that supported collaborative working without a viable alternative. The expectation can't be to direct people to Onedrive to find these in the share section there...it defeats the concept of group-centric working and going to one place to locate everything to do with that group.
Sharepoint Online External Group
Hi, We currently have a Sharepoint 2013 foundation server that we run our small intranet on. We are a small group of companies (3) and each company has their own AD and Tenet on Azure AD. There are about 200 users in total. I have been trialling the Sharepoint Migration tool to migrate over the current intranet which seems a bit buggy but I think it will be enough with a bit of work to do a like for like. It seems pretty straight forward to add in my current users in the org that the Sharepoint site is hosted in. however, I also need to add in all of the other users from the 2 external organisations so they can see the site. Is there a simpler way to do this other than have to add in 130 users manually? Ideally, I was hoping to be able to just apply a group that was created in each external org to the sharepoint site but that doesnt seem possible. Previously (or currently, I should say) as we have forest trusts in place on premises, its pretty easy to do this but now we are moving to the cloud it doesnt seem like it is possible? thanks, Paul
Customize Site Webpart
I've been asked to look into the possibility of surfacing our MS Teams SPO sites/Groups on a page in SPO. The Sites Webpart kind of does this, but its not able to show all o365 groups/Teams that im connected to. I though this would be some sort of Search query but I haven't done many in SP2013 much less SPO. Is this possible with custom code or webparts?
Breaking Inheriting Permissions and creating groups
Hello everyone, I am trying to control access to folders from specific user. So I created a library and "stopped inheriting permissions", my plan was to create groups with different access levels and add different users within. But as I was about to do this, I ran into an issue, I realized that groups are created at the site level and because of this, the groups will also have access to the other Libraries I have within the same site...which is something I do not want and is risky. The only option I see so far is to add individual users into the Library and then restrict each folder to each user individually. But the issue is that this will not work in the long run. Is there any other way to do this that anyone is aware of? Thank you all. The image below shows I am only able to a
