Gain deeper insights with Microsoft Intelligent Data Platform

Data is foundational to any digital transformation strategy, yet many organizations struggle to understand what data they have, how to extract insights from it, and how to govern it—according to a 2022 Evanta survey, over half of Chief Data Officers (CDOs) struggle with siloed operating models when it comes to data sharing and democratization. According to Harvard Business Review, organizations that have embraced their data as a strategic asset have been better positioned to drive strategic differentiation and grow their revenue, but the fragmentation that exists today between databases, analytics, and governance is a common barrier to success.

The Microsoft Intelligent Data Platform, empowers organizations to invest more time creating value rather than integrating and managing their data estate. It integrates best-in-class solutions across Microsoft’s technology stack—breaking down data siloes and enabling organizations to extract real-time insights with the data governance needed to run the business safely.

“Shifting from a legacy on-premises data warehouse to Azure Synapse, supported by Datometry, has allowed us to virtualize the vast majority of our code without needing to repoint it. We have gained speed, performance, and agility while reducing costs and taken a big step forward in modernizing our enterprise data storage and management.”—Charlotte Lock, Director of Data, Digital & Loyalty at Co-op.

Added security and analytics features for the Azure data portfolio

The Microsoft Intelligent Data Platform features everything already available in the Azure Data portfolio (Azure Data Factory, Azure Data Explorer, SQL Server 2022, Azure SQL, Cosmos DB, and more.) as well as new products and features, including SQL Server 2022, Azure Synapse Link for SQL, Microsoft Purview Data Estate Insights, and Datamart in Power BI:

◉ SQL Server 2022, currently in preview, is the most secure database of the last decade. And is now integrated with Microsoft Purview and Azure Synapse Link, allowing for richer insights and governance from data at scale. SQL Server 2022 also comes with new features including AWS S3 support, Azure Active Directory authentication, Query Store hints, as well as security improvements compared to SQL Server 2019.

◉ Azure Synapse Link for SQL, now in preview, offers real-time analytics for data stored in Azure Synapse Analytics and Azure SQL. It is an automated system that allows for replication of data from transactional databases (both SQL Server 2022 and Azure SQL Database) to a dedicated SQL pool in Azure Synapse Analytics. Azure Synapse Link features near real-time analytics, low-code/no-code solutions for replicating data, as well as minimal operational impact on source systems.

◉ Purview Data Estate Insights is an application that provides Chief Data Officers and other strategic leaders with a summary of their data estate and the risk associated with that data. Purview provides insights on data stewardship, inventory, curation, and governance through automatically generated reports which can be easily shared with stakeholders.

◉ Lastly, Datamart in Power BI allows analysts to access richer insights from their data sets through data marts. Datamarts are self-service analytic solutions that help to bridge the gap between business users through a simple and optionally no-code experience. With datamarts, you can easily ingest and prepare data, add business semantics to data, manage and govern data, as well as build and share reports.

Real-world applications for businesses through real-time data

Let’s explore one example of how the Microsoft Intelligent Data Platform helped navigate supply chain issues:

Many operations companies conduct daily batch runs, where they must manually track their inventory levels and input the data at least once a day. With this method, these organizations cannot accurately predict how much product to sell and must err on the side of selling less to avoid running out of inventory. In times when supply chains are uncertain, this means companies miss out on even more sales.

With the Microsoft Intelligent Data Platform, companies can get real-time information on current inventory levels, rather than a daily report. They can also extract AI-driven insights based on demand spikes, shipping delays, and factory status that predict how many units will be available in a week’s time. This information is supported by the upgraded SQL Server 2022 as well as Azure Synapse Link for SQL server, which allows for more on-premises data to be extended to the cloud, analyzed, and used for decision making.

But what about using data for customer-facing solutions? The Microsoft Intelligent Data platform leverages the CosmosDB platform, providing consumers with recommendations for the best product based on real-time availability of units, delivery time, and compatibility with their needs. Consumers also have access to a support number powered by Power Virtual Agents; through Conversational AI, consumers can get intelligent updates on their order status so they can get the information they need quickly.

Source: microsoft.com

Continue reading

How to choose the right Azure services for your applications—It’s not A or B

If you have been working with Azure for any period, you might have grappled with the question—which Azure service is best to run my apps on? This is an important decision because the services you choose will dictate your resource planning, budget, timelines, and, ultimately, the time to market for your business. It impacts the cost of not only the initial delivery, but also the ongoing maintenance of your applications.

Read More: PL-900: Microsoft Power Platform Fundamentals

Traditionally, organizations have thought that they must choose between two platforms, technologies, or competing solutions to build and run their software applications. For example, they ask questions like: Do we use Web Logic or WebSphere for hosting our Java Enterprise applications?, Should Docker Swarm be the enterprise-wide container platform or Kubernetes?, or Do we adopt containers or just stick with virtual machines (VMs)? They try to fit all their applications on platform A or B. This A or B mindset stems from outdated practices that were based on the constraints of the on-premises world, such as packaged software delivery models, significant upfront investments in infrastructure and software licensing, and long lead times required to build and deploy any application platform. With that history, it’s easy to bring the same mindset to Azure and spend a lot of time building a single platform based on a single Azure service that can host as many of their applications as possible—if not all. Then companies try to force-fit all their applications into this single platform, introducing delays and roadblocks that could have been avoided.

There's a better approach possible in Azure that yields higher returns on investment (ROI). As you transition to Azure, where you provision and deprovision resources on an as-needed basis, you don't have to choose between A or B. Azure makes it easy and cost-effective to take a different—and better—approach: the A+B approach. An A+B mindset simply means instead of limiting yourself to a predetermined service, you choose the service(s) that best meet your application needs; you choose the right tool for the right job.

