• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

Tag Archives: Generating

OpenAI debuts DALL-E for generating images from text

January 6, 2021   Big Data

Transform 2021

Join us for the world’s leading event about accelerating enterprise transformation with AI and Data, for enterprise technology decision-makers, presented by the #1 publisher in AI and Data

Learn More


OpenAI today debuted two multimodal AI systems that combine computer vision and NLP, like DALL-E, a system that generates images from text. For example, the photo above for this story was generated from the text prompt “an illustration of a baby daikon radish in a tutu walking a dog.” DALL-E uses a 12-billion parameter version of GPT-3, and like GPT-3 is a Transformer language model. The name is meant to hearken to the artist Salvador Dali and the robot WALL-E.

Above: Examples of images generated from the text prompt “A stained glass window with an image of a blue strawberry”

Image Credit: OpenAI

Tests shared by OpenAI today appear to demonstrate DALL-E has the ability to manipulate and rearrange objects in generated imagery but also create things that just don’t exist like a cube with the texture of a porcupine or cube of clouds. Based on text prompt, images generated by DALL-E can appear as if they were taken in the real world, while others can depict works of art. Visit the OpenAI website to try a controlled demo of DALL-E.

Above: cloud cube

“We recognize that work involving generative models has the potential for significant, broad societal impacts. In the future, we plan to analyze how models like DALL·E relate to societal issues like economic impact on certain work processes and professions, the potential for bias in the model outputs, and the longer term ethical challenges implied by this technology,” OpenAI said in a blog post about DALL-E today.

OpenAI also introduced CLIP today, a multimodal model trained on 400 million pairs of images and text collected from the internet. CLIP uses zero-shot learning capabilities akin to GPT-2 and GPT-3 language models.

“We find that CLIP, similar to the GPT family, learns to perform a wide set of tasks during pre-training including object character recognition (OCR), geo-localization, action recognition, and many others. We measure this by benchmarking the zero-shot transfer performance of CLIP on over 30 existing datasets and find it can be competitive with prior task-specific supervised models,” a paper about the model by 12 OpenAI coauthors reads.

Although testing found CLIP was proficient at a number of tasks, testing also found that CLIP falls short in specialization tasks like satellite imagery classification or lymph node tumor detection.

“This preliminary analysis is intended to illustrate some of the challenges that general purpose computer vision models pose and to give a glimpse into their biases and impacts. We hope that this work motivates future research on the characterization of the capabilities, shortcomings, and biases of such models, and we are excited to engage with the research community on such questions,” the paper reads.

OpenAI chief scientist Ilya Sutskever was coauthor of a paper detailing CLIP, and seems to have alluded to the coming release of CLIP when he told deeplearning.ai recently that multimodal models would be a major machine learning trend in 2021. Google AI chief Jeff Dean made a similar prediction for 2020 in an interview with VentureBeat.

The release of DALL-E follows the release of a number of generative models with the power to mimic or distort reality or predict how people paint landscape and still life art. Some, like StyleGAN, have demonstrated a propensity to racial bias.

OpenAI researchers working on CLIP and DALL-E called for additional research into the potential societal impact of both systems. GPT-3 displayed significant anti-Muslim bias and negative sentiment scores for Black people so the same shortcomings could be embedded in DALL-E. A bias test included in the CLIP paper found that the model was most likely to miscategorize people under 20 as criminals or non-human, people classified as men were more likely to be labeled as criminals then people classified as women, and some label data contained in the dataset are heavily gendered.

How OpenAI made DALL-E and additional details will be shared in an upcoming paper. Large language models that use data scraped from the internet have been criticized by researchers who say the AI industry needs to undergo a culture change.

VentureBeat

VentureBeat’s mission is to be a digital townsquare for technical decision makers to gain knowledge about transformative technology and transact.

Our site delivers essential information on data technologies and strategies to guide you as you lead your organizations. We invite you to become a member of our community, to access:

  • up-to-date information on the subjects of interest to you,
  • our newsletters
  • gated thought-leader content and discounted access to our prized events, such as Transform
  • networking features, and more.

Become a member

Let’s block ads! (Why?)

Big Data – VentureBeat

Read More

Generating JSON Document from SQL Query

November 10, 2018   Microsoft Dynamics CRM
JSON blog 300x225 Generating JSON Document from SQL Query

If you are looking for ways to generate JSON files from SQL server data or if you need to pass JSON data to downstream systems, then you are reading the proper blog! This blog describes how to utilize the FOR JSON feature of SQL server to generate JSON data, and eventually JSON file with well-formatted JSON data in it.

Generating JSON data is straightforward. The syntax is as follows:

Select
contactid,
firstname,
lastname
From contact
FOR  JSON  PATH

This is as simple as appending your SQL select query with the FOR JSON clause. Multiple options are available for the structure of the output, including FOR JSON PATH and FOR JSON AUTO. More information can be found here: Microsoft Documentation.

To generate files out of the retrieved JSON data, a little bit of coding is necessary, as SQL returns chunks of rows of JSON data and each row is not a complete JSON. To combine the JSON data retrieved by the FOR JSON clause to generate complete JSON, use a small piece of C# code as follows:

var queryWithForJson = “SELECT … FOR JSON”;
var conn = new SqlConnection(“<connection string>”);
var cmd = new SqlCommand(queryWithForJson, conn);
conn.Open();
var jsonResult = new StringBuilder();
var reader = cmd.ExecuteReader();
if (!reader.HasRows)
{
    jsonResult.Append(“[]”);
}
else
{
    while (reader.Read())
    {
jsonResult.Append(reader.GetValue(0).ToString());
    }
}

One thing to keep in mind is that if the result of the SQL query is too large, the JSON builder might produce a memory exception. To avoid this, open a file and start appending incoming rows into it as the code loops through each retrieved row. Example code snippet:

File.AppendAllText(path, jsonResult);

Hopefully this helps you generate a JSON document from a SQL query.

As always, happy D365’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

Read More

See the KPIs that Dynamics 365 is Generating for Moneris

June 24, 2018   Microsoft Dynamics CRM
Moneris Case Study 1 300x249 See the KPIs that Dynamics 365 is Generating for Moneris

Moneris has 600 users on Microsoft Dynamics 365, managing the customer experience from onboarding and post-sale, to call center and field service interactions. Beyond the customer-facing roles, which make up about half of their end users, Moneris also uses Dynamics 365 for some of their back-office and risk management teams. This ensures that all customer information is tracked and managed in one system. We sat down with Allan Measor, Vice President, Business Transformation and Customer Enablement at Moneris to hear about their experience.

Moneris overview:

062118 1624 SeetheKPIst1 See the KPIs that Dynamics 365 is Generating for Moneris

Why did you choose to start using Dynamics 365 for all new support inquiries immediately following the implementation?

“For a short time, we ran our old ticket system as a reference tool alongside Dynamics 365. We analyzed our old ticket data, and over 80% of our inquiries were one and done, with 90% solved in a week or less. Because of this, we chose not to import our old tickets. In terms of data migration into Dynamics 365, we imported standard items like customer company data, name, address, account numbers, email address, phone numbers, all key payment device data, and work order information. We also authored over 1,900 knowledge articles into Dynamics 365 for reference.”

Why did you choose to be on the Cloud?

“Our legacy systems tended to take longer in terms of features and updates, so the idea that Cloud solutions get features firsts, thereby empowering our organization to be more current, was appealing to us. We also could avoid costly infrastructure investments and gain reliability and redundancy when on the cloud.”

Why did you choose Dynamics 365 for your CRM platform?

