Microsoft Mesh is a new service that allows your enterprise and your social media to go far together, allowing users and customers to interconnect using a web-enabled mesh. This new internet-based service combines the advantages of social networking and collaboration together with business tools like enterprise mobility management, collaboration, and task management for a really multi-dimensional experience on the internet. Microsoft Mesh also enables groups to work together not just inside the scope of a project but also when creating tasks shared. It follows that group real-time and productivity tasks can be handled in exactly the same fashion across multiple network devices.
Multi-user capabilities extend beyond networking. Microsoft Mesh offers rich editing and publishing experiences for e-commerce, publishing, collaboration, and publishing of all documents in addition to data. A multi-user network can include anywhere from 1 individual to several hundred consumers based on the size of your network. Since Microsoft Mesh is cross platform and cross apparatus capable, it delivers a special opportunity to leverage the capabilities of numerous devices and browsers across your corporate network without any additional investments in software or connection.
For companies looking for a new way to connect with their clients and strengthen their business ties, the usage of Microsoft Mesh is a clear way for moving your company ahead. Employing this cross-platform social network you can achieve a highly targeted audience over several devices while at the exact same time increasing your revenue. The price and complexity of integrating this system in your company should be no problem at all, letting you construct your organization and reach your goals easily. Mesh is a powerful tool and needs to be leveraged for the current generation of mobile computing.
This article is contributed. See the original author and article here.
On September 21, 2021, VMware disclosed that its vCenter Server is affected by an arbitrary file upload vulnerability—CVE-2021-22005—in the Analytics service. A malicious cyber actor with network access to port 443 can exploit this vulnerability to execute code on vCenter Server.
On September 24, 2021, VMware confirmed reports that CVE-2021-22005 is being exploited in the wild. Security researchers are also reporting mass scanning for vulnerable vCenter Servers and publicly available exploit code. Due to the availability of exploit code, CISA expects widespread exploitation of this vulnerability.
To mitigate CVE-2021-22005, CISA strongly urges critical infrastructure entities and other organizations with affected vCenter Server versions to take the following actions.
This article is contributed. See the original author and article here.
This post was authored by Stacia Varga, business intelligence practitioner, author, mentor, and classroom instructor.
As organizations adopt cloud data lakehouses to deliver the best of breed capabilities of data warehouses and data lakes, non-technical users such as data analysts don’t always have an easy way to access the data lakehouse for exploratory analysis. On the other hand, Databricks SQL enables quick access to data insights by enabling BI/SQL workloads in the lakehouse. For those organizations having a well-established Microsoft Power BI center of excellence, Databricks SQL also integrates with Power BI to support queries of the most complete and recent data in the data lake. Before looking at the integration with Power BI, let’s look at the capabilities of Databricks SQL.
Databricks SQL allows you to easily apply your existing SQL skills to big data analysis without learning a new language. You can perform exploratory data analysis using familiar SQL query constructs, which you can then use as the basis for common types of visualizations that enable multiple perspectives of your data. To share the results of your analysis, you can combine these visualizations into a simple dashboard. This dashboard can be further enhanced with text annotations and filters and scheduled to keep the data up-to-date.
For my first experience with Databricks SQL, my goal was to evaluate how quickly and easily these tasks can be performed by a data analyst. I have over twenty years of experience implementing and providing education on data warehousing and business intelligence tools, primarily using the Microsoft data platform. Furthermore, I wanted to determine where Databricks SQL fits into a data analytics strategy.
For this project, the data was prepared in advance for me so that my focus could stay entirely on the data consumer perspective. The purpose of this post is to explain the process I used to produce the NYC Taxi Trips dashboard, shown in Figure 1, and share my observations about using Databricks SQL.
Figure 1 NYC Taxi Trips dashboard
This dashboard consists of multiple visualization widgets based on the month, borough, and measure selected in the dashboard level filters widget at the top of the screen. It uses source data derived from the NYC taxi data set, an open-source big data set of taxi trip records containing trip dates and times, pick-up and drop-off locations, fares, tips, tolls, and payment types. The goals defined for this dashboard were to compare a selected measure across boroughs, provide a variety of time-series comparisons of that measure for the selected borough, and show drop-off locations within that borough on a map.
There are several steps required to produce the NYC Taxi Trips dashboard:
Data preparation. The raw data from the NYC taxi data set must be loaded into tables and then transformed, aggregated, and loaded into Databricks SQL tables.
Query development. A query editor is used to develop queries and view execution results in table form. Some queries are used for data visualizations in the dashboard, while others are used as query parameters to filter the query results by month, borough, and measure.
Visualization creation. A visualization is added to and saved with a query in the query editor. A query can have multiple visualizations.
Dashboard design. After creating a dashboard, visualization and textbox widgets are added and arranged as desired on a design surface. If a visualization is associated with a query containing query parameters, the parameter can either be added as a dashboard level filter or restricted to the widget.
After the data preparation process was complete, Databricks SQL made it easy to try out an idea, see the results in a dashboard quickly, and fine-tune the query and visualization incrementally until I achieved the desired result. Let’s take a closer look at each of these steps.
Data preparation
As I mentioned above, the NYC taxi data set was used for this project and was prepared for me by my data engineer, John. The primary dataset we used is accessible on all Databricks workspaces as raw CSV files through a folder called databricks-datasets. However, it did not have borough or neighborhood data, which I thought would be important, so we also pulled in that data as well. Think of these datasets as belonging to the Scheduled Ingest process shown on the left-hand side of the architecture diagram shown in Figure 2.
Figure 2 Delta Lake architecture
John ingested the datasets into Delta Lake tables and transformed them into one curated Delta Lake table (gallerynyctaxi.gold_summarystats) that I could then consume. To do this, he created a Databricks notebook, which is used for data engineering and data science. We had to collaborate on a couple of changes to the data, which worked out well because I could go into the notebook and watch as John made changes in real-time. I was also amazed at how fast Azure Databricks was able to reprocess the data after these changes.
Before explaining how I developed the queries, I want to take a moment to describe a Delta Lake table since this feature was also new to me. A Delta Lake table is an open table format that enables building a lakehouse architecture on top of my Azure Data Lake. At its core, a Delta Lake table is a set of parquet files, but it has a transaction log and features like ACID transactions that make it look and feel like a data warehouse table in Databricks SQL.
Query development
The process to develop queries for the dashboard visualizations is straightforward. You connect to a SQL endpoint, choose a database, and you’re set. You can use the schema browser to review the table structures and then write SELECT statements to explore the table contents. The user experience here is much like working in SQL Server Management Studio, except that you’re working in a browser instead of an application interface.
Query results are returned quickly and are saved with your query, making it easy to review your work later without re-executing the query. Another handy feature is the ability to revert to the previously saved version after you edit a query. The advantage here is that you can work quickly with your big data set to spend more time analyzing it instead of waiting for your queries to execute.
First iteration. For this project, the first iteration of query development focused on eight static queries to get the query results shaped correctly. These queries are similarly structured and represent a common theme in data analysis by grouping, aggregating, filtering, and sorting the data. Here’s an example of a query in the first iteration:
SELECT
dropoff_date,
dropoff_hour,
count(1) AS measure
FROM
gallerynyctaxi.gold_summarystats
WHERE
borough_dropoff = 'Manhattan'
AND date_part('MONTHS', dropoff_date) = 5
GROUP BY
dropoff_date,
dropoff_hour
ORDER BY
dropoff_date,
dropoff_hour
Use LIMIT instead of TOP. One point to note concerning the query that returns the top 3 zones in the selected borough, associated with the visualization shown in Figure 3. If you come from a Transact-SQL background, you would generally use the TOP and ORDER BY clauses in this type of query. However, in Databricks SQL, you use the LIMIT clause in combination with ORDER BY instead, like this:
SELECT
borough_dropoff, zone_dropoff,
COUNT(1) AS measure
from gallerynyctaxi.gold_boroughs
WHERE borough_dropoff = 'Manhattan'
AND date_part('MONTHS', dropoff_date) = 5
GROUP BY borough_dropoff, zone_dropoff
ORDER BY measure desc
LIMIT 3
Figure 3 Top 3 zones for Manhattan for May based on the trip count
Visualizations were created for each query and added to a dashboard during the first iteration to determine the best way to present the query results and start thinking about how the dashboard could be more dynamic. Details about creating visualizations and the dashboard are provided later in this post.
Parameterization. The tight linkage between a query, a visualization, and a dashboard allows you to move from idea to actualization smoothly and rapidly. For this project, the initial idea was to use parameterization to facilitate the analysis of individual boroughs. Later, as we added more data to the tables, we decided also to add parameters for both the month of year and the measure to be displayed in the visualizations.
A parameter value can be set by typing in a value or selecting a value from a dropdown list. If you choose to use a dropdown list, you can provide a static list of values or reference a query. Of course, the most efficient way to manage parameters across multiple queries is to establish a query for each parameter. Furthermore, using a query to populate parameter values allows the query to adapt to the current contents of the database dynamically. It’s important to make sure that the query contains only a single column of values.
The exception in this project was the list of measure names which is a static list that we placed into a query to facilitate reuse across multiple queries. That way, if any change would be needed later, only this query would require editing:
SELECT measure_name
FROM
(
SELECT 'Trip Distance' AS measure_name
UNION
SELECT 'Total Amount' AS measure_name
UNION
SELECT 'Fare Amount' AS measure_name
UNION
SELECT 'Tip Amount' AS measure_name
UNION
SELECT 'Tolls Amount' AS measure_name
) AS t
ORDER BY measure_name
The query for a drop-down list must exist before a query parameter is configured. You create this query in the query editor, just like any other query, and assign it a name.
Second iteration. The second iteration of query development incorporated the addition of the three query parameters into most dashboard queries. In some cases, only two parameters were required because a query either focused on all boroughs in a given month, as shown in Figure 4, or on all months for a given borough, as shown in Figure 5.
Figure 4 All boroughs for the selected and previous month
Figure 5 All months for the selected borough
Adding a query parameter is a two-step process. First, you add the query parameter to the query, and then you configure its values. To add the query parameter to the query, you provide a name for it and enclose that name in double brackets. For example, to create a query parameter named Measure, you insert {{Measure}} into the query. You can use the query parameter in conditional logic operations in the SELECT clause or the WHERE clause, as shown here:
SELECT
dropoff_date,
dropoff_hour,
CASE
WHEN '{{Measure}}' = 'Trip Count' THEN COUNT(1)
WHEN '{{Measure}}' = 'Trip Distance' THEN SUM(trip_distance)
WHEN '{{Measure}}' = 'Total Amount' THEN SUM(total_amount)
WHEN '{{Measure}}' = 'Fare Amount' THEN SUM(fare_amount)
WHEN '{{Measure}}' = 'Tip Amount' THEN SUM(tip_amount)
WHEN '{{Measure}}' = 'Tolls Amount' THEN SUM(tolls_amount)
ELSE 0 END AS measure
FROM gallerynyctaxi.gold_summarystats
WHERE
borough_dropoff = '{{borough}}'
AND date_part('MONTHS', dropoff_date) = {{Month of Year}}
GROUP BY dropoff_date, dropoff_hour
Notice that when a query-based parameter value is text, like {{Measure}}, single quotes must also enclose it, whereas an integer query-based parameter, like {{Month of Year}}, does not use quotes. On the other hand, if a parameter value can be entered by the user (and not based on a query), and you specify the type as Text, you do not need to enclose the query parameter in single quotes.
Query parameters. The addition of the query parameter into the query triggers the addition of a widget, shown in Figure 6, above the query results table in the query editor. Click the gear icon next to a parameter widget to configure its type. You can choose from Text, Number, Date, Date and Time, Date and Time (with Seconds), Dropdown List, and Query Based Dropdown List. If you choose either of the latter two types, you have the option to allow the user to select multiple values from the list.
Figure 6 Query parameter widget
When choosing the Query Based Dropdown List type, you select the query to associate with the parameter. When you click in the box to select a query, a set of available queries is presented for selection, but you might not see the query you need in this list. If that’s the case, just type a few letters of the query name, and the list is filtered based on this search.
After you configure the query parameter, you’re ready to use it. However, you must supply a value in the parameter widget before you can execute the query.
Visualization creation
Once a query has been executed successfully, you can immediately add one or more visualizations in the query editor. The visualizations are accessible in the results section of the editor as an additional tab and saved with the query, just like the table results, as shown in Figure 7. Whenever you change the query or query parameter values and re-execute the query, the table and visualization results are updated so you can see the effect of changes right away without changing tools.
Figure 7 Visualization results in the query editor
Databricks SQL provides various visualization types, ranging from the typical types, such as bar and line charts, to other visualization types less commonly encountered, such as funnel or sunburst charts. Because the goal of this project was to facilitate comparisons and to show trip activity using geolocation data, we decided to use the following five types of visualizations:
Bar chart. A bar chart is a helpful visualization for making comparisons, whether across a category, such as borough, or across time, such as hours of the day. With the bar chart visualization in Databricks SQL, you can choose vertical bars (also known as a column chart in other tools), horizontal bars, or add a series (to create a clustered column chart).
There are three bar charts in the dashboard shown in Figure 1. The Current vs Previous Month by Borough chart in Figure 4 compares boroughs for the selected statistic, Total Amount in this case. It allows the viewer to see how the measure varies across boroughs and from the selected month of May to the previous month of April. The Selected Statistic by Hour by Payment Type in Figure 7 is similarly structured but compares cash versus credit cards as payments over hours of the day.
The Top 3 Zones chart in Figure 3 uses horizontal bars to emphasize a comparison of the zones within a borough having the highest values for the selected statistic.
Counter. A counter can simply display a single value or a key performance indicator (KPI) value compared to a target value. In the latter case, if the KPI value is greater than the target, the value displays using a green font. A KPI value lower than the target displays using a red font. There is no option to format these font colors differently.
In the dashboard, the Selected Borough visualization, shown in Figure 8, is a counter that displays the value for the selected statistic, Total Amount, for the current month of May for Manhattan. The green color indicates this value is greater than the previous month of April, shown in parentheses below the current month’s value.
Figure 8 Counter
Line chart. A line chart helps the viewer see how data changes shape over time, the category placed on the X-axis. Line charts can also enable a comparison of series across time by using different colored lines.
The dashboard makes use of two line charts. The first, in Figure 4, shows the selected borough’s statistics over 12 months, highlighting the peaks and valleys of taxi revenue over a year. The other line chart, shown in Figure 9, shows similar information for days of the week for the selected month, which in this case is May, and again emphasizing the variation in revenue patterns from day to day.
Figure 9 Line chart
Box plot. A box plot is an effective visualization used to present multiple summarized data points and helps analyze distribution across categories. Each box represents five summary statistics: minimum, first quartile, median (also known as second quartile), third quartile, and maximum.
We added Selected Statistic by Hour as a box plot (shown in Figure 10) to the dashboard to compare the summary values for the selected statistic across hours of the day for the selected borough and month. This type of visualization allows the viewer to easily see at which times of the day there is a greater range of values as compared to the median and see whether the data is distributed normally or skewed positively or negatively.
Figure 10 Box plot
Map. When you have geolocation data, it can be helpful to plot that data on a map to see trends by location. The Databricks SQL map also allows you to create groupings of data points to enable comparisons on the map.
The map (shown in Figure 11) in the dashboard shows clusters of trips by dropoff location within the selected borough. As you zoom in on an area of the map, you can see an increasing level of detail, both in the map itself as well as where the data points are plotted. The grouping in the map is defined as the number of trips, with most drop-offs at locations associated with a single trip. The data includes instances of two or three trips to a single location that are easily distinguishable from the single-trip dropoffs by using different colors.
Figure 11 Map
Each type of visualization has a set of properties that you can configure to produce a particular look and feel, including legend placement, colors, data label formatting, and support for both left and right Y axes. Some visualizations have properties unique to their type, such as bubble size properties for a bubble chart. As you make changes to a visualization’s property inside the Visualization Editor, you can see the effect of that change right away.
Creating a bar chart. Let’s take a closer look at the steps required to produce a bar chart, such as the one shown in Figure 6. After the query successfully executes, you click the Add Visualization button to open the Visualization Editor.
Your first step is to select a value in the Visualization Type dropdown list. Optionally, you can give the visualization a name. This name displays in the visualization tab in the query editor and is the name used by default when you add it to a dashboard. However, you can override this name in the dashboard later if you like.
Then configure the properties, beginning on the General tab. You select the specific type of chart you want to use in the Chart Type dropdown list. In this case, you use Bar, but your other choices include Line, Area, Pie, Heatmap, Scatter, Bubble, and Box. Then there’s a checkbox if you want to convert the bar chart to horizontal bars.
Next, you select the columns to display along the x- and y-axes, dropoff_hour and measure, respectively, in this chart. You can specify a Group By column optionally, which adds a series to the chart. Here you use payment_desc as the grouping column. The Legend Placement property defaults to a position to the right of the chart, which you can move only to the bottom, as we did for this chart, or hide it if you don’t want to display it at all. After the general properties are configured, the chart appears, as shown in Figure 12.
Figure 12 General page of the Visualization Editor
Configuring visualization properties. Although this chart conveys the correct information about the data at this stage, we thought we could improve the appearance of the dashboard in the following ways:
Allocate more screen space to the visualization by removing the axis titles and instead use the widget title in the dashboard to convey the meaning of the axes.
Display each label on the x-axis so the viewer doesn’t have any difficulty discerning the hour of the day associated with each column.
Apply a different color scheme to the series data, using green for cash and a darker blue for credit card.
To achieve the first two of these objectives, go to the X Axis tab and remove the default name, which by default is the name of the column placed on this axis. Then select Category in the Scale dropdown list to add the labels to each column. There are toggle switches on this page which by default sort the label values and display the labels on the axis. There are two other toggle switches here that are disabled by default, Reverse Order and Hide Axis.
Moving on to the Y Axis tab, all that is necessary is to remove the default name. Optional properties allow you to configure a minimum and maximum value for the y-axis, define a different scale type such as logarithmic, or set up a right y-axis.
For the third objective, go to the Colors tab. Here you use a color picker to override the colors automatically assigned to the visualization. The color picker offers a choice of twenty colors, or you can type in the hex color code if you need a specific color not shown. After editing the properties, click Save, and the visualization is ready to add to a dashboard.
Dashboard design
When a dashboard is created, it consists of a blank design surface. Here you add widgets to provide content for the dashboard. Databricks SQL supports two types of widgets—a visualization and a textbox.
You can start adding visualizations to the dashboard by clicking the Add Visualization button and then searching for the visualization’s query by name. You then choose which of the query’s visualizations to display, or you can even choose to display the query’s results table. As an alternative, there’s an Add to Dashboard command available in the query editor that allows you to link the current visualization to an existing dashboard that you search for by name.
You can also add textbox widgets to the dashboard to annotate the visualizations using basic Markdown syntax. You can even place a static image inside a textbox using Markdown’s image element.
Dashboard parameters. If a visualization’s query has parameters, you must decide how to display the parameters in the dashboard. For example, when adding the Selected Statistic by Hour by Payment Type chart to a dashboard, the Add Visualization Widget dialog box, shown in Figure 13, lists the parameters by name and shows the keyword used in the query for each parameter. The default value defined in the query editor is shown but is not editable here.
Figure 13 Add Visualization Widget
Notice that Value Source defaults to Dashboard, which means the parameter will be assigned at the dashboard level. Any other visualizations that you subsequently link to the dashboard parameter will use the same value. You can click the pencil icon to the right of the Value Source name to edit the parameter name to display in the dashboard or change the source. You can either set a static value that hides the parameter and prevents a user from changing it or set the source to the widget and require a user to set a parameter value separately for that visualization.
This ability to control whether parameters affect a single visualization only or multiple visualizations is conceptually like setting visual-level or page-level filters, respectively, in Power BI. However, in Power BI, you’re setting up the visuals filters, adding a filter, and deciding as you add it how the filter applies—visual, page, or report. Instead, in Databricks SQL, the query parameter is part of the visualization already. When you add the visualization to a dashboard, you’re deciding whether the control of that parameter is at the dashboard level or the widget level. And just because a parameter is defined at the dashboard level, it does not necessarily apply to every visualization contained in the dashboard. If you’re not careful with your parameter configuration, this capability has the potential to cause confusion. On the other hand, it does give you greater flexibility that might be useful in some scenarios.
Query filters. It’s also important to note that a query parameter is similar to, but not the same as, a query filter which is not being used in this dashboard project. If you were to use a query with a filter, you must enable the Use Dashboard Level Filters property in the dashboard to ensure the filter applies to all queries used by the dashboard.
Adjusting widget location and size. After adding multiple visualizations to the page, you can rearrange and resize the widgets using drag and drop. There is a limit to how small you can make a widget, affected in part by the widget’s content, so experimentation is required to determine the best sizing. Likewise, the width of any single widget is restricted to the width of the browser page.
As the dashboard designer, you can quickly return to the query editor for any visualization if you need to make some edits or simply want to review the construction of the query or its tabular results. Each visualization contains a menu button that includes a View Query command. This option is not available by default to other users with whom you share the dashboard, however. If you want others to open the visualization’s query editor, you also need to share its query, and you will need to do this query by query as there is no blanket sharing option for all queries linked to a dashboard.
Conclusion
After building and refining the NYC Taxi Data dashboard, my overall impression of Databricks SQL is favorable. It provides an excellent alternative option for exploring big data without the need to download and install dedicated tools or learn traditional big data languages. It’s not intended to be a replacement for your existing business intelligence tools. Instead, it fits into your existing architecture as a browser-based option available for ad hoc exploration of new datasets, as well as supporting tight integration for Power BI workloads. On the other hand, it’s certainly capable of supporting much more of your data analytics needs.
Databricks SQL fulfills its promise of being easy to use and delivers great performance. Queries are easy to create and execute, and results against big data sets are pretty fast. I could experiment with various visualization types and then add them to a dashboard with just a few more clicks. Then I could extend the usefulness of the dashboard by adding parameters to the queries to display the data from multiple perspectives. In addition to the native query and visualization tools, Databricks SQL provides support for all of your existing Power BI workloads. Setting up reliable connections to data lake tables is simple, and you can integrate your existing authentication solution.
Ready to get started with Databricks SQL? Just bring your SQL query skills. There’s no new language required!
This article is contributed. See the original author and article here.
Apple has released security updates to address vulnerabilities in multiple products. An attacker could exploit these vulnerabilities to take control of an affected system. These vulnerabilities have been detected in exploits in the wild.
CISA encourages users and administrators to review the Apple security page for iOS 12.5.5 and Security Update 2021-006 Catalina and apply the necessary updates as soon as possible.
This article is contributed. See the original author and article here.
Empowering everyone to collaborate as one business, everywhere.
Across industries, businesses are undergoing a once-in-a-generation shift in how, when, and where people work. This shift to flexible, hybrid work modelswith some workers on-site, others remoteis forcing many businesses to rethink how people digitally engage and collaborate with colleagues, partners, and customers in the flow of work.
We checked in with two companies reinventing how people collaborate, enabling a new era of work that engages people everywhere across the business. These leaders in their industriesinsurance and legendary music brandsshared how Microsoft Dynamics 365 and Microsoft Teams are helping them bring people, information, and processes togetheracross physical locations, departments, and time zones.
The “expert” approach to a collaborative sales culture
In the world of hybrid work, sales teams need access to experts across the organization to build proposals and service that fits the unique needs of each customer. Sales teams can now invite anyone across the company to collaborate on Dynamics 365 sales records right within the flow of a Teams chat or channel.
Insurance and advisory company Willis Towers Watson (WTW) needed to centralize sales data and processes into one platformin its case, across sales teams around the world. Formed by a merger in 2016, nearly 90 percent of client-facing staff are using Microsoft Dynamics 365 Sales to centralize all engagement into one platform and Power BI to add powerful analytics capabilities. The result: a boost in sales leads thanks to better visibility into data, an increase in actionable insights, and more personalized services.
WTW now wants to build even more structure around how it manages clients with a consistent set of tools to enable better collaboration across account managers and sellers and a full 360-degree view of clients. The company plans to tie its Microsoft Teams channels in closely with Dynamics 365 so that staff can store individual records in Dynamics 365 but have the option to edit them in Teams. By putting Microsoft Teams and Microsoft Dynamics 365 Sales in the hands of the whole company, they will enable anyone from sellers to service agents to share and collaborate from within Teams or Dynamics 365 Sales without switching apps.
“We tested this Teams functionality with some operational leaders recently, and it went really well,” says Amanda Duffield, Director, Corporate Risk and Broking, Great Britain, Willis Towers Watson. “It’s early, but staff understands the bigger-picture possibilities with collaboration from anywhere and how easy it is to connect another Microsoft product and maintain that continuity.”
Connecting conversations and customer experiences across the organization
Every day, employees at a typical organization carry out hundreds, if not thousands, of conversationssharing ideas, information, and insights that move business forward. In the new world of hybrid work, businesses need ways to bring conversations together, so that separate, disjointed work efforts can contribute toward the shared goals and visions. At one of the most beloved and recognized music brands, Dynamics 365 and Microsoft Teams are enabling cross-organizational collaboration to take center stage.
Gibson Brands, the world’s most iconic guitar brand, transitioned to Dynamics 365 Commerce, Dynamics 365 Finance, and Microsoft Teams to simplify internal processes and provide a more immersive experience for customers. The combination has improved communications and cross-functional collaboration within the company, helping to transform retail sales by providing a platform to meet customers everywhere they arethrough retail, direct sales, or a dealer network.
The result has broken down siloes between teams, from production to sales to operationsa level of transparency that lets everyone understand how each team impacts the customer experience.
Cheryl Morgan, Business Analyst at Gibson Brands, explains, “When we decided to go with Dynamics 365 and implement across the business, we were forced to start working together as a collaborative team. We’ve all learned so much about how production works, how that affects sales and how that affects our customers. Dynamics 365 creates the accountability between the different business units. Everybody is having to work together. It’s been a real eye-opener.”
When the Gibson workforce shifted to remote work during COVID-19 lockdowns, the seamless integration with Microsoft Teams ensured no one missed a beat. As Morgan added, “we would not have made it through COVID-19 had this not been a cloud-based environment. Everything I do is Dynamics 365 and Teams and Outlook, which has been pivotal.”
One business, everywhere. Create your own story
These examples are just a snapshot of organizations reinventing collaboration with the tightly integrated capabilities of Microsoft Teams and Dynamics 365. Catch up on the four essential capabilitiesbuilt into Dynamics 365 and Teamsto help everyone across your business work together from anywhere in a more seamless and natural way. Then, learn more ways to bring together people, data, and processes in the flow of work.
This article is contributed. See the original author and article here.
Microsoft commissioned the Enterprise Strategy Group (ESG) to conduct an economic value study to examine the potential cost savings and business benefits that enterprises can achieve from migrating on-premises workloads to Azure Database for MySQL. ESG is an independent strategy firm that provides market intelligence and actionable insights to the global IT community. Aviv Kaufmann, Senior Economic Validation Analyst and author of the report, completed a quantitative economic analysis of Azure Database for MySQL and determined that customers reported savings and benefits in the three categories: 1) elimination of costs, 2) operational savings, and 3) improved business agility.
ESG conducted a survey to evaluate businesses cloud infrastructure services spending trends and found that as of January 2021, 68% of businesses planned to spend more on cloud services as we continue into the second straight year of a pandemic remote – work environment. More specifically, ESG’s survey found that 85% of cloud users have accelerated efforts to move applications to the cloud based on recent events over the last six months. Two of the top motivating factors for organizations to digitally transform their businesses are to become more operationally efficient and to embrace the ever-evolving technology trends that are allowing users to collaborate in new ways. (ESG Research Report: 2021 Technology Spending Intentions Survey, January 19, 2021)
Managed services are one of the many options that businesses are choosing to become operationally efficient. Migrating and modernizing to the cloud is a cost-effective way to streamline application development and improve business agility. MySQL is one of the most popular open source relational databases that organizations choose to power their web applications. ESG’s analysis noted that:
“While many developers have built the expertise required to deploy, manage, and maintain MySQL databases to power their applications, the burden of managing, updating, tuning, protecting, and ensuring high availability for MySQL instances across multiple applications takes valuable cycles away from development of core applications.”
ESG’s economic validation study focuses on the quantitative and qualitative benefits that organizations can expect when migrating on-premises MySQL instances to the fully managed Azure Database for MySQL service.
Azure Database for MySQL eliminates cost, provides operational savings, and improves business agility
ESG interviewed three Azure Database for MySQL customers and found that they were able to significantly eliminate costs related to hardware, infrastructure, and hypervisors, and reduce their MySQL commercial subscriptions. These customers reported that the biggest benefits they’ve seen are from the operational savings achieved by DevOps teams leveraging Azure Database for MySQL. One customer noted that, “Now that we migrated to Microsoft Azure, I don’t need to worry about all the IT operational aspects, like backups and updates, and that’s a huge benefit for my team”.
ESG found that customers were excited to report the different ways that Azure Database for MySQL helped them transform their businesses by allowing them to achieve greater business agility in everyday practice. Some of the ways that DevOps and operations teams have been able to work more efficiently include providing faster time to value, accelerating migration and modernization, improving application performance, increasing flexibility and business agility, integrating Azure Services more quickly, and increasing development velocity.
ESG’s findings
As part of their analysis, ESG leveraged customer interviews, product pricing models, and public and industry knowledge of economics and technologies to create a three-year TCO/ROI model that compares the costs and benefits of migrating on-premises MySQL instances to Azure Database for MySQL. ESG’s model found that Azure Database for MySQL provided up to a 48% lower total cost over a three-year period, including a 93% reduction in the operational cost to deploy, manage, and maintain MySQL instances in an on-premises infrastructure. ESG reports that Azure Database for MySQL offloads a bulk of the work currently performed by developers, which further reduces the cost to administer, tune, secure, protect, and maintain the MySQL databases by up to 86%. This savings equates roughly to the productivity and development capacity of four full-time developers, creating an additional $15.5 million in revenue for the business.
ESG finds that as organizations continue to migrate and modernize to the cloud, they can expect to streamline operations while reducing operational costs and improving business agility with Azure Database for MySQL. Businesses are looking to Microsoft Azure to help digitally transform their applications with more efficient microservice architectures that can be dynamically hosted, scaled, protected, secured, and delivered by the many services provided by the Azure ecosystem. ESG recommends the fully managed Azure Database for MySQL service as a cost-effective solution for successfully increasing business agility, optimizing performance, and maximizing the productivity of developers and IT resources.
This article is contributed. See the original author and article here.
Cisco has released security updates to address vulnerabilities in multiple Cisco products. An attacker could exploit some of these vulnerabilities to take control of an affected system.
This article is contributed. See the original author and article here.
Summary
Immediate Actions You Can Take Now to Protect Against Conti Ransomware • Use multi-factor authentication. • Segment and segregate networks and functions. • Update your operating system and software.
Note: This Alert uses the MITRE Adversarial Tactics, Techniques, and Common Knowledge (ATT&CK®) framework, version 9. See the ATT&CK for Enterprise for all referenced threat actor tactics and techniques.
The Cybersecurity and Infrastructure Security Agency (CISA) and the Federal Bureau of Investigation (FBI) have observed the increased use of Conti ransomware in more than 400 attacks on U.S. and international organizations. In typical Conti ransomware attacks, malicious cyber actors steal files, encrypt servers and workstations, and demand a ransom payment.
To secure systems against Conti ransomware, CISA, FBI, and the National Security Agency (NSA) recommend implementing the mitigation measures described in this Advisory, which include requiring multi-factor authentication (MFA), implementing network segmentation, and keeping operating systems and software up to date.
Click here for indicators of compromise (IOCs) in STIX format.
Technical Details
While Conti is considered a ransomware-as-a-service (RaaS) model ransomware variant, there is variation in its structure that differentiates it from a typical affiliate model. It is likely that Conti developers pay the deployers of the ransomware a wage rather than a percentage of the proceeds used by affiliate cyber actors and receives a share of the proceeds from a successful attack.
Conti actors often gain initial access [TA0001] to networks through:
Spearphishing campaigns using tailored emails that contain malicious attachments [T1566.001] or malicious links [T1566.002];
Malicious Word attachments often contain embedded scripts that can be used to download or drop other malware—such as TrickBot and IcedID, and/or Cobalt Strike—to assist with lateral movement and later stages of the attack life cycle with the eventual goal of deploying Conti ransomware. [1],[2],[3]
Stolen or weak Remote Desktop Protocol (RDP) credentials [T1078].[4]
Phone calls;
Fake software promoted via search engine optimization;
Other malware distribution networks (e.g., ZLoader); and
Common vulnerabilities in external assets.
In the execution phase [TA0002], actors run a getuid payload before using a more aggressive payload to reduce the risk of triggering antivirus engines. CISA and FBI have observed Conti actors using Router Scan, a penetration testing tool, to maliciously scan for and brute force [T1110] routers, cameras, and network-attached storage devices with web interfaces. Additionally, actors use Kerberos attacks [T1558.003] to attempt to get the Admin hash to conduct brute force attacks.
Conti actors are known to exploit legitimate remote monitoring and management software and remote desktop software as backdoors to maintain persistence [TA0003] on victim networks.[5] The actors use tools already available on the victim network—and, as needed, add additional tools, such as Windows Sysinternals and Mimikatz—to obtain users’ hashes and clear-text credentials, which enable the actors to escalate privileges [TA0004] within a domain and perform other post-exploitation and lateral movement tasks [TA0008]. In some cases, the actors also use TrickBot malware to carry out post-exploitation tasks.
According to a recently leaked threat actor “playbook,” [6] Conti actors also exploit vulnerabilities in unpatched assets, such as the following, to escalate privileges [TA0004] and move laterally [TA0008] across a victim’s network:
2017 Microsoft Windows Server Message Block 1.0 server vulnerabilities; [7]
“PrintNightmare” vulnerability (CVE-2021-34527) in Windows Print spooler [8] service; and
“Zerologon” vulnerability (CVE-2020-1472) in Microsoft Active Directory Domain Controller systems.[9]
Artifacts leaked with the playbook identify four Cobalt Strike server Internet Protocol (IP) addresses Conti actors previously used to communicate with their command and control (C2) server.
162.244.80[.]235
85.93.88[.]165
185.141.63[.]120
82.118.21[.]1
CISA and FBI have observed Conti actors using different Cobalt Strike server IP addresses unique to different victims.
Conti actors often use the open-source Rclone command line program for data exfiltration [TA0010]. After the actors steal and encrypt the victim’s sensitive data [T1486], they employ a double extortion technique in which they demand the victim pay a ransom for the release of the encrypted data and threaten the victim with public release of the data if the ransom is not paid.
Conti ransomware can be delivered using TrickBot malware, which is known to use an email with an Excel sheet containing a malicious macro to deploy the malware.
Adversaries may leverage external-facing remote services to initially access and/or persist within a network. Remote services such as virtual private networks (VPNs), Citrix, and other access mechanisms allow users to connect to internal enterprise network resources from external locations. There are often remote service gateways that manage connections and credential authentication for these services. Services such as Windows Remote Management can also be used externally.
Conti ransomware can retrieve the ARP cache from the local system by using the GetIpNetTable() API call and check to ensure IP addresses it connects to are for local, non-internet systems.
Conti ransomware can use CreateIoCompletionPort(), PostQueuedCompletionStatus(), and GetQueuedCompletionPort() to rapidly encrypt files, excluding those with the extensions of .exe, .dll, and .lnk. It has used a different AES-256 encryption key per file with a bundled RAS-4096 public encryption key that is unique for each victim. Conti ransomware can use “Windows Restart Manager” to ensure files are unlocked and open for encryption.
Conti ransomware can delete Windows Volume Shadow Copies using vssadmin.
Mitigations
CISA, FBI, and NSA recommend that network defenders apply the following mitigations to reduce the risk of compromise by Conti ransomware attacks.
Use multi-factor authentication.
Implement network segmentation and filter traffic.
Implement and ensure robust network segmentation between networks and functions to reduce the spread of the ransomware. Define a demilitarized zone that eliminates unregulated communication between networks.
Filter network traffic to prohibit ingress and egress communications with known malicious IP addresses.
Enable strong spam filters to prevent phishing emails from reaching end users. Implement a user training program to discourage users from visiting malicious websites or opening malicious attachments. Filter emails containing executable files to prevent them from reaching end users.
Implement a URL blocklist and/or allowlist to prevent users from accessing malicious websites.
Scan for vulnerabilities and keep software updated.
Set antivirus/antimalware programs to conduct regular scans of network assets using up-to-date signatures.
Upgrade software and operating systems, applications, and firmware on network assets in a timely manner. Consider using a centralized patch management system.
Remove unnecessary applications and apply controls.
Remove any application not deemed necessary for day-to-day operations. Conti threat actors leverage legitimate applications—such as remote monitoring and management software and remote desktop software applications—to aid in the malicious exploitation of an organization’s enterprise.
Investigate any unauthorized software, particularly remote desktop or remote monitoring and management software.
Implement application allowlisting, which only allows systems to execute programs known and permitted by the organization’s security policy. Implement software restriction policies (SRPs) or other controls to prevent programs from executing from common ransomware locations, such as temporary folders supporting popular internet browsers or compression/decompression programs.
Implement execution prevention by disabling macro scripts from Microsoft Office files transmitted via email. Consider using Office Viewer software to open Microsoft Office files transmitted via email instead of full Microsoft Office suite applications.
See the joint Alert, Publicly Available Tools Seen in Cyber Incidents Worldwide—developed by CISA and the cybersecurity authorities of Australia, Canada, New Zealand, and the United Kingdom—for guidance on detection and protection against malicious use of publicly available tools.
Implement endpoint and detection response tools.
Endpoint and detection response tools allow a high degree of visibility into the security status of endpoints and can help effectively protect against malicious cyber actors.
Limit access to resources over the network, especially by restricting RDP.
After assessing risks, if RDP is deemed operationally necessary, restrict the originating sources and require multi-factor authentication.
Secure user accounts.
Regularly audit administrative user accounts and configure access controls under the principles of least privilege and separation of duties.
Regularly audit logs to ensure new accounts are legitimate users.
Use the Ransomware Response Checklist in case of infection.
If a ransomware incident occurs at your organization, CISA, FBI, and NSA recommend the following actions:
CISA, FBI, and NSA strongly discourage paying a ransom to criminal actors. Paying a ransom may embolden adversaries to target additional organizations, encourage other criminal actors to engage in the distribution of ransomware, and/or may fund illicit activities. Paying the ransom also does not guarantee that a victim’s files will be recovered.
Additional Resources
Free Cyber Hygiene Services
CISA offers a range of no-cost cyber hygiene services to help organizations assess, identify, and reduce their exposure to threats, including ransomware. By requesting these services, organizations of any size could find ways to reduce their risk and mitigate attack vectors.
StopRansomware.gov
The StopRansomware.gov webpage is an interagency resource that provides guidance on ransomware protection, detection, and response. This includes ransomware alerts, reports, and resources from CISA and other federal partners, including:
Rewards for Justice Reporting
The U.S. Department of State’s Rewards for Justice (RFJ) program offers a reward of up to $10 million for reports of foreign government malicious activity against U.S. critical infrastructure. See the RFJ website for more information and how to report information securely.
Contact Information
To report suspicious or criminal activity related to information found in this Joint Cybersecurity Advisory, contact your local FBI field office at www.fbi.gov/contact-us/field-offices, or the FBI’s 24/7 Cyber Watch (CyWatch) at (855) 292-3937 or by e-mail at CyWatch@fbi.gov. When available, please include the following information regarding the incident: date, time, and location of the incident; type of activity; number of people affected; type of equipment used for the activity; the name of the submitting company or organization; and a designated point of contact. If you have any further questions related to this Joint Cybersecurity Advisory, or to request incident response resources or technical assistance related to these threats, contact CISA at CISAServiceDesk@cisa.dhs.gov. For NSA client requirements or general cybersecurity inquiries, contact the NSA Cybersecurity Requirements Center at 410-854-4200 or Cybersecurity_Requests@nsa.gov.
This article is contributed. See the original author and article here.
Introduction
This article will show you how to use Azure Synapse (also works with Azure Data Factory) to build a pipeline that gets public data from YouTube using REST API’s and store it in parquet files in ADLS Gen2 and Azure Synapse Analytics. At the end, you should be able to connect to other social media platforms that support REST API’s.
In addition, we will have a look at different ways to analyze the data in Azure Synapse Analytics: using SQL Serverless, SQL Provisioned (SQL Pools) and Spark Clusters.
Considerations The YouTube REST API calls used in this exercise use API Keys as credentials. Using OAuth 2.0 is not possible because we’re using an automated pipeline that runs without a UI, required for user interaction and sign-in. Managing these credentials is not covered here – please refer to each social media platform API official documentation. The pipeline stores data in both ADLS Gen2 parquet files and Azure Synapse Analytics SQL Pool tables – this may or may not be a good implementation option. This decision is taken only to be able to demonstrate the two possibilities, between many – obviously, you should consider your own requirements.
What’s needed Azure Key Vault – to store the API Key; optional but strongly advised. Azure Synapse Analytics workspace – to build the pipeline (optionally Azure Data Factory can be used) SQL Serverless – no need to be provision. SQL Pool – as a provisioned SQL engine; one table and one procedure will be created here. Spark Cluster – as a provisioned Spark engine to run spark notebooks. YouTube API Key – to access the YouTube Rest API
1. Preparation
Azure key vault secret Create a new Azure key vault, or use an existing one, and create a new secret using: Name: YouTubeMicrosoftDemosProjectAPIKey Value: <YOUR-YOUTUBE-API-KEY>
Screenshots of getting the API Key value and creating the new secret:
Note: you need to grant the Azure Synapse Analytics workspace Managed Identity access to your Azure Key vault. Learn more here.
In this example, only “Get” and “List” are selected on the “Secret Permissions” list. Next, open the current version of the secret and store the “Secret Identifier” as it will be used later:
Linked service to the Storage Account Because we will be using the “Primary” Azure Data Lake Storage Gen2 storage account for the Azure Synapse Analytics workspace, specified when creating the workspace, the linked service is already created, and its name should be in this format: “<WORKSPACE-NAME>-WorkspaceDefaultStorage”. Note: You need to create a new linked service if you want to use a storage account other than the “Primary” or if you’re using Azure Data Factory.
Linked service to the SQL Pool Again, there’s no need to create a linked service to a SQL Pool in the same workspace because we will be using an activity that is unique to Azure Synapse Integrate pipelines: the “SQL pool stored procedure”. Note: If you are using Azure Data Factory, then you need to create a linked service to the SQL Pool where you want to store your data. Refer to the “ADF linked service to Azure Synapse Analytics” section in the Azure Synapse SQL Pools Auto DR article.
Create YouTube video statistics table and related stored procedure Create a new table in the SQL Pool you want to store the data. It will be used to store some fields from the REST API responses and well as the entire payload.
CREATE TABLE [dbo].[youtube_video_likes] (
ITEM_ID VARCHAR(100) NOT NULL,
VIDEO_ID VARCHAR(100) NOT NULL,
LIKES INT NOT NULL,
FULL_JSON_RESPONSE VARCHAR(MAX) NOT NULL,
TS_INSERT DATETIME NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN, HEAP)
GO
In addition, create a new stored procedure. This will be called to insert the data in the table.
CREATE PROC [dbo].[p_youtube_video_likes_insert] @p_item_id [VARCHAR](100), @p_video_id [varchar](100), @p_likes [INT], @p_full_json_response [VARCHAR](MAX) AS
BEGIN
INSERT INTO [dbo].[youtube_video_likes](item_id, video_id, likes, full_json_response, ts_insert)
SELECT @p_item_id, @p_video_id, @p_likes, @p_full_json_response, SYSDATETIME();
END
GO
2. Create Datasets
We will use the Copy activity to store the REST API responses in parquet files, so we need to create two datasets for the source and sink.
Dataset for source Create a file and upload it into your storage account. This file is a supporting file as the data we want to store is coming from the REST API calls and not from another file. The content is the simpler JSON snippet we can have, an empty JSON.
Filename
oneLinerEmpty.JSON
Content
{}
Then, create a new dataset for this file:
Data store
Azure Data Lake Storage Gen2
Format
JSON
Name
oneLinerEmptyJSON
Linked service
the linked service for your “Primary” storage account or another one
File path
use the browse button to locate and select your uploaded JSON file. In this example, “filesystem001” is the container name, “socialmedia/supportingfiles” is the folder, and “oneLinerEmpty.JSON” is the file name
Dataset for sink Create a new dataset that will be used to store the data in parquet format. This dataset will be parameterized because we will use it to create different files. Follow the same steps as above but select the Parquet format.
Name
parameterOutputParquet
Linked service
the linked service for your “Primary” storage account or another one
File path
leave empty as this will be parameterized
Import schema
None
Now it’s time to configure this dataset. In the Parameters tab, create 2 parameters as in this image:
P_FILE_PATH
String, no default value
P_FILE_NAME
String, no default value
In the Connection tab, we will use these parameters:
“filesystem001” is the name of the container: Replace accordingly. Now we are ready to start developing the pipeline.
3. Pipelines
Create an auxiliary pipeline The first pipeline is the one that uses the 2 datasets we created above and will only have a Copy activity. The objective of this pipeline is to receive a parameter and write that value in a parquet file. It will be called several times from the main pipeline, to store all the REST API responses.
Create a new pipeline, name it “Store Parameter in Parquet File” and create the following parameters, all with type String and no default value:
P_FILE_PATH
P_FILE_NAME
P_DATA
Add a Copy activity, name it “Store parameter P_DATA in Parquet” and configure the Source, Sink and Mapping tabs as shown in the following images.
Source dataset
the JSON file create in the preparation step
File path type
File path in dataset
Recursively
unchecked
Additional columns
create a new column “VALUE_COLUMN” and use the “Add dynamic content” of the VALUE field to add a reference to the parameter P_DATA (@pipeline().parameters.P_DATA).
The goal of this additional column is to create a new column with the content of the received parameter, as if it were read from the input file. This is a way to inject whatever values we want in columns of the Copy activity.
Sink dataset
parameterOutputParquet
P_FILE_PATH
@pipeline().parameters.P_FILE_PATH
P_FILE_NAME
@pipeline().parameters.P_FILE_NAME
Map complex values to string
checked
Click on the “+ New mapping” to add a new mapping row. Rename the default column name “Column_1” to “VALUE_COLUMN” and map it to the same name in the right side of the mapping.
This pipeline is finished and should look like this:
Create the main pipeline
The main pipeline has these steps:
Get YouTube API Key from the secret in Azure Key Vault
Get the playlist ID for a given channel ID
Get the list of videos for a given playlist ID
For each video, get video statistics
Store results in the SQL Pool
In addition, the responses of all 3 YouTube REST API calls will also be stored in parquet files. This will allow us later to look at the data using different analytical/processing engines.
The complete pipeline and parameters will look like this:
Start by creating a new pipeline, name it “YouTube REST API example with API Key” or whatever you want and add the following parameters and default values:
P_SOCIAL_MEDIA_PLATFORM
YouTube
P_YOUTUBE_CHANNEL_ID
<YOUR-YOUTUBE-API-KEY>
P_FILE_OUTPUT_PATH
socialmedia/runs/
Add 3 Web activities, link them as shown in the previous picture and configure with the following properties. Only the non-default and important to mention properties are listed.
The secure output option will hide the results from the API call, in this case it will hide the value we’re getting from the secret, as per the best practices. When we try to look at the result, it looks like this:
To access the value of the secret, we can now use this expression: activity(‘Get YouTube API Key from Key Vault’).output.value
Note: any developer can turn off the secure output option and see the value of the secret. This can happen in a development environment, where the sensitivity is not the same as in a production environment. Make sure that no sensitive information can be seen in a production environment, by including revision rules that validate that no pipelines can be promoted if proper settings for security are not met.
The secure input option will hide the values sent to this activity, in this case it will hide the API Key used in the URL. If we try to see the runtime input, all we’ll see is this:
We now have the playlist ID for a channel, so we can request a list of videos for that playlist.
This activity will store the REST API response in a parquet file in the storage account. The file name will be similar to “getPlaylistID-2021-04-23T13:13:19.4426957Z.parquet”.
Execute Pipeline 2
Name
Store Playlist Videos result in Parquet
Invoked pipeline
Store Parameter in Parquet File, or any other name you gave to the auxiliary pipeline
This activity will store the REST API response in a parquet file in the storage account. The file name will be similar to “getPlaylistVideos-2021-04-23T13:13:22.3769063Z.parquet”.
Now that we have a list of videos, we need to get the statistics for each one of them. To accomplish that, we add a ForEach activity to the pipeline, connect it to the output of the “Get Playlist Videos” activity and configure as:
Name
For Each Video
Sequential
checked, but can also run in parallel
Items
@activity(‘Get Playlist Videos’).output.items
Next, we add the remaining activities inside the loop, that will run for each video in the playlist. At the end it will look like this:
This activity will store the REST API response in a parquet file in the storage account. The file name will be similar to “getVideoStats-2021-04-23T13:13:25.6924926Z.parquet”.
SQL Pool Stored Procedure
Name
SQL pool SP – Insert data
Azure Synapse dedicated SQL pool
select the SQL pool where you created the table and stored procedure
Stored procedure name
[dbo].[p_youtube_video_likes_insert], or whatever you called it
Stored procedure parameters (use the import button to list them):
p_item_id
@item().id
p_video_id
@item().snippet.resourceId.videoId
p_likes
@activity(‘Get Video Stats’).output.items[0].statistics.likeCount
p_full_json_response
@string(activity(‘Get Video Stats’).output)
The call to the stored procedure will make sure that some individual fields (item id, video id, count of likes) as well as the full response are stored in a table in a SQL pool. In a real scenario, we would need to create a model to hold the data, as per the requirements.
Note: if you use Azure Data Factory, the same goal can be achieved using the “Stored procedure” activity, but you would need to use a linked service to the target SQL pool.
We can now run the full pipeline and validate that several files were created in the storage account and some data inserted in the SQL pool table.
4. Look at the data
Because we stored our data in parquet files and tables in a SQL pool, we have several options to use to manipulate the data. This table summarizes some of the options using Azure Synapse Analytics:
Data format and store
Compute options
Parquet files in ADLS Gen2
Tables in SQL Pools
SQL Serverless
Y (*)
N
SQL Provisioned
Y
Y (*)
Spark Cluster
Y (*)
Y
Below we will look at short examples of the options marked with (*). The goal is not to go deep into technical details of JSON handling but rather have an idea of the possibilities to use.
a) SQL Serverless on Parquet files in ADLS Gen2
We can use the OPENROWSET function to read a parquet file and show its contents.
-- check file as-is
SELECT * FROM OPENROWSET(
BULK 'https://mydatalake.dfs.core.windows.net/filesystem001/socialmedia/runs/YouTube/getVideoStats-2021-04-23T12:37:12.2853807Z.parquet',
FORMAT='PARQUET'
) AS [result]
Since we have a JSON file, we can take advantage of the OPENJSON function to parse the elements in the first level of the file.
-- OPENJSON in action
SELECT [key], [value], [type]
FROM OPENROWSET(
BULK 'https://mydatalake.dfs.core.windows.net/filesystem001/socialmedia/runs/YouTube/getVideoStats-2021-04-23T12:37:12.2853807Z.parquet',
FORMAT='PARQUET'
) as [result2]
CROSS APPLY OPENJSON(VALUE_COLUMN)
You can read more about OPENROWSET and OPENJSON here:
We can also use a Spark Cluster as a Spark processing engine to look at the data.
For this task, we create a new notebook. By default, the selected language is Python (Spark) and we can leave it like that, although we can use different languages by using Cell Magic Commands like %%pyspark or %%sql. We also need to attach the notebook to an existing Apache Spark Pool.
%%sql
SELECT get_json_object(VALUE_COLUMN, '$.kind') as `kind`,
get_json_object(VALUE_COLUMN, '$.etag') as `etag`,
get_json_object(VALUE_COLUMN, '$.items[0].id') as `videoId`,
get_json_object(VALUE_COLUMN, '$.items[0].statistics.likeCount') as `likes`
FROM default.youtube_video_likes
Summary It is easy to build an Azure Synapse Pipeline that gets information from social media. In the example shown in this article, on how to get some data from YouTube, we covered the ingestion, storing and consumption using different techniques, and more can be used. There is not “the correct” way to do it but rather a way that makes sense in your environment.
You can find all the code in the attachment file below, including the pipelines and datasets, SQL scripts, notebooks and supporting files, as well as on GitHub under the repository name SynapseIntegrateSocialMedia.
This article is contributed. See the original author and article here.
Has your perception of the cloud kept up with advancements in technology? Economic and logistic disruptions from COVID-19 have compelled most organizations to reassess whether their legacy systems can deliver on modern goals and priorities. While shifting market conditions have accelerated cloud adoption, some are still weighing the benefits. These businesses have allowed on-premises nostalgia to take hold and determine their cloud strategy.
Don’t allow misconceptions to dictate your journey. Get expert help through the Microsoft Dynamics 365 migration program to move from Dynamics AX or Dynamics CRM to Dynamics 365.
Addressing misconceptions about cloud migration
Moving to the cloud is no small decision. Dynamics 365 on-premises customers historically point to cost, timing, and complexity as reasons to delay or not to migrate. Cloud innovation and process advancements will only continue to erode these inaction arguments, further widening the gap with on-premises solutions.
Cost is always a decision factor. Are the benefits gained greater than the expense? Migrating, like anything, requires funding and resources. However, the hidden costs of remaining on-premises and maintaining your current solution are growing. Consider the graveyard of customizations you’ve built over the years that are no longer in use. Organizations need to weigh the total cost of ownershipinfrastructure costs, support costs, and upgrade coststo make a true cloud and on-premises comparison.
Migrating to the cloud should be a business priority. While customizations and data can make the process time-consuming, few business investments can provide similar efficiency gains. Microsoft engineered the On-Premises to Online Migration Factory to help Dynamics CRM customers. This end-to-end migration process provides a guided way to move on-premises instances without the need of reimporting the datathus reducing time and costs.
Customizations often hold on-premises systems together, allowing to adequately function in our modern environment. This can make knowing how to untangle your solution or what to migrate difficult. The Dynamics 365 migration program offers technical assessments to guide our Dynamics AX and Dynamics CRM customers through this process. Want to identify organization-specific benefits or optimize the transition process? Our Standard Migration Assessment can help you identify goals, understand migration benefits, and reduce outlay as you transition.
Adding value with Dynamics 365
The gap between on-premises and the cloud solutions is growing, and there’s a good reason. Innovation among cloud now outpaces on-premises development. With end-of-support dates nearing, this gap will only continue to grow, limiting productivity gains and configurability an organization might see with Power Apps, Power Automate, or Power BI functionality. Data centralized in one source only enhances the potential. Knowing usage patterns, adoption rates, and performance metrics, we can continuously update the cloud service without significant business disruption. When it comes to providing updates, Microsoft’s priority is the cloud because it’s the most efficient way to innovate, ideate, and provide value to customers. Customers no longer need to:
Host and secure software
Build a data center
Handle identity management
Manage connections to other systems
Instead, the cloud platform empowers an out-of-the-box mentality that allows users to get started with no expertise or additional tools.
The cloud is the future
As businesses continue to prioritize remote work and collaboration, the cloud will continue to be the fiber that connects employees, systems, and resources regardless of time zone. Reducing your physical footprint today is a great way to reduce costs and prepare for tomorrow. Migrating to Dynamics 365 will ensure your organization is prepared to meet future needs and challenges. To see how other companies have made this transition, visit our Dynamics 365 migration community.
DrWare.com uses cookies to provide you the best possible experience. Use of this site means you understand and accept our cookie policy. Find out more.
Recent Comments