Figure 1: Azure enables you to shift your thinking from an A or B to an A+B mindset, which has many benefits.

With A+B thinking, you can:

◉ Select the right tool for the right job instead of force-fitting use cases to a predetermined solution.

◉ Innovate and go to market faster with the greater agility afforded by the A+B approach.

◉ Accelerate your app modernizations and build new cloud-native apps by taking a modular approach to picking the right Azure services for running your applications.

◉ Achieve greater process and cost efficiencies, and operational excellence.

◉ Build best-in-class applications tailored fit for your business

As organizations expand their decision-making process and technical strategy from an A or B mindset to encompass the possibilities and new opportunities offered with an A+B mindset, there are many new considerations. In our new book, we introduce the principles of the A+B mindset that you can use to choose the right Azure services for your applications. We have illustrated the A+B approach using two Azure services as examples in our book; however, you can apply these principles to evaluate any number of Azure Services for hosting your applications–Azure Spring Apps, Azure App Service, Azure Container Apps, Azure Kubernetes Service, and Virtual Machines are commonly used Azure Services for application hosting. A+B mindset applies to any application, written in any language.

Source: microsoft.com

Continue reading

Deliver Java apps quickly using Custom Connectors in Power Apps

In 2021, each month we will be releasing a monthly blog covering the webinar of the month for the Low-code application development (LCAD) on Azure solution. LCAD on Azure is a new solution to demonstrate the robust development capabilities of integrating Low-code Microsoft Power Apps and the Azure products you may be familiar with.

More Info: MS-700: Managing Microsoft Teams

In this blog, I will briefly recap Low-code application development on Azure, how the app was built with Java on Azure, app deployment, and building the app’s front end and user interface (UI) with Power Apps.

What is Low-code application development on Azure?

Low-code application development (LCAD) on Azure was created to help developers build business applications faster with less code, leveraging the Power Platform, and more specifically Power Apps, yet helping them scale and extend their Power Apps with Azure services.  

For example, a pro developer who works for a manufacturing company would need to build a line-of-business (LOB) application to help warehouse employees track incoming inventory. That application would take months to build, test, and deploy. However, with Power Apps’ it can take hours to build, saving time and resources. 

However, say the warehouse employees want the application to place procurement orders for additional inventory automatically when current inventory hits a determined low. In the past, this would require another heavy lift by the development team to rework their previous application iteration. Due to the integration of Power Apps and Azure, a professional developer can build an API in Visual Studio (VS) Code, publish it to their Azure portal, and export the API to Power Apps integrating it into their application as a custom connector. Afterward, that same API is re-usable indefinitely in the Power Apps’ studio for future use with other applications, saving the company and developers more time and resources.

Java on Azure Code

In this webinar the sample application will be a Spring Boot application, or a Spring application on Azure, that is generated using JHipster and will deploy the app with Azure App service. The app’s purpose is to catalog products, product descriptions, ratings, and image links, in a monolithic app. During the development of the API Sandra used H2SQL, and in production, she used MySQL. She then adds descriptions, ratings, and image links to the API in a JDL studio. Lastly, she applies the API to her GitHub repository prior to deploying to Azure App service.

Deploying the Sample App

Sandra leverages the Maven plug-in in JHipster to deploy the app to Azure App service. After providing an Azure resource group name due to her choice of ‘split and deploy’ in GitHub Actions, she only manually deploys once, and any new Git push from her master branch will be automatically deployed. Once the app is successfully deployed, it is available at myhispter.azurewebsites.net/V2APIdocs, where she copies the Swagger API file into a JSON, which will be imported into Power Apps as a custom connector.

Front-end Development

The goal of the front-end development is to build a user interface that end-users will be satisfied with, to do so the JSON must be brought into Power Apps as a custom connector so end users can access the API. The first step is to import the open API into Power Apps; note that much of this process has been streamlined via the tight integration of Azure API management with Power Apps.

After importing the API, you must create a custom connector and connect that custom connector with the Open API the backend developer built. After creating the custom connector, Dawid used Power Apps logic formula language to collect data into a dataset, creating gallery display via the collected data. Lastly, Dawid will show you the data in a finalized application and walk you through the process of sharing the app with a colleague or making them a co-owner. Lastly, once the app is shared, Dawid walks you through testing the app and soliciting user feedback via the app.

Source: microsoft.com

Continue reading

Low-code development series: Modernize your IoT future with Azure and Microsoft Power Platform

In 2021, each month there will be a monthly blog covering the webinar of the month for the low-code application development (LCAD) on Azure solution. LCAD on Azure is a solution to demonstrate the robust development capabilities of integrating low-code Microsoft Power Apps and the Azure products you may be familiar with. 

In this blog, I will briefly recap LCAD on Azure, provide an overview of IoT on Azure and Azure Functions, how to pull an Azure Function into Microsoft Power Automate, and how to integrate your Power Automate flow into Power Apps.

What is LCAD on Azure? 

LCAD on Azure was created to help developers build business applications faster with less code. Utilizing Microsoft Power Platform, and more specifically Power Apps, helps developers scale and extend their Power Apps with Azure services. For example, a pro developer who works for a manufacturing company would need to build a line-of-business (LOB) application to help warehouse employees track incoming inventory. That application would take months to build, test, and deploy. Using Power Apps, it can take only hours to build—saving time and resources. 

However, say the warehouse employees want the application to automatically place procurement orders for additional inventory when current inventory hits a determined low. In the past, the development team would require another heavy lift to rework their previous application iteration. Due to the integration of Power Apps and Azure, a professional developer can build an API in Visual Studio (VS) Code, publish it to their Azure portal, and export the API to Power Apps, integrating it into their application as a custom connector.