“Since we were already using Office 365, our team members were able to easily adjust to the Dynamics 365 and our customers now receive a seamless experience because of our investment in the Microsoft stack. The value of Microsoft’s Canadian data center, the feature richness of Dynamics 365, and the ease of integration into our existing technology footprint were key deciding factors for us.”

Why did you choose PowerObjects as your partner?

“PowerObjects came highly recommended from multiple individuals at Microsoft. We also had several vendor meetings with them, and ultimately liked their collaborative approach. We looked at other big consulting firms but liked the agile and flexible approach that PowerObjects offers, along with their deep Dynamics experience, so we went ahead with PowerObjects and haven’t regretted it once. We also liked that PowerObjects has an office and local staff right here in Toronto.”

What are some of the KPIs that you’ve seen since implementing Dynamics 365?

“We’ve seen a 4% reduction in average handle times (AHT) and a 5% increase in first call resolution (FCR) on top of our already high FCR which was around 80%. We’ve also seen improvements in our onboarding processes for new team members since there are more standardized processes in place, resulting in a decrease in onboarding time and an increase in employee performance. Additionally, we’re experiencing better Interactive Voice Response to Dynamics integration, improving screen pop accuracy for Customer Service Representatives.”

To learn more and read the full case study, click here.

Happy Dynamics 365’ing!

Let’s block ads! (Why?)

PowerObjects- Bringing Focus to Dynamics CRM

Read More

SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

February 13, 2018   BI News and Info

The series so far:

  1. SQL Server Machine Learning Services – Part 1: Python Basics
  2. SQL Server Machine Learning Services – Part 2: Python Data Frames
  3. SQL Server Machine Learning Services – Part 3: Plotting Data with Python
  4. SQL Server Machine Learning Services – Part 4: Finding Data Anomalies with Python
  5. SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

SQL Server Machine Learning Services (MLS) offers a wide range of options for working with the Python language within the context of a SQL Server database. This series has covered a number of those options, with the goal of familiarizing you with some of Python’s many capabilities. One of the most important of these capabilities, along with analyzing data, is being able to visualize data. You were introduced to many of these concepts in the third article, which focused on how to generate individual charts. This article takes that discussion a step further by demonstrating how to generate multiple charts that together provide a complete picture of the underlying data.

The examples in this article are based on data from the Titanic dataset, available as a .csv file from the site https://vincentarelbundock.github.io/Rdatasets/datasets.html. There you can find an assortment of sample datasets, available in both .csv and .doc formats. This is a great resource to keep in mind as you’re trying out various MLS features, whether using the R language or Python.

The Titanic dataset includes a passenger list from the Titanic’s infamous voyage. For each passenger, the data includes the individual’s name, age, gender, service class and whether the passenger survived. The following figure shows a sample of the data.

20screenshots5 stpython5 fig01 png SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

A value of 1 in the Survived column indicates that the passenger survived the voyage. A value of 0 indicates that the passenger did not. The SexCode column is merely a binary duplicate of the Sex column. A value of 0 indicates that the passenger was male, and a value of 1 indicates that the passenger was female.

I created the examples for this article in SQL Server Management Studio (SSMS). Each example retrieves data from a locally saved file named titanic.csv, which contains the Titanic dataset. In most cases, if you’re running a Python script within the context of the SQL Server databases, you’ll likely want to use data from that database, either instead of or in addition to a .csv data, but the principles covered in this article apply regardless of where the data originates, and using a .csv file helps to keep things simple, while demonstrating several important concepts, including how to retrieve data from that file.

Retrieving data from a .csv file

Python, with the help of the pandas module, makes it easy to retrieve data from a .csv file and save the data to a DataFrame object. The pandas module contains a variety of tools for creating and working with data frames, including the read_csv function, which does exactly what the name suggests, reads data from a .csv file.

The Python script in following example uses the function to first retrieve data from the titanic.csv file and then to update the resulting dataset in preparation for grouping the data for the visualizations:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

  DECLARE@pscriptNVARCHAR(MAX);

  SET@pscript=N‘

  # import pandas module

  import pandas as pd

  # define the titanic data frame

  titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0)

  titanic = titanic[titanic[“Age”].notnull()]

  titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”})

  for i, row in titanic.iterrows():

   if (row[“Age”].is_integer() == False):

   titanic.drop(i, inplace=True)

  print(titanic)’;

  EXECsp_execute_external_script

   @language=N‘Python’,

   @script=@pscript;   

  GO

The sp_execute_external_script stored procedure, which is used to run Python and R scripts, should be well familiar to you by now. If you have any questions about the procedure works, refer to the first article in this series. In this article, we focus exclusively on the Python script.

The first statement in the script imports the pandas module and assigns the pd alias:

importpandas aspd

You can then use the pd alias to call the read_csv function, passing in the full path to the titanic.csv file as the first parameter value:

titanic=pd.read_csv(“c:\datafiles\titanic.csv”,index_col=0)

On my system, the titanic.csv file is saved to the c:\datafiles\ folder. Be sure to update the path as necessary if you plan to try out this example or those that follow.

The second function parameter is index_col, which tells the function to use the specified column as the index for the outputted data frame. The value 0 refers to the first column, which is unnamed in the Titanic dataset. Without this parameter, the data frame’s index would be auto-generated.

When the read_csv function runs, it retrieves the data from the file and saves it to a DataFrame object, which, in this case, is assigned to the titanic variable. You can then use this variable to modify the data frame or access its data. For example, the original dataset includes a number of passengers whose ages were unknown, listed as NA in the .csv file. You might choose to remove those rows from the dataset, which you can do by using the notnull function:

titanic=titanic[titanic[“Age”].notnull()]

To use the notnull function, you merely append it to the column name. Notice, however, that you must first call the dataset and then, within brackets, reference the dataset again, along with the column name in its own set of brackets.

Perhaps the bigger issue here is whether it’s even necessary remove these rows. It turns out that over 500 rows have unknown ages, a significant portion of the dataset. (The original dataset includes over 1300 rows.) The examples later in article will use the groupby function to group the data and find the average ages in each group, along with the number of passengers. When aggregating grouped data, the groupby function automatically excludes null values, so in this case, the aggregated results will be the same whether or not you include the preceding statement.

I’ve included the statement here primarily to demonstrate how to remove rows that contain null values. (The subsequent examples also include this statement so they’re consistent as you build on them from one example to the next.) One advantage to removing the rows with null values is that the resulting dataset is smaller, which can be beneficial when working with large datasets. Keep in mind, however, that modifying datasets can sometimes impact the analytics or visualizations based on those datasets. Always be wary of how you manipulate data.

The next statement in the example replaces the numeric values in the Survived column with the values deceased and survived:

titanic[“Survived”]=titanic[“Survived”].map({0:“deceased”,1:“survived”})

To change values in this way, use the map function to specify the old and new values. You must enclose the assignments in curly braces, separating each set with a comma. For the assignments themselves, you separate the old value from the new value with a colon.

The next part of the Python script includes a for statement block that removes rows containing Age values that are not whole numbers:

fori,row intitanic.iterrows():

   if(row[“Age”].is_integer()==False):

   titanic.drop(i,inplace=True)

The Titanic dataset uses fractions for infant ages, and you might decide not to include these rows in your data. As with the notnull function, I’ve included this logic primarily to demonstrate a useful concept, in this case, how you can loop through the rows in a dataset to take an action on each row.

The initial for statement specifies the i and row variables, which represent the current index value and dataset row in each of the loop’s iterations. The statement also calls the iterrows function on the titanic data frame for iterating through the rows.

