Error converting data type varchar to numeric where ISNUMERIC finds only numbers

Posted on February 18, 2019 by Jen Stirrup

I am writing some SQL to form the basis of views to pull data from a Microsoft SQL Server source into Azure SQL Database, and then the data would go to Power BI. The data was all presented in string format initially (not my data, not my monkeys, not my circus), and I wanted to correct the data types before the data got into Power BI.

I noted that one of the columns failed to convert VARCHAR to DECIMAL. The error message is below, and it’s usually fairly easy to sort:

Error converting data type varchar to numeric

Normally, I’d use ISNUMERIC to identify the rows that fail to have a value in that column that could be converted to a number. Then, I could identify the value, and then I could replace or exclude it, as required.

However, on this occasion, using ISNUMERIC failed to identify any columns as being non-numeric. ISNUMERIC returned all of the rows as TRUE, and that confused me. I knew that something was triggering the CONVERT instruction to go wrong.

Confused? yes, me too. Credit: https://pixabay.com/en/users/lovelikeahurricane-720440/

I ran a quick query, ordering the column in ASCENDING order, while running the original offending query that failed. This showed that the query stopped at the value 9.45. I then ran another query that returned the rows, where the value was greater than 9.45, and ordered the results.

In this result set, the value came through as follows:

9.450000001e-05

Aha! This explained why SQL Server could convert the value to numeric, because of the scientific notation used when the values are very large or very small.

Now, I ran my query again, using a NOT LIKE (which I also do not like!)

WHERE [My Column Name] NOT LIKE ‘%e%’

Now, out of my large record set, I got one offending row with the scientific notation, out of millions of rows. At least I had something to work with now; I could remove the data, run an update, or work with the scientific notation.

I hope that helps someone!

Moving WordPress websites between Azure Subscriptions

Posted on February 16, 2019 by Jen Stirrup

I’m keen to learn practical aspects of Azure and cloud computing, so I can really understand their value for small businesses who rely on cloud computing. I don’t feel comfortable advocating for something I don’t really understand, or haven’t tried myself. So I set up my Data Relish website using Azure and WordPress, and integrated it with HubSpot so I could use Power BI and HubSpot together. I also set up other tools such as SendGrid by Twilio and CloudFlare. I learned a lot about technologies with which I’m not very familiar.

action-1854117_1920 So now that my learning and confidence has increased, I decided to move my Azure website from a trial/test subscription to a different subscription. So how did I do that?

It turned out to be easy to move my website and all of the artefacts from one Azure Subscription to another. Note that my setup met the following conditions and limitations for moving a Web App, which I’ve copied here from the Azure website:

The destination resource group must not have any existing App Service resources. App Service resources include:
- Web Apps
- App Service plans
- Uploaded or imported SSL certificates
- App Service Environments
All App Service resources in the resource group must be moved together.
App Service resources can only be moved from the resource group in which they were originally created. If an App Service resource is no longer in its original resource group, it must be moved back to that original resource group first, and then it can be moved across subscriptions.

How did I move Subscriptions?

AzureMySQLLogo In the Azure Portal, I selected the Azure database for MySQL database that underpins my WordPress site.

Then, I clicked on the Change Subscription link in the Overview blade.

The next page told me the associated Azure artefacts I’d need to move with it. This page was super helpful since it saved me a step in working out what else I needed to move.

From the drop-down list, I chose my new Subscription, and then clicked Apply.

I waited for two minutes while it deployed to the new subscription, and then the Azure notification popped up in the browser to say that the move had completed… and then I checked to see if my website was up and running.

Much to my huge relief, yes, my website was still up and running. As far as I can see, it all moved seamlessly across. I will be checking the functionality over the next few days just to check it is all running.

Not all Azure operations can be moved so easily, and it is worth checking before you move anything. Here’s a good Azure reference page to review before you start.

Connecting #Azure WordPress, #HubSpot data for analyzing data in #PowerBI for a small business #CRM

Posted on February 14, 2019 by Jen Stirrup

I got to the end of the free WordPress account for my small business account and I wanted to analyse my CRM and sales data better. I wanted to dial up my sales and marketing, and, of course, use data to understand my audience better. With the free WordPress edition, I could not do some of the things that I wanted, such as HubSpot integration and advanced analytics.

