Text Mining and Sentiment Analysis: Data Visualization in Tableau

The series so far:

1. Text Mining and Sentiment Analysis: Introduction
2. Text Mining and Sentiment Analysis: Power BI Visualizations
3. Text Mining and Sentiment Analysis: Analysis with R
4. Text Mining and Sentiment Analysis: Oracle Text
5. Text Mining and Sentiment Analysis: Data Visualization in Tableau

The previous articles of the Text Mining and Sentiment Analysis series focused on Power BI as the data visualization tool of choice. Tableau is another data visualization and analytics tool widely used in the industry. Tableau Desktop was first released in 2004, and this platform has been in use for much longer than Power BI, which was released for general availability in 2015. Current market research by Sintel shows Tableau Software has a 14.27 % market share, while Microsoft Power BI has a 10.47 % market share in the Business Intelligence (BI) category. Given the popularity and widespread use of Tableau, this article focuses on creating visualizations (workbooks) using Tableau Desktop (version 2020.2) to analyze the key-phrases and sentiment scores for gaining meaningful insights from text data.

Initial setup

This article uses an Oracle Database Virtual Box Appliance / Virtual Machine and Tableau Desktop installed on the host machine. The term host machine refers to your laptop, desktop, or any computer on which you install Oracle Virtual Box to host the virtual machine.

Set up section 1: Oracle Database Virtual Box Appliance / Virtual Machine

• Download and install VirtualBox by following instructions listed on the VirtualBox website.
• Download and set up the Oracle Database Virtual Box Appliance / Virtual Machine by following instructions listed on the Oracle website.
• Start up the Virtual Machine and verify SQL Developer is available on the Desktop screen. Please note that you may need to create a free account to download the Virtual Machine.
• In the Virtual Machine, launch the Oracle SQL Developer application. Under the Connections menu, click on system. When prompted for credentials, enter sys as sysdba in the username field and oracle in the password field. Ensure you can successfully connect to the Oracle Database before proceeding to the next step.

Figure 1. Oracle Database Developer Virtual Machine

Figure 2. Test new connection to database

Set up section 2: Configure Oracle Database on this Virtual machine for access from your Host system

• Navigate to the Oracle VM Virtual Box Manager and shut down this VM.
• Click the Settings gear icon from the toolbar. Select Network, click the Adapter 2 tab. Check the Enable Network Adapter checkbox.
• Select Host-only Adapter from the drop-down list options for the field Attached to.
• Select VirtualBox Host-Only Ethernet Adapter from drop-down list options for field Name.
• Click OK.

Figure 3. Configure Network setting for Oracle DB Developer VM

• Start the virtual machine.
• Click the Network icon on the top right side of the toolbar. There are two Ethernet network adapters (eth0 and eth1).
• If either one of the network adapters is in the Off state, click Connect on it. Both network adapters (eth0 and eth1) should be in the Connected state before proceeding further.

Figure 4. Connect Network Adapter inside the VM

• Open a Terminal inside the VM. Type ifconfig -a and hit enter/return key. Make a note of the IP address for network adapter eth1. This IP address will be used later during the demo to connect Tableau Desktop from your host machine to the Oracle database orcl running on this VM. In my demo, the IP address is 192.168.52.101.

Figure 5. Note IP address for network adapter eth1

Set up section 3: Install Tableau Desktop on your Host and connect to Oracle Database running on the VM

• Follow instructions on the Tableau website to download and install Tableau Desktop. This demo uses Tableau Desktop Version 2020.2. Please note that the download requires a business email, and the free trial expires in fourteen days. You may skip this step if you already have Tableau Desktop installed and running on your host machine
• If you have never used an Oracle client on your host machine before, you would also need to install Oracle JDBC driver. Please follow the download and install instructions from Tableau support page.
• Launch Tableau Desktop. Navigate under Connect Menu > Server and click on Oracle.

Figure 6. Launch Tableau Desktop on your Host machine

• On Connect to Oracle server screen
  • Enter the IP address from the previous step in the Server field
  • Enter orcl in the Service field
  • Enter 1521 in the Port field
  • Select Use a specified username and password radio button
  • Enter value system in the Username field
  • Enter value oracle in the Password field
  • Click Sign In

Figure 7. Connect to Oracle database from Tableau Desktop

• Upon successful sign-in, Tableau Desktop will proceed to the Data Source screen

Figure 8. Tableau Desktop showing connected to Oracle Database in the Data Source screen

Set up section 4: Import the demo data set into the Oracle Database

This article uses a demo data set where Sentiment Score and Key Phrases are already generated from the text response to ensure the demo’s simplicity, clarity, and consistency. The data set is a CSV file with fields:

• Period (Year & Quarter number)
• Manager (Name)
• Team (Name)
• Response (free form text responses from the Survey, to the question – How do you feel about your team’s health in this recent quarter)
• SentimentScore (a numeric score ranging from 0 to 1, indicating the degree of positivity of sentiment found in the Response text. 1 indicates highest positivity and 0 indicates the lowest positivity)
• KeyPhrases (a comma-separated list of meaningful words and phrases extracted from the Response text)

1. Download demo data file from my Github repository and save it in a convenient location on the Virtual Machine.
2. In the Virtual Machine, launch the Oracle SQL Developer application. Under the Connections menu, double click on system. Enter credentials: system in the username field and oracle in the password field. Click OK.

Figure 9. Connect to the Oracle database using Oracle SQL Developer

3. Run the following SQL to create a table for loading the demo data file into

-- Create a Table to load text data
CREATE TABLE SYSTEM.DEMO_SENTIMENTANALYSIS_TEAMHEALTH
(
    ID                     NUMBER GENERATED ALWAYS AS IDENTITY
   ,PERIOD                 VARCHAR2( 500 ) NOT NULL
   ,MANAGER                VARCHAR2( 500 ) NOT NULL
   ,TEAM                   VARCHAR2( 500 ) NOT NULL
   ,RESPONSE               VARCHAR2( 4000 ) NOT NULL
  ,SENTIMENT_SCORE        NUMERIC(4,2) 
  ,KEY_PHRASES VARCHAR2( 4000 ) 
   ,DW_CREATION_DATE       DATE DEFAULT SYSDATE
);

 

Figure 10. Create a table to load the demo data file

4. Under the connection menu, find table DEMO_SENTIMENTANALYSIS_TEAMHEALTH. Right-click on it and select Import Data to launch Data Import Wizard.

