Data Warehousing and Business Intelligence in the Cloud with #Azure: How do we get good, fast, cheap and easy?

Customers want their data good, fast, cheap and easy. A tall order, right?

One of the biggest challenges that I see with data warehousing in the cloud is that customers are concerned with cost. I was interested to see the Gigaom report on the topic of Data Warehousing in the cloud, which contained a number of benchmarks, including cost.

The study by GigaOm showed that Azure SQL Data Warehouse is now outperforming the competition up to a whopping 14x times at up to 17 times cheaper than the competition, namely, Google BigQuery and AWS Redshift. This is an incredible achievement and the Azure team should be proud!

As part of my work in Business Intelligence, often, this involves a move to the cloud by default. Simply put, customers want quick Business Intelligence and they don’t want to spend time or effort in looking after kit. They want to delegate the responsibility. This means that cost is a key differentiator, since they want their data, good, fast and cheap. I’m glad to see that the Azure SQL Datawarehouse is competing on cost and performance since customers do want their data good, fast, cheap and easy.

Customers also want their data easy and this is where Power BI comes in. If a customer wants to use Power BI, I generally recommend that they put their data into Azure so that the data is traversing the Azure network. This means that the customer is not paying to extract or access their data from another cloud system and then put it into Power BI.

The Gigaom paper on cloud data warehousing is worth a read – and I am not just saying that because I‘ve done work as a Gigaom analyst! You can access the paper here.

 

Power BI Dataflows and fixing ‘Your Azure storage account must be in the same Azure Active Directory tenant as your Power BI tenant.’

I’m excited about Power BI dataflows, since I believe it’s a great way forward for companies to sort out the issue that nobody likes to talk about: cleaning data. I’m passionate about this topic since I believe in inheriting technical debt as much as I can, so I leave the organization’s technical debt in a better place than I found it. I’m taking the long view when I de

I tried to connect my Azure Data Lake gen 2 storage to Power BI, and I ran into this error message:

”Your Azure storage account must be in the same Azure Active Directory tenant as your Power BI tenant.’

I checked, and my Azure storage account was definitely in the same Azure Active Directory tenant as Power BI. So I was confused. What a pesky error! So I decided to investigate.

It turns out that I had missed a step when setting up the connectivity between Azure Data Lake gen 2 storage account and Power BI. I had missed assigning the Reader role to the Power BI service on the storage account.

Microsoft’s instructions are here and make sure you follow them to the letter. I missed a step because I rushed through it, and then spent time trying to figure it out, so it was my fault since I scrolled past a step. I’m recording this issue here in case anyone else gets this error message, and wonders what’s going on.

 

Forming #Leadership habits: Getting to Inbox Zero while increasing sales

There are plenty of websites dedicated to helping you to become a good leader, and to form good leadership habits.  I believe that authenticity and emotional intelligence (EQ) form some of the hallmarks of a leader, but it is hard to demonstrate. Habits don’t make leaders in themselves, and neither does authenticity or EQ on their own. It all has to add up properly.  It seems that either you have authenticity or not. It becomes very apparent, very quickly, that you are simply ‘at it’ and people can smell that far away.

I started to look at developing habits that would help me to perform better as a team player and as a leader. I was also very aware that I needed to do better in sales, and I needed to record my sales better. I also needed to get better at following up with people. This would demonstrate EQ, since I’d be better able to show people that I cared by getting back to them. I also needed to follow up sales leads better, something I wasn’t good at.

I’m going to share how I got to Inbox Zero while increasing sales.

Get into the habit of… diligence

The number one point, though, is that at some point you really need to do the work. No Facebook, no distractions, no whatever; you need to plough through it. I created this system to help me to focus better, which, in turn, dialled down my email level and it also led to increasing sales for Data Relish. So here are the steps:

Get through email in Outlook like a Rockstar

I recommend you follow Luise Freese’s recommendations for setting up priorities and quicksteps for labelling email, which mean you can priorities tasks and emails like a Rockstar.  I follow every single step that Luise devised. Luise clearly explains how to set up your mailbox for productivity, using Quick Steps, folders and labelling. I follow Luise’s system, and I focus on the TODAY folder that she recommends you set up.

