Category Archives: BI News and Info

Representing a simple hierarchical list in SQL Server with JSON, YAML, XML and HTML.

How difficult can it be to produce a simple hierarchical list in JSON, YAML, XML and HTML from a SQL Server table that represents a simple hierarchy within an organisation. Well once you know, it is easy and William Brewer is on a mission to tell you how

JSON, XML, YAML and HTML are great for recording hierarchies such as organisations, taxonomies, and parts lists. How do we output structured document fragments to show a hierarchical list using SQL? I was hoping that the advent of JSON to SQL Server would make this easier but I found its use frustrating to the point that I keep it as arms-length as possible.

Because I would have found it useful myself, I’ve recorded here how to use T-SQL to get the four main types of document types to represent a simple hierarchical list in SQL Server.

First, before we do anything else, we’ll create some test data. In this example, I’ll steal the employee hierarchy from AdventureWorks2014, and put it in a test table.

First, we’ll tackle a JSON rendering of the hierarchy

We can now try it out …

To get this JSON rendition (after prettifying to make it easier to read)

So, emboldened, we try YAML. It turns out to be very easy as there is no support for it in SQL Server

This will render the same manager and his reports even more simply, and doesn’t need prettifying to understand

This, when executed gives the following YAML document …

The XML version is pretty simple

Giving the following XML (prettified to make it easier to read) …

And finally, for the sake of completeness here is the HTML List version

Which can be executed like this

…to give this HTML fragment …

So here we have it, all four commonly-used document types used for hierarchical lists, output from SQL Server. These are fairly simple to elaborate, and apologies in advance for any errors.

Let’s block ads! (Why?)

SQL – Simple Talk

Plot the results of a nonlinear system of equations

I have this system of four nonlinear equations:
pzaU7 Plot the results of a nonlinear system of equations

the unknowns are a1, a2, gamma1 and gamma 2. all other parameters are known. What I want is to plot the a1 vs sigma2 as below:
9luQC Plot the results of a nonlinear system of equations

I tried findinstance but it takes a lot of time to find the results for any value of sigma2, Let alone plotting a1 for different values of sigma2!
Can anybody help me?
Thanks in advance

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

Released: System Center Management Pack for SQL Server, Replication, AS, RS, Dashboards (6.7.31.0)

We are happy to announce new updates to SQL Server Management Pack family!

  • Microsoft System Center Management Pack for SQL Server enables the discovery and monitoring of SQL Server Database Engines, Databases, SQL Server Agents, and other related components.

Microsoft System Center Management Pack for SQL Server

Microsoft System Center Management Pack for SQL Server 2014

Microsoft System Center Management Pack for SQL Server 2016

  • Microsoft System Center Management Pack for SQL Server Replication enables the monitoring of Replication as set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Microsoft System Center Management Pack for SQL Server 2008 Replication

Microsoft System Center Management Pack for SQL Server 2012 Replication

Microsoft System Center Management Pack for SQL Server 2014 Replication

Microsoft System Center Management Pack for SQL Server 2016 Replication

  • Management Pack for SQL Server Analysis Services enables the monitoring of Instances, Databases and Partitions.

Microsoft System Center Management Pack for SQL Server 2008 Analysis Services

Microsoft System Center Management Pack for SQL Server 2012 Analysis Services

Microsoft System Center Management Pack for SQL Server 2014 Analysis Services

Microsoft System Center Management Pack for SQL Server 2016 Analysis Services

  • Management Pack for SQL Server Reporting Services (Native Mode) enables the monitoring of Instances and Deployments.

Microsoft System Center Management Pack for SQL Server 2008 Reporting Services (Native Mode)

Microsoft System Center Management Pack for SQL Server 2012 Reporting Services (Native Mode)

Microsoft System Center Management Pack for SQL Server 2014 Reporting Services (Native Mode)

Microsoft System Center Management Pack for SQL Server 2016 Reporting Services (Native Mode)

  • Management Pack for SQL Server Dashboards

Microsoft System Center Management Pack for SQL Server Dashboards

Please see below for the new features and improvements. More detailed information can be found in guides that can be downloaded from the links above.

Important Notes About This Release

  • Please note that we implemented a new alerting rule for SQL Server event #18456 “Login failed” in this release. However, this rule may become rather noisy due to the fact that the target events appear quite frequently in some environments. The rule is enabled by default; thus, if it creates too many alerts in your environment, consider disabling it via the override. The ideal would be to resolve the underlying issues causing the alert but we understand this is not always a priority.
  • We are introducing a new monitor in this release (see the first bullet below) based on customer feedback. You have now a better solution for monitoring backups of AG databases.

New SQL Server 2008-2016 MP Features and Fixes (6.7.31.0)

  • Added new “Availability Database Backup Status” monitor in Availability Group to check the existence and age of the availability database backups (this monitor is disabled by default)
  • “Database Backup Status” monitor has been changed to return only “Healthy” state for the databases that are Always On replicas, since availability database backups are now watched by the dedicated monitor
  • Improved performance of DB Space monitoring workflows
  • Added new “Login failed” alerting rule for SQL Server event #18456
  • Fixed issue: “Active Alerts” view does not show all alerts
  • Fixed issue: DB space monitoring scripts fail with “Cannot connect to database” error.
  • Fixed issue: PowerShell scripts fail with “Cannot process argument because the value of argument ‘obj’ is null” error
  • Fixed issue: Alert description of “Disk Ready Latency” and “Disk Write Latency” monitors displays the sample count instead of the performance value that was measured
  • Fixed issue: Different file location info from “sys.master_files” and “sysfiles” causes error when Availability Group secondary database files are in different path
  • Fixed issue: “DB Transaction Log Free Space Total” rules return wrong data
  • Introduced minor updates to the display strings
  • Deprecated “Garbage Collection” monitor and the appropriate performance rule
  • Resource Pool Discovery is disabled by default for pools not containing databases with Memory-Optimized Tables
  • “XTP Configuration” monitor now supports different file path types (not only those starting with C:, D:, etc.)
  • Fixed issue: “Resource Pool State” view shows incorrect set of objects
  • Fixed issue: Invalid group discovery in SQL Server 2016 Always On
  • Updated the visualization library

New SQL Server 2008-2016 Replication MP Features and Fixes (6.7.31.0)

  • Added Distributor name caching to Subscription discovery
  • Restricted the length of some string class properties
  • Improved the internal structure of SQL scripts storage
  • Fixed variable types in SQL scripts
  • Fixed connectivity issues in SmartConnect module
  • Introduced minor updates to the display strings
  • Updated the visualization library

New SQL Server 2008-2016 Reporting Services MP Features and Fixes (6.7.31.0)

  • Reimplemented Instance seed discovery: replaced the managed module with a PowerShell script
  • Reimplemented Deployment seed discovery: added a retry policy and improved error handling
  • Updated the visualization library

New SQL Server 2008-2016 Analysis Services MP Features and Fixes (6.7.31.0)

  • Restricted length of some string class properties
  • Updated the visualization library

New SQL Server Dashboards MP Features and Fixes (6.7.31.0)

  • Increased the version number to comply with the current version of SQL Server MPs

We are looking forward to hearing your feedback. This release is in English only. If you are an international customer looking for a localized version of this release, we would like to hear from you at sqlmpsfeedback@microsoft.com.

Let’s block ads! (Why?)

SQL Server Release Services

MATCH_RECOGNIZE and predicates – everything you need to know

PaperTapes 5and8Hole MATCH RECOGNIZE and predicates   everything you need to know

At a recent user conference I had a question about when and how  predicates are applied when using MATCH_RECOGNIZE so that’s the purpose of this blog post. Will this post cover everything you will ever need to know for this topic? Probably!