Figure 11. Launch the Data Import Wizard

5. In Step 1 of the Data Import Wizard, from the File field navigate to the location of your saved file DemoDataWith_sc_kps.csv. Leave all other fields to their default values as shown in Figure 11. Click Next. Please note in some versions of this environment, some users may face issues with loading a .csv where the Data Import Wizard tried to create a link ID field. If you experience this issue, please save the file as .xlsx and retry.

Figure 12. Data Import Wizard – Navigate to the demo data file to import

6. Proceed to the next steps in Data Import Wizard, leaving all fields to their default values. In Step 3 of the wizard, in some cases, you may need to select only the six named columns from the CSV file and ignore the long list of empty excel columns with names like “column7, column8,….”. Click Next.

Figure 13. Step 3 of Data Import Wizard – select columns to import

7. In step 4 of Data Import Wizard, map source file field SentimentScore to target table column SENTIMENT_SCORE and source file field KeyPhrases to target table column KEY_PHRASES. Click Next.

Figure 14. Step 4 of Data Import Wizard – map and source and target columns

8. Upon completion of the Data Import Wizard, you will see a message indicating data import has successfully completed. Click OK to exit the wizard

Figure 15. Data Import wizard completed successfully

Visualization One – Word Cloud

A Word cloud is one of the most popular ways to analyze text data by visualizing frequent keywords/phrases. It’s an image composed of keywords/phrases found within a document, where the size of each word indicates its relative frequency in that document.

Unlike Power BI, Tableau does not have an out-of-the-box visualization to create a word cloud. For creating a word cloud visualization, Tableau expects you to format input data such that each row only has one word. However, data in the KEY_PHRASES column is a comma-separated list of keywords, which needs to be transformed before it can be used in Tableau.

Run the following SQL to create a view that applies the necessary transformation logic.

CREATE OR REPLACE VIEW SYSTEM.DEMO_TEAMHEALTH_WORDFREQUENCY AS 
WITH TEMP
     AS      
     -- get necessary data , trim whitespace and convert to lower case
     SELECT ID,PERIOD,MANAGER,TEAM, RTRIM (
LTRIM (LOWER (KEY_PHRASES))) KEY_PHRASES
          FROM system.DEMO_SENTIMENTANALYSIS_TEAMHEALTH),
     TEMP1
     AS   -- split into rows on commas
       (SELECT DISTINCT T.ID,T.PERIOD,T.MANAGER,TEAM,
                        TRIM (REGEXP_SUBSTR (T.KEY_PHRASES,
                                             '[^,]+',
                                             1,
                                             LEVELS.COLUMN_VALUE))
                           AS KEY_PHRASES
          FROM TEMP T,
               TABLE (
                  CAST (
                     MULTISET (
                            SELECT LEVEL
                              FROM DUAL
                        CONNECT BY LEVEL <=
                                        LENGTH (
                                         REGEXP_REPLACE (T.KEY_PHRASES,
                                                           '[^,]+'))
                                   + 1) AS SYS.ODCINUMBERLIST)) LEVELS),
     TEMP2
     AS         -- split into rows on space
       (SELECT DISTINCT T.ID,T.PERIOD,T.MANAGER,TEAM,
                        TRIM (REGEXP_SUBSTR (T.KEY_PHRASES,
                                             '[^ ]+',
                                             1,
                                             LEVELS.COLUMN_VALUE))
                           AS KEY_PHRASES
          FROM TEMP1 T,
               TABLE (
                  CAST (
                     MULTISET (
                            SELECT LEVEL
                              FROM DUAL
                        CONNECT BY LEVEL <=
                                        LENGTH (
                                           REGEXP_REPLACE (T.KEY_PHRASES,
                                                           '[^ ]+'))
                                    + 1) AS SYS.ODCINUMBERLIST)) LEVELS)
SELECT ID,PERIOD,MANAGER,TEAM, KEY_PHRASES AS WORDS
  FROM TEMP2
 WHERE       -- list of custom words to remove here
       KEY_PHRASES NOT IN ('team', 'of', 'health','i');

Figure 16. Create view to transform KEY_PHRASES

Run the following SELECT statement to verify the view transforms data in the expected format of one keyword/phrase per row.

SELECT * FROM SYSTEM.DEMO_TEAMHEALTH_WORDFREQUENCY ORDER BY ID;

Figure 17. Results of view to transform key phrases into a frequency table

Launch Tableau Desktop. Reconnect to Oracle Database as shown in Figure 6. From the Data Source tab, set Schema = SYSTEM and Table = DEMO_TEAMHEALTH_WORDFREQUENCY. Drag and drop view DEMO_TEAMHEALTH_WORDFREQUENCY to the right-hand side window. Select the Extract radio button. Click the prompt at the bottom of the screen to Go to Worksheet.

Figure 18. Use Oracle database view in Tableau Data Source

The Extract option pulls data one time from the Oracle database and saves it as a Tableau Extract, whereas the Live option performs a fresh data pull from the source database each time any user interacts with the Tableau Dashboard. The Live option ensures that users get current data from the database when interacting with their Tableau Dashboards. This is useful in situations where the underlying data is fast-changing, and Tableau Dashboards must reflect the latest data. However, the potential drawback of this option is slower response time/performance of Dashboards, compared to the saved Extract.

Click the Go to Worksheet prompt at the bottom of the screen and save the extract file when prompted. Tableau Desktop will open an empty worksheet. On this empty worksheet, perform the following steps to create the basic word cloud visualization.

1. Drag the WORDS dimension to Text on the Marks card.
2. Drag the WORDS dimension to Size on the Marks card.
3. Drag the WORDS dimension to Color on the Marks card, to add color.
4. Right-click on the dimension on the Size card and select Measure > Count.
5. Change the Mark type from Automatic to Text.

Figure 19. Create the basic Word Cloud visualization

Add the WORDS dimension to the Filter card, which brings up the filter menu as shown in Figure 20. Switch over to the Top tab. Select the By field radio button > Top, 50, by Words and Count. Click OK. This filters the word cloud to display only the 50 most frequent words, vastly improving the readability of this visualization.

Figure 20. Filter Word cloud by Top 50 most frequent words

Add dimensions Manager, Period, and Team to the Filters card. Right-click on each of these dimensions in Filters card and select Show Filter, which will display them on the right side for the user to slice and dice the Word Cloud for their desired combination of Manager, Period and Team. Right click on sheet1 at the bottom of Tableau Desktop screen to rename this sheet to WordCloud. Update the Title to Word Cloud. Save this as a Packaged Tableau workbook (.twbx file)