For each iteration, the for statement block runs an if statement, which evaluates whether the Age value for the current row is an integer, using the is_integer function. If the function evaluates to False, the value is not an integer, in which case the drop function is used to remove that row identified by the current i value. When using the drop function in this way, be sure to include the inplace parameter, set to True, to ensure that the data frame is updated in place, rather than copied.

Once again, you should determine whether you do, in fact, want to remove these rows. In this case, the aggregated passenger totals will be impacted by their removal because there will be eight fewer rows, so be sure to tread carefully when making these sorts of changes.

The final statement in the Python script uses the print function to return the titanic data to the SSMS console:

print(titanic)

The following figure shows a portion of the results. Note that, when returning a dataset that is considered wide, Python wraps the columns, as indicated by the backslash to the right of the column names. As a result, only the index and the first four columns are included in this particular view.

20screenshots5 stpython5 fig02 png SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

If you scroll down the results, you’ll find the remaining columns, similar to those shown in the following figure.

20screenshots5 stpython5 fig03 png SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

Notice that the index value for the first row is 1. This is the first value from the first column from the original dataset. If the index for the titanic data frame had been auto-generated, the first value would be 0.

Grouping and aggregating the data

Once you get the titanic data frame where you want it, you can use the groupby function to group and aggregate the data. The following example uses the function to group the data first by the PClass column, then the Sex column, and finally the Survived column:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

  DECLARE@pscriptNVARCHAR(MAX);

  SET@pscript=N‘

  # import pandas module

  import pandas as pd

  # define the titanic data frame

  titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0)

  titanic = titanic[titanic[“Age”].notnull()]

  titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”})

  for i, row in titanic.iterrows():

   if (row[“Age”].is_integer() == False):

   titanic.drop(i, inplace=True)

  # define the df data frame based on titanic data

  df = titanic.groupby([“PClass”, “Sex”, “Survived”], 

    as_index=False).agg({“Age”: [“mean”, “count”]})

  df[“Age”] = df[“Age”].round(2)

  df.columns = [“class”, “gender”, “status”, “age”, “count”]

  print(df)’;

  EXECsp_execute_external_script

   @language=N‘Python’,

   @script=@pscript;   

  GO

You call the groupby function by tagging it onto the titanic data frame, specifying the three columns on which to base the grouping (in the order that grouping should occur):

df=titanic.groupby([“PClass”,“Sex”,“Survived”], 

as_index=False).agg({“Age”:[“mean”,“count”]})

After specifying the grouped columns and as_index parameter, you add the agg function, which allows you to find the mean and count of the Age column values. (The groupby function is covered extensively in the first three articles of this series, so refer to them for more details about the function’s use.)

The groupby function results are assigned to the df variable. From there, you can use the variable to call the Age column in order to round the column’s values to two decimal points:

df[“Age”]=df[“Age”].round(2)

As you can see, you need only call the round function and pass in 2 as the parameter argument. You can then assign names to the columns in the df data frame:

df.columns=[“class”,“gender”,“status”,“age”,“count”]

That’s all you need to do to prepare the data for generating visualizations. The last statement in the example again uses the print function to return the data from the df data frame to the SSMS console. The following figure shows the results.

20screenshots5 stpython5 fig04 png SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

You now have a dataset that is grouped first by class, then by gender, and finally by the survival status. If the rows with fractional Age values had not been removed, the passenger counts would be slightly different.

Generating a bar chart

From the df data frame you can create multiple charts that provide different insights into the data, but before we dive into those, let’s start with a single chart. The following example creates a bar chart specific to the first-class passengers, showing the average age of the males and females who survived and who did not:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

  DECLARE@pscriptNVARCHAR(MAX);

  SET@pscript=N‘

  # import Python modules

  import matplotlib

  matplotlib.use(“PDF”)

  import matplotlib.pyplot as plt

  import matplotlib.patches as ptch

  import pandas as pd

  # define the titanic data frame

  titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0)

  titanic = titanic[titanic[“Age”].notnull()]

  titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”})

  for i, row in titanic.iterrows():

   if (row[“Age”].is_integer() == False):

   titanic.drop(i, inplace=True)

  # define the df data frame based on titanic data

  df = titanic.groupby([“PClass”, “Sex”, “Survived”], 

    as_index=False).agg({“Age”: [“mean”, “count”]})

  df[“Age”] = df[“Age”].round(2)

  df.columns = [“class”, “gender”, “status”, “age”, “count”]

  # define the subplot data frame based on df data

  df1 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “age”]]

  # define variables for the legend

  dc = ptch.Patch(color=”navy”, label=”deceased”)

  sv = ptch.Patch(color=”darkgreen”, label=”survived”)

  # define the ax1 bar chart

  ax1 = df1.plot.bar(x=”gender”, y=”age”, alpha=.8, 

    color=df1.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax1.set_title(label=”First-class passengers”, y=1.02)

  ax1.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax1.xaxis.label.set_visible(False)

  ax1.set_ylabel(“Average ages”, labelpad=10, fontsize=12)

  ax1.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax1.legend(handles=[dc, sv], loc=”best”)

  # save the bar chart to PDF file

  plt.savefig(“c:\datafiles\titanic1.pdf”, 

    bbox_inches=”tight”, pad_inches=.5)’;

  EXECsp_execute_external_script

   @language=N‘Python’,

   @script=@pscript;   

  GO

The Python script starts with several new import statements related to the matplotlib module, along with the use function, which sets PDF as the backend:

  importmatplotlib

  matplotlib.use(“PDF”)

  importmatplotlib.pyplot asplt

  importmatplotlib.patches asptch

The first three statements should be familiar to you. If they’re not, refer back to the third article. The fourth statement is new. It imports the patches package that is included with the matplotlib module. The patches package contains tools for refining the various charts. In this case, you’ll be using it to customize the chart’s legend.

The next step is to create a data subset based on the df data frame:

df1=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“age”]]

The df1 data frame filters out all rows except those specific to first-class passengers. It also filters out all but the gender, status, and age columns. Filtering out the data in advance helps to simplify the code used to create the actual plot.

The next step is to define the property values for the legend. For this, you use the Patch function that is part of the patches package:

dc=ptch.Patch(color=“navy”,label=“deceased”)

sv=ptch.Patch(color=“darkgreen”,label=“survived”)

The goal is to provide a legend that is consistent with the colors of the chart’s bars, which will be color-coded to reflect whether a group represents passengers that survived or passengers that did not. You’ll use the dc and sv variables when you define your chart’s properties.

Once you have all the pieces in place, you can create your bar chart, using the plot.bar function available to the df1 data frame:

ax1=df1.plot.bar(x=“gender”,y=“age”,alpha=.8, 

color=df1.status.map({“deceased”:“navy”,“survived”:“darkgreen”}))

You’ve seen most of these components before. The chart uses the gender column for the X-axis and the age column for the Y-axis. The alpha parameter sets the transparency to 80%. What’s different here is the color parameter. The value uses the map function, called from the status column, to set the bar’s color to navy if the value is deceased and to dark green if the value is survived.

From here, you can configure the other chart’s properties, using the ax1 variable to reference the chart object:

  ax1.set_title(label=“First-class passengers”,y=1.02)

  ax1.set_xticklabels(labels=df1[“gender”],fontsize=9,rotation=0)

  ax1.xaxis.label.set_visible(False)

  ax1.set_ylabel(“Average ages”,labelpad=10,fontsize=12)

  ax1.grid(color=“slategray”,alpha=.5,linestyle=“dotted”,linewidth=.5)

