Issues and Resolutions in starting R and R Server on SQL Server 2017

I am helping some people learn Data Science and we are having a ton of fun! There are lots of things to remember. So I am noting things here, in case I forget!

We noted the following error message, when we saw that R was not running on our SQL Server 2017 install:

‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

Here is the longer version:

Msg 39023, Level 16, State 1, Procedure sp_execute_external_script, Line 1 [Batch Start Line 3]

‘sp_execute_external_script’ is disabled on this instance of SQL Server. Use sp_configure ‘external scripts enabled’ to enable it.

Msg 11536, Level 16, State 1, Line 4

EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

Grr! What’s happened here? We had installed R as part of the SQL installation, and we had run the command to enable it, too. In case you are wondering, here is the command:

EXEC sp_configure ‘external scripts enabled’, 1
RECONFIGURE WITH OVERRIDE

So what happens next? Initial things to check:

Is R Server installed properly along with SQL Server? Here are some guidelines to help you.

Is the Launchpad service running? One of my colleagues and friends Tomaž Kaštrun  wrote a nice article on SQL Server Central. If not, this could be due to a lack of permissions in being able to start the service.

Did you restart the MSSQL Service on the machine? This will also restart the Launchpad service as well. If you didn’t restart the service, you will need to do that so it can pick up the results.

Once R is running properly, you can check it by using the following command, borrowed from the official installation guide over at Microsoft:

EXEC sp_execute_external_script @language =N’R’,
@script=N’
OutputDataSet <- InputDataSet;
‘,
@input_data_1 =N’SELECT 1 AS RIsWorkingFine’
WITH RESULT SETS (([RIsWorkingFine] int not null));
GO

If that returns a 1, then you are all set! To prove it works properly, you can retrieve the world famous Iris dataset using the following command, borrowed from the official documentation on sp_execute_external_script:

DROP PROC IF EXISTS get_iris_dataset;

go

CREATE PROC get_iris_dataset

AS BEGIN

EXEC sp_execute_external_script @language = N‘R’ , @script = N‘iris_data <- iris;’ , @input_data_1 = N , @output_data_1_name = N‘iris_data’ WITH RESULT SETS ((“Sepal.Length” float not null, “Sepal.Width” float not null, “Petal.Length” float not null, “Petal.Width” float not null, “Species” varchar(100)));

END;

GO

Once you’ve created the command, execute the following SQL command and you will see the iris dataset:

exec get_iris_dataset

You’re all set! Enjoy R!

First week as a Microsoft Regional Director – what did I learn?

It’s been my first week as a Regional Director and I thought it would be useful to report back on what I’ve learned so far.

Firstly, many existing RDs and Microsoft team members have been in touch to welcome me and it’s been such a nice experience. They are all incredibly nice. I feel I’m joining a warm group of people. There has been no shred of animosity and I have not heard anyone say a bad thing about anyone else. That’s very important to me. They are exemplary models of being the ‘bigger’ person. I will learn to be more sharing and perhaps even more trusting as part of this group, and, on an individual level, some healing as well. I’m joining a group of people who will be good for me. Sometimes it’s hard to work out who is good for you and who isn’t, and I am inspired by them to work even harder at being someone who is good for others.

Secondly, I learned that some of my friends are joining the Program too – Stacia Varga and Reza Rad. I’m thrilled to be joining with them and very excited about the opportunity to work with them.

Strangely, I learned that a lot of people don’t know what an RD is. Some Microsoft team members didn’t know, either, but I was buoyed by their happiness that they thought I was joining them! So I’ve had to explain that it is a community role. Hopefully I can help to explain as I figure things out, too.

Any questions, please leave a comment.

 

Microsoft Regional Director and setting an example with diversity and inclusion

I am now a Microsoft Regional Director! I have a lot of people to thank and I’m still overwhelmed and digesting the news. I want to do a really good job of this role and it’s a real gift. I understand it’s a mark of people’s faith in me and I don’t want to let people down. As always, this is a personal opinion and I don’t represent anyone else.

Microsoft, particularly recently, have become champions of diversity and inclusion. I was always happy to see diverse group members taking centre spotlight on the stage at events, for example, and women like Lara Rubbelke, Rimma Nehme and Julie Lerman were women I admired very much as leaders in their own right, who happened to be female as well. In my field of data, there are a ton of very talented women that inspire me: Jen Underwood, Claudia Imhoff, Stacia Varga, to name a few.

I want to thank Microsoft for continuing their journey as leaders in Diversity and Inclusion. In doing so, Microsoft are really doing more than ‘democratizing data and AI to the masses’. They are redefining the future by striving to becoming impact champions for diversity and equality in the workplace globally, and they are setting an example. It’s part of their DNA; embedded and unfolding.  Women are a minority in IT, that’s for sure. But our clout is growing and it gives me hope for the future, and it’s companies like Microsoft who are leading the way.

cups