Rename

Figure 21. Word Cloud with Filters

This word cloud visualization is used to interpret the following themes.

• The overall top three most frequently recurring terms are good, work and projectx, probably indicating that “good work (was done in) project” is the most prominent theme in this body of text
• The next three most frequently occurring terms are support, improvement and great. This could be interpreted as “great improvements (were made with) support” is the second most prominent theme in this body of text
• With the filters on the right-hand side, the Word cloud can be sliced and diced for the desired combination of TEAM, PERIOD and MANAGER, to identify the prominent themes by these dimensions

The next few visualizations use the Sentiment Score field.

Visualization Two – Bar Chart of Average Sentiment Score Trend

In Tableau Desktop, return to the Data Source tab found at the bottom left. Search for Table DEMO_SENTIMENTANALYIS_TEAMHEALTH. Drag and drop it to the right. Make sure that both the table and view are in the work area on the right. In the Edit Relationship screen, select the ID field on both left and right sides to blend the two data sets, creating a relationship. This is Tableau’s way of expressing the SQL equivalent of an Inner Join.

Figure 22. Add the Sentiment Analysis Table Data Source

Close the Edit Relationship pop-up window. Create a new sheet by clicking on New Worksheet from the option on the bottom right.

On this new sheet under the Tables section, expand DEMO_SENTIMENTANALYSIS_TEAMHEA. Right-click on dimensions Id, Manager, Period and Team. rename them to remove any extra characters not needed for display (This renaming of dimension names is optional and only done for cosmetic reasons, with no impact on the actual analysis.)

Figure 23. Rename fields to remove extra characters

• Drag and drop the Period dimension to the Columns shelf.
• Drag and drop the Sentiment Score measure to the Rows shelf. Right-click on it and change the aggregation to Average.
• Rename the Sheet to SentimentScoreTrend.
• Edit the Title to Average Sentiment Score Quarterly Trend.
• Right-click in the Y-Axis of the resulting bar-chart. Click Edit Axis. Select the General. Choose the Fixed option under the Range radio button. Enter a value of 0 in the field Fixed Start and value of 1 in the field Fixed end. This sets the range for the Y-axis.

Figure 24. Set range for y-axis

• Drag and drop Manager and Team dimensions to the Filters card.
• Right-click on each dimension in the Filters card and select Show Filter, which displays them on the right side where users can interact with them.

Figure 25. Average Sentiment Score Quarterly Trend

This Bar Chart visualization can be used to make the following observations

• The Average Sentiment Score across all teams trended marginally upwards from period 2018-Q3 (0.67) to 2019-Q1 (0.70), then remained almost flat for the next period of 2019-Q2 (0.70).
• When filtered for Team 5, you can see that Team 5 experienced a significant uptick in their Average Sentiment score from 0.45 (45% positive) in 2018-Q3 to 0.79 (79% positive) in 2019-Q2.
• When filtered for other teams, note that some teams don’t have bars on the chart for certain periods. This could indicate missing data or potentially a re-structuring of teams/departments in the company.

Visualization Three – Bar Chart of Average Sentiment Score Comparison across Teams

• Create a new worksheet. Drag and drop the Teams dimension to the Columns shelf.
• Drag and drop the Sentiment Score measure to the Rows shelf. Right-click on it and change the aggregation to Average.
• Right-click in the Y-Axis of the resulting bar-chart and click Edit Axis. Select the General Tab. Choose the Fixed option under the Range radio button. Enter a value of 0 in the field Fixed Start and value of 1 in the field Fixed end. This sets the range for the Y-axis.
• Right-click on the Team dimension in the Columns shelf and select Sort. Then change the Sort By drop-down to Field and select Sentiment Score in the Field Name drop-down list. Select Ascending in the Sort Order radio button. Select Average in the Aggregation drop-down list. This will sort the bar chart by increasing the value of Average Sentiment Score.

Figure 26. Sort the Bar-chart

• Drag and drop the Period dimension to the Filters card and right-click it. Select Show filter to display period on the right side and allow users to see how the team’s rankings changes over time
• Rename this sheet to SentimentScore_Teams and change the Title to Sentiment Score by Team

Figure 27. Compare Sentiment Scores across teams

This bar chart indicates the following.

• Aggregated across all Periods, Team 5 has the lowest Average Sentiment Score, and Team 8 has the highest Average Sentiment Score
• Filtering Data for Period 2018-Q3 shows that Team 5 had the lowest Average Sentiment Score, and Team 2 has the highest Average Sentiment Score
• Filtering Data for Period 2019-Q2 shows that Team 1 has the lowest Average Sentiment Score, and Team 5 has the highest Average Sentiment Score
• An observation to make note of is that the aggregated data across all periods shows that Team 5 has the lowest Average Sentiment Score. However, analyzing the data one period at a time reveals that Team 5 has made tremendous improvements in their Average Sentiment Score over the course of four periods and has gone from having the lowest score to the highest.

Visualization Four – Histogram

A histogram is a representation of the distribution of numerical data. To construct a histogram, the first step is to bin (or bucket) the range of values into a series of intervals, then count how many values fall into each interval. The bins are usually specified as consecutive, non-overlapping intervals of a variable. The bins (intervals) must be adjacent and are often (but not necessarily) of equal size. In this scenario, the sentiment score bin will form the x axis, and the frequency (count of responses) belonging to that bin will be on the y axis.

Bin the Sentiment Score measure before using it to create the histogram. This will simply categorize the sentiment scores into ten distinct buckets

• Create a New Worksheet. Right-click on the Sentiment Score measure > Create > Bins
• On the Edit Bins screen, enter a value of 0.1 in the field Size of bins. Click OK.

Figure 28. Create Sentiment Score Bins to use in the histogram

• Drag and drop the Sentiment Score(bin) dimension to the Columns shelf
• Drag and drop the Sentiment Score measure to the Rows shelf
• Right-click on the Sentiment Score measure in the Rows shelf and change the measure (aggregation) to Count
• Drag and drop Manager, Team and Period dimensions to the Filters card
• Right-click on each dimension in the Filters card and select Show Filter, which will display them on the right side for users to interact with
• Rename the worksheet to Histogram
• Edit the Title to Histogram

Figure 29. Histogram