Where to start….the first thing to remember is that the table listed in the FROM clause of your SELECT statement acts as the input into the MATCH_RECOGNIZE pattern matching process and this raises the question about how and where are predicates actually applied. I briefly touched on this topic in part 1 of my deep dive series on MATCH_RECOGNIZE: SQL Pattern Matching Deep Dive – Part 1.

In that first post I looked at the position of predicates within the explain plan and their impact on sorting. In this post I am going to use the built in measures (MATCH_NUMBER and CLASSIFIER) to show the impact of applying predicates to the results that are returned.

First, if you need a quick refresher course in how to use the MATCH_RECOGNIZE built-in measures then see part 2 of the deep dive series: SQL Pattern Matching Deep Dive – Part 2, using MATCH_NUMBER() and CLASSIFIER().

 As per usual I am going to use my normal stock ticker schema to illustrate the specific points. You can find this schema listed on most of the pattern matching examples on livesql.oracle.com. There are three key areas within the MATCH_RECOGNIZE clause that impact on predicates… 

  1. PARTITION BY column
  2. ORDER BY column
  3. All other columns

1. Predicates on the PARTITION BY column

 Let’ start with a simple query:

select * from ticker
MATCH_RECOGNIZE(
  PARTITION BY symbol ORDER BY tstamp
  MEASURES match_number() as mn
  ALL ROWS PER MATCH
  PATTERN (strt)
 DEFINE strt as 1=1
);

Note that we are using an always-true pattern STRT which is defined as 1=1 to ensure that we process all rows and the pattern has no range so it will be matched once and then reset to find the next match. As our ticker table contains 60 rows, the output also contains 60 rows

 MATCH RECOGNIZE and predicates   everything you need to know

Checkout the column headed mn which contains our match_numnber() measure. This shows that within the first partition for ACME we matched the always-true event 20 times, i.e. all rows were matched. If we check the explain plan for this query we can see that all 60 rows (3 symbols, and 20 rows for each symbol) were processed:

 MATCH RECOGNIZE and predicates   everything you need to know

If we now apply a predicate on the PARTITION BY column, SYMBOL, then we can see that the first “block” of our output looks exactly the same, however, the explain plan shows that we have processed fewer rows – only 20 rows.

 Let’ modify and rerun our simple query:

select * from ticker
MATCH_RECOGNIZE(
  PARTITION BY symbol ORDER BY tstamp
  MEASURES match_number() as mn
  ALL ROWS PER MATCH
  PATTERN (strt+)
 DEFINE strt as 1=1
)
WHERE symbol = ‘ACME';

 the results look similar but note that the output summary returned by SQL Developer indicates that only 20 rows were fetched:

 MATCH RECOGNIZE and predicates   everything you need to know

notice that the match_number() column (mn) is showing 1 – 20 as values returned from the pattern matching process. If we look at the explain plan….

 MATCH RECOGNIZE and predicates   everything you need to know

…this also shows that we processed 20 rows – so partition elimination filtered out the other 40 rows before pattern matching started. Therefore, if you apply predicates on the PARTITION BY column then MATCH_RECOGNIZE is smart enough to perform partition elimination to reduce the number of rows that need to be processed.

Conclusion – predicates on the PARTITION BY column.

Predicates on the partition by column reduce the amount of data being passed into MATCH_RECOGNIZE.

Built-in measures such as MATCH_NUMBER work as expected in that a contiguous sequence is returned.

2. Predicates on the ORDER BY column

What happens if we apply a predicate to the ORDER BY column? Let’s amend the query and add a filter on the tstamp column:

select * from ticker
MATCH_RECOGNIZE(
 PARTITION BY symbol ORDER BY tstamp
 MEASURES match_number() as mn
 ALL ROWS PER MATCH
 PATTERN (strt)
 DEFINE strt as 1=1
)
WHERE symbol='ACME'
AND tstamp BETWEEN '01-APR-11' AND '10-APR-11';

 returns a smaller resultset of only 10 rows and match_number is correctly sequenced from 1-10 – as expected:

 MATCH RECOGNIZE and predicates   everything you need to know

 however, the explain plan shows that we processed all the rows within the partition (20).

 MATCH RECOGNIZE and predicates   everything you need to know

This becomes a little clearer if remove the predicate on the SYMBOL column:

select * from ticker
MATCH_RECOGNIZE(
 PARTITION BY symbol ORDER BY tstamp
 MEASURES match_number() as mn
 ALL ROWS PER MATCH
 PATTERN (strt)
 DEFINE strt as 1=1
)
WHERE tstamp BETWEEN ’01-APR-11' AND '10-APR-11';

now we see that 30 rows are returned

 MATCH RECOGNIZE and predicates   everything you need to know

but all 60 rows have actually been processed!

 MATCH RECOGNIZE and predicates   everything you need to know

Conclusion

Filters applied to non-partition by columns are applied after the pattern matching process has completed: rows are passed in to MATCH_RECOGNIZE, the pattern is matched and then predicates on the ORDER BY/other columns are applied.

Is there a way to prove that this is actually what is happening?

3.Using other columns

Lets add another column to our ticker table that shows the day name for each trade. Now let’s rerun the query with the predicate on the SYMBOL column:

select * from ticker
MATCH_RECOGNIZE(
  PARTITION BY symbol ORDER BY tstamp
  MEASURES match_number() as mn
  ALL ROWS PER MATCH
  PATTERN (strt)
 DEFINE strt as 1=1
)
WHERE symbol = ‘ACME';

 MATCH RECOGNIZE and predicates   everything you need to know

the column to note is MN which contains a contiguous sequence of numbers from 1 to 20.

What happens if we filter on the day_name column and only keep the working-week days (Mon-Fri):

select * from ticker
MATCH_RECOGNIZE(
  PARTITION BY symbol ORDER BY tstamp
  MEASURES match_number() as mn
  ALL ROWS PER MATCH
  PATTERN (strt)
 DEFINE strt as 1=1
)
WHERE symbol = ‘ACME'
AND day_name in (‘MONDAY’, ’TUESDAY’, ‘WEDNESDAY’, ’THURSDAY’, ‘FRIDAY’);

 now if we look at the match_number column, mn, we can see that the sequence is no longer contiguous: the value in row 2 is now 4 and not 2, row 7 the value of mn is 11 even though the previous row was 8:

 MATCH RECOGNIZE and predicates   everything you need to know

It is still possible to “access” the rows that have been removed. Consider the following query with the measure PREV(day_name):

select * from ticker
MATCH_RECOGNIZE(
 PARTITION BY symbol ORDER BY tstamp
 MEASURES match_number() as mn,
          prev(day_name) as prev_day
 ALL ROWS PER MATCH
 PATTERN (strt)
 DEFINE strt as 1=1
)
WHERE symbol='ACME'
AND day_name in ('MONDAY', 'WEDNESDAY', 'FRIDAY');

this returns the following:

 MATCH RECOGNIZE and predicates   everything you need to know

where you can see that on row 2 the value for SUNDAY has been returned even though logically looking at the results the previous day should be FRIDAY.

This has important implications for numerical calculations such as running totals, final totals, averages, counts, min and max etc etc because these will take into account all the matches (depending on how your pattern is defined) prior to the final set of predicates (i.e. non-PARTITION BY columns) being applied.

One last example

Let’s now change the always-true pattern to search for as many rows as possible (turn it into a greedy quantifier)

select symbol, tstamp, mn, price, day_name, prev_day, total_rows from ticker
MATCH_RECOGNIZE(
 PARTITION BY symbol ORDER BY tstamp
 MEASURES match_number() as mn,
          prev(day_name) as prev_day,
          count(*) as total_rows 
 ALL ROWS PER MATCH
 PATTERN (strt+)
 DEFINE strt as 1=1
)
WHERE symbol='ACME'
AND day_name in ('MONDAY', 'WEDNESDAY', 'FRIDAY');