Most of this should be familiar to you as well. The only new statement is the third one, which uses the set_visible function to set the visibility of the X-axis label to False. This prevents the label from being displayed.

The next step is to configure the legend. For this, you call the legend function, passing in the dc and sv variables as values to the handles parameter:

ax1.legend(handles=[dc,sv],loc=“best”)

Notice that the loc parameter is set to best. This setting lets Python determine the best location for the legend, based on how the bars are rendered.

The final step is to use the savefig function to save the chart to a .pdf file:

plt.savefig(“c:\datafiles\titanic1.pdf”, 

bbox_inches=“tight”,pad_inches=.5)‘;

When you run the Python script, it should generate a chart similar to the one shown in the following figure.

20screenshots5 stpython5 fig05 png SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

The chart includes a bar for each first-class group, sized according to the average age of that group. In addition, each bar is color-coded to match the group’s status, with a legend that provides a key for how the colors are used.

Generating multiple bar charts

The bar chart above provides a straightforward, easy-to-understand visualization of the underlying data. Restricting the results to the first-class group makes it clear how the data is distributed. We could have incorporated all the data from the df dataset into the chart, but that might have made the results less clear, although in this case, one chart might have been okay, given that the df dataset includes only 12 rows.

However, another approach that can be extremely effective when visualizing data is to generate multiple related charts, as shown in the following example:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

  DECLARE@pscriptNVARCHAR(MAX);

  SET@pscript=N‘

  # import Python modules

  import matplotlib

  matplotlib.use(“PDF”)

  import matplotlib.pyplot as plt

  import matplotlib.patches as ptch

  import pandas as pd

  # define the titanic data frame

  titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0)

  titanic = titanic[titanic[“Age”].notnull()]

  titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”})

  for i, row in titanic.iterrows():

   if (row[“Age”].is_integer() == False):

   titanic.drop(i, inplace=True)

  # define the df data frame based on titanic data

  df = titanic.groupby([“PClass”, “Sex”, “Survived”], 

    as_index=False).agg({“Age”: [“mean”, “count”]})

  df[“Age”] = df[“Age”].round(2)

  df.columns = [“class”, “gender”, “status”, “age”, “count”]

  # define the subplot data frames based on df data

  df1 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “age”]]

  df2 = df.loc[(df[“class”] == “2nd”), [“gender”, “status”, “age”]]

  df3 = df.loc[(df[“class”] == “3rd”), [“gender”, “status”, “age”]]

  # define variables for the legend

  dc = ptch.Patch(color=”navy”, label=”deceased”)

  sv = ptch.Patch(color=”darkgreen”, label=”survived”)

  # set up the subplot structure

  fig, (ax1, ax2, ax3) = plt.subplots(nrows=1, 

    ncols=3, sharey=True, figsize=(12,6))

  # define the ax1 subplot

  ax1 = df1.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax1,

    color=df1.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax1.set_title(label=”First-class passengers”, y=1.02)

  ax1.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax1.xaxis.label.set_visible(False)

  ax1.set_ylabel(“Average ages”, labelpad=10, fontsize=12)

  ax1.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax1.legend(handles=[dc, sv], loc=”best”)

  # define the ax2 subplot

  ax2 = df2.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax2,

    color=df2.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax2.set_title(label=”Second-class passengers”, y=1.02)

  ax2.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax2.xaxis.label.set_visible(False)

  ax2.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax2.legend(handles=[dc, sv], loc=”best”)

  # define the ax3 subplot

  ax3 = df3.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax3,

    color=df3.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax3.set_title(label=”Third-class passengers”, y=1.02)

  ax3.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax3.xaxis.label.set_visible(False)

  ax3.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax3.legend(handles=[dc, sv], loc=”best”)

  # save the bar chart to PDF file

  plt.savefig(“c:\datafiles\titanic2.pdf”, 

    bbox_inches=”tight”, pad_inches=.5)’;

  EXECsp_execute_external_script

   @language=N‘Python’,

   @script=@pscript;   

  GO

Much of the code in the Python script is the same as the preceding example, with a few notable exceptions. The first is that the script now defines three subsets of data, one for each service class:

  df1=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“age”]]

  df2=df.loc[(df[“class”]==“2nd”),[“gender”,“status”,“age”]]

  df3=df.loc[(df[“class”]==“3rd”),[“gender”,“status”,“age”]]

The next addition to the script is a statement that creates the structure necessary for rendering multiple charts:

fig,(ax1,ax2,ax3)=plt.subplots(nrows=1, 

ncols=3,sharey=True,figsize=(12,6))

The statement uses the subplots function that is part of the matplotlib.pyplot package to define a structure that includes three charts, positioned in one row with three columns. The nrows parameter determines the number of rows, and the ncols parameter determines the number of columns, resulting in a total of three charts. This will cause the function to generate three AxesSubplot objects, which are saved to the ax1, ax2, and ax3 variables. The function also generates a Figure object, which is saved to the fig variable, although you don’t need to do anything specific with that variable going forward.

The third parameter in the subplots function is sharey, which is set to True. This will cause the Y-axis labels associated with the first chart to be shared across the row with all charts, rather than displaying the same labels for each chart. This is followed with the figsize parameter, which sets this figure’s width and height in inches.

The next step is to define each of three charts. The first definition is similar to the preceding example, with one important difference in the plot.bar function:

ax1=df1.plot.bar(x=“gender”,y=“age”,alpha=.8,ax=ax1,

color=df1.status.map({“deceased”:“navy”,“survived”:“darkgreen”}))

The function now includes the ax parameter, which instructs Python to assign this plot to the ax1AxesSubplot object. This ensures that the plot definition is incorporated into the single fig object.

The remaining chart definitions take the same approach, except that ax1 is swapped out for ax2 and ax3, respectively, and the df1 data frame is swapped out for df2 and df3. The two remaining chart definitions also do not define a Y-axis label. The following figure shows the resulting charts.

20screenshots5 stpython5 fig06 png SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

Notice that only the first chart includes the Y-axis labels and that each chart is specific to the class of service. In addition, the legend is positioned differently within the charts, according to how the bars are rendered.