This histogram shows an imbalanced bimodal data distribution, indicating some degree of polarization in terms of how team members feel about their team’s health. Many team members (200+ responses clustered towards the right) feel strongly positive about their team’s health, while some (about 100 responses clustered towards the left) feel strongly negative. Very few (only 17 responses in the middle) are in the neutral score range. Users can filter the Histogram by Period, Team, or Manager for further in-depth analysis.

Visualization Five – Focus on Targeted Responses

The first four visualizations helped to answer the following questions.

• What are the trending topics for each team, across various periods and managers?
• How does the sentiment score trend over time?
• How do the Sentiment Scores for teams compare against each other, and how has that evolved over time?
• How does the distribution of Sentiment Scores look like across various members of the same team? Are there any groupings/clusters in certain areas of the score range?

Next, you might want to read the text of responses that are of interest to you. A set of users may be interested in reading the most negative responses for a period. In contrast, the most positive responses from a particular team might interest other group users. You may want to share this Tableau Dashboard with the managers of these teams. A particular manager may be interested in reading the responses only for their own team(s). In this section, I will demonstrate a visualization for serving up this detailed information in an easy to search format.

• Create a new sheet and drag the Period, Manager, Team and Response dimensions to the Rows shelf
• Drag the Sentiment Score measure and drop it on Text within the Marks shelf
• Drag the Period, Manager, Team and Sentiment Score (bin) dimensions and drop them to the Filters card
• Right-click on each dimension in the Filters card and select Show filter
• Rename the worksheet to Response Details
• Edit the title to add the following text to it ‘Select 1 or more Sentiment Score Bins, to view all the responses that fall within the selected score range. Then filter by Team/Manager/Period as needed’

Figure 30. Response Details

The Tableau visualizations demonstrated so far have helped to analyze the team health data.

• The word cloud identifies popular topics/themes and allows drill down by Period, Team and Manager
• The Sentiment Score Trend bar chart shows how the average sentiment scores are trending over a period of time
• The Sentiment Score by Teams bar chart allows for quick and easy comparison of average sentiment scores across teams. It helps to identify which teams have the highest/lowest average scores and if they changed over time.
• The histogram visualizes distribution of Responses across the range of sentiment scores. It helps to identify clusters/groupings in the positive, neutral or negative ranges and gauge polarization.
• The Response details visualization allows users to focus on reading text of only a sub-set of responses, that are of interest of them.

Over the course of these five visualizations, you (or your users) can identify which teams are doing great, which ones may need some help to improve their team’s health, and which areas deserve further in-depth conversations with Team managers.

Conclusion

This article demonstrated how to connect with an Oracle Database from Tableau Desktop, to visualize the Team Health data. It also covered creating five different visualizations in Tableau Desktop to gain insights into themes, identify trends, extract business value, and narrate a meaningful story from the team health survey responses.

References:

• Tableau desktop – https://www.tableau.com/products/desktop
• Creating a word cloud in Tableau – https://kb.tableau.com/articles/howto/creating-a-word-cloud
• Bar charts – https://en.wikipedia.org/wiki/Bar_chart
• Histogram – https://en.wikipedia.org/wiki/Histogram
• Bimodal distribution – https://www.statisticshowto.com/what-is-a-bimodal-distribution/

 

The post Text Mining and Sentiment Analysis: Data Visualization in Tableau appeared first on Simple Talk.

from Simple Talk https://ift.tt/3lRR94D
via

Creating Views in Azure Portal

Resource groups and subscriptions sometimes are not enough to organize the content of our Azure Portal. I was preparing for a presentation and looking into a lot of resource groups, but during the presentation only a few of them should appear for me. How could I control this?

The need is the mother of the invention. Of course, I didn’t invented anything, probably I’m the last one to discover we can create views for each kind of resource we have.

Tag The Resources

On this example, I was using many resource groups. How to include them, and only them, on the view? Tagging them. We can select many resources at once, in this case many resource groups at once and add a tag to all of them clicking on the button Assign Tag

 

 

Create a Filter

The screen has a filter option. You can filter based on many attributes of the objects, one of them is the value of the tags. Each tag you created will appear as a field and you can filter by the value of the tag, what could be the name of the project you are working on.

 

 

Create a View from your Filter

On the Manage View button, you create a new view from your filter clicking on Save View. That’s it: Every time you would like to see only that set of objects, you can select your view.

 

Play with the View Options

The button Manage View opens many options for us to manage views.

 

Save View: You will save the changes you made on this view or create a new one

Save View as: You will save the changes you made on this view as a new one

Edit Columns: Change the visible columns on this view

Choose favorite view: You can choose the view to use as default view

Default and Azure Summit : These are two existing views. The generated by the portal and the one I created

Browse all views for “Resource Groups” : This will allow you to manage all the existing views for Resource Groups. The interesting part of this option is to notice you can have different views for each kind for object and managed them isolated.

The post Creating Views in Azure Portal appeared first on Simple Talk.

from Simple Talk https://ift.tt/3FVQMOp
via

ARM Template Visualizer

Recently a new feature appeared in Azure Portal, an ARM Template Visualizer, capable to show a graphic view of the resources inside an ARM template. The feature appears as a new button in screens where we are editing or downloading templates.

These are some of the screens where you will see the new button:

• When you are provisioning new resources, if you click the Download Template for Automation link
• When you choose the Export Template option under Automation on the left menu

• The screen for template deployment
• Resource Visualizer on any Resource Group menu item

The best usage for this new feature is when visualizing templates for resource groups. Templates for resource groups will contain all the objects inside the resource group, so you will be able to see even the relation between them when this relation is exposed during the provisioning.

The image below shows some of these relations:

• There is an alert inside Application Insights
• The App Service is inside an App Service Plan
• The App Services Certificate and the Web Certificate are inside Key Vault

 

If you try to visualize a template of a single object, it may not be very useful, you will get an image like the one below, generated by a Log Analytics Workspace

 

The post ARM Template Visualizer appeared first on Simple Talk.

from Simple Talk https://ift.tt/3BJomoc
via

Automating Extended Events data collection

Extended Events are an excellent way to collect data about a SQL Server that provides a vast array of events that can be used for performance monitoring, troubleshooting, or auditing a server.

While using Extended Events is not overly complex, building a reliable system to collect, parse, and store events over time without any data loss can be challenging.

This article dives into one method for collecting and retaining all event data for a specific set of events. A system like this is highly customizable and provides a solid starting point for Extended Events development. This should be viewed as a collection of puzzle pieces; individual pieces can be adjusted as needed to produce a monitoring solution that fits the needs of a given situation, even if it is vastly different from what is demonstrated here.

Recap

The previous article walked through the creation, configuration, and use of Extended Events with code and customization provided for the reader. The next steps are to turn a temporary process into a permanent one.

Creating the process requires permanent database objects to manage XML indexing and store event XML and the resulting event data. For data to be useful for monitoring, alerting, and analytics, it needs a permanent place to reside. How this data is managed after collection is up to the user. It can be retained forever, for a shorter time frame (if forever is too long), and/or centralized to a location where event data from many servers is aggregated. Centralization may be beneficial regardless of the source details if the central location is a server built for analytics.

Often in projects like this, alerting and monitoring occur close to the source data. In contrast, analytics occur elsewhere to ensure a separation between these processes, their cost in computing resources, and their function.

Note that the stored procedure created in this article uses xp_cmdshell. PowerShell may be easily substituted for this system stored procedure if needed. For the purposes of demonstration, though, having all code in a single script made these concepts easier to understand than by combining the script with PowerShell.

Reading Event Data into permanent tables

Having a permanent target table to store the XML and the results of the XML parsing process is an important step. Some organizations may choose to move the files to another server for processing or store data in another system. For the demonstration, tables will be created in SQL Server to store both the XML and the values extracted from it. Depending on its size, a columnstore index may be beneficial for the table containing the resulting data. If tens of millions of rows are to be stored, use a columnstore index; otherwise, page compression should suffice.

The following code creates two tables: One for the XML and one for the metrics that result from its shredding. It also creates an XML collection as a prerequisite that will allow for indexing the XML column:

CREATE XML SCHEMA COLLECTION dbo.extended_events_xml_schema_collection
AS
N'<xs:schema attributeFormDefault="unqualified" 
      elementFormDefault="qualified" 
      xmlns:xs="http://www.w3.org/2001/XMLSchema">
   <xs:element name="event">
   <xs:complexType>
   <xs:sequence>
   <xs:element name="data" maxOccurs="unbounded" minOccurs="0">
   <xs:complexType>
   <xs:sequence>
   <xs:element type="xs:string" name="value"/>
   <xs:element type="xs:string" name="text" minOccurs="0"/>
   </xs:sequence>
   <xs:attribute type="xs:string" name="name" use="optional"/>
   </xs:complexType>
   </xs:element>
   <xs:element name="action" maxOccurs="unbounded" minOccurs="0">
<xs:complexType>
<xs:sequence>
   <xs:element type="xs:string" name="value"/>
</xs:sequence>
<xs:attribute type="xs:string" name="name" use="optional"/>
   <xs:attribute type="xs:string" name="package" use="optional"/>
</xs:complexType>
</xs:element>
</xs:sequence>
<xs:attribute type="xs:string" name="name"/>
<xs:attribute type="xs:string" name="package"/>
<xs:attribute type="xs:dateTime" name="timestamp"/>
</xs:complexType>
</xs:element>
</xs:schema>';
GO
        
CREATE TABLE dbo.extended_events_xml
( extended_events_xml_id INT NOT NULL IDENTITY(1,1) 
     CONSTRAINT PK_extended_events_xml PRIMARY KEY CLUSTERED,
  sample_time_utc DATETIME2(3) NOT NULL,
  event_data_xml XML(dbo.extended_events_xml_schema_collection)
          NOT NULL)
  WITH (DATA_COMPRESSION = PAGE);
CREATE NONCLUSTERED INDEX NCI_extended_events_xml_sample_time_utc 
         ON dbo.extended_events_xml (sample_time_utc);
CREATE PRIMARY XML INDEX PXMLI_extended_events_xml_event_data_xml 
         ON dbo.extended_events_xml (event_data_xml);
CREATE TABLE dbo.extended_events_data
(extended_events_data_id INT NOT NULL IDENTITY(1,1) 
    CONSTRAINT PK_extended_events_data PRIMARY KEY CLUSTERED,
  sample_time_utc DATETIME2(3) NOT NULL,
  database_name VARCHAR(128) NOT NULL,
  event_name VARCHAR(50) NOT NULL,
  session_id SMALLINT NOT NULL,
  cpu_time BIGINT NOT NULL,
  duration BIGINT NOT NULL,
  physical_reads BIGINT NOT NULL,
  logical_reads BIGINT NOT NULL,
  writes BIGINT NOT NULL,
  row_count BIGINT NOT NULL,
  client_app_name VARCHAR(128) NOT NULL,
  client_host_name VARCHAR(128) NOT NULL,
  username VARCHAR(128) NOT NULL);
CREATE NONCLUSTERED INDEX NCI_extended_events_data_sample_time_utc 
ON dbo.extended_events_data (sample_time_utc)
WITH (DATA_COMPRESSION = PAGE);

The tables are structured to allow the data to be organized chronologically. dbo.extended_events_xml is a temporary repository for XML retrieved from Extended Events files and will not retain data longer than is needed to process it. dbo.extended_events_data will contain the fields shredded from the XML and is intended to be a more permanent home for Extended Events data.

What remains are two steps to manage event data:

1. Read XML data from the Extended Events file and write it to dbo.extended_events_xml.
2. Shred that XML and insert the results into dbo.extended_events_data.

This example reads the file created in the first article. Reading the XML takes the function sys.fn_xe_file_target_read_file and inserts the results into dbo.extended_events_xml:

INSERT INTO dbo.extended_events_xml
                (sample_time_utc, event_data_xml)
SELECT
                timestamp_utc AS sample_time_utc,
                CAST(event_data AS VARCHAR(MAX))
FROM sys.fn_xe_file_target_read_file(
    'C:\SQLBackup\Sql_Server_Query_Metrics*.xel', NULL, NULL, NULL);

Note that the three NULLs in the function can be replaced with parameters that adjust how to read the file. For our work, the entire file will be read from start to finish. Therefore, the additional parameters may be left NULL. The following confirms that work was done:

The contents of the table can be confirmed by selecting from the XML table:

SELECT

*
FROM dbo.extended_events_xml;

 

Clicking on any of the XML results displays the raw XML:

The results show some of the fields available in the XML, such as query duration, logical reads, and CPU time. The next step is to parse that XML into those data elements and insert them into dbo.extended_events_data, where they can then be used for analysis:

INSERT INTO dbo.extended_events_data
  (sample_time_utc, database_name, event_name, session_id, cpu_time, 
    duration, physical_reads,    logical_reads, writes, row_count, 
     client_app_name, client_host_name, username)
SELECT
   sample_time_utc,
   event_data_xml.value('(event/action[@name="database_name"]/value)[1]',
         'SYSNAME') AS database_name,
   event_data_xml.value('(event/@name)[1]', 'VARCHAR(50)') 
         As event_name,
   event_data_xml.value('(event/action[@name="session_id"]/value)[1]', 
          'SMALLINT') AS session_id,
   event_data_xml.value('(event/data[@name="cpu_time"]/value)[1]', 
        'BIGINT') AS cpu_time,
   event_data_xml.value('(event/data[@name="duration"]/value)[1]', 
        'BIGINT') AS duration,
   event_data_xml.value('(event/data[@name="physical_reads"]/value)[1]', 
        'BIGINT') AS physical_reads,
   event_data_xml.value('(event/data[@name="logical_reads"]/value)[1]', 
        'BIGINT') AS logical_reads,
   event_data_xml.value('(event/data[@name="writes"]/value)[1]', 
        'BIGINT') AS writes,
   event_data_xml.value('(event/data[@name="row_count"]/value)[1]', 
        'BIGINT') AS row_count,
   event_data_xml.value(
           '(event/action[@name="client_app_name"]/value)[1]', 
        'VARCHAR(128)') AS client_app_name,
   event_data_xml.value(
          '(event/action[@name="client_hostname"]/value)[1]', 
        'VARCHAR(128)') AS client_host_name,
   event_data_xml.value('(event/action[@name="username"]/value)[1]', 
        'SYSNAME') AS username
FROM dbo.extended_events_xml;

This parses the XML column and pulls out each component of interest to this demo. Additional data elements available in the XML are not used here (result, spills, statement, etc.) but could be if needed. Note that not every XML field is populated in every event, therefore NULL (or defaults) may be needed for some columns in the output table. The results can be confirmed by selecting from dbo.extended_events_data:

SELECT
*
FROM dbo.extended_events_data;

The results show an easy-to-use dataset that has been derived from the event XML. Data will likely be found that may not be useful for a given application, such as:

• Events from system databases
• Events with no reads/writes/CPU/rows returned
• Events from specific logins/hosts
• Events with zero duration

Feel free to add filters to the Extended Events session to remove any scenarios that are not useful for a given application. Unneeded event data will consume extra computing resources throughout this process, and therefore it is beneficial to trim the data set to only what is needed and nothing more.

Automating the data dollection

With each of the building blocks defined, the fun part begins! A process can be built to automate everything introduced thus far, reducing the need for manual intervention, cleanup, or maintenance of Extended Events or the resulting data. The prerequisites for this process are the creation of the following objects, and all introduced previously in this article:

• The XML schema collection, called dbo.extended_events_xml_schema_collection
• The XML table created above, called dbo.extended_events_xml
• The event data table created above, called dbo.extended_events_data

There are many ways to approach this challenge, and no one way is the “right” way. It is up to the consumer of this code to decide how to implement it and adjust it to best fit their database environment.

The simplest way to do this is to encapsulate each of the following T-SQL code blocks into their own chunks of dynamic SQL:

1. Create Extended Events session
2. Start Extended Events session
3. Stop Extended Events session
4. Drop Extended Events session
5. Read Extended Events log files
6. Delete old Extended Events log files

This process allows for code reuse, improved maintainability, and the ability to modify code more easily. The guts of this process will be simplified into a short sequence of dynamic SQL or stored procedure calls, allowing that logic to be adjusted quickly and with minimal effort and, more importantly, minimal risk. (The complete stored procedure can be downloaded here.)

A stored procedure will be created that accepts three parameters:

CREATE PROCEDURE dbo.process_extended_events
        @extended_events_session_name VARCHAR(MAX),
@extended_events_file_path VARCHAR(MAX),
@stop_and_drop_all_extended_events_sessions_and_files BIT

The first two parameters specify a generic Extended Events session name and a place to store event data. This will be modified later on with a suffix to ensure that a new session can quickly be created while events from the old session are read.

The last parameter is used to clean up all Extended Events sessions and data for the session specified in the other parameters. When set to 1, it:

1. Stops the Extended Events session, if started.
2. Drops the Extended Events session, if it exists.
3. Deletes event log files for Extended Events session matching the prefix of the session name provided in @extended_events_session_name

This parameter is useful for testing, cleanup, or when the use of this process is complete.

To streamline this code, as many steps as possible are encapsulated into variables or blocks of T-SQL. The first of these steps retrieves details about the current Extended Events session, if one exists:

DECLARE @current_extended_events_session_name VARCHAR(MAX);
DECLARE @does_event_session_exist BIT = 0;
DECLARE @is_event_session_started BIT = 0;
IF EXISTS (SELECT * FROM sys.dm_xe_sessions
  WHERE dm_xe_sessions.name LIKE @extended_events_session_name + '%')
  BEGIN
   SELECT @does_event_session_exist = 1;
   SELECT @is_event_session_started = 1;
   SELECT @current_extended_events_session_name 
                      = dm_xe_sessions.name
   FROM sys.dm_xe_sessions 
   WHERE dm_xe_sessions.name 
   LIKE @extended_events_session_name + '%';
END
ELSE
        IF EXISTS (SELECT * FROM sys.server_event_sessions 
                   WHERE server_event_sessions.name 
                        LIKE @extended_events_session_name + '%')
        BEGIN
                SELECT @does_event_session_exist = 1;
                SELECT  @current_extended_events_session_name 
                      = server_event_sessions.name
FROM sys.server_event_sessions WHERE server_event_sessions.name 
  LIKE @extended_events_session_name + '%';
END

This code retrieves 3 details about Extended Events:

• Does an Extended Events session with the specified name exist?
• Is the Extended Events session started?
• What is the currently running Extended Events session name, if one exists?

This information simplifies code later on as it allows a variable to be checked, rather than system views. It also allows for reuse of this info whenever needed.

Extended Events created by this process will automatically append a sequential suffix to the Extended Events session name that is passed into the stored procedure. This ensures uniqueness and the ability to read data from old sessions after the new session has started. Code is needed to try and collect the suffix and will do so like this:

DECLARE @session_number_previous INT;
DECLARE @session_number_next INT;
IF @does_event_session_exist = 1
BEGIN
   SELECT @session_number_previous =
     CASE
        WHEN ISNUMERIC(RIGHT(@current_extended_events_session_name, 1)) 
                   = 1
        AND LEN(@current_extended_events_session_name) <> 
                  LEN(@extended_events_session_name)
        THEN SUBSTRING(@current_extended_events_session_name, 
                LEN(@extended_events_session_name) + 1, 
                LEN(@current_extended_events_session_name) - 
                LEN('query_metrics'))
        ELSE 0
                END
        END
SELECT @session_number_next = @session_number_previous + 1;

This populates two variables that will contain the previous session suffix, as well as the next session suffix. If no session exists, then the new session will be suffixed with “1”.

The next step reuses code from earlier in this article. The only difference is that variables such as file name and session name are spliced into dynamic SQL based on the parameters supplied to the stored procedure. For example, the following command will create the Extended Events session:

DECLARE @next_extended_events_session_name VARCHAR(MAX)  = 
       @extended_events_session_name + 
       CAST(@session_number_next AS VARCHAR(MAX));
DECLARE @extended_events_session_create_command NVARCHAR(MAX);
SELECT @extended_events_session_create_command = '
   CREATE EVENT SESSION ' + @next_extended_events_session_name 
          + ' ON SERVER
   ADD EVENT sqlserver.rpc_completed (
   ACTION (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE ''SQLAgent%'')),
ADD EVENT sqlserver.sql_batch_completed (
   ACTION (
        sqlserver.client_app_name,
        sqlserver.client_hostname,
        sqlserver.database_name,
        sqlserver.session_id,
        sqlserver.username)
WHERE (sqlserver.client_app_name NOT LIKE ''SQLAgent%''))
ADD TARGET package0.event_file
   (SET FILENAME = ''' + @extended_events_file_path + 
          @next_extended_events_session_name + '.xel'',
        MAX_FILE_SIZE = 1000, -- 1000MB
        MAX_ROLLOVER_FILES = 3)
        WITH (  EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 15 SECONDS,
MAX_MEMORY = 1024MB,
STARTUP_STATE = OFF);';

Now that this code is stored in @extended_events_session_create_command, it can be executed using sp_executesql any time. This process will be followed for all subsequent processes, and the code will not be repeated here as it has already been introduced. The entirety of this stored procedure is attached to this article for testing, customization, and use.

After this work is complete, the following commands will exist:

• @extended_events_session_create_command
• @extended_events_session_start_command
• @extended_events_session_stop_command
• @extended_events_session_file_delete_command
• @extended_events_session_file_delete_all_command***
• @extended_events_session_drop_command
• @extended_events_read_data_command

Note that @extended_events_session_file_delete_all_command is used when no previous sessions exist and removes any files with the Extended Events session name provided by @extended_events_session_name.

The last component of this process consists of the following logic:

1. If @stop_and_drop_all_extended_events_sessions_and_files = 1
   1. Does @is_event_session_started = 1?
      1. If yes, then stop the current session.
   2. If @does_event_session_exist = 1
      1. Then drop the current session.
   3. Delete all log files for the session name provided.
2. If @stop_and_drop_all_extended_events_sessions_and_files = 0
   1. If @does_event_session_exist = 0
      1. Then Delete all log files for the session name provided.
      2. Create the new session.
      3. Start the new session.
   2. If @does_event_session_exist = 1
      1. If @is_event_session_started = 1
         1. Stop the existing session.
      2. Create the new session.
      3. Start the new session.
      4. Read data from the previous session.
      5. Drop the previous session.
      6. Delete log files from the previous session.

If the parameter is passed in to stop and drop all sessions, then the logic above checks for the state of the previous session before stopping it, dropping it, and deleting files.

Otherwise, the process checks to see if a session already exists and if it is started, and then walks through the steps needed to stop it, create a new session, and read data from the previous session.

Why is this logic so involved? The goal is to minimize lost events. If the old session were read before the new session was started, then any events that occur in between would never be captured by Extended Events and would be lost to us.

The table dbo.extended_events_xml is truncated after each use. Once events are read, there is no need to retain the raw XML anymore.

Customize! Customize! Customize!

This process is expected to be modified to meet the needs of a given organization. With near certainty, the collected events will be adjusted to capture different events, actions, and possibly other bits of metadata collected along the way (such as the database server name).

The XML table can be reused as-is without any modifications.

The data table will need to be adjusted to account for different data elements being parsed from the XML. This table does not need to include columns for XML elements that will be ignored but should have columns for any elements that are to be retained.

Deadlock example

If there was a need to automate event collection for deadlocks, the following could be used as the Extended Events session create command:

DECLARE @extended_events_session_create_command NVARCHAR(MAX);
SELECT @extended_events_session_create_command = '
        CREATE EVENT SESSION ' + @next_extended_events_session_name 
             + ' ON SERVER
ADD EVENT sqlserver.xml_deadlock_report (
    ACTION(sqlserver.client_app_name,
           sqlserver.client_hostname,
           sqlserver.database_name))
ADD TARGET package0.asynchronous_file_target
                        (SET FILENAME = ''' + @extended_events_file_path 
                       + @next_extended_events_session_name + '.xel'',
                                MAX_FILE_SIZE = 1000, -- 1000MB
                                MAX_ROLLOVER_FILES = 3)
WITH (  EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
                MAX_DISPATCH_LATENCY = 15 SECONDS,
                MAX_MEMORY = 1024MB,
STARTUP_STATE = OFF);';

To start, there would be value in dumping the Extended Events data into the XML table and examining it to determine which columns should be maintained and which may not be needed. There are likely some columns that are not needed and many that will be the same as those used thus far in this article. Others, such as the deadlock graph, would be essential.

Note that @next_extended_events_session_name was previously calculated above. The code used to populate this (and other dependant variables) can be reused.

Automating Extended Events data collection

The stored procedure presented (and attached) in this article represents a process that can be executed regularly and will automatically manage an Extended Events session.

By making each step of the process a modular component, the ability to test, customize, and maintain this code is far simpler than it would be otherwise. With this structure, it is possible to completely change how an Extended Events session is read without affecting the other building blocks of the process.

The learning curve to using Extended Events via T-SQL is not trivial, but focusing on macro commands rather than individual queries helps make it easier to dive into and become familiar with the process.