I’m not sure why I was awarded the Microsoft Regional Director Award. I’m hugely grateful. I’ll probably never find out. But I like to think that Microsoft are on a mission to ensure that diverse voices are given leadership roles and a place at the table. I don’t think I got it because I’m female; people forget I’ve done postgraduate work in Artificial Intelligence at a university in Paris. I’ve run my own business for 8 years, and my current customer list in my current projects have combined turnovers of nearly £25 billion. In terms of other collateral, I also have a customer who reports right into Whitehall. My customers don’t pick me because I’m female; they pick me because I can help them and I’m prepared to cut myself on bleeding edge projects. Not bad for a single mother from a deprived part of Scotland. I’ve had to work ten times as hard for everything I have, and it’s been hard work plus love and support of great people that has fuelled me to get me this far.

What is a Microsoft Regional Director?

I got a whole slew of messages on LinkedIn, Twitter and Facebook to congratulate me, and I was overwhelmed. I did also get the question ‘what is an RD?’ so hence I’m writing this blog. What is an RD?

Taken from the official blog: The Regional Director Program provides Microsoft leaders with the customer insights and real-world voices it needs to continue empowering developers and IT professionals with the world’s most innovative and impactful tools, services, and solutions.

Established in 1993, the program consists of 150 of the world’s top technology visionaries chosen specifically for their proven cross-platform expertise, community leadership, and commitment to business results. You will typically find Regional Directors keynoting at top industry events, leading community groups and local initiatives, running technology-focused companies, or consulting on and implementing the latest breakthrough within a multinational corporation.

Regional Directors and MVPs are not Microsoft employees

I have had a lot of ‘Congratulations for joining Microsoft!’ messages – some of them from Microsoft team members – so I thought it was a good opportunity to clear up that RDs, like MVPs, are not paid. It’s an honour and a responsibility and there is no pecuniary advantage.

So what’s next?

I am not sure. But watch this space. I have ideas and things are bubbling. I look forward to your comments and thoughts; please leave a note below, if you like!

 

pumpkins

Useful Data Sources for Demos, Learning and Examples

One question that pops up from time to time is the question over sample datasets for use in self-learning, creating training materials or just for playing with data. I love this question: I learn by actively trying things out too. I love the stories in the data, and this is a great way to find the stories that bring the data to life, and offer real impact.

narrative

Since I deliver real projects with customer impact, I can’t demonstrate any real customer data during any of my presentations since my projects are confidential, so I have three approaches:
  • I use sample data and I have a signed NDA
  • I ask the customer for their data, anonymised and have a signed NDA.
  • I use their live data and have a signed NDA
If the customer elects the first option, then I use sample data from below.
To help you get started, I’ve cobbled together some pointers here, and I hope it’s useful. Please feel free to leave more ideas in the comments.

Entrepreneur

The latest edition of Entrepreneur has an insightful article on open source (frameworks vs libraries) and it has some good pointers to datasets at the bottom of the page. https://www.entrepreneur.com/article/310965 I’ve also pasted them here for you:
Bernard Marr has an updated list of datasets here, on Forbes. I’m not going to steal Marr’s list so I recommend that you go and head over to his page, where you’ll find sixty-plus options.

Data Source Direct Connectivity to R, Python, Ruby and Stata

R has a number of APIs that connect to public datasets e.g. the World Data Bank, which allows connectivity from R, Python, Ruby and Stata.  I used this for my recent demos at the Power BI event in the Netherlands, and it worked sweetly. SO you’d write your script to call the package, embed it in Power BI and it will go and get the data for you. I then create the chart in R, and put it into the Power BI workbook.

Quandl

Quandl offers financial data, and it has a hook so that R can connect directly to it as well.

Kaggle

Kaggle is owned by Google, presumably so that Google can promote Tensorflow. Since people share code based on Kaggle datasets, it’s very easy to pick code, copy it, change it, and see how it works. However, this can be an issue, since you can’t be sure that the code is correct.

Final Note

If you’re teaching or presenting using this data and/or sample code, you can be pretty sure that your training delegates have got access to the Internet too so you need to be sure that you credit people properly.
I am not mostly doing training, although I do training now and again. I am a consultant first and foremost. I’m meta-tracking my time with Desktime and with Trello since I am measuring exactly how I spend my time, and training does not account for a high percentage; project delivery takes the majority of my time.
Although I’m a guest lecturer for the MBA program at the University of Hertfordshire, and I’m going to be a guest lecturer on their MSc Business Analysis and Consultancy course, I do not consider myself a trainer. I am a consultant who sometimes does training as part of a larger project. I haven’t gone down the MCT route because I regard training as part of a bigger consultancy route. I never stop learning, and I don’t expect anyone else to stop learning, either.

literature

Nominating MVPs; growing the tech community via the MVP Program

Over the weekend, I nominated another two people for the MVP Award. It’s possible that they will not be awarded, of course; I have no influence on the process. I like to nominate people; if they aren’t put forward for an Award, then they will never get it, but I always hope that they do. Why not put someone forward? It only takes a few moments and you could change someone’s life!

What does it really mean to be an MVP?  I’ve had this privilege for the last seven years, and this is just a list of my opinions. I don’t represent the Microsoft MVP Program or anyone else here; this is just a list of my opinions.

