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 athttps://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.
View Group membership in outlook app for any user
Earlier version of Outlook Desktop application use to provide Group / DL membership information of any user, within the user property window opened from the Address Book or from any email. But the latest version of Outlook Windows app as well as the Outlook Webapp for Microsoft 365 no longer provide this Group / DL membership info within the User property. Does this require some configuration change at the Admin level (Hide Microsoft 365 Groups from the global address list) ? OR Is there an alternate option available within the new Outlook Windows app to view the Group / DL membership of any user?
Introducing the Groups Admin Role
Today, we are thrilled to announce that the the Groups admin role in Azure Active Directory (Azure AD) is now generally available. This new Azure Active Directory role enables you to perform group management tasks for and Azure AD security groups without requiring Global administrator permissions. Users with the Groups administrator role can use the Microsoft 365 Admin center, the Azure portal and other methods to create, edit, delete, and restore groups, and manage Office 365 Groups policies (example - creation, naming, and expiration policies). Figure 1 - Managing Groups in the Microsoft 365 Admin center Figure 2 - Managing Groups in the Azure portal You can provision the Groups admin role using Azure AD PowerShell: #Below steps need to be completed only once to install the Azure AD scripts Install-Module -Name AzureAD #Connect (use privileged role) Connect-AzureAD # Get the user to be assigned the role, replacing foo@contoso.com with the email address of the user $roleMember = Get-AzureADUser -SearchString "foo@contoso.com" # Enable the role for the tenant (skip this step if you have already enabled the role for your tenant). If you have already enabled the role, you will get an error (which you can ignore) $newRole = Enable-AzureADDirectoryRole -RoleTemplateId "fdd7a751-b60b-444a-984c-02652fe8fa1c" # Get the newly added role - replace the role template Id as per need $newRole = Get-AzureADDirectoryRole -Filter "roleTemplateId eq 'fdd7a751-b60b-444a-984c-02652fe8fa1c'" # Add the user to this role - copy the object Id from the output of the above command and use below Add-AzureADDirectoryRoleMember -ObjectId $newRole.ObjectId -RefObjectId $roleMember.ObjectId Limitations There are a few limitations that we are looking to address soon: The admin role is designed mainly for Office 365 groups and cannot manage other group types like distribution groups, mail-enabled security groups or shared mailboxes. This role cannot be used to update a Group’s email address or modify external mail or mail delivery options in the Microsoft 365 admin center. You cannot use Exchange PowerShell cmdlets to manage Office 365 Groups. A Groups admin cannot manage audit logs, access reports, or guest settings. We would love to hear your feedback or suggestions. Leave a comment here or reach out to us on user voice.