Why CRM?

As a small business, I rely on a lot of word of mouth business. When business leads come in, I need to track them properly. I have not always been very good at following-up in the past, and I am learning to get better at actioning and following-up.

Credit: Pexels at Pixabay

I love the HubSpot CRM solution, and I decided I’d take it a step further by integrating HubSpot with my WordPress website, which is hosted in Azure and you can see my Data Relish company site here, with the final result. HubSpot have got great help files here, and I am referring you to them.

What technology did I use?

Microsoft Azure WordPress – Azure met my needs since it could give me the opportunities for integration, plus additional space for storing resources such as downloads or videos.

Power BI – great way to create dashboards

HubSpot – CRM marketing and sales for small business

I found that using Microsoft Azure was a great way to make the jump from free WordPress to a hosted solution. Now, I am not a web developer and I do not intend to become one. However, I do want to use technology to meet my small business needs, and to do so in a way that is secure. I’m going to write up some posts on how to get started.

To get started with a website in Azure, you can follow the instructions here or watch the Channel 9 video for instructions.

Now, I needed a way of working with the HubSpot data in Power BI, and this is where the CData PowerBI and HubSpot connector comes in.
In running a small business, you need to be super-precious with your time. I could spend ages trying to create my own connector, or I could use a robust, off-the-counter connector that would do it for me.

In a small business, spending your time badly is still a cost.

In a business, you have to decide between spending money or spending time on an activity. If something is taking too long to do by yourself and someone/something could do it better but you have to pay for it, then it’s a false economy and a bad decision to do it by yourself. You’ve got a choice between expending time and effort, or a choice between spending money. Experience will tell you when to do what, but wasting time is difficult to measure.

There aren’t many options for Power BI and HubSpot, but I was pleased to find the CData connector.

Disclaimer: I didn’t tell HubSpot or CData that I was writing this blog so it isn’t endorsed by either of them.

What does CData look like?

You can download the CData ODBC Driver, which connects Power BI to HubSpot. Here’s a snip of their site:

I downloaded the trial, and then went through the install. It was easy and ‘next next next’. When it is installed, it launches a browser to ask you to log into HubSpot, which I did. Then, quickly, I got the following screen – yay, I am in business!

Then, off to Power BI to download the latest edition of Power BI Desktop. It’s easy to install, and I could get cracking very quickly.

How do we get access to the HubSpot data?

In Power BI Desktop, click on the Get Data icon in the Home tab, and then choose the ODBC option.

Click on the Connect button

Look for the HubSpot ODBC connector in the drop-down list. It should appear something like this:

Then, you will be asked for your name and password, and then click Connect:

Once you have connected, you will be presented with a list of HubSpot tables

Click the tables that you want, and the data will be loaded into Power BI.

If you don’t know which table you want, load in the tables starting with Deals first, then then compare it with the HubSpot screen. This will help you to understand better how the columns relate to your HubSpot data on your screen.

I’ll add more about HubSpot analysis in the future, but for now, happy PowerBI-ing!

Where do I find my #PowerBI tenant?

Posted on February 3, 2019 by Jen Stirrup

I’m writing this post for me, more than anyone else. I keep forgetting!

Log into your Power BI tenant and click on the Question Mark at the top right hand side.

Look for the option About Power BI. Here is an illustration below:

Choose the option About Power BI and you will get a window appear. It’s the last item that shows what you need:

//embedr.flickr.com/assets/client-code.js

So my tenant is located in North Europe.

I usually end up going to look for it in Tenant Settings but I suppose it isn’t there because you can’t change it. So I got fed up with myself forgetting it and wrote this post. If this is you too, I hear you.

Invisible voices of #diversity: being ignored, being hepeated, and what you can do to be heard and help others to be heard

Posted on December 27, 2018 by Jen Stirrup

One of the issues of diversity is that people can suffer from diversity myopia, which I understand to be the situation where people don’t see diversity clearly. I now think that the issue is that sometimes that people from diverse backgrounds are simply not seen in the first place. Like the protagonist in the Invisible Man by Ralph Ellison, you’re simply not seen in the first place.