Breaking data into smaller charts can make the groups easier to grasp and to compare. You can do the same thing with the passenger counts, as shown in the following example:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

  DECLARE@pscriptNVARCHAR(MAX);

  SET@pscript=N‘

  # import Python modules

  import matplotlib

  matplotlib.use(“PDF”)

  import matplotlib.pyplot as plt

  import matplotlib.patches as ptch

  import pandas as pd

  # define the titanic data frame

  titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0)

  titanic = titanic[titanic[“Age”].notnull()]

  titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”})

  for i, row in titanic.iterrows():

   if (row[“Age”].is_integer() == False):

   titanic.drop(i, inplace=True)

  # define the df data frame based on titanic data

  df = titanic.groupby([“PClass”, “Sex”, “Survived”], 

    as_index=False).agg({“Age”: [“mean”, “count”]})

  df[“Age”] = df[“Age”].round(2)

  df.columns = [“class”, “gender”, “status”, “age”, “count”]

  # define the subplot data frames based on df data

  df1 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “count”]]

  df2 = df.loc[(df[“class”] == “2nd”), [“gender”, “status”, “count”]]

  df3 = df.loc[(df[“class”] == “3rd”), [“gender”, “status”, “count”]]

  # define variables for the legend

  dc = ptch.Patch(color=”navy”, label=”deceased”)

  sv = ptch.Patch(color=”darkgreen”, label=”survived”)

  # set up the subplot structure

  fig, (ax1, ax2, ax3) = plt.subplots(nrows=1, 

    ncols=3, sharey=True, figsize=(12,6))

  # define the ax1 subplot

  ax1 = df1.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax1,

    color=df1.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax1.set_title(label=”First-class passengers”, y=1.02)

  ax1.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax1.xaxis.label.set_visible(False)

  ax1.set_ylabel(“Total passenger count”, labelpad=10, fontsize=12)

  ax1.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax1.legend(handles=[dc, sv], loc=”best”)

  # define the ax2 subplot

  ax2 = df2.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax2,

    color=df2.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax2.set_title(label=”Second-class passengers”, y=1.02)

  ax2.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax2.xaxis.label.set_visible(False)

  ax2.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax2.legend(handles=[dc, sv], loc=”best”)

  # define the ax3 subplot

  ax3 = df3.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax3,

    color=df3.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax3.set_title(label=”Third-class passengers”, y=1.02)

  ax3.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax3.xaxis.label.set_visible(False)

  ax3.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax3.legend(handles=[dc, sv], loc=”best”)

  # save the bar chart to PDF file

  plt.savefig(“c:\datafiles\titanic3.pdf”, 

    bbox_inches=”tight”, pad_inches=.5)’;

  EXECsp_execute_external_script

   @language=N‘Python’,

   @script=@pscript;   

  GO

This example is very similar to the preceding one, except that the data subsets have been updated to return the count column, rather than the age column:

  df1=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“count”]]

  df2=df.loc[(df[“class”]==“2nd”),[“gender”,“status”,“count”]]

  df3=df.loc[(df[“class”]==“3rd”),[“gender”,“status”,“count”]]

You must also update the plot.bar function calls that defines the subplots to reflect that the count column should be used for Y-axis:

ax1=df1.plot.bar(x=“gender”,y=“count”,alpha=.8,ax=ax1,

color=df1.status.map({“deceased”:“navy”,“survived”:“darkgreen”}))

Also, be sure to update the Y-axis labels:

ax1.set_ylabel(“Total passenger count”,labelpad=10,fontsize=12)

Other than these changes, the rest of the script is the same. Your charts should now look like those shown in the following figure:

20screenshots5 stpython5 fig07 png SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

Although the charts are similar to those in the preceding example, the data is much different. However, you can still easily find meaning in that data.

Generating multiple types of bar charts

You can also mix things up, providing charts that reflect both the average ages and passenger counts within one figure. You need only do some rearranging and make some additions, as shown in the following example:

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

  DECLARE@pscriptNVARCHAR(MAX);

  SET@pscript=N‘

  # import Python modules

  import matplotlib

  matplotlib.use(“PDF”)

  import matplotlib.pyplot as plt

  import matplotlib.patches as ptch

  import pandas as pd

  # define the titanic data frame

  titanic = pd.read_csv(“c:\datafiles\titanic.csv”, index_col=0) # 1313 rows

  titanic = titanic[titanic[“Age”].notnull()] # 756 rows

  titanic[“Survived”] = titanic[“Survived”].map({0:”deceased”, 1:”survived”})

  for i, row in titanic.iterrows():

   if (row[“Age”].is_integer() == False):

   titanic.drop(i, inplace=True) # 748 rows

  # define the df data frame based on titanic data

  df = titanic.groupby([“PClass”, “Sex”, “Survived”], 

    as_index=False).agg({“Age”: [“mean”, “count”]})

  df[“Age”] = df[“Age”].round(2)

  df.columns = [“class”, “gender”, “status”, “age”, “count”]

  # define the subplot data frames based on df data

  df1 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “age”]]

  df2 = df.loc[(df[“class”] == “1st”), [“gender”, “status”, “count”]]

  df3 = df.loc[(df[“class”] == “2nd”), [“gender”, “status”, “age”]]

  df4 = df.loc[(df[“class”] == “2nd”), [“gender”, “status”, “count”]]

  df5 = df.loc[(df[“class”] == “3rd”), [“gender”, “status”, “age”]]

  df6 = df.loc[(df[“class”] == “3rd”), [“gender”, “status”, “count”]]

  # define variables for the legend

  dc = ptch.Patch(color=”navy”, label=”deceased”)

  sv = ptch.Patch(color=”darkgreen”, label=”survived”)

  # set up the subplot structure

  fig, ((ax1, ax2), (ax3, ax4), (ax5, ax6)) = plt.subplots(nrows=3, 

    ncols=2, sharex=True, figsize=(14,12))

  # define the ax1 subplot

  ax1 = df1.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax1,

    color=df1.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax1.set_title(label=”First-class passengers”, y=1.02)

  ax1.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax1.xaxis.label.set_visible(False)

  ax1.set_ylabel(“Average ages”, labelpad=5, fontsize=12)

  ax1.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax1.legend(handles=[dc, sv], loc=”best”)

  # define the ax2 subplot

  ax2 = df2.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax2,

    color=df2.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax2.set_title(label=”First-class passengers”, y=1.02)

  ax2.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax2.xaxis.label.set_visible(False)

  ax2.set_ylabel(“Total passenger count”, labelpad=5, fontsize=12)

  ax2.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax2.legend(handles=[dc, sv], loc=”best”)

  # define the ax3 subplot

  ax3 = df3.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax3,

    color=df3.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax3.set_title(label=”Second-class passengers”, y=1.02)

  ax3.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax3.xaxis.label.set_visible(False)

  ax3.set_ylabel(“Average ages”, labelpad=5, fontsize=12)

  ax3.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax3.legend(handles=[dc, sv], loc=”best”)

  # define the ax4 subplot

  ax4 = df4.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax4,

    color=df4.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax4.set_title(label=”Second-class passengers”, y=1.02)

  ax4.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax4.xaxis.label.set_visible(False)

  ax4.set_ylabel(“Total passenger count”, labelpad=5, fontsize=12)

  ax4.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax4.legend(handles=[dc, sv], loc=”best”)

  # define the ax5 subplot

  ax5 = df5.plot.bar(x=”gender”, y=”age”, alpha=.8, ax=ax5,

    color=df5.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax5.set_title(label=”Third-class passengers”, y=1.02)

  ax5.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax5.xaxis.label.set_visible(False)

  ax5.set_ylabel(“Average ages”, labelpad=5, fontsize=12)

  ax5.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax5.legend(handles=[dc, sv], loc=”best”)

  # define the ax6 subplot

  ax6 = df6.plot.bar(x=”gender”, y=”count”, alpha=.8, ax=ax6,

    color=df6.status.map({“deceased”:”navy”, “survived”:”darkgreen”}))

  ax6.set_title(label=”Third-class passengers”, y=1.02)

  ax6.set_xticklabels(labels=df1[“gender”], fontsize=9, rotation=0)

  ax6.xaxis.label.set_visible(False)

  ax6.set_ylabel(“Total passenger count”, labelpad=5, fontsize=12)

  ax6.grid(color=”slategray”, alpha=.5, linestyle=”dotted”, linewidth=.5)

  ax6.legend(handles=[dc, sv], loc=”best”)

  # save the bar chart to PDF file

  plt.savefig(“c:\datafiles\titanic4.pdf”, 

    bbox_inches=”tight”, pad_inches=.5)’;

  EXECsp_execute_external_script

   @language=N‘Python’,

   @script=@pscript;   

  GO

This time around, the script includes six subsets of data, three specific to age and three specific to passenger count:

  df1=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“age”]]

  df2=df.loc[(df[“class”]==“1st”),[“gender”,“status”,“count”]]

  df3=df.loc[(df[“class”]==“2nd”),[“gender”,“status”,“age”]]

  df4=df.loc[(df[“class”]==“2nd”),[“gender”,“status”,“count”]]

  df5=df.loc[(df[“class”]==“3rd”),[“gender”,“status”,“age”]]

  df6=df.loc[(df[“class”]==“3rd”),[“gender”,“status”,“count”]]