Afterward, that same API is reusable indefinitely in the Power Apps’ studio for future use with other applications, saving the company and developers more time and resources.

IoT on Azure and Azure Functions

This webinar aims to understand how to use Azure IoT Hub and Power Apps to control an IoT device. To start, one would write the code in Azure IoT Hub to send commands directly to your IoT device. In this webinar, Samuel wrote in Node for Azure IoT Hub and wrote two basic commands: toggle fan on and off.

The commands are sent through the code in Azure IoT Hub, which at first run locally. Once tested and confirmed to be running correctly, the next question is how can one rapidly call the API from anywhere across the globe? The answer is to create a flow in Power Automate and connect that flow to Power Apps, which will be a complete dashboard that controls the IoT device from anywhere in the world. To accomplish this task, you have to first create an Azure Function, which will then be pulled into Power Automate using a Get function creating the flow.

Once you've built the Azure Function, run and test it locally first, test the on and off states through the Azure Function URL. To build a trigger for the Azure Function, in this case, a Power Automate flow, you need to create an Azure resources group to check the Azure Function and test its local capabilities. If the test fails it could potentially be that you did not create or have an access token for the IoT device. To connect a device, IoT, or otherwise to the cloud, you need to have an access token.

In the webinar, Samuel added two application settings to his function for the on and off commands. After adding these access tokens and adjusting the settings of the IoT device, Samuel was able to run his Azure Function successfully.

Azure Function automated with Power Automate

After building the Azure Function, you can build your Power Automate flow to start building your globally accessible dashboard to operate your IoT device. Samuel starts by building a basic Power Automate framework, then flow, and demonstrates how to test the flow once complete. He starts with an HTTP request and implements a Get command. From there, it is a straightforward process to test and get the IoT device to run.

Power Automate flow into Power Apps

After building your Power Automate flow, you develop a simple UI to toggle the fan on and off. Do this by building a canvas Power App and importing the Power Automate flow into the app.

To start, create a blank canvas app, and name it. In the Power Apps ribbon, you select button, and pick the button’s source, selecting Power Automate and add a flow. Select the flow that is connected to the Azure IoT device—its name should be reflected in the selection menu. If everything is running correctly, your IoT device will turn on. In the webinar, Samuel is running out of time, so he creates a new Power Automate flow, which he imports into the canvas app.

Source: microsoft.com

Continue reading

Six reasons customers trust Azure to run their SAP solutions

As global organizations across every industry adjust to the new normal, SAP solutions are playing an increasingly vital role in addressing immediate needs and paving a path to a resilient future. Now more than ever, companies are realizing the value of running their SAP solutions in the cloud. While some are using advanced analytics to process their SAP data to make real-time business decisions, others are integrating their SAP and non-SAP data to build stronger supply chains. Whether it’s meeting urgent customer needs, empowering employees to make quick decisions, or planning for the future, customers running SAP solutions in the cloud have been well prepared to face the new reality. Check out how Walgreens delivers superior customer service with SAP solutions on Microsoft Azure.

Many organizations running their SAP solutions on-premises have become increasingly aware of the need to be more agile and responsive to real-time business needs. According to an IDC survey, 54 percent of enterprises expect the future demand for cloud software will increase. As global organizations seek agility, cost savings, risk reduction, and immediate insights from their ERP solutions, here are some reasons many of the largest enterprises choose Microsoft Azure as their trusted partner when moving their SAP solutions to the cloud.

1. Running SAP solutions on Azure delivers immediate insights and increased agility

Organizations running SAP solutions on Azure gain real-time and predictive insights that empower them to break into new ways of doing business. Azure offers the ability to tap into more than 100 cloud services, access SAP Cloud Platform, apply intelligent analytics, and also integrate with an organization’s existing productivity and collaboration tools such as Microsoft 365, Microsoft Teams, Microsoft Power Apps, and Microsoft Power BI.

With Azure, organizations can integrate their SAP and non-SAP data through an extensive portfolio of Azure data services and create real-time dashboard views of the current operations using SAP and Microsoft business intelligence tools. Using intelligent analytics deepens real-time and predictive insights to improve decision-making by responding dynamically as business conditions change, and how that change impacts your customers or products. Integration with Teams and Microsoft 365 improves team collaboration and enhances user experience and productivity. Using Microsoft Power Automate, Power Apps, and Power BI, organizations can create customized workflows, apps, and business insight reports without having to write any code.

2. An ever-evolving and growing set of Azure cloud services drives continuous innovation

While Zuellig Pharma is building an app that uses Azure blockchain services and data from the SAP Business Suite on HANA to track and capture counterfeit products and illegal parallel imports in its region, Walgreens plans to use AI and machine learning to develop new customer offerings quickly and respond in real time to changes in the marketplace.

Customers such as Rio Tinto are using Azure’s secure and scalable IoT applications to pilot a solution to take real-time data from trucks, drills, smelters, and other equipment and analyze it to gain equipment health, preemptive maintenance, supply chain efficiency, and other operational intelligence. Additionally, with DevOps with GitHub and Azure Kubernetes Service, customers can build, manage, and deploy applications on a massive global network.

3. Running SAP solutions on Azure offers costs savings

A Forrester study showed customers achieved more than 100 percent ROI, a 50 percent reduction in data center costs, and a 100 percent reduction in SAP release delays by migrating their SAP systems to Azure. Moving to Azure not only eliminates capital expenditure and cost of underutilized hardware, but it also offers cost management tools such as on-demand scaling during peak usage periods, using cheaper storage, and optimizing disaster recovery environments.

