Immersive Reader is now LIVE! New addition to Azure Cognitive Services family

Today, Microsoft launched Immersive Reader, aimed at increasing the opportunities for developers to embed inclusivity in their apps. I love reading, and I love the idea that reading will be brought to people who might otherwise struggle with text reading and comprehension.

You can learn more over at the Azure Cognitive Services website. If you want to get started straight away, here is the Ibiza Portal Create Blade and here’s the Documentation.

Developers have such a unique place in the world, using their creativity to make things better for other people. I’m excited about this journey and let’s see the magic that people create!

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.

 

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!

Microsoft Ignite interview with Kevin Farlee on Azure SQL Database Hyperscale

Azure SQL Database is introducing two new features to cost-effectively migrate workloads to the cloud. SQL Database Hyperscale for single databases, available in preview, is a highly scalable service tier that adapts on demand to workload needs. It auto-scales up to 100 TB per database to significantly expand potential for app growth.
What does this mean? It’s one of the most fundamental changes to SQL Server storage since 7pm. So this is big: big news, and very big data stores. I am very lucky because I got to interviewe Kevin Farlee of the SQL Server team about the latest news, and you can find the video below.

I am sorry about the sound quality and I have blogged so that the message is clear. When I find the Ignite sessions published, I will add in a link as well.
What problem are the SQL Server team solving, with Hyperscale? The fundamental problem is how do you deal with very large databases in the cloud. VLDBs is the problems that people want to do with normal operations. All the problems with VLDBs occur due to the sheer size of data, such as backups, restores, maintenance operations, scaling. Sometimes these can take days to conduct these activities, and the business will not wait for these downtimes.  If you are talking tens of terabytes, that takes day and ultimately Microsoft needed a new way to protect data and VLDBs. The SQL Team did something really smart and rethought very creatively on how they do storage, in order to take care of the issues with VLDBs in the cloud.
So, the Azure SQL Server team did something that is completely in line with one of the main benefits and key features of cloud architecture: they split out the storage engine from the relational engine. Storage implementation was completely rethought and remastered from the ground up. They took the viewpoint over how you would go about architecting, designing and building for these solutions in the cloud, if you were to start from scratch?
The Azure SQL Server database team did a smart thing: Azure SQL Server is using microservices to handle VLDBs.
The compute engine is one microservice which is taking care of it’s role, and then another microservice that is taking care of the logging, and then a series of microservices that handle data. These are called page servers, and they interface at the page level. The page servers host and maintain the data files. Each page server handles about a terabyte of data pages. You can add on as many as you need.
Ultimately, compute and storage are decoupled so you can scale compute without moving the data. This means it’s possible to keep adding more and more data, and it also means that you don’t have to deal with the movement of data. Moving data around when there are terabytes and terabytes of data isn’t a trivial task. The page servers have about a terabyte of data each, and the page servers have about a terabyte’s worth of SSD cache.
The ultimate storage is Azure Blob Storage, because blob storage is multiply redundant and it has features like snapshots, so this means that they can do simultaneous backups by just doing a snapshot across all of the blobs. This has no impact on workload.
Restores
Restores are just instantiating a new set of writeable disks from a set of snapshots, and works with the the page servers and the compute engine to take care of it, working in symphony. Since you’re not moving the data, it is faster.
I’m personally very impressed with the work that the team they’ve done, and I’d like to thank Kevin Farlee for his time. Kevin explains things exceptionally well.
It’s worth watching the video to understand it. As well as the video here, Kevin goes into detail in his Microsoft Ignite sessions, and I will publish more links when I have them.
Community
One advantage in doing the MIcrosoft Community Reporter role is that I get to learn from the experts, and I enjoyed learning from Kevin throughout the video.
It seems to me that the Azure SQL database team have really heard the voice of their technical audience and they’ve worked passionately and hard to tackle these real life issues. I don’t know if it is always very clear that Microsoft is listening but I wanted to blog about it, since I can see how much the teams take on board the technical ‘voice’ from the people who care about their solutions, and who care enough to share their opinions and thoughts so that Microsoft can improve their solutions.
From the Azure architecture perspective, it works perfectly with the cloud computing concept of decoupling the compute and the storage. I love watching the data story unfold for Azure and I’m excited by this news.

Why I changed my mind about #PowerApps and became a fan… and why you should, too

This blog post title was initially titled ‘Why I disregarded PowerApps but now I radically changed my mind’. I changed my mind and I now think that PowerApps is a very powerful tool and basically it does a lot of the app form functions that Access does but way better. I get it now and I want to explain how I changed my mind about PowerApps.

PowerApps performs the same function as Access can do: building the business apps you need and extend or customize the apps you already use. Only, PowerApps does it way better because it has the robustness, scalability, security and performance of the Azure cloud with Office365 inbuilt in the app from the start.

In this blog post, I’ll set out where I think PowerApps fits in the business, and why it’s way better than Access. There are plenty of PowerApps templates but I hope that this post will convince you to give it a try.