The idea here is to include each class of service in its own row, giving us a figure with three rows and two columns. To achieve this, you must update the subplots function call to reflect the new structure:

fig,((ax1,ax2),(ax3,ax4),(ax5,ax6))=plt.subplots(nrows=3, 

ncols=2,sharex=True,figsize=(14,12))

In addition to the updated nrows and ncols parameter values, the statement also includes six subplot variables, rather than three, with the variables grouped together by row. In addition, the sharey parameter has been replaced with the sharex parameter and the figure size increased.

The next step is to define the six subplots, following the same structure as before, but providing the correct data subsets, subplot variables, column names and labels. The result is a single .pdf file with six charts, as shown in the following figure.

20screenshots5 stpython5 fig08 png SQL Server Machine Learning Services – Part 5: Generating multiple plots in Python

The challenge with this approach is that each chart scales differently. Mixing charts in this way can make it difficult to achieve matching scales. On the plus side, you’re able to visualize a great number of details within a collected set of charts, while still making it possible to quickly compare and understand the data.

The approach taken in this example is not the only way to create these charts. For instance, you might be able to set up a looping structure or use variables for repeated parameter values to simplify the subplot creation, but this approach allows you to see the logic behind each step in the chart-creation process. Like any other aspect of Python, there are often several ways to achieve the same results.

Python’s expansive world

As datasets become larger and more complex, the need is greater than ever for tools that can present the data to various stakeholders in ways that are both meaningful and easy to understand. SQL Server’s support for Python and the R language, along with the analytic and visualization capabilities they bring, could prove an invaluable addition to your arsenal for working with SQL Server data.

When it comes to visualizations in particular, Python has a great deal to offer, and what we’ve covered so far barely scratches the surface. Of course, we could say the same thing about most aspects of Python. It is a surprisingly extensive language that is both intuitive and flexible, and can be useful in a wide range of circumstances. Being able to work with Python within the context of a SQL Server database makes it all the better.

Let’s block ads! (Why?)

SQL – Simple Talk

Read More

Generating HTML from SQL Server Queries

April 25, 2017   BI News and Info

You can produce HTML from SQL because SQL Server has built-in support for outputting XML, and HTML is best understood as a slightly odd dialect of XML that imparts meaning to predefined tags. There are plenty of edge cases where an HTML structure is the most obvious way of communicating tables, lists and directories. Where data is hierarchical, it can make even more sense. William Brewer gives a simple introduction to a few HTML-output techniques.

Can you produce HTML from SQL? Yes, very easily. Would you ever want to? I certainly have had to. The principle is very simple. HTML is really just a slightly odd dialect of XML that imparts meaning to predefined tags. SQL Server has built-in ways of outputting a wide variety of XML. Although I’ve had in the past to output entire websites from SQL, the most natural thing is to produce HTML structures such as tables, lists and directories.

HTML5 can generally be worked on in SQL as if it were an XML fragment. XML, of course, has no predefined tags and is extensible, whereas HTML is designed to facilitate the rendering and display of data. By custom, it has become more forgiving than XML, but in general, HTML5 is based on XML.

Generating Tables from SQL expressions.

In HTML5, tables are best done simply, but using the child elements and structures so that the web designer has full control over the appearance of the table. CSS3 allows you to specify sets of cells within a list of child elements. Individual TD tags, for example, within a table row (TR) can delimit table cells that can have individual styling, but the rendering of the table structure is quite separate from the data itself.

The table starts with an optional caption element, followed by zero or more colgroup elements, followed optionally by a thead element. This header is then followed optionally by a tfoot element, followed by either zero or more tbody elements or one or more tr elements, followed optionally by a tfoot element, but there can be only one tfoot element.

The HTML5 ‘template’ for tables

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

<table>

<caption></caption>

<colgroup></colgroup>1..n

<thead>

<tr>

<th></th>1..n

</tr>1..n

</thead>

<tfoot>

<tr>

<td></td>1..n

</tr>1..n

</tfoot>

<tbody>

<tr>

<td></td>1..n

</tr>1..n

</tbody>1..n

</table>

In SQL Server, one can create the XML for a table like this with this type of query which is in the form of a template with dummy data.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SELECT

    (SELECT‘Table I’FORXMLPATH(”),TYPE)AS‘caption’,

    (SELECT‘first’ASth,‘second’ASthFORXMLraw(‘tr’),ELEMENTS,TYPE)AS‘thead’,

    (SELECT‘sum’ASth,‘twenty’ASthFORXMLraw(‘tr’),ELEMENTS,TYPE)AS‘tfoot’,

    (SELECTF.unusAStd,F.duoAStd

       FROM

         (VALUES

            (‘one’,‘two’),

            (‘three’,‘four’),

            (‘five’,‘six’),

            (‘seven’,‘eight’)

         )F(unus,duo)

    FORXMLRAW(‘tr’),ELEMENTS,TYPE

    )AS‘tbody’

  FORXMLPATH(”),ROOT(‘table’)

Which produces (after formatting it nicely) this

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

    

    

      

        

        

      

    

    

      

        

        

      

    

    

      

        

        

      

      

        

        

      

      

        

        

      

      

        

        

      

    

  

Table I
first second
sum twenty
one two
three four
five six
seven eight

So, going to AdventureWorks, we can now produce a table that reports on the number of sales for each city, for the top thirty cities.

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

SELECTCONVERT(NVARCHAR(MAX),(SELECT

    (SELECT‘Top Thirty Sales per City’FORXMLPATH(”),TYPE)AS‘caption’,

    (SELECT‘Sales’ASth,‘City’ASthFORXMLRAW(‘tr’),ELEMENTS,TYPE)AS‘thead’,

    (

    SELECTTOP30COUNT(*)AStd,

      a.City+‘, ‘+sp.Name+‘, ‘+cr.NameAStd

      FROMSales.SalesOrderHeaderASsoh

        JOINPerson.AddressASa

          ONa.AddressID=soh.BillToAddressID

        JOINPerson.StateProvinceASsp

          ONsp.StateProvinceID=a.StateProvinceID

        JOINPerson.CountryRegionAScr

          ONcr.CountryRegionCode=sp.CountryRegionCode

      GROUPBYa.City+‘, ‘+sp.Name+‘, ‘+cr.Name

      ORDERBYCOUNT(*)DESC

    FORXMLRAW(‘tr’),ELEMENTS,TYPE

    )AS‘tbody’

  FORXMLPATH(”),ROOT(‘table’)));

I’ve left out the tfoot row because I didn’t need that. Likewise colgroup. I use tfoots mostly for aggregate lines, but you are limited to one only at the end, so it is not ideal for anything other than a simple ‘bottom line’.

When this is placed within and html file, with suitable CSS, it can look something like this

word image 28 Generating HTML from SQL Server Queries

Generating directory lists from SQL expressions.

The HTML is for rendering name-value groups such as dictionaries, indexes, definitions, questions and answers and lexicons. The name-value group consists of one or more names (dt elements) followed by one or more values (dd elements). Within a single dl element, there should not be more than one dt element for each name.