By running SAP solutions on Azure, organizations replace expensive, manual, and error-prone processes with automated, flexible processes, and with a single ticket-to-solution experience, enterprises empower employees to focus on value-added activities by putting data in their hands.

4. Running SAP solutions on Azure offers immense flexibility and scalability

Customers across every industry run their largest production SAP landscapes on Azure because it is a proven cloud platform certified by SAP to run their most mission-critical SAP applications. Azure offers the industry’s most performant and scale-able cloud infrastructure—offering 192 GB to 12 TB SAP HANA certified VMs in more regions than any other public cloud provider along with support for both Linux and Windows OS. Azure offers on-demand scalability and agility that reduces the time to market —customers can spin up or spin down resources as needed. For instance, Daimler AG reduced operational costs by 50 percent and increased agility by spinning up resources on-demand in 30 minutes with SAP S/4HANA and Azure. 

Azure also offers access to more than 1,000 pre-built integrations, out-of-the-box business services, SAP HANA services, and apps built by SAP and our partners. Customers such as Tate and Lyle appreciate that with Azure, they get access to compute, network, and storage resources preconfigured for SAP HANA that they didn’t have to build, install, or manage.

5. SAP solutions on Azure offer best-in-class security, compliance, and business continuity

Azure’s intelligent security services are backed by a $1 billion annual investment in enterprise-grade security and compliance offers and 3,500 cybersecurity professionals. Azure has the most compliance offerings of any public cloud. Azure offers the best-in-class security services such as Azure Sentinel for SIEM, Azure security center for threat monitoring, and Azure Active Directory for identity management. Additionally, customers can leverage built-in availability and recovery options such as Azure Backup and Azure Site Recovery to ensure business continuity and data protection. Microsoft teams work closely with partners to ensure that critical systems remain online during migration and offer a robust set of joint planning workshops, migration programs such as FastTrack, POCs, and training and certifications.

6. Organizations benefit from the trusted partnership between SAP and Microsoft

After decades of working together to serve our customers, SAP and Microsoft deepened their relationship by signing the Embrace initiative. As part of Embrace, SAP will lead with Azure to move on-premise SAP ERP and SAP S/4HANA customers to the cloud through industry-specific best practices, reference architectures, and cloud-delivered services. Our engineering teams co-residing in Germany and Redmond, Washington work together to develop joint reference architectures, product integration roadmaps, and best practices; our industry teams are jointly developing industry-specific transformation roadmaps, and our support teams have developed collaborative support models.

SAP and Microsoft have been partners for more than 25 years and are also mutual customers. Microsoft is the only cloud provider that’s been running SAP for its own finance, HR, and supply chains for the last 20 years, including SAP S/4HANA. Likewise, SAP has chosen Azure to run a growing number of its own internal system landscapes, including those based on SAP S/4HANA. Microsoft IT and SAP IT generously share their learnings from running SAP solutions on Azure with our customers.

More than 95 percent of Fortune 500 companies run their business on Azure. Our experience and history give us a powerful understanding of the needs of enterprise customers. Together with SAP, customers have trusted us with their most critical workloads for decades because we understand what it takes to support our customers in their journey to the cloud.

Source: microsoft.com

Continue reading

Azure Data Factory Managed Virtual Network

Azure Data Factory is a fully managed, easy-to-use, serverless data integration, and transformation solution to ingest and transform all your data. Choose from over 90 connectors to ingest data and build code-free or code-centric ETL/ELT processes.

Security is a key tenet of Azure Data Factory. Customers want to protect their data sources and hope that data transmission occurs as much as possible in a secure network environment. Any potential man-in-the-middle or spoof traffic attack on public networks could bring problems of data security and data exfiltration.

Now we are glad to announce the preview of Azure Data Factory Managed Virtual Network. This feature provides you with a more secure and manageable data integration solution. With this new feature, you can provision the Azure Integration Runtime in Managed Virtual Network and leverage Private Endpoints to securely connect to supported data stores. Your data traffic between Azure Data Factory Managed Virtual Network and data stores goes through Azure Private Link which provides secured connectivity and eliminates your data exposure to the internet. With the Managed Virtual Network along with Private Endpoints, you can also offload the burden of managing virtual network to Azure Data Factory and protect against the data exfiltration.

High-level architecture

Azure Data Factory Managed Virtual Network terminology

Managed Virtual Network

The Managed Virtual Network is associated with Azure Data Factory instance and managed by Azure Data Factory. When you provision Azure Integration Runtime, you can choose to have the Azure Integration Runtime within Managed Virtual Network.

Creating an Azure Integration Runtime within managed Virtual Network ensures that data integration process is completely isolated and secure.

Managed Private Endpoints

Managed Private Endpoints are private endpoints created in the Azure Data Factory Managed Virtual Network establishing a private link to Azure resources. Azure Data Factory manages these private endpoints on your behalf.

Private endpoint uses a private IP address in the managed virtual network to effectively bring the service into it. Private endpoints are mapped to a specific resource in Azure and not the entire service. Customers can limit connectivity to a specific resource approved by their organization.

Continue reading

Updates to geospatial features in Azure Stream Analytics – Cloud and IoT edge

Azure Stream Analytics is a fully managed PaaS service that helps you run real-time analytics and complex event processing logic on telemetry from devices and applications. Numerous built-in functions available in Stream Analytics helps users build real-time applications using simple SQL language with utmost ease. By using these capabilities customers can quickly realize powerful applications for scenarios such as fleet monitoring, connected cars, mobile asset tracking, geofence monitoring, ridesharing, etc.