What if this happens to you? What can you do about it? In thist post, I’m going to discuss why this happens, and what you can do about it.

Not Seeing Clearly, or not Seeing at All?

This is a key challenge in diversity. People can unthinkingly believe that they accept people regardless of backgrounds, but the reality is, they don’t always see them. This forms a confirmation bias, since we don’t measure ourselves on the data that we haven’t seen. To be really diverse, you have to see first. If someone remains ‘invisible’ and ‘unseen’, then their voices aren’t heard of their voices are ignored. This means that unthinking people are unconsciously unaware that they don’t move from ‘seeing’ to ‘understanding’ diversity. And people don’t ‘understand’ diversity if they can’t ‘see’ it, which means that they never come to value diversity.

Therefore, when dealing with people from different backgrounds, there can be instances of ‘not seeing’. For example, if someone ignores my participation in an email thread and forks a new thread that ignores my contribution, it tells me that I’m simply not being ‘seen’ in people’s inboxes. Right away, they just pass over the name, so I get dismissed right away as irrelevant.

But that was my idea…. Being Hepeated

It can also lead to instances of he-peating, where someone takes or copies your contribution and then owns it, and I think it’s probably because the idea has impinged itself upon the hepeater but you haven’t done so. Your idea has made it’s way to someone’s consciousness, but you haven’t made it. If you complain about it, then this can be interpreted as ‘unladylike’ behaviour and people only remember that you complained about something. It’s easy to ignore somoene as a complainer, than it is to really question yourself over your behaviour.

Mastering the Lizard Brain

crocodile-2434838_1920 Mastering the lizard brain really developing a harsh lens on yourself to obtain self-awareness and realize your impact on others. It’s my hunch that many adults never reach that stage of development at all, but if you are leader, then you have to be very self-honest to strive to master the lizard brain.

Ask yourself hard questions. What would you think if someone did that to you? Can you see things throug their lens? It becomes very uncomfortable to ask yourself if you are really diverse or not. We like to live in the comfort zone but that’s not where the growth takes place.

Reasons or excuses? You have to ask yourself if you are giving yourself reasons or giving yourself excuses.

Something isn’t true just because you said it. In conversation, I can hear someone give themselves an excuse for behaving in a certain way, and it can seem as if they accept it as truth simply because they said it. Our brains fool us into being directed down that path, because we said it.

Again, it’s about mastering the lizard brain and being flexible and adaptable to new data and analysis of ourselves, even if we feel uncomfortable.

What can you do to be heard?

timeout-3373329_1920 I think that a lot of people struggle with this issue. I know that I do. Why does this happen?

I’m going to propose ways that you can try to overcome these issues.

Always add value to the meeting or conversation. Don’t use weasel words. Remove phrases from your vocabulary such as ‘I think’, ‘perhaps’, ‘in my opinion, ‘this could just be me but maybe’ and other weasel words.

Practice speaking up more, and with more confidence and power. Ask yourself; do I sound sure or do I invalidate myself with disclaimer type phrases?