We’ll take as an example an excerpt from the excellent SQL Server glossary

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

SELECTF.TermASDT,F.DefinitionASDD

    FROM

      (

      VALUES

        (‘cache aging’,

          ‘The mechanism of caching that determines when a cache row is outdated and must be refreshed’

        ),

        (‘cache client’,

          ‘A .NET application that uses the Windows Server AppFabric client APIs to communicate with and store data to a Windows Server AppFabric distributed cache system.’

        ),

        (‘cache cluster’,

          ‘The instantiation of the distributed cache service, made up of one or more instances of the cache host service working together to store and distribute data. Data is stored in memory to minimize response times for data requests. This clustering technology differs from Windows Clustering.’

        ),

        (‘cache invalidation’,

          ‘The process of flagging an object in the cache so that it will no longer be used by any cache clients. This occurs when an object remains in cache longer than the cache time-out value (when it expires).’

        ),

        (‘cache item’,

          ‘An object that is stored in the cache and additional information associated with that object, such as tags and version. It can be extracted from the cache cluster using the GetCacheItem client API.’

        ),

        (‘cache notification’,

          ‘An asynchronous notification that can be triggered by a variety of cache operations on the cache cluster. Cache notifications can be used to invoke application methods or automatically invalidate locally cached objects.’

        ),

        (‘cache operation’,

          ‘An event that occurs on regions or cached items that can trigger a cache notification.’

        ),

        (‘cache port’,

          ‘A TCP/IP port used by cache hosts to transmit data to and from the cache clients. The port number used for the cache port can be different on each cache host. These settings are maintained in the cluster configuration settings.’

        ),

        (‘cache region’,

          ‘A container of data, within a cache, that co-locates all cached objects on a single cache host. Cache Regions enable the ability to search all cached objects in the region by using descriptive strings, called tags.’

        ),

        (‘cache service’,

          ‘The distributed, in-memory caching solution that enables users to build highly scalable and responsive applications by bringing data closer to end users.’

        ),

        (‘cache tag’,

          ‘One or more optional string-based identifiers that can be associated with each cached object stored in a region. Regions allow you to retrieve cached objects based on one or more tags.’

        ),

        (‘cache-aside programming pattern’,

          ‘A programming pattern in which if the data is not present in the cache, the application, not the distributed cache system, must reload data into the cache from the original data source.’

        ),

        (‘cache-enabled application’,

          ‘An application that uses the Windows Server AppFabric cache client to store data in cache on the cache cluster.’

        )

      )F(Term,Definition)

  FORXMLRAW(”),ROOT(‘DL’),ELEMENTS,TYPE;

This produces a directory list which can be rendered as you wish

word image 29 Generating HTML from SQL Server Queries

Generating hierarchical lists from SQL expressions.

HTML Lists represent probably the most useful way of passing simple hierarchical data to an application. You can actually use directories (DLs) to do this for lists name-value pairs and even tables for more complex data. Here is a simple example of a hierarchical list, generated from AdventureWorks. You’d want to use a recursive CTE for anything more complicated.

1

2

3

4

5

6

7

8

9

10

11

12

SELECTCONVERT(NVARCHAR(MAX),

           (SELECTf.[Group]ASli,

              (SELECTg.NameASli

                 FROMSales.SalesTerritoryg

                 WHEREg.[Group]=f.[Group]

              FORXMLRAW(”),ROOT(‘ul’),ELEMENTS,TYPE

              )

              FROMSales.SalesTerritoryf

              GROUPBYf.[Group]

           FORXMLRAW(”),ROOT(‘ul’),ELEMENTS,TYPE

           )

        );

…giving…

word image 30 Generating HTML from SQL Server Queries

Conclusions

There are quite a few structures now in HTML5. Even the body tag has subordinate header, nav, section, article, aside, footer, details and summary tags. If you read the W3C Recommendation it bristles with good suggestions for using markup to create structures. The pre tag can use child code, samp and kbd tags to create intelligent formatting. Data in SQL Server can easily generate this sort of structured HTML5 markup. This has obvious uses in indexes, chaptering, glossaries as well as the obvious generation of table-based reports. There is quite a lot of mileage in creating HTML from SQL Server queries

Let’s block ads! (Why?)

SQL – Simple Talk

Read More

Demand Generation 101: 7 Tactics For Generating High Quality Leads

May 24, 2016   CRM News and Info
what does DG look like Demand Generation 101: 7 Tactics For Generating High Quality Leads

Not all nurtured leads are always going to be sales ready. But if they aren’t ready to buy now, that doesn’t mean that they won’t ever buy. Sales can return those leads back to marketing for further nurturing. Marketing can re-engage those leads, and try to again reach that benchmark level of interaction to return them back to sales for further conversations.

3 parts to demand generation programs

There are three main parts to successful demand generation programs: the players, the methods, and the goals.

  • The players

Demand gen should be a collaborative activity between your company’s sales and marketing teams. Some of the players you can expect to see have job titles such as demand gen marketer, marketing operations, or marketing technologist. On the sales side typical titles include sales operations or sales managers. “The sales people play a key role in helping to define these processes and maintain an open line of communication with the marketing team members, which really makes a huge difference,” Rosin said.

  • The methods

Demand generation uses coordinating multi-tiered marketing activities to identify and engage buyers through targeted inbound and outbound activities. There are seven key tactics or methods of demand generation: web insights and inbound marketing, content marketing, social media engagement, lead nurturing, lead scoring, measuring and optimization, and sales and marketing alignment. We’ll dive deeper into each of these methods in a bit.

Your goal should be to have a well-oiled demand generation process that improves lead quality, accelerates the buying cycle, and improves conversion rates from your initial inquiries to qualified opportunities.

“The most important goal, however, is to generate higher revenue from your marketing source leads,” said Janelle Johnson, Act-On’s Senior Director for Global Segment Marketing. “What it all boils down to is quality, not quantity.”

Pumping your pipeline full of high volume with low quality leads, isn’t gonna help your company, especially in the long run. And it can definitely deteriorate your relationship with your sales team. You really have to focus your time and energy on generating those high quality leads that will convert in order to truly achieve demand generation success.

3 challenges for today’s marketer

Generating those high quality leads is made tougher by three challenges for today’s marketer: empowered buyers, high expectations, and pressure to perform.

  • Empowered buyers

Consumers are now in the driver’s seat in their buying journey. In fact, 40 percent of buyers wait longer than they did in the past to initiate contact with a vendor, and buyers typically get 60 percent through the buying journey before they engage with a salesperson. Today’s marketers have to find a way to position their companies as trusted advisors to buyers as they move along their journey. With 58 percent of buyers spending more time researching their options than they did in the past, it’s imperative that you engage with your buyers by building relationships and trust. Because if you don’t, you better believe that your competitors will. Read about proven strategies, in this free eBook: 10 Ways to Nurture the Buyer’s Journey.

  • Higher expectations

With so much access to information and choices, buyers expect more from the companies with whom they interact. Today’s buyers live in an always-on world, filled with instantly available, highly personalized apps, messages, offers, and services. That’s pretty incredible, but it also challenges us as marketers to anticipate the needs of our consumers and deliver thoughtful content related to their needs. Cookie-cutter marketing content and one-size-fits all communication does not work in this new environment. Check out this infographic to see the new buyer’s funnel.

  • Pressure to perform

With all the technology available at our fingertips, we can now tie most of our marketing efforts to revenue. It’s becoming more and more common for marketing to have a revenue goal, just like sales. A well-planned demand gen process can address these challenges by offering an efficient and reliable process to identify and engage these empowered buyers and turn quality leads into revenue that’s measured.