Today, we are excited to announce several enhancements to geospatial features. These features will help customers manage a much larger set of mobile assets and vehicle fleet easily, accurately, and more contextually than previously possible. These capabilities are available both in the cloud and on Azure IoT edge.

Here is a quick run-down of the new capabilities:

Geospatial indexing

Previously, to track ‘n’ number of assets in streaming data across ‘m’ number of geofence reference data points, in the geospatial context, translated into a cross join of every reference data entry with every streaming event thus resulting in an O(n*m) operation. This presented scale issues in scenarios where customers need to manage thousands of assets across hundreds of sites.

To address this limitation, Stream Analytics now supports indexing geospatial data in relevant queries. When indexed, geospatial data is joined with streaming events. Instead of generating a cross join of every streaming event with reference data, an index is created with the reference data of geospatial objects and every lookup is optimized using the index. This will enable a faster reference data lookup to O(n * log m), thereby offering support for scale that is magnitudes of order higher than what was previously possible.

Support for WKT format

GeoJSON is an open standard format designed for representing simple geographical features, along with their non-spatial attributes, based on JavaScript Object Notation. Previously, Azure Stream Analytics, did not extend support for all the types otherwise defined in GeoJSON specification. As a result, users could not successfully export some of their geospatial objects and process them in Stream Analytics.

To remedy this gap, we are adding full support for WKT geospatial format in Stream Analytics. This format is natively supported by Microsoft SQL Server and hence can be readily used in reference data to represent specific geospatial entities or attributes. This will enable users to easily export their data into WKT and add each entry as nvarchar(max).

Geometry based calculations

Previously, in Stream Analytics we implemented Geographical calculations without the possibility of geometric projections. This would mean that users would ingress projected coordinates and expect calculations to follow geometric projections. Unfortunately, in many cases the output would not match their expectation as calculations were based on geography and were ignoring projections.

To help users overcome this limitation and to allow full fidelity projected calculations, we are moving away from geographic based computation and towards geometric calculations. This means that developers can now input their projected geo coordinates using the same functions as before, but the output will preserve their projection properties. That said, ST_DISTANCE function will continue to be the only function over geography.

Continue reading

Microsoft 365 boosts usage analytics with Azure Cosmos DB – Part 2

Finding the right partition key—a critical design decision

After moving to Azure Cosmos DB, the team revisited how data would be partitioned (referred to as “sharding” in MongoDB). With Azure Cosmos DB, each collection must have a partition key, which acts as a logical partition for the data and provides Azure Cosmos DB with a natural boundary for distributing data across partitions. The data for a single logical partition must reside inside a single physical partition. Physical partition management is managed internally by Azure Cosmos DB.

The Microsoft 365 usage analytics team worked closely with the Azure Cosmos DB team to optimize data distribution in a way that would ensure high performance. The team initially tried the same approach as they used with MongoDB, which was using a random GUID as the partition key. However, this required scanning all of the partitions for reads and over allocating resources for writes, making writes fast but reads slow. The team then tried using Tenant ID as the partition key but found that the vast difference in the amount of report data for each tenant made some partitions too hot, which would have required throttling, while others remained cold.

The solution lay in creating a synthetic partition key. In the end, the team solved both the slow read and too hot and too cold issues by grouping 100 documents per tenant ID into a bucket and then using a combination of tenant IDs and bucket IDs as the partition key. The bucket ID loops from 1 to n, where n is a variable and can be adjusted for each report.

Handling four terabytes of new data every day

In one region alone, more than 6 TB of data is stored in Azure Cosmos DB, with 4 TB of that written and refreshed daily. Both of those numbers are continuing to grow. The database consists of more than 50 different collections, and the largest is more than 300 GB in size. It consumes an average of 150,000 request units per second (RU/s) of throughput, scaling this number up and down as needed.

The different collections map closely to the different reports that the system serves, which in turn have different throughput requirements. This design enables the Microsoft 365 usage analytics team to optimize the number of RU/s that are allocated to each collection (and thus to each report), and to elastically scale that throughput up or down on a per-collection and per-report basis.

Built-in, cost-effective scalability and performance

With Azure Cosmos DB, the Microsoft 365 usage analytics team is delivering real-time customer insights with less maintenance, better performance, and improved availability—all at a lower cost. The new usage analytics system can now easily scale to handle future growth in the number of Office 365 commercial customers. All that was accomplished in less than five months, without any service interruptions. “The benefits of moving from MongoDB to Azure Cosmos DB more than justify the effort that it took,” says Guo Chen, Principal Software Development Manager on the Microsoft 365 usage analytics team.

Improved performance and service availability

The team’s use of built-in, turnkey geo-distribution provided a way to easily distribute reads and writes across two regions. Combined with the other work done by the team, such as rewriting the data access layer using the Azure Cosmos DB Core (SQL) API, this enabled the team to reduce the time for the majority of reads from 12 milliseconds to 3 milliseconds. The image below illustrates this performance improvement.

Although this difference may seem negligible in the context of viewing a report, it resulted in significant service improvements. “There are two ways to access reporting data in the usage analytics system: through the Microsoft 365 admin center, and through Microsoft Graph,” explains Xiaodong Wang, a Software Engineer on the Microsoft 365 usage analytics team. “In the past, people complained that the Graph API was too slow. That’s no longer an issue. In addition, service availability is better now because the chances of any query timing-out are reduced.”

The image below shows just how much service availability is improved. The graph illustrates successful API requests divided by the total API requests and shows that the system is now delivering a service availability level of greater than 99.99 percent.

Zero maintenance and administration

Because Azure Cosmos DB is a fully managed service, the Office 365 development team no longer needs to devote one full-time person to database maintenance and administration. Annual certificate maintenance is no longer a burden, and VMs no longer need to be restarted weekly to protect against any compromises in service availability.