You will not get business out of it. Again, my personal opinion: I don’t believe that the MVP Award is given out for paid activities. I could be wrong but I believed it was only due to community unpaid contributions. If you are trying to build a business on being an MVP,  or you think it will help you to build a business, then you haven’t understood the Award. It’s hard to understand where the line is drawn, however. I think of it as the ‘Father Ted’ rule, referring to the series’ running joke about Father Ted’s continued defense of himself over money, and the other characters simply do not believe him. If an activity is open to interpretation and you have to keep justifying it to other people, or to yourself, perhaps it isn’t falling on the MVP side of the fence and you’d have to speak with the MVP lead in order to get clarification and advice as well as some direction in areas where you could contribute in order to get or keep the MVP Award. The MVP Lead can keep you right.

The MVP Award is a gift that can be taken away at any time. So why not share it with other people? I believe that the true mark of a leader is that they give power away, and take care of what they leave behind. You can nominate other people at the MVP Site.

Be technically outstanding. The MVP Award can be a label that people will try to use against you. I do see this in the workplace from time to time, where people can see you as being put on a pedestal, and before they have even met you in person, they are intent on knocking you off and knocking you down. My response to this is simply that I need to be ten times as good in order to get to the same place. So, I work incredibly hard in order to make sure that happens. It can feel like you’re the Red Queen in Alice in Wonderland, running a race to keep up. The end result is that, once you’re an MVP, you can’t rest on your laurels. You have to keep running. Also, note that the MVP Award doesn’t always mean as much to other people as it does to you. I visited an organization this week, actually, who had never heard of it and couldn’t care less; they were only interested in what I could offer them. Fortunately I stood up to the test!

redqueen

Forget about number one, and have a higher vision in mind. You take a risk by being ‘seen’. You have to prepare yourself for greatness, if you want to be great at anything. This involves risk, which is the risk of being seen. You have to work at balancing a need for acceptance, which can make you invisible, versus the risks of making yourself seen. Being seen can make you vulnerable, and my way through it is by being authentic. People aren’t always going to like you. This is a tough one; it’s important to rise above it when people criticize you, and it’s important not to join in criticism of other people, too. I think you have to strive to be the person and the leader that you’d like to be. Don’t get pulled down. I’ve had some really terrible things said about me, and I just ignore it. It’s not weakness or stupidity if you don’t fight back: it’s about letting people show themselves, and having faith that others will see it.  Hard as it might be to swallow, you have to strive to show people a better way. This attitude can feel very out-of-place in the world of social media where everyone’s opinions are regarded as equivalent, and it comes down to ‘who shouts the loudest’. You have to strive to be better than that. It’s one of the risks and vulnerabilities of being seen.

Share your passions for technology. So, pass the Microsoft exams, blog, produce videos, or whatever content is your passion. You’ll learn more by sharing, trust me.

I don’t know if I will make it to 8 years as an MVP. I will find out in July. I have had a blast and I am grateful to be part of it. I show it by nominating others; so why don’t you do the same thing?

 

 

Past and Future of Self-Service Business Intelligence

I was very pleased to appear on the Izenda website along with five other Business Intelligence experts, discussing the past, present and future of self-service Business Intelligence. I was delighted and honoured to appear with luminaries such as Wayne Eckerson, John Myers, Kevin Smith, Rich Ghiossi, and Ron Powell.

 

 

Self-service Business Intelligence is a much larger topic than you might think, and it’s clear that some organizations who market themselves as ‘self-service’ aren’t really meeting the criteria. I recommend that you head over to the post in order to read it all.  I’m interested in the idea of self-service analytics as well as self-service business intelligence, and I do think that will become increasingly relevant as the industry matures.

Thank you to Izenda for having me along. Please let me know what you think; I look forward to your comments.

 

 

Cloud computing as a leveler and an enabler for Diversity and Inclusion

I had the honour and pleasure of meeting a young person with autism recently who is interested in learning about Azure and wanted some advice on extending his knowledge.
It was a great reminder that we can’t always see people who have conditions such as autism. It also extends to disability, particularly those that you can’t see; examples include epilepsy or even Chronic Fatigue Syndrome.

Diversity gives us the opportunity to become more thoughtful, empathetic human beings.

dyslexia-3014152_1920

Credit: https://pixabay.com/en/users/geralt-9301/

I love cloud because it’s a great leveler for people who want to step into technology. It means that these personal quirks, or differences, or ranges of abilities can be sidestepped since we don’t need to all fit the brogrammer model in order to be great at cloud computing. Since we can do so many things remotely, it means that people can have flexibility to work in ways that suit them.

In my career, I couldn’t lift a piece of Cisco kit to rack it, because I was not strong enough. With cloud, it’s not a problem. The literally heavy lift-and-shift is already done. It really comes down to a willingness to learn and practice. I can also learn in a way that suits me, and that was the main topic of conversation with the autistic youth that I had the pleasure to meet.

I believe that people should be given a chance. Diversity gives us the opportunity to become more thoughtful, empathetic human beings. In this world, there is nothing wrong with wanting more of that humanness.