Let’s block ads! (Why?)

Act-On Marketing Blog

Read More

The Simple, 3-Step Process to Generating Leads and Revenue through Social Media Channels

August 22, 2015   CRM News and Info

Social Media Icons chalk 300x136 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

Social media is a huge opportunity for businesses to spread their message to a large number of people in a relatively short amount of time. However, businesses now pay for exposure through sponsored posts and ads that don’t always produce the results they hope for. Facebook click rates are unimpressive, LinkedIn sponsored posts are quite expensive, and tweets get lost in the mix.

So first, how do you drive more traffic to your site through social channels? And second, how do you convert these visitors who do end up on your site into email leads and customers? Below, I’ve laid out a straightforward process that will generate tangible results from your social media marketing campaigns.

Step 1: Give People a Reason to Visit Your Site

Before you can acquire new leads, you need to get people onto your website. Social channels should be used as a way to get your marketing message and content in front of a larger audience. The end goal here is to provide a solid reason for people to click on your post/ad and get them to land on your site. Let’s look at a few ways that you can drive people to your website.

Actionable Content

Successful social posts provide useful information that individuals and businesses can actually use. “How to” guides and lists of tips will drive the most social engagement and will entice visitors to your website, giving you the opportunity to convert them into new leads. Keep attention span in mind when developing content. Whether it’s short or long form content, make sure it’s scannable and provides value to even the casual reader who’s just skimming subheads. Craft intriguing headlines and use examples whenever applicable.

Webinars

Webinars are a fantastic way to appeal to high-value leads on social platforms. Typically, you can ask for additional information besides an email address and gain insight on what benefit your products and services can provide them. Since this is a long form of content, develop actionable titles that individuals will want to engage with. Instead of using “Best Practices for Marketing Overlays” try something like “10 Tips to make your Marketing Overlays Pop!”

Contests

This one is a no-brainer for any business that wants to see incredible email opt-in results. Select an item of value (this could be a product or service package). Then prompt website visitors to submit their email for an opportunity to win that item. The great thing about this tactic is it’s very appealing on social platforms. The offer is attractive and the process to enter is straightforward for individuals.

In this ecommerce example, The Fifth Watches offers visitors a package of goodies upon arrival.

Untitled 690x1024 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

Sales/Discounts

Running a sale or offering a special discount? Advertise it on social media. According to Forrester Research, 100% of B2B decision makers use social media for work. This is a tremendous opportunity to sway these decision makers with attractive savings. Sometimes it just takes a bit of an extra push to get someone’s business and sales/discounts are a proven way to do just that.

Step 2: Drive people from social channels to relevant pages on your website

This step has to do with aligning the proper path for an individual to arrive on your site, making it more likely that they will perform the desired action that you have in mind. It starts with your social post, company profile, or advertisement. Clearly state what you are posting about and why it is beneficial. Headlines like “How to Generate More Revenue with Transactional Emails” or “5 Simple Email Headline Tweaks that will Increase Open Rates” tend to do better in social posts because they are actionable and offer an immediate benefit. Also, include an image within your social post. Images help your post stand out and will increase engagement (as noted in this article from Social Media Examiner).

Marketing Land does an excellent job with their social posts by crafting enticing headlines and also including an image. These two aspects can rapidly increase social post engagement. Here’s how they execute on Facebook:

Untitled1 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

…and the same post looks much the same on Twitter:

3 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

…and in both cases the call to action takes you to their website, and very obvious encouragement to sign up:

4 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

Next, choose where you want to send visitors once they’ve clicked on the ad or post. Send them to a page that‘s relevant to what they clicked on. If they clicked on an ad for a free eBook, then send them to a landing page where they can get the eBook. Make it easy for visitors to find exactly what they want and expect.

Step 3: Engage visitors in an effective manner

If you want to convert visitors then you have to be the proactive one. According to experts at Chartbeat, 55% of visitors spend fewer than 15 seconds on your website. People have short attention spans and can leave your site in the blink of an eye, so capitalize on the opportunity that’s at hand! Here are some effective ways to engage onsite visitors in order to drive conversions.

Simple Landing Pages

Landing pages are obviously a key component of lead generation, yet many businesses are still missing the boat. The first step of building a landing page is to determine what its sole purpose is. If you want to get visitors to fill out a form to receive an eBook, then design this page to drive this action. There is no added value by including distracting links to other areas of your website. Keep it simple by creating a clear path for visitors to sign up and download your eBook.

Here’s a fantastic landing page example from ZenPayroll. The form is front and center allowing visitors to sign up for free. Additional information like testimonials, features, and pricing is accessible lower down on the page so it does not distract the visitor from the initial sign up form.

5 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

Overlays

The most effective way to engage individuals on your site is with overlays. Overlays (whether over video, a web page, or a graphic), or float your message over the top of prime real estate to grab people’s attention and provide businesses with many unique ways to engage incoming traffic. Perhaps the largest advantage that overlays provide marketers is the ability to target segments of traffic with specific messages and offers. This allows you to hit visitors with more relevant information, convert more incoming social traffic, and maximize ROI from social PPC campaigns.

The Fifth Watches uses an overlay to collect the emails from visitors who enter the contest giveaway. Since they only sell on specific days each month, they rely heavily on reaching customers and leads through email marketing. The use of overlays has enabled them to collect an average of 500 new email subscribers on a daily basis!

6 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

Side Tabs

For a more subtle way to engage visitors, consider using a side tab. Visitors can click a side tab which would then open an overlay where they can opt-in easily. This can be a fun way to market to website visitors if you get a little creative. Here’s a tasty side tab for a promotion from the web’s top bacon retailer, Bacon Freak:

7 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

The rasher of bacon slides in on the right, and says “Click for a tasty offering.” If you click, you’re served a tempting offer:

8 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

Exit Offers

Exit offers are an incredibly effective tactic for acquiring more email subscribers, decreasing bounce rates, and saving shopping cart abandonment. Engage visitors right before they leave your site for another chance for a conversion. To prevent cart abandonment directly, set up an exit offer for shoppers that are directly leaving from your cart and checkout pages.

Bill for First Lady uses this hilarious exit offer to prevent shoppers from leaving with items in their cart. By making the promo code available, they can directly drive purchases from the cart page.

9 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

Social channels are beneficial to businesses by providing ways to reach a lot of people. However, the inefficiencies with social media marketing make it difficult to create engaging relationships. By focusing on converting social traffic into email leads, you open a direct channel to market to these individuals … which increases the likelihood of getting their business.

blog leads are better thumb 250x249 The Simple, 3 Step Process to Generating Leads and Revenue through Social Media Channels

To learn more about how to create revenue from your social media campaigns, take a look at our eBook Likes are Great. Leads are Better. You’ll more about the individual twists each platform requires, how to budget your campaigns, and how to segment and nurture.

Related Blog Posts

This entry passed through the Full-Text RSS service – if this is your content and you’re reading it on someone else’s site, please read the FAQ at fivefilters.org/content-only/faq.php#publishers.

Marketing Action Blog – Act-On

Read More
  • Recent Posts

    • Lucas Brothers To Write And Star In Semi-Autobiographical Comedy For Universal
    • NortonLifeLock’s AI-powered smartphone app blurs out sensitive information in photos
    • WHEN IDEOLOGY TRUMPS TRUTH
    • New Customer Experience Needs and Commerce Trends for 2021
    • A data transformation problem in SQL and Scala: Dovetailing declarative solutions
  • Categories

  • Archives

    • February 2021
    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited