Greetings!

I have created a MS Form in which the responses are forwarded to email using the following Discussion (https://techcommunity.microsoft.com/t5/microsoft-forms/microsoft-forms-help-with-forwarding/m-p/1825857). While the look is different that the current version of Power Automate, the steps walked me through how to complete this task. Purpose and flow are outlined below.

Function: Out Team Members submit Content and a Photo to add to social mediaFlowThe Form is completed by the Team MemberAn email is generated with the post informationThe email is forwarded onto the Admin who has access to our social media accountsAdmin can then copy and paste the post content, download the pic, and to add to social media

The Form Submission and email work as designed. However, the photo that is forwarded is not an image file type that by photo viewer. Attached are screenshots of the Form settings and the reply that the user receives in their Inbox.

Any assistance would be HUGELY helpful! Thanks in advance.

​Greetings! I have created a MS Form in which the responses are forwarded to email using the following Discussion (https://techcommunity.microsoft.com/t5/microsoft-forms/microsoft-forms-help-with-forwarding/m-p/1825857). While the look is different that the current version of Power Automate, the steps walked me through how to complete this task. Purpose and flow are outlined below. Function: Out Team Members submit Content and a Photo to add to social mediaFlowThe Form is completed by the Team MemberAn email is generated with the post informationThe email is forwarded onto the Admin who has access to our social media accountsAdmin can then copy and paste the post content, download the pic, and to add to social media The Form Submission and email work as designed. However, the photo that is forwarded is not an image file type that by photo viewer. Attached are screenshots of the Form settings and the reply that the user receives in their Inbox. Any assistance would be HUGELY helpful! Thanks in advance.  Read More

​

In this post, we’ll cover recommendations on how to gather insights from the SharePoint Files dataset in the Microsoft Graph Data Connect (MGDC). If you’re not familiar with MGDC for SharePoint, start with https://aka.ms/SharePointData.

1. DO – Follow the process for other MGDC datasets

The SharePoint Files dataset in MGDC delivers the largest results, reaching hundreds of millions and even billions of rows for the largest tenants. That would be one row for each document (item in a document library) in SharePoint or OneDrive.

Even though it’s typically very large, this dataset follows the same MGDC process as other datasets. From a pipeline and data source standpoint, it’s just another dataset and it uses the same procedure. There is also support for sampling, filtering, deltas and history of the last 21 days. For details, see MGDC for SharePoint FAQ: Dataset types and features.

2. DO NOT – Load hundreds of millions of rows in Power BI

In general, when using MGDC, you can pull the resulting datasets from Power BI to create your own analytics. Power BI is an amazing tool that will make it easy to pull lots of data and do all sorts of aggregations and summarizations, showing them as reports, charts and dashboards. Power BI can easily pull that data, which is basically a set of folders with JSON files inside.

However, if your tenant ends up outputting tens of millions of rows or more, it is possible that Power BI won’t be able to handle that large dataset. From my experience, Power BI Desktop running on a fast PC with 32GB of RAM can typically handle a few million rows of data. If you have more than that, which is common for the Files dataset, you will need to do some preparation work outside Power BI.

We will discuss a few of these options below. 

3. DO – Create a summarized table in ADLS Gen2

If you have too many rows to load into Power BI, you could run a Synapse pipeline to do some data aggregation before pulling the data into Power BI.

For instance, you could use a Synapse data flow to summarize the Files Dataset by “Extension”, calculating the number of sites, files, and bytes for each file extension and pulling that summary into Power BI. This will require some additional work in Synapse, and you will have less flexibility on how to pivot the data once you are in Power BI.

Here are the steps to follow, using a summary by file extension as an example:

Go to the Azure portal and create a new Synapse instance.
In Synapse, create a new data flow. Make sure to enable “data flow debug”.
Add a source to your data flow, pointing to the existing ADLS Gen2 storage, using JSON as the format
Use an aggregate transformation to summarize the data as needed
In the aggregate settings, for the group by column, choose extension
In the aggregate settings, for the aggregates, choose these 3 columns:

SiteCount – countAllDistinct(SiteId)
FileCount – count()
TotalSizeGB – round(sum(SizeInBytes)/1024/1024/1024,2)

Use the “data preview” tab to make sure things are working as expected (see picture below).
Add a sink to write the summarized data back to a new JSON file in ADLS Gen2.
Trigger the pipeline to execute the data flow and summarize the data.
Pull the summarized data into Power BI.

4, DO – Create a summarized table using a Synapse Notebook with PySpark

You can use Azure Synapse Analytics with a notebook to summarize a JSON-file-based dataset in ADLS Gen2.

Here’s a step-by-step guide to help you get started:

Go to the Azure portal and create a new Synapse workspace if you don’t already have one.
Make sure the storage account is configured to allow access by the Synapse workspace. In the Access Control section of the storage account configuration, you can add a role to the app that represents the synapse workspace.
In Synapse Studio, create a new notebook. 
Add some code to the notebook. Use PySpark to read the JSON file from ADLS Gen2, perform the necessary summarization operations (for example, group by a field and calculate the sum of another field) and write the summarized data back to ADLS Gen2.
Here’s an example:

from pyspark.sql import SparkSession
from pyspark.sql.functions import count, countDistinct, sum

spark = SparkSession.builder.appName(“SummarizeJSON”).getOrCreate()

# Read the file as text and parse each line as JSON
input_json_path = “abfss://<container>@<account>.blob.core.windows.net/<filespath>/<filename.json>”
rdd = spark.sparkContext.textFile(input_json_path)
df = spark.read.json(rdd)

# Group by Extension and aggregate
summary_df = df.groupBy(“Extension”).agg(
count(“*”).alias(“FileCount”),
countDistinct(“SiteId”).alias(“SiteCount”),
sum(“SizeInBytes”).alias(“TotalBytes”)
)

# Write the output
output_json_path = “abfss://<container>@<account>.dfs.core.windows.net/<extensionspath>”
summary_df.write.mode(“overwrite”).json(output_json_path)

Execute the cell in your notebook to perform the summarization.
Use the new summarized data when loading into Power BI.

5. DO – Load the data into a SQL Server database

For large datasets, you might also want to move the entire dataset from the folder with JSON files into tables in a SQL Server.  If your dataset is larger than 100GB, this could become expensive, and you would need to consider using indexes to help with your query. Columnstore indexes might be particularly useful for analytical queries that end up reading the entire table.

In Azure Synapse, you can use a “Copy Data” task where the source is your Azure Data Lake Storage Gen2 (ADLSGen2) and the destination (called the sink) is a table in SQL. You could also use the “Data Flow” task shown previously to transform the data and sink to SQL.

Moving to SQL will typically also require you to flatten the dataset, projecting nested objects. That means that objects inside objects must be represented as a flat list of properties. For instance, instead of having an “Author” object with two properties inside (“Email” and “Name”), you get two columns (“Author.Email” and “Author.Name”). In the Files Dataset, you will need to flatten the “Sensitivity Label Info”, the “Author” and the “Modified By” columns.

Note that you must first land the Microsoft Graph Data Connect dataset in an Azure Storage account before you can transform it and/or move it to SQL Server.

After the data is available in SQL Server, use the Power BI option to get data using a SQL query. Here is an example of a SQL query to summarize the data in the Files table by extension:

SELECT
Extension,
COUNT(*) AS FileCount,
COUNT(DISTINCT SiteId) AS SiteCount,
SUM(SizeInBytes) AS TotalBytes
FROM Files
GROUP BY Extension

If performance is more important than absolute accuracy, you might want to help SQL by using approximate distinct counts. This delivers faster results and guarantees up to a 2% error rate within a 97% probability. Here is an example:

SELECT
Extension,
COUNT(*) AS FileCount,
APPROX_COUNT_DISTINCT(SiteId) AS SiteCount,
SUM(SizeInBytes) AS TotalBytes
FROM Files
GROUP BY Extension

6. DO – Filter data

Instead of reducing the size of the data by summarizing, you can also filter the data in the Files dataset. That could be done by filtering the dataset for a specific site or possibly looking only at files with a specific author. You can use any of the methods described here (Synapse data flow, Synapse notebook or SQL Server query) to perform this filtering.

7. DO – Join the Files dataset with the Sites dataset

It might also be useful to join the Files dataset with the Sites dataset, so you can do specific aggregations or filtering. For instance, you could look into how the Files are distributed across the different types of SharePoint site using the Template or Template Id.

Here is an example using a SQL query:

SELECT
S.RootWebTemplate AS Template,
COUNT(*) AS FileCount,
COUNT(DISTINCT F.SiteId) AS SiteCount,
SUM(F.SizeInBytes) AS TotalBytes
FROM Files AS F
LEFT OUTER JOIN Sites AS S ON F.SiteId = S.Id
GROUP BY 1

Here is an example where we first calculate a summary of files per site, then do the join. This eliminates the need to use a COUNT DISTINCT:

SELECT
S.RootWebTemplate AS Template,
COUNT(*) AS TotalSites,
SUM(G.FilesPerSite) AS TotalFiles,
SUM(G.BytesPerSite) AS TotalBytes
FROM
(SELECT
F.SiteId,
COUNT(*) AS FilesPerSite,
SUM(F.SizeInBytes) AS BytesPerSite
FROM Files AS F
GROUP BY 1) AS G
LEFT OUTER JOIN Sites AS S ON G.SiteId = S.Id
GROUP BY 1

8, DO NOT – Join Files with Permissions on ItemId

You should be very careful when you attempt to join the Files dataset with the Permissions dataset. These are typically huge datasets and the way they should be joined is a bit complicated. You definitely do not want to join them by Item Id, since not every permission has an ItemId (it could be a permission on a Site, Library or Folder) and not every file has an associated permission (again, it could be a permission declared further up in the hierarchy).

If you must find the permissions for a given ItemId, the correct way to do it is by ScopeId. I suggest that you first filter the Files dataset for a specific ItemId and then join that with the Permissions dataset using the ScopeId. Note that a single item might have multiple permissions (with different roles, for instance) and these permissions might be granted for different item types.

Here is an example of a SQL query that shows the permissions for a given file, identified by a SiteId and an ItemId. It is very important to filter the output, otherwise the query might return billions of rows and take a very long time to process.

SELECT
F.SiteId,
F.ItemId,
F.ScopeId,
F.SiteUrl,
F.DirName,
F.FileName,
F.Extension,
P.ItemType,
P.ItemURL,
P.RoleDefinition,
P.LinkId,
P.LinkScope
FROM Files AS F
LEFT OUTER JOIN Permissions AS P
ON F.SiteId = P.SiteId AND F.ScopeId = P.ScopeId
WHERE F.SiteId = ‘ FCBDFC28-9335-4666-A852-6B1C1E7EC165’
AND F.ItemId = ‘ 647DCA3A-A3B8-4DBA-B1E8-6000389E696A’

To understand more about permission scopes, see MGDC for SharePoint FAQ: What is in the Permissions dataset?

9, Conclusion

I hope this clarifies what you can do with the Files dataset in MGDC for SharePoint. Let us know in the comments if you have other suggestions on how to get more out of the Files dataset.

For further details about the schema of all SharePoint datasets in MGDC, including SharePoint Sites and SharePoint File Actions, see https://aka.ms/SharePointDatasets.

​Microsoft Tech Community – Latest Blogs –Read More 

Hello everyone,

Is there a way to change the color of the table borders? Maybe by adding CSS styles to the page? I’m not sure if it’s possible, and if so, I don’t know how to do it either.

Thank you in advance, 

​Hello everyone, Is there a way to change the color of the table borders? Maybe by adding CSS styles to the page? I’m not sure if it’s possible, and if so, I don’t know how to do it either.  Thank you in advance,    Read More

​

In the era of digital transformation, businesses cannot afford downtime or data loss. High availability is crucial for maintaining business continuity and ensuring that applications are always accessible to users. Azure Database for MySQL – Flexible Server is designed with high availability (HA) as a fundamental feature, providing robust solutions to keep your databases operational even in the face of unexpected failures, but how do you ensure you’re getting the most out of it? This blog post delves into some of the effective monitoring and alerting mechanisms that are crucial for maintaining high availability.

High Availability in Azure Database for MySQL – Flexible Server

High Availability (HA) in Azure Database for MySQL – Flexible Server is designed to ensure uninterrupted database operation, even in the event of hardware or infrastructure failures in the availability zone. When high availability is configured, the flexible server automatically provisions and manages a standby replica in an alternate zone. The data and log files are hosted in zone-redundant storage (ZRS). The standby server reads and replay the log files continuously from the primary server’s storage account, which is protected by storage-level replication. If there’s a failover:

The standby replica is activated.
The binary log files of the primary server continue to apply to the standby server to bring it online to the last committed transaction on the primary.

With a Zone-redundant HA architecture, the guaranteed SLA is 99.99% of uptime and failover time is typically between 60-120 seconds.

Factors that impact High Availability

Lack of primary keys on tables – Azure Database for MySQL – Flexible Server allows configuring high availability with automatic failover. If there is a failure detected in primary, the standby replica in a HA configuration takes over the role of the primary to ensure business continuity. The failover time is typically between 60-120 seconds but in the absence of primary keys the failover time may get adversely affected. Azure Database for MySQL – Flexible Server leverages logical MySQL replication to replicate changes to the standby server. Absence of primary keys, increases the overhead on replication process, potentially causing replication lag or delays in applying changes on the standby server, thereby impacting the failover time or sometimes even resulting HA functionality to get impaired.
Storage IOPS – Storage IOPS have a significant impact on failover time in high availability mode in Azure Database for MySQL – Flexible Server. High IOPS contribute to faster data synchronization between the primary and standby servers, reduce transaction processing time, and ensure that the system can handle the increased I/O load during failover. If you have pre-provisioned IOPS and if it reaches its threshold value, it can adversely impact the replication lag and failover time. It is ideally recommended to switch to Auto scale of IOPS configuration for an optimized failover time in a HA configuration. You can learn more about Auto scale of IOPS here.

What is Azure Advisor?

Azure Advisor is a free, personalized cloud consultant service provided by Microsoft Azure. It analyses your Azure resources and usage telemetry to provide recommendations that help you improve the cost, security, performance, reliability, and operational efficiency of your cloud environment. By leveraging these insights, you can make data-driven decisions to enhance your Azure Database for MySQL – Flexible Server.

Leveraging Azure Advisor Alerts for High Availability

Azure Advisor monitors your MySQL – Flexible Server configurations and usage patterns. It identifies potential risks and provides alerts to help you address them before they impact your database availability.

You can access Advisors via the Azure portal by navigating to Settings-> Advisor recommendations.

Actionable Recommendations

Each alert from Azure Advisor comes with detailed recommendations on how to resolve the identified issues. These recommendations are customized to your specific environment, ensuring that they are relevant and actionable. For high availability, recommendations might include an Advisor highlighting:

The lack of primary key on the table.
High IO usage.

An example of an active advisor notification appearing in Azure portal is shown in the following screenshot:

Each advisor notification comes with a recommended action for the end user. Acting on these recommendations ensures a healthy HA configuration.

Automated Alerts and Notifications

It is always conceivable that advisor recommendations might go unnoticed, resulting in a lack of prompt action to the recommendations shared as part of the advisor notification. Therefore, Azure Advisor also integrates with Azure Monitor to provide automated alerts and notifications. You can set up alert rules to receive notifications via email, SMS, or through other channels whenever an issue is detected, and Azure advisor notification is triggered. This ensures you are promptly informed of any potential problems affecting high availability, allowing for quick action. To configure alerts on the new advisor recommendations on your Azure resource, follow the detailed steps in the article Create Azure Advisor alerts on new recommendations by using the Azure portal.

A sample alert generated appears in the following screenshot:

Note – You can set up alerts to be notified when you have a new Advisor recommendation on one of your resources. These alerts can notify you through email or text message. They can also be used to integrate with your existing systems through a webhook.

Conclusion

Monitoring the HA health in Azure Database for MySQL – Flexible Server is vital for ensuring continuous availability and enhancing disaster recovery preparedness. By using Azure Advisors and Azure Monitor, you can detect potential issues with your HA configuration beforehand, which ultimately contributes to a more reliable and resilient database environment.

If you have any questions or tips on using Azure Advisor with Azure Database for MySQL – Flexible Server, please share them in the Comments below. Happy monitoring!

​Microsoft Tech Community – Latest Blogs –Read More