I changed the system a little. For the ‘TO READ’ folder, I set up a quickstep that forwards the email to Evernote. I have loved Evernote since 2011 and it is a great way of storing notes which I want to read later.

Get into the habit of… delegating

How can you delegate?

I hired a part-time PA

Initially, it was tough to have a PA because I am not used to diary management or someone handling my email. These are hard things to give up, but I had to do it in order to shed things that were holding me back from spending my time better.

Get into the habit of diligently recording every sales lead

It is important to record every single lead, no matter how small. It is a habit which is just as hard. It’s easy to procrastinate, or get distracted.

I use two systems; HubSpot and Insightly. HubSpot is great at marketing, and I use it with Power BI. I use it for my website, and mainly for marketing and sales tracking. I also use Insightly, which also works with Power BI. This might seem like a repeat step, but I like the small project feature which comes with Insightly because I can convert my Opportunities into Projects. HubSpot doesn’t have that final step. I use the Outlook addins for Insightly, Hubspot and Evernote.

I am still test-driving both systems, but it is easy to automate both systems. Put together, they enable my process for my marketing, sales and post-sales work. My PA can support me in this venture, too.

Get into the habit of… better directing your attention

I get a lot of requests for my time by people who want to obtain my help for something, and want my time for free. As an MVP and an RD, I’m active in the tech community and I do help people without expectation of recompense. However, I just can’t service them all. I got 20 requests in the past three days, and it’s a lot for one person.

I already do a lot of volunteer work so I already have an impact in the philanthropic and charitable space. So I have to triage and prioritise free requests for my time. I am trying to be fair to everyone, including myself. My PA really helps here; she gets back to people, explains that I am unavailable but I’ve suggested that they speak to X or Y (for example) or read Z as a starting point, and to come back to me if they are still struggling. So I try to help people on their way, within the limits of my capacity and demands on my time. Usually, people are pretty understanding, particularly if I explain that I’m doing some charity work which impacts issues I care about, such as diversity, and that there will be other routes for people to get what they need. It puts things into perspective, I think.

I have switched off email notifications on my phone, so I can focus properly.

Follow up on sales leads

If you’re following Luise’s system, then you have a Today folder which forms your working memory of things that you need to do.

Using HubSpot and Insightly means that I am better at following-up on sales leads. I use Outlook tools to log emails to both systems, since I’m still test-driving. This only takes a few seconds with both plug-ins.

However, the point here isn’t about the technology; it is about the process. I have a process which means I follow up on leads better. It was something I identified that I was bad at, and it really came down to having a proper process in place, supported by tech.

Actually doing the work

There are plenty of sites and books which promise productivity. The reality is, at some point, you have to do the work. It is not going to go away.

For me, I took out office space in Hertfordshire, and I find I’m able to work well there. I have a separate private room for phone calls, and I tend to book out an hour or so of my time to get through all my calls, one after the other. Once that’s done, I can concentrate on other things.

I chew my way through my Today folder, and I follow the GTDish methodology: delete, delegate, defer, do. And I just keep going.

Remembering what you did

I often need to search for documents. I use the Insightly Opportunity tracking number everywhere, and then the Project number when the Opportunity becomes a real project. I hate spending time to search for documents. I also use tagging in SharePoint Office365.

Journaling

I journal to increase my EQ. I am watching myself for inconsistencies in my behaviour, and to look at things through different lenses. It means I challenge myself to see if I’m thinking and behaving with authenticity, and I ask a lot of myself. But, if it means I become a better person, then it is worth it.

 

I think self-improvement is important, and I’ll keep doing it until I die. This system may change, but it is working for now. I’m open to other ideas, so please feel free to comment if you have any thoughts which might help me.

 

 

 

 

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

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.

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

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_1920So 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?

AzureMySQLLogoIn 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

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.

 

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:

CData PowerBI ODBC Driver

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!

CData Authorization Successful

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.

Get Data ODBC

Click on the Connect button

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

ODBC Hubspot Power BI

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

ODBC HubSpot Username password

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

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?

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:

Power BI Images Find Tenant

 

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

 Power BI Images Find Tenant//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.