“In the past, with MongoDB, we had to allocate core developer resources to administrative management of the data store,” says Shilpi Sinha, Principal Program Manager on the Microsoft 365 usage analytics team. “Now that we are running on a fully managed service, we are able to repurpose developer resources towards adding new customer value instead of managing the infrastructure.”

Elastic scalability

The Microsoft 365 usage analytics team can now scale database throughput up or down on demand, as needed to accommodate a fluctuating workload that on average, is growing at a rate of 8 percent every three months. By simply adjusting the number of RU/s allocated to each collection, which can be done in the Azure portal or programmatically, the team can easily scale up during heavy data-ingestion periods to handle new reports, and most importantly, to accommodate continued overall growth of Office 365 around the world.

“Today, all we need to do is keep an eye on request unit usage versus what we have budgeted,” says Wang. “If we’re reaching capacity, we can allocate more RU/s in just a few minutes. We don’t have to pay for spare capacity until we need it and more importantly, we no longer need to worry whether we can handle future growth in data volumes or report usage.”

Lower costs

On top of all of those benefits, the Microsoft 365 usage analytics team increased data and reporting volumes while reducing its monthly Microsoft Azure bill for the usage analytics system by more than 13 percent. “After we cut over to Azure Cosmos DB, our monthly Azure expenses decreased by almost 20 percent,” says Chen. “We undertook this project to better serve our customers. Being able to save close to a quarter-million dollars per year—and likely more in the future—is like icing on the cake.”

“Usage analytics are offered as part of the base capability to all Microsoft 365 customers, irrespective of the type of subscription they purchase," said Sinha. "Keeping the costs of operating this service as low as possible contributes to our goal of running the overall Microsoft 365 service as efficiently as possible while at the same time giving our customers new and improved insights into how their people are using our services.”

Continue reading

Azure cloud business value for retail and consumer goods explained

For brick and mortar retailers, the world has been overturned. Online retailers have been demolishing their market share and icons of commerce are struggling. But what helped online retailers can help the offline. The cloud can also be used by brick and mortar retailers. In fact, the brick and mortar experience, transformed with cloud technology, can be a real advantage in competition with online only.

Reasons for retailers and consumer brands to move to the cloud

Cloud technologies are enabling new capabilities and those new powers are disrupting the business models of traditional retailers and sellers of consumer goods. The cloud is at the heart of digital transformation.

◈ It is changing the way technology is implemented and managed.
◈ It offers the benefit of massive scale, increased business speed, and organizational agility.
◈ It makes possible economic benefits related to variable expense, maintenance and deployment.
◈ It enables seamless consumer experiences between offline and online.
◈ It encourages differentiated experiences that wow customers.

Now you have the key to competing in today’s landscape. For these reasons, it is no longer a question of “if,” but “when” and “how” to move to the cloud for most brands.

Business value of the cloud

Born-in-the-cloud retailers are entering the marketplace by solving long-standing consumer challenges in new and innovative ways. Modern technology capabilities allow them to accelerate benefits to both the consumer and business objectives. These new experiences raise the bar on what’s possible. They elevate consumers’ expectations by delivering relevancy and convenience, often at a fraction of the ecosystem footprint of long-standing retailers.

Each organization’s journey to the cloud will be unique. There will be a variety of reasons and benefits that should be acknowledged. However, here are the four major categories for cloud business value: cost, agility, performance, and new sources of value.

Evolved cost structure and transparency

Innovation doesn’t stop because of an organization’s budgeting cycle. Your internal processes should not impact your speed and agility to deliver improved experiences to your consumers.  If it does, as a leader you should add those processes to your list of things to evolve.

The cloud enables and encourages a continuous planning approach. It allows you to reap the full benefits of the cloud despite the traditional annual budgeting cycles. The dominant conversation related to cost becomes the shift from CapEx to OpEx. This fundamentally changes how organizations budget and pay for technology. Since fixed costs associated with shared infrastructure are distributed, the cloud enables greater visibility into the true cost of individual applications. The shift to variable expense offers the organization the ability to begin executing more quickly. And the organization becomes more agile through a fail-fast approach, especially given the lower barrier to initiatives. This enables you to experiment and deliver new concepts to customers. And for some brands, the ability to continually test and learn before committing to significant investments is extremely valuable. Especially when determining the relevancy of the offer and viability of the concept.

Improved agility, speed and productivity

Developing and deploying via on-premises infrastructures (datacenters) can take weeks to months. The cloud provides greater agility and speed-to-consumer. Development teams can be more productive and can quickly develop services that reach global markets. Azure offers near-instant provisioning, allowing projects to move quickly without the need to over-provision resources. As an added bonus, infrastructure planning costs disappear.

The flexibility of the cloud enables organizations to deploy new approaches more effectively. It lets you deliver value to customers and productivity to the organization. Profits accrue with the adoption of agile software development methodologies, DevOps, CI/CD, and modern SOA and PaaS-based architectures.

Azure cloud, made to order

Azure is designed with the developer in mind. Applications can be built with the language of choice, including Node.js, Java, and .NET. Development tools are available for PC or MAC. Visual Studio and Visual Studio Code are premier environments with built-in features for Azure. For example, mobile app development is accelerated by integrating the development lifecycle with Visual Studio App Center. Features include automated builds, and testing for cross-platform, hybrid, and native apps on iOS and Android.

Most compliant

Azure’s infrastructure has been developed to support global demand. Azure is available in 54 global Azure regions, more than any cloud provider. Azure has 70+ compliance offerings—the largest portfolio in the industry. Azure meets a broad set of international and industry-specific compliance standards, such as General Data Protection Regulation (GDPR), as well as country-specific standards, including Australia IRAP, UK G-Cloud, and Singapore MTCS. 