the results from the following two queries:

Query 1:

select symbol, tstamp, mn, price, day_name, prev_day, total_rows, avg_price, max_price 

from ticker
MATCH_RECOGNIZE(
 PARTITION BY symbol ORDER BY tstamp
 MEASURES match_number() as mn,
 prev(day_name) as prev_day,
 count(*) as total_rows,
 trunc(avg(price),2) as avg_price,
 max(price) as max_price
 ALL ROWS PER MATCH
 PATTERN (strt+)
 DEFINE strt as 1=1
)
WHERE symbol=‘ACME';

 Query 2:

select symbol, tstamp, mn, price, day_name, prev_day, total_rows, avg_price, max_price from ticker
MATCH_RECOGNIZE(
 PARTITION BY symbol ORDER BY tstamp
 MEASURES match_number() as mn,
   prev(day_name) as prev_day,
   count(*) as total_rows,
   trunc(avg(price),2) as avg_price,
   max(price) as max_price
 ALL ROWS PER MATCH
 PATTERN (strt+)
 DEFINE strt as 1=1
)
WHERE symbol='ACME'
AND day_name in ('MONDAY', 'WEDNESDAY', 'FRIDAY');

the number of rows returned is different but the values for the calculated columns (previous day, count, max and min) are exactly the same:

Resultset 1:

 MATCH RECOGNIZE and predicates   everything you need to know

Resultset 2:

 MATCH RECOGNIZE and predicates   everything you need to know

Conclusion

When I briefly touched on this topic in part 1 of my deep dive series on MATCH_RECOGNIZE, SQL Pattern Matching Deep Dive – Part 1, the focus was on the impact predicates had on sorting – would additional sorting take place if predicates were used.

In this post I have looked at the impact on the data returned. Obviously by removing rows at the end of processing there can be a huge impact on calculated measures such as match_number, counts and averages etc.

Hope this has been helpful. If you have any questions then feel free to send me an email: keith.laker@oracle.com.

Main image courtesy of wikipedia

Let’s block ads! (Why?)

Oracle Blogs | Oracle The Data Warehouse Insider Blog

A minimization which works but not too much

 A minimization which works but not too much

Here under is a method to calculate Dean’s apportionment method

dean[v_, s_] := Module[{vv = v, ss = s}, ww = DeleteCases[vv, 0];
  var = Table[x[i], {i, Length[ww]}];
  obj = Total[
    Table[Log[(x[i]!)^3 Rationalize[2^(
         x[i] - 1)]/(x[i]^2 ww[[i]]^(x[i] - 1) ((2 x[i] - 1)!))], {i, 
      Length[ww]}]];
  const = Total[Table[x[i], {i, Length[ww]}]];
  cons1 = 
   ToExpression[
    StringReplace[
     ToString[Table[x[i] >= 0, {i, Length[ww]}]], {"{" -> "", 
      "}" -> "", "," -> " &&"}]];
  int = ToExpression[
    StringReplace[
     ToString[
      Table[x[i] ∈ Integers, {i, Length[ww]}]], {"{" -> "", 
      "}" -> "", "," -> " &&"}]];
  argmin = ArgMin[{obj, const == ss && cons1 && int}, var];
  zer = Table[0, {i, 1, Length[vv] - Length[ww]}];
  fin = Join[argmin, zer];
  Fold[Insert[#1, 0, #2] &, DeleteCases[fin, 0], Position[vv, 0]]]
dean[{0, 40, 20, 12, 0}, 20]

It works nicely until s=20. But at s=21, it fails. It’s the same thing if I increase the length of v. Of course, I have tried to change ArgMin to NArgMin and some methods but it doesn’t ameliorate the situation. Mathematica complains of a division by 0. Is there a way to resolve this problem?

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

See How Easily You Can Copy Data Between Object Store and HDFS

Object Stores tend to be a place where people put there data in the cloud (see also The New Data Lake – You Need More Than HDFS). Add data here and then share it, load it or use it across various other services. Here we won’t discuss the architecture and whether or not the data lake now is the object store (hint: not yet…), but instead focus on how to easily move data back and forth between object stores and your Big Data Cloud Service (BDCS) cluster(s).

ODCP

The underlying foundation for the coming screen shots and for Big Data Manager – a free component included with Big Data Cloud Service – is Oracle Distributed CoPy. The utility is based loosely on DistCP but made data movement leveraging Object Stores scalable and simple.

For a good overview and some performance numbers on ODCP and a comparison with a host of other ways of loading data into BDCS I would recommend reviewing this post from the A-team at Oracle.

For production workloads I would expect everyone to go command line, as it enables scripting of jobs or embedding this in your favorite ETL tool for execution in a more comprehensive flow. The command line reference manual is published here.

Big Data Manager

For those looking to get going, the command line may be a bit intimidating. Big Data Manager resolves that by providing an elegant way of:

  • Creating reusable storage providers, and managing access to these providers
  • Providing an intuitive file browser and drag and drop capabilities between providers
  • Providing a simple GUI to choose between scheduled (and repeated) and immediate execution of jobs

Creating Data Providers (Storages)

The cluster pages for BDCS have a link to Big Data Manager. The tool requires a specific log in once working in the cluster. After you log in you will end up on the main page:

mainscreen See How Easily You Can Copy Data Between Object Store and HDFS


Selecting the Administration Tab in the tool enables the creation and editing of the Storages as they are called. You can create these providers for a number of – ever expanding – providers. For example, Oracle Storage Cloud, Amazon S3, BDCS HDFS etc. Check back for new ones frequently or simply keep an eye on your updated Big Data Manager.

Tip: Creating a Storage for Oracle Object Store, the Tenant starts with “storage-” and you add your identity domain after that.

Once you have your storages created, you are in business and dragging and dropping can start. In my example here, I am going from Oracle Storage Cloud Service to my HDFS in my BDCS cluster, and so I am loading data into my BDCS system:

draganddrop See How Easily You Can Copy Data Between Object Store and HDFS

Now simply drag and drop from left to right (or of course the other way) and you will be asked whether or not to do the move from Object Store to HDFS now, or schedule it and repeat on a specified frequency.

movenow See How Easily You Can Copy Data Between Object Store and HDFS

Clicking Create will spawn an Apache Spark job on the BDCS cluster, open a connection to Object Store and run a data transfer in parallel based on the setting you can tweak in the advanced tab.

Switching the “Run Immediately” toggle to “Repeated Execution” gives you the scheduling information:

movelater See How Easily You Can Copy Data Between Object Store and HDFS

Once done, the job runs, and can be monitored in Big Data Manager:

jobdone See How Easily You Can Copy Data Between Object Store and HDFS

 

 

 

SDK

Last but not least, there is both a Python and Java SDK for Big Data Manager. Feel free to give all this a whirl in your BDCS instance and let us know how things go. 

Let’s block ads! (Why?)

Oracle Blogs | Oracle The Data Warehouse Insider Blog

Using R to illustrate relationships from graph objects

My new article about Graph Database Objects was just published, it’s really a very good new feature.

Another new feature we have since SQL Server 2016 is R language. We can use R language inside the database to analyze our data.

The possibility to use both technologies together is very interesting. Using graph objects we can store relationships between elements, for example, relationships between forum members. Using R scripts we can build a cluster graph from the stored graph information, illustrating the relationships in the graph.

The script below creates a database for our example with a subset of the objects used in my article and a few more relationship records between the forum members.

use master
drop database if exists RGraph
go
create database RGraph
go

use RGraph
go

drop table if exists dbo.Likes
drop table if exists dbo.ForumMembers

CREATE TABLE [dbo].[ForumMembers](
       [MemberID] [int] IDENTITY(1,1) NOT NULL,
       [MemberName] [varchar](100) NULL
)
AS NODE
GO

CREATE TABLE [dbo].[Likes]
AS EDGE

INSERT ForumMembers values (‘Mike’),(‘Carl’),(‘Paul’),(‘Christy’),(‘Jennifer’),(‘Charlie’)

INSERT Likes ($ to_id,$ from_id)
    VALUES
    ((SELECT $ node_id FROM dbo.ForumMembers WHERE MemberID = 1),
         (SELECT $ node_id FROM dbo.ForumMembers WHERE MemberID = 2)),
    ((SELECT $ node_id FROM dbo.ForumMembers WHERE MemberID = 3),
         (SELECT $ node_id FROM dbo.ForumMembers WHERE MemberID = 2)),
    ((SELECT $ node_id FROM dbo.ForumMembers WHERE MemberID = 1),
         (SELECT $ node_id FROM dbo.ForumMembers WHERE MemberID = 4)),
    ((SELECT $ node_id FROM dbo.ForumMembers WHERE MemberID = 5),
         (SELECT $ node_id FROM dbo.ForumMembers WHERE MemberID = 4))

insert into likes ($ from_id,$ to_id) values
  ((select $ node_id from dbo.forummembers where MemberName=‘Mike’),
    (select $ node_id from dbo.forummembers where MemberName=‘Paul’)),
  ((select $ node_id from dbo.forummembers where MemberName=‘Paul’),
    (select $ node_id from dbo.forummembers where MemberName=‘Christy’)),
  ((select $ node_id from dbo.forummembers where MemberName=‘Christy’),
    (select $ node_id from dbo.forummembers where MemberName=‘Carl’)),
  ((select $ node_id from dbo.forummembers where MemberName=‘Paul’),
    (select $ node_id from dbo.forummembers where MemberName=‘Jennifer’)),
  ((select $ node_id from dbo.forummembers where MemberName=‘Jennifer’),
    (select $ node_id from dbo.forummembers where MemberName=‘Carl’))

You need to follow a few steps to enable R scripts:

1) Install R in SQL Server

You can follow the instructions in this link to install R in SQL Server

2) Install the package iGraph in R

You can follow the instructions in this link to install iGraph package

3) Enable the use of external scripts. You can execute the following t-sql code:

exec sp_configure ‘external scripts enabled’, 1;

RECONFIGURE;

4) Execute the script

Below is the R script to build a cluster graph using iGraph our relationships stored in SQL Server. You need to change the folder to store the graph.

exec sp_execute_external_script @language = N’R’,
@script = N’

require(igraph)

g <- graph.data.frame(graphdf)

V(g)$ label.cex <- 2

png(filename = “c:\R\plot1.png”, height = 1200, width = 1200, res = 100); plot(g, vertex.label.family = “sans”, vertex.size = 40)

dev.off() ‘,
@input_data_1 = N’select LikeMember.MemberName as LikeMember,    LikedMember.MemberName as LikedMember         from dbo.ForumMembers as LikeMember,  dbo.ForumMembers as LikedMember,  Likes

        where Match(LikeMember-(Likes)->LikedMember) ‘,
@input_data_1_name = N’graphdf’

GO

The image below shows the resulting graph with the relations between forum members:

RAndGraph1 Using R to illustrate relationships from graph objects

It’s interesting to remember about the new feature in SQL Server 2017: We can also build the same script using Python.

Let’s block ads! (Why?)

SQL – Simple Talk

Robots And AI In Retail: 8 Things You Must Know

Last August, a woman arrived at a Reno, Nevada, hospital and told the attending doctors that she had recently returned from an extended trip to India, where she had broken her right thighbone two years ago. The woman, who was in her 70s, had subsequently developed an infection in her thigh and hip for which she was hospitalized in India several times. The Reno doctors recognized that the infection was serious—and the visit to India, where antibiotic-resistant bacteria runs rampant, raised red flags.

When none of the 14 antibiotics the physicians used to treat the woman worked, they sent a sample of the bacterium to the U.S. Centers for Disease Control (CDC) for testing. The CDC confirmed the doctors’ worst fears: the woman had a class of microbe called carbapenem-resistant Enterobacteriaceae (CRE). Carbapenems are a powerful class of antibiotics used as last-resort treatment for multidrug-resistant infections. The CDC further found that, in this patient’s case, the pathogen was impervious to all 26 antibiotics approved by the U.S. Food and Drug Administration (FDA).

In other words, there was no cure.

This is just the latest alarming development signaling the end of the road for antibiotics as we know them. In September, the woman died from septic shock, in which an infection takes over and shuts down the body’s systems, according to the CDC’s Morbidity and Mortality Weekly Report.

Other antibiotic options, had they been available, might have saved the Nevada woman. But the solution to the larger problem won’t be a new drug. It will have to be an entirely new approach to the diagnosis of infectious disease, to the use of antibiotics, and to the monitoring of antimicrobial resistance (AMR)—all enabled by new technology.

sap Q217 digital double feature2 images2 Robots And AI In Retail: 8 Things You Must KnowBut that new technology is not being implemented fast enough to prevent what former CDC director Tom Frieden has nicknamed nightmare bacteria. And the nightmare is becoming scarier by the year. A 2014 British study calculated that 700,000 people die globally each year because of AMR. By 2050, the global cost of antibiotic resistance could grow to 10 million deaths and US$ 100 trillion a year, according to a 2014 estimate. And the rate of AMR is growing exponentially, thanks to the speed with which humans serving as hosts for these nasty bugs can move among healthcare facilities—or countries. In the United States, for example, CRE had been seen only in North Carolina in 2000; today it’s nationwide.

Abuse and overuse of antibiotics in healthcare and livestock production have enabled bacteria to both mutate and acquire resistant genes from other organisms, resulting in truly pan-drug resistant organisms. As ever-more powerful superbugs continue to proliferate, we are potentially facing the deadliest and most costly human-made catastrophe in modern times.

“Without urgent, coordinated action by many stakeholders, the world is headed for a post-antibiotic era, in which common infections and minor injuries which have been treatable for decades can once again kill,” said Dr. Keiji Fukuda, assistant director-general for health security for the World Health Organization (WHO).

Even if new antibiotics could solve the problem, there are obstacles to their development. For one thing, antibiotics have complex molecular structures, which slows the discovery process. Further, they aren’t terribly lucrative for pharmaceutical manufacturers: public health concerns call for new antimicrobials to be financially accessible to patients and used conservatively precisely because of the AMR issue, which reduces the financial incentives to create new compounds. The last entirely new class of antibiotic was introduced 30 year ago. Finally, bacteria will develop resistance to new antibiotics as well if we don’t adopt new approaches to using them.

Technology can play the lead role in heading off this disaster. Vast amounts of data from multiple sources are required for better decision making at all points in the process, from tracking or predicting antibiotic-resistant disease outbreaks to speeding the potential discovery of new antibiotic compounds. However, microbes will quickly adapt and resist new medications, too, if we don’t also employ systems that help doctors diagnose and treat infection in a more targeted and judicious way.

Indeed, digital tools can help in all four actions that the CDC recommends for combating AMR: preventing infections and their spread, tracking resistance patterns, improving antibiotic use, and developing new diagnostics and treatment.

Meanwhile, individuals who understand both the complexities of AMR and the value of technologies like machine learning, human-computer interaction (HCI), and mobile applications are working to develop and advocate for solutions that could save millions of lives.

sap Q217 digital double feature2 images3 1024x572 Robots And AI In Retail: 8 Things You Must Know

Keeping an Eye Out for Outbreaks

Like others who are leading the fight against AMR, Dr. Steven Solomon has no illusions about the difficulty of the challenge. “It is the single most complex problem in all of medicine and public health—far outpacing the complexity and the difficulty of any other problem that we face,” says Solomon, who is a global health consultant and former director of the CDC’s Office of Antimicrobial Resistance.