TL;DR – I care about producing business-friendly solutions that are maintainable, robust, perform well, have potential for scale, and that I can hand off to an IT support team. PowerApps gives me these things. Access does not because it is not always backed up, or placed on a network so people can update it, and it is not always secured properly. So you can think about migrating your Access apps to Power Apps, for example. In this blog post, we will answer the following questions:

  • What can PowerApps do for you?
  • How do you get started with PowerApps?
  • What I’d like to see PowerApps do next

contact-icon
Any questions, please let me know or get in touch to schedule some time to talk further.

 

What can PowerApps do for you?

PowerApps allows you to design, create, use and share custom business apps with your team and organization. PowerApps installation means that you can use apps that have been shared with you, and it also means that you can create your own apps.

What kind of apps can you create? Intelligent laziness is very important! The automation of tasks means that you can focus on the activities that are more meaningful and add more value to the organization. If you are leading a team, then it means that you and your team can work wherever you happen to be, on any device.

What kind of apps can you create? Many Access forms could be redone in PowerApps since they involve data entry. Here are some examples:

  • Connect apps to your existing data, such as SharePoint, Salesforce, Dropbox, Google Drive and more
  • Create and update customer invoice, receipt and order forms
  • Create Helpdesk apps
  • Create Expense apps

As a BI person, I have spent time curing businesses from cottage industry Access databases and Access forms. Often, in my experience of rescuing Access databases and forms, I find that they proliferate through the business, usually unmonitored, untested properly and aren’t always production ready. They are often built in an ad-hoc fashion. In short, when I find some Access, I perform an internal sigh and I immediately start to mastermind it’s replacement. Access is often a the sticking plaster to solve a genuine business problem and it is quick to produce something quick. As Brent Ozar puts it, Access has Play-Doh factor. Yes, it’s an old post from Brent Ozar ( b ¦ t ) but it is still holding true.

play-doh-3308885_1920

So when I saw PowerApps at first, my reaction was ‘Oh no, it’s the new Access’. My initial vision was that developers would hate it, and I’d spend my life running around chasing after apps as well as Access to make them production ready.

So here’s what I found:

PowerApps has the IT guardian role built in

PowerApps solves the Access problem of scaleability, robustness and performance. PowerApps is built in Azure as part of Office365 so that means that Azure takes care of these factors for you. For me, this is a massive relief because I care about producing solutions that are maintainable, robust, perform well, have potential for scale, and that I can hand off to an IT support team. When I leave site, I want to leave with a job well done. I don’t intend to be there forever; it’s not what I do. So I like to enable others and bring people on a journey that they can continue without me. So what does PowerApps mean for the small business?

 keyboard-2553530_1920

PowerApps for the Small Business 

PowerApps is robust and secure. If you are using Access or Excel to run your business, then you should consider PowerApps. It provides a much more robust way for small companies to track data and projects than Excel or Word because it is in the cloud so you don’t need to worry about losing or screwing up your Excel spreadsheet of inventory, orders and so on. Also, it means that you can be more confident that your data is secure because it is in the cloud, and not on a USB.

PowerApps is mobile. It is also an easy way to mobilise apps as well. The functionality is built in. This is another big win over Access, in my book. This means that PowerApps has the most value added for tracking your organization in terms of projects, budgets, and growth.

PowerApps lets IT do their job more easily. PowerApps will give your IT people an easier way to manage your business-built apps rather than having them hidden on a laptop somewhere. PowerApps lets IT do their job, of being the guardian of the data and the IT function. It’s easy to maintain, which is idea for the organic business user turned power user turned ad-hoc techie. The idea of creating databases and trying to maintain them seems like an unnecessary use of resources; however, for small businesses, PowerApps creates a database for you so you can focus on what you need to do for your organization: managing growth and delivering success.

PowerApps has Play-Doh factor but in a good way. Creating apps in PowerApps allows a business to customize its own apps, thereby streamlining business processes. For example, you can use it to track crucial business information for each customer, including contact information, addresses, order information, invoices, and payments. Since it is in the Azure cloud, team members can access and update it, so the information can stay current and secured. Since the frm is a central idea to Power Apps, this helps small businesses ensure that employees enter data accurately and consistently so your data is cleaner at source.

How do you get started with PowerApps?

At a high level, here are the steps:

  • Choose from professionally designed templates to start quickly
  • Start with a blank canvas, and customize to suit your needs
  • Add business logic and intelligence using the power of Excel-like expressions
  • Create interactive visuals and media to build unique, professional apps

PowerApps is an easy jump for the BI Developer who already knows DAX. To create more custom apps, PowerApps has some object-oriented ‘formula’ that you can change, configure and set. It also has intellisense so you are guided to program correctly.

PowerApps is constantly being updated. For information about what’s new in this release, please see this topic: https://aka.ms/powerapps-releasenotes