Try to learn to interrupt politely. There are some great tips here. Here’s my favourite idea and I do this a lot: in a business meeting with a lot of people with big egos, pauses dont’ come often so you will have to jump in. You’ll seem less rude if you first restate (“If I hear you correctly, you’re saying XYZ’ and then follow on from there.

Learn to hold the floor when you speak. You can do this by adding some colour to what someone else has just said (e.g. ‘following on from Jane’s point) but adding new insights that add depth or breadth.

masks-1242822_1920 You can set up allies in the room so you can echo and support each other. I have had to do this, and it doesn’t feel good because it feels like you’ve already lost before you’ve opened your mouth. However, it does work and it will help you to network and get things done under the radar.

Learn to sell your achievements without going overboard e.g. ‘my team and I’ is a nice starter as a way of discussing your achievements.

Sit near the centre of the table or the floor. Sitting at the side or the back can mean it’s harder to grab and retain attention.

Be judicious in allowing yourself to be interrupted. If someone simply repeats what you said, pretty much, then be careful that it isn’t a potential hepeater.

Practice and improvisation. I did a course in improv, and it helped a lot. Here’s a reference if you’d like to follow this up: Leading with Applied Improv with Izzy Gesell. There’s a lot of wisdom there and I recommend that you follow it up on LinkedIn. https://www.linkedin.com/learning/leading-with-applied-improv/reflection-leadership-and-empathy

When you think about this list for yourself, keep others in mind, too. You can be the person that brings others along a journey to new heights and experiences in their career, thereby building your network and being a leader. Think about the lizard brain in yourself and others.

Custom Modules for #AzureML

Posted on December 19, 2018 by Jen Stirrup

Have you seen the custom modules for AzureML yet?

One neat custom module that I’ve been using is the Create Scatterplot module, which uses ggplot2 and R in order to create a scatterplot. It’s possible to do this in AzureML already but it takes a few extra steps, and it is good to be able to reduce clicks in doing activities where possible.

It is useful for identifying outliers, which has been one of my areas of investigation in the current Data Science project that I’m currently leading.

How do you use custom modules?

Open the sample experiment in AzureML Studio
Add the module to a project so it is accessible to all of the experiments in the project
When you next open your experiment, you will see a new module group on the left hand side, called Custom. The Custom module will be in it.

And that’s it! You can now use the module in your experiment.

Business Goals for 2019: Looking back over 2018 and forward

Posted on December 14, 2018 by Jen Stirrup

December is a great time for setting personal and business goals for next year. I’d hoped for some downtime at this time of year, but I’ve picked up a crucial data science project which needs to be delivered over Christmas so there isn’t much downtime. I’m also starting to write another book, which will be my fourth published book, and that will require focus. So I will be working every day except Christmas Day, and that includes weekends as usual.

As part of my MBA program, I’ve done lots of strategic analysis modelling for businesses, starting with the default SWOT and a PESTLE analysis through to different models: Porter, Business Canvas Model, SOAR, and so on. I am not going to share here. The reality is, with Brexit, it’s more difficult to plan. Markets, and businesses, do not like uncertainty. Businesses, like countries, benefit in the longer term from open trade. Increasingly, global competition will occur on a more level playing field but there has to be open access for that to occur. Brexit is a plan for the right by the right, and it is stopping the open access that we enjoyed previously.

For what it is worth, here is my take on Brexit: I believe that May’s Remainy Brexit plan will never get through Parliament because it pretty much keeps us in the Customs Union. This means that the People’s Vote option will become increasingly likely. We are no longer talking about the big red bus lie and we see the chaos. If there is another vote, I hope that we will stay in the EU as we are now. So I am going to assume that all other things remain equal.

More than talking a good game

I was contacted me recently, by a former colleague who explained to me that someone had spoken to them about me. This ‘someone’ had told them that I was too small to deliver a project, that I didn’t know what I was talking about but I talked a ‘good game’, and that I would never deliver on time or on budget. The former colleague was so concerned about what they’d heard, that they invited me to lunch to explain the accusations which were stated behind my back. I was so horrified and fairly mystified; I had never worked with this individual and I didn’t really know them. I just asked for specific examples where I had just ‘talked a good game’ and not delivered on time or on budget, because I had no idea where they got their evidence from. It was all fairly alarming but of course the ‘someone’ hadn’t given any basis for their assertions at all.

In any case, the former colleague simply said that they would continue to work with me and they enjoyed working with me in the past; they had their own evidence to go on, and it is a mark of our good relationship that they talked to me about it at all. I was hugely relieved but I do wonder how often these tactics work, so I’ve decided to explain myself here and discuss this in terms of my goals.

So if you’re here because you’ve heard something similar, please let me know and we can have an honest discussion. I’ve got over twenty years as a Consultant, and I have postgraduate degrees plus industry experience in delivering Artificial Intelligence solutions. I’m writing my fourth book in Business Intelligence and I’ve edited another two. I have presented to five thousand people in the past month. So I do talk but I also deliver.

How was 2018 for you?

Data Relish Ltd was originally set up as Copper Blue Consulting, and was rebranded three years ago in order to make the offerings more clear. I have employed people in the past, and I had a business partner for a few years but that relationship is no longer in place. I do work for myself but note the following points:

Contracts and Partnerships

Current Status for 2018: I have partners who help me to deliver effectively. I have contracts in place with large partner organizations which allow me to scale; I have partnerships for 24 by 7 support, for example. Partnerships allow me access to sales, marketing and legal backup when I need it. Some of these partnerships are with Azure resellers and organizations, and I do not directly sell Azure so that my customers can benefit from the support that my partner organizations offer.

I am not releasing partner names here; I have a degree of concern, originating from the conversation with my customer, that there is a strong need for confidentiality on the basis that I’ve had the aforementioned stunt pulled on me. I am not giving any names away so that I inadvertently give detractors the opportunity to repeat their performance.

Customers

Current Status for 2018: 50% repeat customers with 50% new customers generated by word–of-mouth from previous customers

I am able to provide references on request from organizations of different sizes, verticals and global impact. Some of my customers are multi-billion turnover, right down to startups. I don’t make a huge effort to market myself because all of my work comes from word-of-mouth. Since I am so dependent on word-of-mouth marketing, it can mean I’m susceptible to stunts like the one mentioned above since I don’t have the flashy marketing that might give some people comfort. I have started to look more closely at marketing and I have set up a partnership with a small consulting firm to help me. It works well since they give me help, but I also pass on leads that I receive, and we have jointly delivered projects on occasion.

Business Community

Current Status for 2018: I started more business networking last year but it tailed off in February. Reason: I was not picking the right events well enough and I got disheartened.

I have started to attend Chamber of Commerce events again, and I met with David Gauke MP (Minister for Justice) and Richard Harrington MP (Minister of Business and Industry) this year. I am interested in learning from the local business leaders here in Hertfordshire, and also looking at ways that I can feed back into the business community. Networking is important and it builds trust. Additionally, being part of the local Chamber groups is offering the route of having our voices heard jointly in Government, and I was surprised to learn that our local MPs have regular and frequent meetings with Chamber representatives. Business is crucial to the success of society by keeping cashflow moving, providing jobs and stability and homes. I advise businesses strategically because I believe in putting the business back into business intelligence, and at the front-and-centre of artificial intelligence efforts.

Charity

I was a Data Ambassador for DataKind this year. Wow! What a rewarding, creative experience and it was incredibly satisfying to work with people who love data and want to do good with data science to help charities.

Current Status for 2018: I got involved with DataKind. You know who you are and I love you all; you have brought such knowledge, fun and great company to my life and you shine your light far out. You cancel out the darkness where people behave as in the anecdote above. Thank you for your healing.

So what are my Goals?

Here is the list of goals for 2019:

Contracts and Partnerships

Goals for 2019: I have partners who help me to deliver effectively and I will continue to honour the trust that they have put in me.

In 2019, I do not intend to employ people now since it takes me away from doing things that I love. I may do so in 2020 but it is not the right thing for me at this time. I have previously lost sleep over making people redundant and I do not want that burden again. I work on projects, often, that are simply too big for me to run out and hire a whole team of people and it would be madness to try. How could I even support things like 24 hour support? These are big projects and I need help quickly. Customers need help with AI, BI and Data Science leadership. So it works well.

Customers

I have done extremely well this year and I am proud of my customer list. Through partnerships and on my own, I have punched well above my diminuitive size to have a customer list where my customers have billions and billions in turnover and it is a mind-boggling amount when I add up the turnover amount of all these customers combined.

I do help them to be even more successful and I do help them make technology choices as part of the vision and strategy that I help them to formulate and devise.

Goal: More repeat custom and generate new customers. 50%/50% split between regular customers and new customers is a good split, and it worked well for me this year. As I lead customers to success, they drop off my customer list because they don’t need me any more; but that’s what I want! Self-sufficient, satisfied customers who are enabled and empowered to move on, and who recommend me to their colleagues and community.

Business Community

I am going to set myself the target of attending more business events this year. I am currently researching the business events that have value, and the ones that do not offer value to me.

Goal: I intend to re-ignite my business networking in a more targeted way. I have been pleased to meet leaders that I can learn from, and i hope to contribute more.

Charity

Goal for 2019: More of the same. I get enormous richness from philanthropic efforts and I want to help people.

I wish you all the best for 2019! And don’t let anyone steal your pixie dust.

pixiedust