Solomon wants to take the battle against AMR beyond the laboratory. In his view, surveillance—tracking and analyzing various data on AMR—is critical, particularly given how quickly and widely it spreads. But surveillance efforts are currently fraught with shortcomings. The available data is fragmented and often not comparable. Hospitals fail to collect the representative samples necessary for surveillance analytics, collecting data only on those patients who experience resistance and not on those who get better. Laboratories use a wide variety of testing methods, and reporting is not always consistent or complete.

Surveillance can serve as an early warning system. But weaknesses in these systems have caused public health officials to consistently underestimate the impact of AMR in loss of lives and financial costs. That’s why improving surveillance must be a top priority, says Solomon, who previously served as chair of the U.S. Federal Interagency Task Force on AMR and has been tracking the advance of AMR since he joined the U.S. Public Health Service in 1981.

A Collaborative Diagnosis

Ineffective surveillance has also contributed to huge growth in the use of antibiotics when they aren’t warranted. Strong patient demand and financial incentives for prescribing physicians are blamed for antibiotics abuse in China. India has become the largest consumer of antibiotics on the planet, in part because they are prescribed or sold for diarrheal diseases and upper respiratory infections for which they have limited value. And many countries allow individuals to purchase antibiotics over the counter, exacerbating misuse and overuse.

In the United States, antibiotics are improperly prescribed 50% of the time, according to CDC estimates. One study of adult patients visiting U.S. doctors to treat respiratory problems found that more than two-thirds of antibiotics were prescribed for conditions that were not infections at all or for infections caused by viruses—for which an antibiotic would do nothing. That’s 27 million courses of antibiotics wasted a year—just for respiratory problems—in the United States alone.

And even in countries where there are national guidelines for prescribing antibiotics, those guidelines aren’t always followed. A study published in medical journal Family Practice showed that Swedish doctors, both those trained in Sweden and those trained abroad, inconsistently followed rules for prescribing antibiotics.

Solomon strongly believes that, worldwide, doctors need to expand their use of technology in their offices or at the bedside to guide them through a more rational approach to antibiotic use. Doctors have traditionally been reluctant to adopt digital technologies, but Solomon thinks that the AMR crisis could change that. New digital tools could help doctors and hospitals integrate guidelines for optimal antibiotic prescribing into their everyday treatment routines.

“Human-computer interactions are critical, as the amount of information available on antibiotic resistance far exceeds the ability of humans to process it,” says Solomon. “It offers the possibility of greatly enhancing the utility of computer-assisted physician order entry (CPOE), combined with clinical decision support.” Healthcare facilities could embed relevant information and protocols at the point of care, guiding the physician through diagnosis and prescription and, as a byproduct, facilitating the collection and reporting of antibiotic use.

sap Q217 digital double feature2 images4 Robots And AI In Retail: 8 Things You Must Know

Cincinnati Children’s Hospital’s antibiotic stewardship division has deployed a software program that gathers information from electronic medical records, order entries, computerized laboratory and pathology reports, and more. The system measures baseline antimicrobial use, dosing, duration, costs, and use patterns. It also analyzes bacteria and trends in their susceptibilities and helps with clinical decision making and prescription choices. The goal, says Dr. David Haslam, who heads the program, is to decrease the use of “big gun” super antibiotics in favor of more targeted treatment.

While this approach is not yet widespread, there is consensus that incorporating such clinical-decision support into electronic health records will help improve quality of care, contain costs, and reduce overtreatment in healthcare overall—not just in AMR. A 2013 randomized clinical trial finds that doctors who used decision-support tools were significantly less likely to order antibiotics than those in the control group and prescribed 50% fewer broad-spectrum antibiotics.

Putting mobile devices into doctors’ hands could also help them accept decision support, believes Solomon. Last summer, Scotland’s National Health Service developed an antimicrobial companion app to give practitioners nationwide mobile access to clinical guidance, as well as an audit tool to support boards in gathering data for local and national use.

“The immediacy and the consistency of the input to physicians at the time of ordering antibiotics may significantly help address the problem of overprescribing in ways that less-immediate interventions have failed to do,” Solomon says. In addition, handheld devices with so-called lab-on-a-chip  technology could be used to test clinical specimens at the bedside and transmit the data across cellular or satellite networks in areas where infrastructure is more limited.

Artificial intelligence (AI) and machine learning can also become invaluable technology collaborators to help doctors more precisely diagnose and treat infection. In such a system, “the physician and the AI program are really ‘co-prescribing,’” says Solomon. “The AI can handle so much more information than the physician and make recommendations that can incorporate more input on the type of infection, the patient’s physiologic status and history, and resistance patterns of recent isolates in that ward, in that hospital, and in the community.”

Speed Is Everything

Growing bacteria in a dish has never appealed to Dr. James Davis, a computational biologist with joint appointments at Argonne National Laboratory and the University of Chicago Computation Institute. The first of a growing breed of computational biologists, Davis chose a PhD advisor in 2004 who was steeped in bioinformatics technology “because you could see that things were starting to change,” he says. He was one of the first in his microbiology department to submit a completely “dry” dissertation—that is, one that was all digital with nothing grown in a lab.

Upon graduation, Davis wanted to see if it was possible to predict whether an organism would be susceptible or resistant to a given antibiotic, leading him to explore the potential of machine learning to predict AMR.

sap Q217 digital double feature2 images5 Robots And AI In Retail: 8 Things You Must Know

As the availability of cheap computing power has gone up and the cost of genome sequencing has gone down, it has become possible to sequence a pathogen sample in order to detect its AMR resistance mechanisms. This could allow doctors to identify the nature of an infection in minutes instead of hours or days, says Davis.

Davis is part of a team creating a giant database of bacterial genomes with AMR metadata for the Pathosystems Resource Integration Center (PATRIC), funded by the U.S. National Institute of Allergy and Infectious Diseases to collect data on priority pathogens, such as tuberculosis and gonorrhea.

Because the current inability to identify microbes quickly is one of the biggest roadblocks to making an accurate diagnosis, the team’s work is critically important. The standard method for identifying drug resistance is to take a sample from a wound, blood, or urine and expose the resident bacteria to various antibiotics. If the bacterial colony continues to divide and thrive despite the presence of a normally effective drug, it indicates resistance. The process typically takes between 16 and 20 hours, itself an inordinate amount of time in matters of life and death. For certain strains of antibiotic-resistant tuberculosis, though, such testing can take a week. While physicians are waiting for test results, they often prescribe broad-spectrum antibiotics or make a best guess about what drug will work based on their knowledge of what’s happening in their hospital, “and in the meantime, you either get better,” says Davis, “or you don’t.”

At PATRIC, researchers are using machine-learning classifiers to identify regions of the genome involved in antibiotic resistance that could form the foundation for a “laboratory free” process for predicting resistance. Being able to identify the genetic mechanisms of AMR and predict the behavior of bacterial pathogens without petri dishes could inform clinical decision making and improve reaction time. Thus far, the researchers have developed machine-learning classifiers for identifying antibiotic resistance in Acinetobacter baumannii (a big player in hospital-acquired infection), methicillin-resistant Staphylococcus aureus (a.k.a. MRSA, a worldwide problem), and Streptococcus pneumoniae (a leading cause of bacterial meningitis), with accuracies ranging from 88% to 99%.

Houston Methodist Hospital, which uses the PATRIC database, is researching multidrug-resistant bacteria, specifically MRSA. Not only does resistance increase the cost of care, but people with MRSA are 64% more likely to die than people with a nonresistant form of the infection, according to WHO. Houston Methodist is investigating the molecular genetic causes of drug resistance in MRSA in order to identify new treatment approaches and help develop novel antimicrobial agents.