Security matters

Security is essential to you and your customers. Here is a short list of how Azure offers improvements in reliability and security over on-premises infrastructure.

◈ The Azure Security Center spans on-premises and cloud workloads. From a single dashboard, you can monitor and manage all of your resources.

◈ The Azure Advisor is a free service that gives you the best advice based on the most current data. Azure Active Directory helps you to manage user identities and create intelligence-driven access policies to secure your resources.

◈ Site Recovery gives you some assurance that you can recover from a disaster.

◈ Individual services have security features. For example, see the security features of Azure SQL Database.

Possibilities to wow customers

The cloud enables unlimited computing scale and storage while removing boundaries. This freedom is a distinct advantage over on-premises infrastructure. This opens a wealth of new opportunities. It frees your organization’s creatives. They can imagine, prototype, and deliver new experiences that wow customers, leading to new business model opportunities.

These cloud capabilities, plus the availability of data and digital networks, provide an opportunity for modern technologies such as artificial intelligence, IoT, machine learning, and AR/VR to thrive. These technologies enable you to innovate and experiment. This leads to competitive advantages, many of which are only available in the cloud, and that are cost-prohibitive if implemented on-premises.

This is where it gets exciting for retail and consumer goods brands who are focused on delivering new and/or improved digital experiences. The cloud opens possibilities as new data signals are captured and used to provide insights fuelled with artificial intelligence.

Continue reading

Securing the connection between Power BI and Azure SQL Database

How can you connect to Azure SQL Database from the Power BI service in a secure fashion? The easiest way to limit access to the database is to select the “allow access to Azure Services” option (Figure1). This can be found in the database server options in the Azure portal. This allows Power BI to access your database. However, it also makes the database visible to any component deployed within Azure, such as a virtual machine. For many organizations this is not sufficient for their security and compliance requirements.

Figure 1: Setting the database access in the Azure Portal.

  The following is a list of suggestions that one may want to consider achieving the organizations security goals:

To start, use VNet service endpoints to further secure access. This feature was introduced at the start of 2018. This is easy to configure. In the Azure portal either create a new virtual network or edit an existing VNet and enable service endpoints for SQL in the VNet (Figure2).

Figure 2: Creating a service endpoint in the virtual network.

Once completed, the next task is to set up a virtual network rule on the database server. This allows us to restrict access to all the SQL databases on that database server to just a subnet within the virtual network. This might be a little too restrictive, so additionally you may also add specific ip addresses that can also have access. The example below (Figure3) illustrates the scenario where a vnet rule called newVnetRule1 restricts access to just objects within the subnet and in addition external access is granted to a machine using the ip address 80.90.100.110. The latter is useful if you also need to allow access from on-premise machines connecting to the database with Power BI desktop. You can simply restrict external access to your companies ip address range.

Figure 3: Adding a virtual network rule and a client ip to the database server.

By restricting access to the database server, we have also prevented the Power BI service from connecting to the database. The solution is to install the on premise data gateway on a virtual machine that resides within the subnet. There are two steps to this, firstly install and configure the gateway on the VM within the subnet. Afterwards use the Power BI portal to configure the gateway so that it is aware on the database you wish to connect to, and the security you want to apply. There is a good write-up of this process. The gateway can either communicate via TCP or HTTPS. The former is more efficient but will require ports 443 (default), 5671, 5672, 9350 thru 9354 to be opened for outgoing traffic whereas HTTPS will only require port 80. To use TCP, ensure that in the gateway configuration screens under networking that the Azure service bus connectivity mode is correctly selected (Figure 4). Note: the gateway does not require any inbound ports to be opened.

Figure 4: Setting the on-premise gateway to connect over TCP.

It is important to note that for the gateway connector we need to use at present is the SQL Server connector. This provides support for both basic and windows authentication. That means that not only can the gateway connect to the backend SQL Server database with a single username and password, but you can also use Windows authentication to pass through the current users’ credentials when issuing queries directly to the database. This provides a solution where the Power BI user’s access to data can be restricted to a data subset by the SQL Server DBA. We are using the same connector to access the Azure SQL Database. The only authentication method common to both databases is database authentication. Therefore, we are restricted to basic authentication when configuring the gateway and therefore user credentials cannot be passed onto the database via the gateway. This may change over time but at the time of writing this article this is the current restriction.

To avoid having a single point of failure in accessing the database through the gateway it is also possible to install multiple gateways in a cluster to provide resiliency. Simply create a second virtual machine and install a second gateway. During the installation you will be able to choose an option to cluster the gateways.

The final step is to go back and make the following changes to the virtual network: create a network security group that can be applied to the subnet. That security group will restrict access to resources on just the incoming ports that you wish to allow. In the above scenario with only SQL databases in the network we would restrict the ports to just the IP ports listed above. If the virtual network is being shared by other resources then they may require additional ports to be opened. A final point to be aware of is that we have deliberately prevented access to the database from other Azure services. As a consequence we will restrict the use of certain Azure SQL Database features.

Continue reading

Creating your first data model in Azure Analysis Services

Azure Analysis Services is a new preview service in Microsoft Azure where you can host semantic data models. Users in your organization can then connect to your data models using tools like Excel, Power BI and many others to create reports and perform ad-hoc data analysis.

To understand the value of Azure Analysis Services, imagine a scenario where you have data stored in a large database. You want to make that data available to your business users or customers so they can do their own analysis and build their own reports. To do this, one option would be to give those users access to that database. Of course, this option has several drawbacks. The design of that database, including the names of tables and columns may not be easy for a user to understand. They would need to know which tables to query, how those tables should be joined, and other business logic that needs to be applied to get the correct results. They would also need to know a query language like SQL to even get started. Most often this will lead to multiple users reporting the same metrics but with different results.