As with any homegrown process, customization is essential to getting the most out of it. Microsoft has documented Extended Events quite well:
Quickstart: Extended events in SQL Server – SQL Server | Microsoft Docs

Many writers have shared their tips and tricks for how to capture various events and view the resulting event data. Combining that knowledge with automation makes this into a process that is easy to deploy, manage, and scale, regardless of whether it is needed on one server or one hundred.

 

The post Automating Extended Events data collection appeared first on Simple Talk.

from Simple Talk https://ift.tt/3FfdgK3
via

Template Specs: Storing ARM templates on Azure

Infrastructure as code is evolving in large steps to improve the way we build and manage cloud infrastructure. ARM templates is the Azure way to code the infrastructure, besides the fact it’s evolving towards BICEP.

Since January, a new feature to manage ARM templates is being tested and made available in Azure Portal: ARM Template Specs.

The Template Specs allow us to store ARM templates in Azure Portal and re-use them. These ARM templates will work as a model to build resources in our company cloud environment. In this way, we can set standards for many resources and re-use their ARM templates to make it easier to follow those standards.

Sample Template

Let’s use a sample template for a Log Analytics workspace. It’s possible that your company would like each solution to have its own log analytics workspace, so we can use an ARM template to define one.

A sample template like this can be generated from an existing service in Azure Portal, on the left menu, choose Export Template item and on the following screen we have the option to download the template.

 

 

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "workspace_name": {
            "defaultValue": "logs",
            "type": "String"
        }
    },
    "variables": {},
    "resources": [
        {
            "type": "microsoft.operationalinsights/workspaces",
            "apiVersion": "2021-06-01",
            "name": "[parameters('workspace_name')]",
            "location": "uksouth",
            "properties": {
                "sku": {
                    "name": "pergb2018"
                },
                "retentionInDays": 398,
                "features": {
                    "enableLogAccessUsingOnlyResourcePermissions": true
                },
                "workspaceCapping": {
                    "dailyQuotaGb": -1
                },
                "publicNetworkAccessForIngestion": "Enabled",
                "publicNetworkAccessForQuery": "Enabled"
            }
        }
    ]
}

 

Of course, this template is too simple. It could be way better, for example, include many saved queries defined as a pattern in your company.

A template like this needs to be parameterized, otherwise it would have low to no value. This sample has its name parameterized.

Creating the Template Specs

Creating a template spec is very simple, like any other simple resource in the Azure Cloud. The template spec has a versioning system, so we can control the versions of the template.

 

Once specified the basic information, we edit the template and that’s it, no additional secret.

It would be nice if when editing the template we could load a file from our local machine or if we could link the template spec with a git repository, maybe these could be future resources in this feature.

 

Creating new Versions

We can use the Create New Version button  to create new versions changing the existing ones. If we already have more than a single version the UI  will ask us what version we would like to use as a start point for the new one. We will be able to edit the template in the portal, creating the new version.

 

 

Deploying the Template Spec in the Portal

The deployment using the portal is very simple. Once clicked on Deploy button, we will face some typical questions about the deployment. The only difference is the parameters included on this screen.

 

Deploying the Template Spec in Cloud Shell

We have a few rules we need to follow to deploy the template using Powershell:

• It’s a resource group deployment, we use New-AzResourceGroupDeployment
• The resource group needs to already exist, we are deploying resources inside it
• We need to use the Id of the template spec following some syntax rules
• The parameter can be inline on the powershell statement.
• We can pass the parameter using a parameter file

This example is using the parameter inline:

$id = "/subscriptions/4d72480d-0adb-4df7-b5e3-866c027fe3e0/resourceGroups/Templates/providers/
               Microsoft.Resources/templateSpecs/LogAnalyticsModel/versions/1.0"

New-AzResourceGroupDeployment `
-TemplateSpecId $id `
-ResourceGroupName demoRG `
-workspace_name myLogs

We can upload a parameter file to the cloud shell storage and use it. The parameter file would be like this:

{
"$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentParameters.json#",
"contentVersion": "1.0.0.0",
"parameters": {
      "workspace_name": {
            "value": "myLog"
        }
   }
}

The statement to make the deployment using the parameter file would be like this:

New-AzResourceGroupDeployment `
-TemplateSpecId $id `
-ResourceGroupName demoRG `
-TemplateParameterFile ./parameters.json

Deploying as a Template Link

I believe this is the most useful one. We can use a template link to deploy the template spec as part of another template. By doing so, we can use the template spec to hold pieces of standard services and deploy them together bigger applications.

Let’s consider an example, deploying an Azure SQL Database. We only need to add to the template one additional resource to deploy the template spec:

{
"type": "Microsoft.Resources/deployments",
"apiVersion": "2020-10-01",
"name": "createStorage",
"properties": {
    "mode": "Incremental",
    "templateLink": {
          "id": "/subscriptions/4d72480d-0adb-4df7-b5e3-866c027fe3e0/resourceGroups/Templates/providers/
                Microsoft.Resources/templateSpecs/LogAnalyticsModel/versions/1.0"
      },
    "parameters": {
        "workspace_name": {
               "value": "myLog"
               }
        }
  }
}

In this way we can use the regular template deployment in marketplace to deploy the Azure SQL Database together the Log Analytics workspace specified by the Template Spec.

What do We Miss

There are some details that would be great on this feature but they aren’t present yet:

• Integration with a code repository
• Deployment without specifying version – using the most recent
• Update the deployed services when a new version is available

Let’s see what the future has for us.

What’s around

There are many features that go to a similar result than we have here. There are different reasons to choose than. Let’s have a small summary:

Blueprints

Blueprints go way beyond, creating a link between the definition and the resources deployed. We also can use the template specs together blueprints.

However, blueprints have some strange needs and are considered deprecated. Microsoft is focusing on BICEP

BICEP

Bicep is evolving everyday. However, it doesn’t have a UI yet, it’s all code. It may have many benefits, but with the entire Azure Portal UI integrated with ARM templates, in my humble opinion it’s not time yet to replace ARM by BICEP.

Terraform

Terraform achieves the same result as blueprints, with a language accepted across multiple cloud services. However, even working in Azure it’s not so integrated on Azure Portal as the ARM templates

References

• Template Specs
• Blueprints
• BICEP
• Terraform

The post Template Specs: Storing ARM templates on Azure appeared first on Simple Talk.

from Simple Talk https://ift.tt/3AlyOkF
via