sap Q217 digital double feature2 images6 1024x572 Robots And AI In Retail: 8 Things You Must Know

The Hunt for a New Class of Antibiotics

There are antibiotic-resistant bacteria, and then there’s Clostridium difficile—a.k.a. C. difficile—a bacterium that attacks the intestines even in young and healthy patients in hospitals after the use of antibiotics.

It is because of C. difficile that Dr. L. Clifford McDonald jumped into the AMR fight. The epidemiologist was finishing his work analyzing the spread of SARS in Toronto hospitals in 2004 when he turned his attention to C. difficile, convinced that the bacteria would become more common and more deadly. He was right, and today he’s at the forefront of treating the infection and preventing the spread of AMR as senior advisor for science and integrity in the CDC’s Division of Healthcare Quality Promotion. “[AMR] is an area that we’re funding heavily…insofar as the CDC budget can fund anything heavily,” says McDonald, whose group has awarded $ 14 million in contracts for innovative anti-AMR approaches.

Developing new antibiotics is a major part of the AMR battle. The majority of new antibiotics developed in recent years have been variations of existing drug classes. It’s been three decades since the last new class of antibiotics was introduced. Less than 5% of venture capital in pharmaceutical R&D is focused on antimicrobial development. A 2008 study found that less than 10% of the 167 antibiotics in development at the time had a new “mechanism of action” to deal with multidrug resistance. “The low-hanging fruit [of antibiotic development] has been picked,” noted a WHO report.

Researchers will have to dig much deeper to develop novel medicines. Machine learning could help drug developers sort through much larger data sets and go about the capital-intensive drug development process in a more prescriptive fashion, synthesizing those molecules most likely to have an impact.

McDonald believes that it will become easier to find new antibiotics if we gain a better understanding of the communities of bacteria living in each of us—as many as 1,000 different types of microbes live in our intestines, for example. Disruption to those microbial communities—our “microbiome”—can herald AMR. McDonald says that Big Data and machine learning will be needed to unlock our microbiomes, and that’s where much of the medical community’s investment is going.

He predicts that within five years, hospitals will take fecal samples or skin swabs and sequence the microorganisms in them as a kind of pulse check on antibiotic resistance. “Just doing the bioinformatics to sort out what’s there and the types of antibiotic resistance that might be in that microbiome is a Big Data challenge,” McDonald says. “The only way to make sense of it, going forward, will be advanced analytic techniques, which will no doubt include machine learning.”

Reducing Resistance on the Farm

Bringing information closer to where it’s needed could also help reduce agriculture’s contribution to the antibiotic resistance problem. Antibiotics are widely given to livestock to promote growth or prevent disease. In the United States, more kilograms of antibiotics are administered to animals than to people, according to data from the FDA.

One company has developed a rapid, on-farm diagnostics tool to provide livestock producers with more accurate disease detection to make more informed management and treatment decisions, which it says has demonstrated a 47% to 59% reduction in antibiotic usage. Such systems, combined with pressure or regulations to reduce antibiotic use in meat production, could also help turn the AMR tide.

sap Q217 digital double feature2 images7 1024x572 Robots And AI In Retail: 8 Things You Must Know

Breaking Down Data Silos Is the First Step

Adding to the complexity of the fight against AMR is the structure and culture of the global healthcare system itself. Historically, healthcare has been a siloed industry, notorious for its scattered approach focused on transactions rather than healthy outcomes or the true value of treatment. There’s no definitive data on the impact of AMR worldwide; the best we can do is infer estimates from the information that does exist.

The biggest issue is the availability of good data to share through mobile solutions, to drive HCI clinical-decision support tools, and to feed supercomputers and machine-learning platforms. “We have a fragmented healthcare delivery system and therefore we have fragmented information. Getting these sources of data all into one place and then enabling them all to talk to each other has been problematic,” McDonald says.

Collecting, integrating, and sharing AMR-related data on a national and ultimately global scale will be necessary to better understand the issue. HCI and mobile tools can help doctors, hospitals, and public health authorities collect more information while advanced analytics, machine learning, and in-memory computing can enable them to analyze that data in close to real time. As a result, we’ll better understand patterns of resistance from the bedside to the community and up to national and international levels, says Solomon. The good news is that new technology capabilities like AI and new potential streams of data are coming online as an era of data sharing in healthcare is beginning to dawn, adds McDonald.

The ideal goal is a digitally enabled virtuous cycle of information and treatment that could save millions of dollars, lives, and perhaps even civilization if we can get there. D!

Read more thought provoking articles in the latest issue of the Digitalist Magazine, Executive Quarterly.


About the Authors:

Dr. David Delaney is Chief Medical Officer for SAP.

Joseph Miles is Global Vice President, Life Sciences, for SAP.

Walt Ellenberger is Senior Director Business Development, Healthcare Transformation and Innovation, for SAP.

Saravana Chandran is Senior Director, Advanced Analytics, for SAP.

Stephanie Overby is an independent writer and editor focused on the intersection of business and technology.

Comments

Let’s block ads! (Why?)

Digitalist Magazine

Mathematica Online Editing Performance

 Mathematica Online Editing Performance

Does anyone have any tips for improving editing performance in Mathematica Online? I am pulling my hair out… There seems to be editing lag like in early versions of online text editors, where it takes a while to catch up. I can’t figure out if it is the computers I’ve tried, if it’s a network problem, etc… So, I’m reaching out to see what your tips are and whether you have the same experience.

The part that is the most debilitating is when I have a lot of text or numbers on screen, such as a list of rules or just numeric data. Scrolling performance can’t keep up. I tried to think of something that I could share, but my data is proprietary.

However, I found out that the same phenomenon also happens with large formulas, which are easy to generate. I guess you can change the 5 to a higher number if the result is shorter than about two pages (otherwise you won’t be able to test scrolling). Remember to hit “show all”.