With Azure Analysis Services, you can encapsulate all the information needed into a semantic model which can be more easily queried by those users in an easy drag-and-drop experience. And you can ensure that all users will see a single version of the truth. Some of the metadata included in the semantic model includes; relationships between tables, friendly table/column names, descriptions, display folders, calculations and row level security.

Once your data is properly modeled for your users to consume, Azure Analysis Services offers additional features to enhance their querying experience. The biggest of which is the option to put the data in an in memory columnar cache which can accelerate queries to sub second performance. This not only improves the query experience but by hitting the cache also reduces the query load on your underlying database.

Create an Analysis Services server in Azure

1. Go to http://portal.azure.com.

2. In the Menu blade, click New.

3. Expand Intelligence + Analytics, and then click Analysis Services.

4. In the Analysis Services blade, enter the following and then click Create:

◈ Server name: Type a unique name.

◈ Subscription: Select your subscription.

◈ Resource group: Select Create new, and then type a name for your new resource group.

◈ Location: This is the Azure datacenter location that hosts the server. Choose a location nearest you.

◈ Pricing tier: For our simple model, select D1. This is the smallest tier and great for getting started. The larger tiers are differentiated by how much cache and query processing units they have. Cache indicates how much data can be loaded into the cache after it has been compressed. Query processing units, or QPUs, are a sign of how many queries can be supported concurrently. Higher QPUs may mean better performance and allow for a higher concurrency of users.

Now that you’ve created a server, you can build your first model. In the next steps, you’ll use SQL Server Data Tools (SSDT) to create a data model and deploy it to your new server in Azure.

Create a sample data source

Before you can create a data model with SSDT, you’ll need a data source to connect to. Azure Analysis Services supports connecting to many different types of data sources both on-premises and in the cloud. For this post, we’ll use the Adventure Works sample database.

1. In Azure portal, in the Menu blade, click New.

2. Expand Databases, and then click SQL Database.

3. In the SQL Database blade, enter the following and then click Create:

◈ Database name: Type a unique name.

◈ Subscription: Select your subscription.

◈ Resource group: Select the same resource group you created for your Analysis Services server.

◈ Select source: Select Sample (Adventure Works LT).

◈ Server: Choose a location nearest you.

◈ Pricing tier: For your sample database, select B.

◈ Collation: Leave the default, SQL_Latin1_General_CP1_CI_AS.

Now that you’ve created a sample data source, you’ll have some data to connect to when you build your data model.In the next steps, you’ll use SQL Server Data Tools (SSDT) to connect to your new data source, create a data model, and deploy it to your new server in Azure.

Create a data model

To create Analysis Services data models, you’ll use Visual Studio and an extension called SQL Server Data Tools (SSDT).

1. In SSDT, create a new Analysis Services Tabular Project.

If asked to select a workspace type, select Integrated.

2. Click the Import From Data Source icon on the toolbar at the top of the screen.

3. Select Microsoft SQL Azure as your data source type and click Next.

4. Fill in the connection information for the sample SQL Azure database created earlier and click Next.

◈ Server Name: Name of SQL Azure server to connect to.

◈ User Name: Name of the user which will be used to login to the server.

◈ Password: Password for the account.

◈ Database Name: Name of the SQL database to connect to.

Note: If using SQL Azure ensure that you have allowed your IP address access through the firewall. Also, ensure that “Allow access to Azure Services” is set to “on” for the firewall.

5. Select Service Account for the impersonation mode and click Next.

6. Select the tables you wish to import into cache and click Finish:

◈ At this step, you can optionally provide a friendly name for each table. For large tables, which may not fit into cache, you can also specify a filter expression to reduce the number of rows. When complete, click next.

◈ Data will now be read from the database and pulled into a local cache within Visual Studio.

◈ Once loading is complete, you will have your first model created and will be able to see each table and the data within them. You can also switch to a diagram view by clicking the little diagram option at the bottom right of the screen:

The diagram view makes it really easy to see all of the tables and the relationships between them.

Improving the model

Now that your basic model is built, you could start querying it now or you could enhance it further by using more of the available modeling features. Some of these features include:

◈ Create or edit relationships. You can add, remove or change relationships between tables by going to the diagram view and dragging a line between two columns in different tables. Once tables are joined together, they can automatically be queried together when a user selects columns from both tables.

◈ Edit properties for a table or column. You can update multiple properties for tables and columns by clicking on them and updating the values in the properties pane.

◈ Add more business logic to the model by creating calculations and measures.

Deploy

Once your model is complete, you can now deploy it to the Azure AS server which you created in the first step. This can be done with the following steps:

1. Copy your Azure Analysis Services server name for the Azure portal. This can be found at the top of the overview section of your server.

2. In the solution explorer in Visual Studio, right click on the project and click properties.

3. Change the deployment server to the name of your Azure AS server and click OK.

4. Right click the project name again, but this time click Deploy.

Connect

Now that you model has been creating you can connect with it through tools like the Power BI Desktop or Excel.

Power BI Desktop

If you don’t already have the Power BI Desktop, you can download it for free.

1. Open the Power BI Desktop

2. Click Get Data.

3. Select Databases/SQL Server Analysis Services and then click connect.

4. Enter your Azure AS server name and click OK.

5. On the Navigator screen, select your model and click OK.

You will now see your model displayed in the field list on the side. You can drag and drop the different fields on to your page to build out interactive visuals.

Continue reading