Solve[Total[c@# x^#&/@Range@5]==0,x]

On the computers and networks I’ve tested in Chrome, when I randomly scroll up and down rapidly for several seconds, it takes more than 10 seconds after releasing the mouse scroll wheel for the window to stop scrolling.

Obviously that isn’t a normal use case. Normally what happens is I keep missing where I intend to click, move, or type, because of the editing lag. The “ghost scrolling” is just a way to confirm that I’m not hallucinating.

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

Will Organizational Culture Ever Catch Up With Technology?

When it comes to buying things—even big-ticket items—the way we make decisions makes no sense. One person makes an impulsive offer on a house because of the way the light comes in through the kitchen windows. Another gleefully drives a high-end sports car off the lot even though it will probably never approach the limits it was designed to push.

We can (and usually do) rationalize these decisions after the fact by talking about needing more closet space or wanting to out-accelerate an 18-wheeler as we merge onto the highway, but years of study have arrived at a clear conclusion:

When it comes to the customer experience, human beings are fundamentally irrational.

In the brick-and-mortar past, companies could leverage that irrationality in time-tested ways. They relied heavily on physical context, such as an inviting retail space, to make products and services as psychologically appealing as possible. They used well-trained salespeople and employees to maximize positive interactions and rescue negative ones. They carefully sequenced customer experiences, such as having a captain’s dinner on the final night of a cruise, to play on our hard-wired craving to end experiences on a high note.

sap Q217 digital double feature1 images1 Will Organizational Culture Ever Catch Up With Technology?

Today, though, customer interactions are increasingly moving online. Fortune reports that on 2016’s Black Friday, the day after Thanksgiving that is so crucial to holiday retail results, 108.5 million Americans shopped online, while only 99.1 million visited brick-and-mortar stores. The 9.4% gap between the two was a dramatic change from just one year prior, when on- and offline Black Friday shopping were more or less equal.

When people browse in a store for a few minutes, an astute salesperson can read the telltale signs that they’re losing interest and heading for the exit. The salesperson can then intervene, answering questions and closing the sale.

Replicating that in a digital environment isn’t as easy, however. Despite all the investments companies have made to counteract e-shopping cart abandonment, they lack the data that would let them anticipate when a shopper is on the verge of opting out of a transaction, and the actions they take to lure someone back afterwards can easily come across as less helpful than intrusive.

In a digital environment, companies need to figure out how to use Big Data analysis and digital design to compensate for the absence of persuasive human communication and physical sights, sounds, and sensations. What’s more, a 2014 Gartner survey found that 89% of marketers expected customer experience to be their primary differentiator by 2016, and we’re already well into 2017.

As transactions continue to shift toward the digital and omnichannel, companies need to figure out new ways to gently push customers along the customer journey—and to do so without frustrating, offending, or otherwise alienating them.

sap Q217 digital double feature1 images6 1024x572 Will Organizational Culture Ever Catch Up With Technology?

The quest to understand online customers better in order to influence them more effectively is built on a decades-old foundation: behavioral psychology, the study of the connections between what people believe and what they actually do. All of marketing and advertising is based on changing people’s thoughts in order to influence their actions. However, it wasn’t until 2001 that a now-famous article in the Harvard Business Review formally introduced the idea of applying behavioral psychology to customer service in particular.

The article’s authors, Richard B. Chase and Sriram Dasu, respectively a professor and assistant professor at the University of Southern California’s Marshall School of Business, describe how companies could apply fundamental tenets of behavioral psychology research to “optimize those extraordinarily important moments when the company touches its customers—for better and for worse.” Their five main points were simple but have proven effective across multiple industries:

  1. Finish strong. People evaluate experiences after the fact based on their high points and their endings, so the way a transaction ends is more important than how it begins.
  2. Front-load the negatives. To ensure a strong positive finish, get bad experiences out of the way early.
  3. Spread out the positives. Break up the pleasurable experiences into segments so they seem to last longer.
  4. Provide choices. People don’t like to be shoved toward an outcome; they prefer to feel in control. Giving them options within the boundaries of your ability to deliver builds their commitment.
  5. Be consistent. People like routine and predictability.

For example, McKinsey cites a major health insurance company that experimented with this framework in 2009 as part of its health management program. A test group of patients received regular coaching phone calls from nurses to help them meet health goals.

The front-loaded negative was inherent: the patients knew they had health problems that needed ongoing intervention, such as weight control or consistent use of medication. Nurses called each patient on a frequent, regular schedule to check their progress (consistency and spread-out positives), suggested next steps to keep them on track (choices), and cheered on their improvements (a strong finish).

McKinsey reports the patients in the test group were more satisfied with the health management program by seven percentage points, more satisfied with the insurance company by eight percentage points, and more likely to say the program motivated them to change their behavior by five percentage points.

sap Q217 digital double feature1 images2 Will Organizational Culture Ever Catch Up With Technology?

The nurses who worked with the test group also reported increased job satisfaction. And these improvements all appeared in the first two weeks of the pilot program, without significantly affecting the company’s costs or tweaking key metrics, like the number and length of the calls.

Indeed, an ongoing body of research shows that positive reinforcements and indirect suggestions influence our decisions better and more subtly than blatant demands. This concept hit popular culture in 2008 with the bestselling book Nudge.

Written by University of Chicago economics professor Richard H. Thaler and Harvard Law School professor Cass R. Sunstein, Nudge first explains this principle, then explores it as a way to help people make decisions in their best interests, such as encouraging people to eat healthier by displaying fruits and vegetables at eye level or combatting credit card debt by placing a prominent notice on every credit card statement informing cardholders how much more they’ll spend over a year if they make only the minimum payment.

Whether they’re altruistic or commercial, nudges work because our decision-making is irrational in a predictable way. The question is how to apply that awareness to the digital economy.

sap Q217 digital double feature1 images7 1024x572 Will Organizational Culture Ever Catch Up With Technology?

In its early days, digital marketing assumed that online shopping would be purely rational, a tool that customers would use to help them zero in on the best product at the best price. The assumption was logical, but customer behavior remained irrational.

Our society is overloaded with information and short on time, says Brad Berens, Senior Fellow at the Center for the Digital Future at the University of Southern California, Annenberg, so it’s no surprise that the speed of the digital economy exacerbates our desire to make a fast decision rather than a perfect one, as well as increasing our tendency to make choices based on impulse rather than logic.

sap Q217 digital double feature1 images3 Will Organizational Culture Ever Catch Up With Technology?

Buyers want what they want, but they don’t necessarily understand or care why they want it. They just want to get it and move on, with minimal friction, to the next thing. “Most of our decisions aren’t very important, and we only have so much time to interrogate and analyze them,” Berens points out.

But limited time and mental capacity for decision-making is only half the issue. The other half is that while our brains are both logical and emotional, the emotional side—also known as the limbic system or, more casually, the primitive lizard brain—is far older and more developed. It’s strong enough to override logic and drive our decisions, leaving rational thought to, well, rationalize our choices after the fact.

This is as true in the B2B realm as it is for consumers. The business purchasing process, governed as it is by requests for proposals, structured procurement processes, and permission gating, is designed to ensure that the people with spending authority make the most sensible deals possible. However, research shows that even in this supposedly rational process, the relationship with the seller is still more influential than product quality in driving customer commitment and loyalty.

sap Q217 digital double feature1 images8 1024x572 Will Organizational Culture Ever Catch Up With Technology?

Baba Shiv, a professor of marketing at Stanford University’s Graduate School of Business, studies how the emotional brain shapes decisions and experiences. In a popular TED Talk, he says that people in the process of making decisions fall into one of two mindsets: Type 1, which is stressed and wants to feel comforted and safe, and Type 2, which is bored or eager and wants to explore and take action.

People can move between these two mindsets, he says, but in both cases, the emotional brain is in control. Influencing it means first delivering a message that soothes or motivates, depending on the mindset the person happens to be in at the moment and only then presenting the logical argument to help rationalize the action.

In the digital economy, working with those tendencies means designing digital experiences with the full awareness that people will not evaluate them objectively, says Ravi Dhar, director of the Center for Customer Insights at the Yale School of Management. Since any experience’s greatest subjective impact in retrospect depends on what happens at the beginning, the end, and the peaks in between, companies need to design digital experiences to optimize those moments—to rationally design experiences for limited rationality.

This often involves making multiple small changes in the way options are presented well before the final nudge into making a purchase. A paper that Dhar co-authored for McKinsey offers the example of a media company that puts most of its content behind a paywall but offers free access to a limited number of articles a month as an incentive to drive subscriptions.

Many nonsubscribers reached their limit of free articles in the morning, but they were least likely to respond to a subscription offer generated by the paywall at that hour, because they were reading just before rushing out the door for the day. When the company delayed offers until later in the day, when readers were less distracted, successful subscription conversions increased.

Pre-selecting default options for necessary choices is another way companies can design digital experiences to follow customers’ preference for the path of least resistance. “We know from a decade of research that…defaults are a de facto nudge,” Dhar says.

For example, many online retailers set a default shipping option because customers have to choose a way to receive their packages and are more likely to passively allow the default option than actively choose another one. Similarly, he says, customers are more likely to enroll in a program when the default choice is set to accept it rather than to opt out.

Another intriguing possibility lies in the way customers react differently to on-screen information based on how that information is presented. Even minor tweaks can have a disproportionate impact on the choices people make, as explained in depth by University of California, Los Angeles, behavioral economist Shlomo Benartzi in his 2015 book, The Smarter Screen.

A few of the conclusions Benartzi reached: items at the center of a laptop screen draw more attention than those at the edges. Those on the upper left of a screen split into quadrants attract more attention than those on the lower left. And intriguingly, demographics are important variables.

Benartzi cites research showing that people over 40 prefer more visually complicated, text-heavy screens than younger people, who are drawn to saturated colors and large images. Women like screens that use a lot of different colors, including pastels, while men prefer primary colors on a grey or white background. People in Malaysia like lots of color; people in Germany don’t.

This suggests companies need to design their online experiences very differently for middle-aged women than they do for teenage boys. And, as Benartzi writes, “it’s easy to imagine a future in which each Internet user has his or her own ‘aesthetic algorithm,’ customizing the appearance of every site they see.”

Applying behavioral psychology to the digital experience in more sophisticated ways will require additional formal research into recommendation algorithms, predictions, and other applications of customer data science, says Jim Guszcza, PhD, chief U.S. data scientist for Deloitte Consulting.

In fact, given customers’ tendency to make the fastest decisions, Guszcza believes that in some cases, companies may want to consider making choice environments more difficult to navigate— a process he calls “disfluencing”—in high-stakes situations, like making an important medical decision or an irreversible big-ticket purchase. Choosing a harder-to-read font and a layout that requires more time to navigate forces customers to work harder to process the information, sending a subtle signal that it deserves their close attention.

That said, a company can’t apply behavioral psychology to deliver a digital experience if customers don’t engage with its site or mobile app in the first place. Addressing this often means making the process as convenient as possible, itself a behavioral nudge.

A digital solution that’s easy to use and search, offers a variety of choices pre-screened for relevance, and provides a friction-free transaction process is the equivalent of putting a product at eye level—and that applies far beyond retail. Consider the Global Entry program, which streamlines border crossings into the U.S. for pre-approved international travelers. Members can skip long passport control lines in favor of scanning their passports and answering a few questions at a touchscreen kiosk. To date, 1.8 million people have decided this convenience far outweighs the slow pace of approvals.

sap Q217 digital double feature1 images9 1024x572 Will Organizational Culture Ever Catch Up With Technology?

The basics of influencing irrational customers are essentially the same whether they’re taking place in a store or on a screen. A business still needs to know who its customers are, understand their needs and motivations, and give them a reason to buy.

And despite the accelerating shift to digital commerce, we still live in a physical world. “There’s no divide between old-style analog retail and new-style digital retail,” Berens says. “Increasingly, the two are overlapping. One of the things we’ve seen for years is that people go into a store with their phones, shop for a better price, and buy online. Or vice versa: they shop online and then go to a store to negotiate for a better deal.”

Still, digital increases the number of touchpoints from which the business can gather, cluster, and filter more types of data to make great suggestions that delight and surprise customers. That’s why the hottest word in marketing today is omnichannel. Bringing behavioral psychology to bear on the right person in the right place in the right way at the right time requires companies to design customer experiences that bridge multiple channels, on- and offline.

Amazon, for example, is known for its friction-free online purchasing. The company’s pilot store in Seattle has no lines or checkout counters, extending the brand experience into the physical world in a way that aligns with what customers already expect of it, Dhar says.

Omnichannel helps counter some people’s tendency to believe their purchasing decision isn’t truly well informed unless they can see, touch, hear, and in some cases taste and smell a product. Until we have ubiquitous access to virtual reality systems with full haptic feedback, the best way to address these concerns is by providing personalized, timely, relevant information and feedback in the moment through whatever channel is appropriate. That could be an automated call center that answers frequently asked questions, a video that shows a product from every angle, or a demonstration wizard built into the product. Any of these channels could also suggest the customer visit the nearest store to receive help from a human.

sap Q217 digital double feature1 images4 Will Organizational Culture Ever Catch Up With Technology?

The omnichannel approach gives businesses plenty of opportunities to apply subtle nudges across physical and digital channels. For example, a supermarket chain could use store-club card data to push personalized offers to customers’ smartphones while they shop. “If the data tells them that your goal is to feed a family while balancing nutrition and cost, they could send you an e-coupon offering a discount on a brand of breakfast cereal that tastes like what you usually buy but contains half the sugar,” Guszcza says.

Similarly, a car insurance company could provide periodic feedback to policyholders through an app or even the digital screens in their cars, he suggests. “Getting a warning that you’re more aggressive than 90% of comparable drivers and three tips to avoid risk and lower your rates would not only incentivize the driver to be more careful for financial reasons but reduce claims and make the road safer for everyone.”

Digital channels can also show shoppers what similar people or organizations are buying, let them solicit feedback from colleagues or friends, and read reviews from other people who have made the same purchases. This leverages one of the most familiar forms of behavioral psychology—reinforcement from peers—and reassures buyers with Shiv’s Type 1 mindset that they’re making a choice that meets their needs or encourages those with the Type 2 mindset to move forward with the purchase. The rational mind only has to ask at the end of the process “Am I getting the best deal?” And as Guszcza points out, “If you can create solutions that use behavioral design and digital technology to turn my personal data into insight to reach my goals, you’ve increased the value of your engagement with me so much that I might even be willing to pay you more.”

sap Q217 digital double feature1 images10 1024x572 Will Organizational Culture Ever Catch Up With Technology?

Many transactions take place through corporate procurement systems that allow a company to leverage not just its own purchasing patterns but all the data in a marketplace specifically designed to facilitate enterprise purchasing. Machine learning can leverage this vast database of information to provide the necessary nudge to optimize purchasing patterns, when to buy, how best to negotiate, and more. To some extent, this is an attempt to eliminate psychology and make choices more rational.

B2B spending is tied into financial systems and processes, logistics systems, transportation systems, and other operational requirements in a way no consumer spending can be. A B2B decision is less about making a purchase that satisfies a desire than it is about making a purchase that keeps the company functioning.

That said, the decision still isn’t entirely rational, Berens says. When organizations have to choose among vendors offering relatively similar products and services, they generally opt for the vendor whose salespeople they like the best.

This means B2B companies have to make sure they meet or exceed parity with competitors on product quality, pricing, and time to delivery to satisfy all the rational requirements of the decision process. Only then can they bring behavioral psychology to bear by delivering consistently superior customer service, starting as soon as the customer hits their app or website and spreading out positive interactions all the way through post-purchase support. Finishing strong with a satisfied customer reinforces the relationship with a business customer just as much as it does with a consumer.

sap Q217 digital double feature1 images11 1024x572 Will Organizational Culture Ever Catch Up With Technology?

The best nudges make the customer relationship easy and enjoyable by providing experiences that are effortless and fun to choose, on- or offline, Dhar says. What sets the digital nudge apart in accommodating irrational customers is its ability to turn data about them and their journey into more effective, personalized persuasion even in the absence of the human touch.

Yet the subtle art of influencing customers isn’t just about making a sale, and it certainly shouldn’t be about persuading people to act against their own best interests, as Nudge co-author Thaler reminds audiences by exhorting them to “nudge for good.”

Guszcza, who talks about influencing people to make the choices they would make if only they had unlimited rationality, says companies that leverage behavioral psychology in their digital experiences should do so with an eye to creating positive impact for the customer, the company, and, where appropriate, the society.

In keeping with that ethos, any customer experience designed along behavioral lines has to include the option of letting the customer make a different choice, such as presenting a confirmation screen at the end of the purchase process with the cold, hard numbers and letting them opt out of the transaction altogether.

“A nudge is directing people in a certain direction,” Dhar says. “But for an ethical vendor, the only right direction to nudge is the right direction as judged by the customers themselves.” D!

Read more thought provoking articles in the latest issue of the Digitalist Magazine, Executive Quarterly.


About the Authors:

Volker Hildebrand is Global Vice President for SAP Hybris solutions.

Sam Yen is Chief Design Officer and Managing Director at SAP.

Fawn Fitter is a freelance writer specializing in business and technology.

Comments

Let’s block ads! (Why?)

Digitalist Magazine