Artificial Intelligence Decision Tree

Let’s discuss decision points for selecting right components for Artificial Intelligence (AI) solutions. This is also an update to Machine Learning Decision Tree (v1). Keep in mind here that AI is a broader term compared to Machine Learning.

Artificial Intelligence functionality in the decision tree is divided between following groups:

  • AI in Business Applications: Dynamics 365 AI (AI for Customer Service, Market Insights, Sales), Microsoft 365 AI (Office 365 Workplace Analytics, ML in Power BI, O365 Search).
  • Knowledge Mining: O365 Search, Azure Search.
  • AI Apps and Agents: Azure Bot Service (Framework), Cognitive Services (Vision APIs, Speech APIs, Language APIs, Search APIs, Custom APIs).
  • Machine Learning Tools: Azure Notebooks, Jupiter Notebooks, Code, PyCharm, Visual Studio, Azure ML Studio.
  • Cloud-based Machine Learning: Azure ML Service, Azure ML Studio, Data Preparation (Azure Data Factory, Azure Databricks), Model Training/Testing (Azure Databricks, Azure HDInsight, Data Science VM), Container Registry, Model Deployment (Azure Container Instances, Azure Kubernetes Service, Azure Batch, Azure IoT Edge), Azure Infrastructure (CPUs, GPUs, FPGAs).
  • On-premises Machine Learning: Edge Devices, Cognitive Services Containers, SQL Server ML Services, On-prem Hadoop.
  • Machine Learning Frameworks: Deep Learning (ONNX, PyTorch, TensorFlow), General ML (Spark MLllib, SparkR, SparklyR, MML Spark).

The text description of the decision points will be available in a few days…

Reference materials

Other Decision Trees/Maps

Big Data Decision Tree v4

This is a 4th version of the Big Data Decision Tree (Mind Map), which reflects the last changes in Microsoft products.

As usual, a disclaimer: The process of solution selection for Big Data projects is very complex with a lot of factors. That’s why you may use this decision tree only as a first approximation to start looking deep into described and other solutions. Also, please double check the information provided here in an official documentation.

In the decision tree Big Data is divided into the “three V’s”: velocity, volume and variety. How we choose the right solution depends on which one of these problems we are trying to solve first:

  • Volume: need to store and query hundreds of terabytes of data or more, and the total volume is growing. Processing systems must be scalable to handle increasing volumes of data, typically by scaling out across multiple machines.
  • Velocity: need to collect data at an increasing rate from many new types of devices, from a fast-growing number of users, and from an increasing number of devices and applications per user. Processing systems must be able to return results within an acceptable timeframe, often almost in real-time.
  • Variety: situation when data do not match any existing data schema – semi-structured or unstructured data.

There are three groups of solutions addressing described areas:

  • Complex event processing (CEP) is method of tracking and processing streams of data from multiple sources about events, identifying meaningful events, deriving a conclusion from them, and responding to them as quickly as possible. Use CEP if you need to process hundreds of thousands of events per second.
  • Data Warehouses (DWHs) are central relational repositories of integrated data from one or more disparate sources. They store current and historical data and are used for different analytical tasks in organizations. Use DWH is you have structured relational data with defined scheme.
  • NoSQL systems provide a mechanism for storage and retrieval of data without tabular relations. Characteristics of NoSQL: simplicity of design, simpler “horizontal” scaling to clusters of machines. The data structures used by NoSQL databases (e.g. key-value, wide column, graph, or document) are more flexible, and therefore more difficult to store in relational databases. Use NoSQL systems if you have non-relational, semi-structured, or unstructured data; with no schema defined.

Here is the decision tree, which maps the three types of problems to specific solutions.

Here are some most important comments on each of the big data components and corresponding decision points.

Complex event processing

Azure Event Hub

Azure Event Hubs is a highly scalable data streaming platform and event ingestion service, capable of receiving and processing millions of events per second. Event Hubs can process and store events, data, or telemetry produced by distributed software and devices. Data sent to an event hub can be transformed and stored using any real-time analytics provider or batching/storage adapters. Event Hubs provides publish-subscribe capabilities with low latency at massive scale, which makes it appropriate for big data scenarios.

Integration: supports AMQP and HTTPS protocols

Advantages: easy to use.

Disadvantages: limited access revocation through publisher policies.

Azure IoT Hub

Azure IoT Hub is a managed service that enables reliable and secure bidirectional communications between millions of IoT devices and a cloud-based back end.

Feature of IoT Hub include: multiple options for device-to-cloud and cloud-to-device communication; message routing to other Azure services; queryable store for device metadata and synchronized state information; secure communications and access control using per-device security keys or X.509 certificates; monitoring of device connectivity and device identity management events.

In terms of message ingestion, IoT Hub is similar to Event Hubs. However, it was specifically designed for managing IoT device connectivity, not just message ingestion.

Integration: supports MQTT, AMQP, HTTPS protocols.

Advantages: supports cloud-to-device communications, device-initiated file upload, device state information using Device twins; per-device identity; revocable access control.

Azure HDInsight Kafka

Apache Kafka is an open-source distributed streaming platform that can be used to build real-time streaming data pipelines and applications. Kafka also provides message broker functionality similar to a message queue, where you can publish and subscribe to named data streams.

Programmability and integration: Kafka is often used with Apache Storm or Spark for real-time stream processing; Kafka 0.10.0.0 streaming API allows to build streaming solutions without requiring Storm or Spark; supports Kafka Protocol

Advantages: simplified configuration process; 99.9% SLA on Kafka uptime; scaling (changing number of worker nodes) and rebalancing Kafka partitions and replicas using Update Domains (UD) and Fault Domains (FD); monitor Kafka using Azure Log Analytics; integration with external authentication services supported.

Disadvantages: complexity.

Azure Stream Analytics (ASA)

Azure Stream Analytics (ASA) may be used for real-time insights from devices, sensors, infrastructure, and applications. Scenarios: real-time remote management and monitoring. ASA is optimized to get streaming data from Azure Event Hubs and Azure Blob Storage. ASA SQL-like queries run continuously against the stream of incoming events. The results can be stored in Blob Storage, Event Hubs, Azure Tables and Azure SQL database. So, if the output is stored in Event Hub it can become the input to another ASA job to chain together multiple real-time queries.

Programmability: Stream analytics query language, JavaScript; Declarative programming paradigm

Advantages: SQL-like query language, cloud-based: close to globally distributed data; largest amount of supported data sinks;

Disadvantages: supports only Avro, JSON or CSV, UTF-8 encoded input data formats.

Notes: priced by Streaming units; scaled by Query partitions

Azure HDInsight with Apache Storm

Apache Storm is a distributed stream processing computation framework written predominantly in the Clojure programming language. A Storm application is designed as a “topology” in the shape of a directed acyclic graph (DAG). Edges on the graph are named streams and direct data from one node to another. Together, the topology acts as a data transformation pipeline. Storm topologies run indefinitely until “killed”. Storm uses Zookeeper to manage its processes. Storm can read and write files to HDFS.

Architecture: Storm processes the events one at a time.

Performance: millisecond latency.

Programmability: Java, C#, Python; Imperative paradigm; HDInsight Tools for Visual Studio; integrates with Azure Event Hubs, Azure SQL DB, Azure Storage, and Azure Data Lake Storage

Advantages: complete stream processing engine with micro-batching support; 99% Service Level Agreement (SLA) on Storm uptime; Dynamic scaling;

Disadvantages: supports only streaming data, not integrated with Azure platform.

Notes: priced per cluster hour.

Azure HDInsight with Spark Streaming

Spark Streaming is used to build interactive and analytical applications. Used to create low-latency dashboards and security alert system, to optimize operations or prevent specific outcomes. Includes high-level operators to read streaming data from Apache Flume, Apache Kafka, and Twitter; historical data – from HDFS.

Architecture: Spark streams events in small batches that come in short time window before it processes them.

Programmability: Scala, Python, Java; Dstreams; mixture of declarative and imperative paradigm

Performance: 100s of MB/s with low latency (few seconds).

Disadvantages: not integrated with Azure platform.

Notes: priced per cluster hour

Azure App Service WebJobs

WebJobs is a feature of Azure App Service that enables you to run a program or script in the same context as a web app, API app, or mobile app.

Programmability: C#, Node.js, PHP, Java, Python; imperative paradigm.

Advantages: more control over JobHost behavior in the host.json file (for example, to configure a custom retry policy for Azure Storage).

Disadvantages: No built-in temporal/windowing support; no late arrival and out of order event handling support.

Notes: priced per app service plan hour.

Azure Functions

Azure Functions is a solution for easily running small pieces of code, or “functions,” in the cloud. Azure Functions lets you respond to events delivered to an Azure Event Hub. Useful in application instrumentation, user experience or workflow processing, and internet-of-things (IoT) scenarios.

Programmability: C#, F#, Node.js; imperative paradigm.

Advantages: pay only for the time your code runs and trust Azure to scale as needed.

Disadvantages: No built-in temporal/windowing support; limited by up to 200 function app instances processing in parallel; No late arrival and out of order event handling support.

Notes: priced per function execution and resource consumption.

Big data warehouses

Azure SQL DW Gen1

Azure SQL Data Warehouse (DW) is MPP version of SQL Server in Azure for data warehousing workloads. It allows to quickly run complex queries across petabytes of data, allows resize of compute nodes in a minute, and integrated with Azure platform.

Advantages: highly scalable, MPP architecture, lower cost relational storage than Blobs, feature of pausing compute is available, relational store, T-SQL, flexible indexing, security.

Disadvantage: 4-5 times less powerful than Azure SQL DW Gen2; cannot query from external relational stores; no row-level security; no dynamic data masking.

Azure SQL DW Gen2

Azure SQL DW Gen2 comes with five times the compute capacity and four times the concurrent queries of the Gen1 offering. The enhanced storage architecture on Gen2 introduces unlimited columnar storage capacity, while maintaining the ability to independently scale compute and storage.

Azure SQL Data Warehouse Compute Optimized Gen2 tier comes with up to 5 times better query performance, 4 times more concurrency, and 5 times higher computing power compared to the Gen 1. It can serve 128 concurrent queries from a single cluster.

Powering these performance gains is adaptive caching technology that understands where data needs to be and when it needs to be there for the best possible performance. Azure SQL Data Warehouse takes a blended approach of using remote storage in combination with a fast SSD cache layer (using NVMes) that places data next to compute based on user access patterns and frequency.

Automatically upgrade from Gen1 to Gen2 is available from the Azure portal.

Programmability: T-SQL.

Advantages: 4-5 times more performant, concurrent and higher compute compared to Gen 1; supports pausing compute, Transparent Data Encryption with customer-managed keys.

Disadvantages: no row-level security; no dynamic data masking

Microsoft APS/PDW

Microsoft Analytics Platform System (APS) is a combination of the massively parallel processing (MPP) engine in Microsoft Parallel Data Warehouse (PDW) with Hadoop-based big data technologies. It uses the HDP to provide an on-premises solution that contains a region for Hadoop-based processing, together with PolyBase—a connectivity mechanism that integrates the MPP engine with HDP, Cloudera, and remote Hadoop-based services such as HDInsight. It allows data in Hadoop to be queried and combined with on-premises relational data, and data to be moved into and out of Hadoop.

Advantages: very cost-effective fast MPP architecture if constantly and fully used.

Disadvantages: make sure that for most of queries you don’t initiate data movements between the nodes which is more expensive operation; increasing size requires to buy additional rack and reconfigure manually.

NoSQL on-premises or IaaS in the cloud

SQL Server Big Data Cluster

Starting with SQL Server 2019 preview, SQL Server big data clusters allow you to deploy scalable clusters of SQL Server, Spark, and HDFS containers running on Kubernetes. These components are running side by side to enable you to read, write, and process big data from Transact-SQL or Spark, allowing you to easily combine and analyze your high-value relational data with high-volume big data.

In SQL Server big data clusters, Kubernetes is responsible for the state of the SQL Server big data clusters; Kubernetes builds and configures the cluster nodes, assigns pods to nodes, and monitors the health of the cluster. This means that SQL Server Big Data Cluster can be easily deployed in any cloud supporting Kubernetes clusters.

Advantages: on-pSQremises/IaaS allows customization; SQL Server supports dynamic data masking, row level security; using PolyBase can query and join with external data sources without moving or copying the data; scalable HDFS storage pool; scale-out data marts; integrated AI and Machine Learning; can be deployed in non-Microsoft clouds supporting Kubernetes clusters.

Disadvantages: currently in a public preview.

Hadoop on-prem or in VMs

Apache Hadoop is the original open-source framework for distributed processing and analysis of big data sets on clusters. The Hadoop technology stack includes related software and utilities, including Apache Hive, Apache HBase, Spark, Kafka, and many others.

In Azure Marketplace there are available Hortonworks, Cloudera and MapR implementations of Hadoop.

Advantages: flexibility: can be used on-premises or in IaaS cloud environment (easy migration); full control over deployment.

Disadvantages: complexity of deployment; need to manage updates.

Notes: can be deployed in Azure IaaS using custom Docker images with the Distributed Data Engineering Toolkit (AZTK)

NoSQL Processing in Azure

Azure Databricks

Azure Databricks is an Apache Spark-based analytics platform optimized for the Microsoft Azure cloud services platform. Designed with the founders of Apache Spark, Databricks is integrated with Azure to provide one-click setup, streamlined workflows, and an interactive workspace that enables collaboration between data scientists, data engineers, and business analysts.

Programmability: Python, Scala, Java, R, SQL.

Advantages: user friendly UI for collaboration and experimentation (notebooks, cluster creation etc.); fast cluster start times, auto-termination, auto-scaling; supports pausing compute; supports fast scale-out (less than 1 minute); supports GPU-enabled clusters; security with native AD integration.

Disadvantages: cannot act as a relational data store; no row-level security.

Notes: priced by Databricks Unit (DBU) and cluster hour; does not support firewalls.

Azure Data Lake Analytics

Azure Data Lake Analytics (ADLA) is a distributed analytics service built on Apache YARN. It handles jobs of any scale instantly by setting how much compute power is needed. It allows to do analytics on Exabytes of data, and customers still pays only for the cost of the query. ADLA supports Azure Active Directory for Access Control, Roles, Integration with on-premises identity systems. It also includes U-SQL, a language that unifies the benefits of SQL with the expressive power of C#; runtime processes data across multiple Azure data sources. ADLA allows you to compute on data anywhere and a join data from multiple cloud sources like Azure SQL DW, Azure SQL DB, ADLS, Azure Storage Blobs, SQL Server in Azure VM.

Programmability: U-SQL.

Advantages: easy to start on big data leveraging SQL and C# skills; AAD; security; integrated with Azure platform and Visual Studio; can be priced per job; supports fast scale-out (less than 1 minute).

Disadvantages: currently only batch mode is supported — you may use HDInsight for other types of workloads; no clear roadmap; not compatible with ADLS Gen2; no in-memory caching of data; no row-level security; no dynamic data masking.

Azure HDInsight

HDInsight is a cloud-hosted service available to Azure subscribers that uses Azure clusters to run HDP (Hortonworks’ distribution of Hadoop), and integrates with Azure storage.

Supports a variety of open source analytics engines such as Hive LLAP, Storm, Kafka, HBase, Apache Storm, Spark.

Advantages: cloud-based which means that the cluster can be created approximately in 15 minutes; scale nodes on demand; fully managed by Microsoft (upgrades, patching); some Visual Studio and IntelliJ integration; 99.9% SLA.

Concerns: Ranger (Kerberos-based) security; requires manual configuration and scaling; cannot act as a relational data store

Azure HDInsight with Spark

Apache Spark is an open source cluster computing framework. It provides API based on resilient distributed dataset (RDD), a read-only multiset of data items distributed over a cluster of machines. RDDs function as a working set for distributed programs that offers a form of distributed shared memory. Components built on top of Spark: Spark SQL, Spark Streaming, MLlib, GraphX.

Programmability: Python, Scala, Java, R, SQL.

Advantages: in-memory, fast (5-7 times faster than MapReduce).

Disadvantages: less components if you compare with components based on MapReduce; no row-level security.

Azure HDInsight Hadoop

Apache Hadoop includes two core components: the Apache Hadoop Distributed File System (HDFS) that provides storage, and Apache Hadoop Yet Another Resource Negotiator (YARN) that provides processing. With storage and processing capabilities, a cluster becomes capable of running MapReduce programs to perform the desired data processing.

Advantages: a lot of open source components on top of MapReduce.

Disadvantages: much slower that Spark; not in-memory.

Azure HDInsight with Hive LLAP

Interactive Query (also called Apache Hive LLAP, or Low Latency Analytical Processing) is an Azure HDInsight cluster type. Interactive Query supports in-memory caching, which makes Apache Hive queries faster and much more interactive. An Interactive Query cluster contains only the Hive service.

Programmability: HiveQL (can be executed from Power BI, Apache Zeppelin, Visual Studio, Visual Studio Code, Apache Ambari Hive View, Beeline, Hive ODBC).

Advantages: fast performance with intelligent caching (low latency); support ACID transactions; scalable query concurrency; optimized for speed serving layer.

Disadvantages: cannot query external relational stores (like Azure SQL DB, SQL Server in VM, Azure SQL DW); manual configuration and scaling; no redundant regional servers for high availability.

Azure HDInsight with ML Services

Azure HDInsight with ML Services (AKA R Server cluster on HDInsight) provides data scientists, statisticians, and R programmers with on-demand access to scalable, distributed methods of analytics on HDInsight. The service provides the latest capabilities for R-based analytics on datasets of virtually any size, loaded to either Azure Blob or Data Lake storage. Since ML Services cluster is built on open-source R, the R-based applications you build can leverage any of the 8000+ open-source R packages. The routines in ScaleR, Microsoft’s big data analytics package are also available. ML Services bridges these Microsoft innovations and contributions coming from the open-source community (R, Python, and AI toolkits) all on top of a single enterprise-grade platform.

Programmability: ML Services includes highly scalable, distributed set of algorithms such as RevoscaleR, revoscalepy, and microsoftML that can work on data sizes larger than the size of physical memory, and run on a wide variety of platforms in a distributed manner. Includes Microsoft’s custom R packages and Python packages.

Advantages: can run AI packages from Microsoft and open-source; RevoscaleR allows to apply ML algorithms on top of data which cannot fit in memory of the cluster.

NoSQL Database in Azure

Azure Cosmos DB

Azure Cosmos DB is a globally distributed database service designed to elastically and independently scale throughput and storage across any number of geographical regions with a comprehensive SLA. It supports document, key/value, or graph databases leveraging popular APIs and programming models: DocumentDB API, MongoDB API, Graph API, and Table API.

Development: SQL query and transactions over JSON documents, REST; SDKs: .NET, Node.js, Java, JavaScript, Python, Xamarin, Gremlin.

Advantages: different formats of storage, global distribution, elastic scale out, low latency, 5 consistency models, automatically indexed, schema agnostic, native JSON, stored procedures.

Disadvantages: no support for in-memory caching of data; no dynamic data masking.

Azure HDInsight with HBase

Apache HBase is a NoSQL wide-column store for writing large amounts of unstructured or semi-structured application data to run analytical processes using Hadoop. It provides random access and strong consistency for large amounts of unstructured and semistructured data in a schemaless database organized by column families. Data is stored in the rows and columns of a table, and data within a row is grouped by column family.

Programmability and integration: Phoenix, OpenTSDB, Kiji, and Titan etc. can run on top of HBase by using it as a datastore; Apache Hive, Apache Pig, Solr, Apache Storm, Apache Flume, Apache Impala, Apache Spark , Ganglia, and Apache Drill also can integrate with HBase.

Advantages: NoSQL wide-column store; can be used as key-value store, for sensor data, for real-time querying; optimized for speed serving layer.

Disadvantages: manual configuration and scaling; no support for in-memory caching of data.

Notes: supports SQL language using Phoenix JDBC driver.

NoSQL storage

Azure Data Lake Store Gen1

Azure Data Lake Store (ADLS) is a distributed, parallel file system in the cloud performance-tuned and optimized for analytics based on different data types. It is supported by leading Hadoop distributives: Hortonworks, Cloudera, MapR, HDInsight and Azure Data Lake Analytics (ADLA).

Development: WebHDFS protocol (behaves like HDFS); REST API over HTTPS.

Advantages: hierarchical file system; optimized performance for parallel analytical workloads; high throughput and IOPs; no limit on account sizes, file sizes or number of files.

Disadvantages: only locally redundant; not available in some regions.

Azure Blob Storage

Azure Blob Storage is a general purpose object store for a wide variety on storage scenarios. It is highly available, secure, durable, scalable, and redundant. It provides hot, cool, and archive storage tiers for different use cases.

Administrative tools: PowerShell, AzCopy.

Development: .NET, Java, Android, C++, Node.js, PHP, Ruby, and Python; REST API with HTTP/HTTPS requests.

Advantages: most compatible; globally redundant; lowest storage costs; better for simple non-hierarchical storages; client-side encryption.

Disadvantages: flat namespace; not optimized for analytical workloads; max 500 TB per account and 4.75 TB per file.

Azure Table Storage

Azure Table Storage allows you to store petabytes of semi-structured data while keeping costs down, without manual sharding. Using geo-redundant storage, stored data is replicated 3 times within a region—and an additional 3 times in another region.

Development: OData-based queries.

Azure Data Lake Storage Gen2

Azure Data Lake Storage Gen2 is a set of capabilities dedicated to big data analytics, built on Azure Blob storage. In Data Lake Storage Gen2 features from Azure Data Lake Storage Gen1, such as file system semantics, directory, and file level security and scale, are combined with low-cost, tiered storage, high availability/disaster recovery capabilities from Azure Blob storage.

Advantages: able to store and serve many exabytes of data with throughput measured in gigabits per second (Gbps) at high levels of input/output operations per second (IOPS); near-constant per-request latencies; hierarchical namespace significantly improve the overall performance of many analytics jobs.

Disadvantages: A little more expensive on transactions costs compared to Gen1.

Reference

  1. Choosing a real-time message ingestion technology in Azure
  2. Choosing a stream processing technology in Azure
  3. Choosing an analytical data store in Azure
  4. Choosing a batch processing technology in Azure
  5. Choosing a big data storage technology in Azure
  6. Apache Kafka on HDInsight
  7. Apache Storm on Azure HDInsight
  8. ML Services and open-source R capabilities on HDInsight
  9. Interactive Query with HDInsight
  10. SQL Server 2019 big data clusters
  11. Apache HBase in HDInsight
  12. Azure Databricks Documentation
  13. Connecting IoT Devices to Azure: IoT Hub and Event Hubs
  14. Melissa Coates. Data Lake Use Cases and Planning Considerations
  15. Big Data Architectures
  16. James Serra. SQL Server 2019 Big Data Clusters
  17. James Serra. Azure Data Lake Store Gen2 is GA

What “near 100% compatibility” of Azure SQL DB Managed Instance actually means?

Azure SQL Database Managed Instance (Azure SQL DB MI) is a fully managed SQL Server Database Engine Instance hosted in Azure cloud. This is the most compatible PaaS option for migrating on-premises SQL Server databases to the cloud (PaaS is good if you want to use capabilities like automatic patching and version updates, automated backups, built-in high-availability etc. to reduce management overhead and TCO).

So, what “near 100% compatibility of Azure SQL DB MI with the latest SQL Server on-premises (Enterprise Edition) Database Engine” actually means?

First, components of SQL Server which are not related to the Database Engine are not available in Azure SQL DB MI. Reporting Services, Integration Services, Analysis Services, Master Data Services, Data Quality Services are not there.

Second, some of the features of SQL Server EE needed for enterprise database workloads, are still not available in Azure SQL DB MI, probably due to complexity considerations, not enough requests/impact, or due to availability of similar or better capabilities in Azure.

Feature comparison of SQL Server and Azure SQL DB MI can be found in the official documentation.

Below is a graphical representation of most important differences between SQL Server, Azure SQL DB MI and some other PaaS offerings in Azure.

On the picture features on the borders are partially compatible.

Reference:

Azure Search with Knowledge-based Cognitive Capabilities

Azure Search is a search-as-a-service cloud solution that gives developers APIs and tools for adding a rich search experience over private, heterogenous content in web, mobile, and enterprise applications.

In a context of organizations, applications with search capabilities can be used by External Customers or Internal Business Users. Example of Cognitive Search on top of publicly available JFK files (see JFK Files Public Site and JFK Files Project).

When you use Full Text Search, query execution is done over a user-defined index on top files and searchable datasets.

Cognitive search

Cognitive search can be added to create searchable information out of non-searchable content by attaching AI algorithms to an indexing pipeline. AI integration is provided through cognitive skills, enriching source documents before creating a search index.

Cognitive Skills are based on the same AI algorithms used in Cognitive Services APIs:

  1. Natural language processing skills include entity recognition, language detection, key phrase extraction, text manipulation, and sentiment detection. With these skills, unstructured text becomes structured, mapped to searchable and filterable fields in an index.
  2. Image processing skills include OCR and identification of visual features, such as facial detection, image interpretation, image recognition (famous people and landmarks) or attributes like colors or image orientation. You can create text-representations of image content, searchable using all the query capabilities of Azure Search.
  3. Custom skills are a way to insert transformations unique to application content. A custom skill executes independently, applying whatever enrichment step you require. For example, you could define field-specific custom entities, build custom classification models to differentiate business and financial contracts and documents, or add a speech recognition skill to reach deeper into audio files for relevant content.

Skills can be chained. For instance, you may want to use the language you detected to improve the accuracy of the key-phrase extractor.

Video from Ignite: AI for Knowledge Mining by Luis Cabrera:

Azure Search Global Distribution

To reduce latency for remote users (in a case of geo-distributed workloads) it makes sense to create search services in each corresponding region (that is in closer proximity to these users). For example, you may use multiple Azure Search indexers in different regions that will point to the same datastore. To route requests to multiple geo-located websites that are then backed by multiple Azure Search Services, you may use Azure Traffic Manager. This approach also provides high availability and load balancing.

List of Azure Search Features

Reference

Monitoring Azure SQL Databases

In this article we will discuss different ways of monitoring data solutions including different types of Azure SQL databases like Azure SQL DB, Azure SQL DW, Azure SQL Managed Instance. This can be done using 2 approaches:

  • Single database monitoring using Internal SQL Server features (SQL Server Query Data Store, SQL Server Dynamic Management Views, SQL Server Extended Events), DTU consumption in Azure portal, Query Performance Insight, SQL Database Advisor;
  • Multiple database monitoring and reacting using Azure Monitor with Azure SQL Analytics, Event Hubs, Logic Apps, and Power BI.

Monitoring and troubleshooting single database performance:

  • DTU consumption in Azure portal
  • Query Performance Insight
  • SQL Database Advisor
  • Azure SQL Intelligent Insights
  • Real-time monitoring: dynamic management views (DMVs), extended events, and the Query Store.

Monitoring Multiple databases and reacting to events can be done using Azure Monitor with Azure SQL Analytics, Event Hubs, Logic Apps, and Power BI.

DTU consumption in Azure portal

DTU consumption in Azure portal: for each SQL database use Monitoring chart to look for resources approaching their maximum.

Query Performance Insight

Query Performance Insight lets you spend less time troubleshooting database performance by providing the following:

  • Deeper insight into your databases resource (DTU) consumption
  • The top CPU-consuming queries, which can potentially be tuned for improved performance
  • The ability to drill down into the details of a query

SQL Database Advisor

SQL Database Advisor allows to view recommendations for creating and dropping indexes, parameterizing queries, and fixing schema issues. The advisor assesses performance by analyzing SQL database’s usage history. The recommendations that are best suited for running your database’s typical workload are recommended.

Azure SQL Intelligent Insights

Azure SQL Intelligent Insights can be used for automatic monitoring of database performance. Once a performance issue is detected (for example, performance degradation), a diagnostic log is generated with details and Root Cause Analysis (RCA) of the issue. Performance improvement recommendation is provided when possible.

Real-time monitoring

You also can use dynamic management views (DMVs), extended events, and the Query Store to get performance parameters in real time. See performance guidance to find techniques that you can use to improve performance of Azure SQL Database if you identify some issue using these reports or views.

SQL Server Query Data Store (QDS) allows to ask questions about workloads gathering a history of compilation and runtime metrics throughout query executions:

  • Compile-time statistics: Query text; Semantic-affecting settings; Containing objects: SP, TVF, trigger; Parametrization type; Compilation, binding, and optimization stats; Query plan plus initial and last compile/execute times.
  • Run-time stats (aggregated on an interval): Count of executions and first/last execution time; AVG, LAST, MIN, MAX, and STDEV for metrics Duration, CPU time, Logical I/O reads and writes, Physical I/O reads, DOP, Memory grants, Number of rows.

Dynamic Management Views (DMVs) – Microsoft Azure SQL Database enables a subset of dynamic management views to diagnose performance problems, which might be caused by blocked or long-running queries, resource bottlenecks, poor query plans, and so on. This topic provides information on how to detect common performance problems by using dynamic management views. SQL Database partially supports database-related, execution-related and transaction-related DMVs.

Extended Events (XE) allows to monitor and troubleshoot performance issues, SQL statement executions and full-text related errors. The results can be captured to Ring buffer target (briefly holds event data in memory), Event counter target (counts all events that occur during an extended events session), or Event file target (Writes complete buffers to an Azure Storage container).

Azure Monitor

Azure Monitor maximizes the availability and performance of your applications by delivering a comprehensive solution for collecting, analyzing, and acting on telemetry from your cloud and on-premises environments. It helps you understand how your applications are performing and proactively identifies issues affecting them and the resources they depend on.

Azure Monitor uses two data stores (for metrics and for logs). It takes data from the sources that collect telemetry from different monitored resources and populate the data stores. Azure Monitor provides analysis, alerting, and streaming to external systems of the collected data.

Most important services related to SQL monitoring using Azure monitor are following:

  • Azure SQL Analytics – provides SQL-related dashboards
  • Logic Apps – allows creating automated workflows reating to events in Azure Monitor
  • Event Hubs – allows streaming of monitoring data to partner monitoring tools
  • Power BI – allows to create and customize custom visualizations.

Azure SQL Analytics is a cloud monitoring solution for monitoring performance of Azure SQL databases, elastic pools, and Managed Instances at scale and across multiple subscriptions. It collects and visualizes important Azure SQL Database performance metrics with built-in intelligence for performance troubleshooting.

Logic Apps is a service that allows you to automate tasks and business processes using workflows that integrate with different systems and services. Activities are available that read and write metrics and logs in Azure Monitor, which allows you to build workflows integrating with a variety of other systems.

Azure Event Hubs is a streaming platform and event ingestion service that can transform and store data using any real-time analytics provider or batching/storage adapters. Use Event Hubs to stream log data from Azure Monitor to partner SIEM and monitoring tools.

Power BI is a business analytics service that provides interactive visualizations across a variety of data sources and is an effective means of making data available to others within and outside your organization. You can configure Power BI to automatically import log data from Azure Monitor to take advantage of these additional visualizations. Note. This is especially convenient when you also monitor SaaS solutions, for example Power BI reports usage with Power BI Premium Capacities – in this case you will have all dashboards and interactive reports in one collaboration environment.

Important Notes

Log Analytics and Application Insights have been consolidated into Azure Monitor. Operations Management Suite (OMS) brand, as a combination of Application Insights, Azure Automation, Azure Backup, Log Analytics, Site Recovery (for licensing purposes) is retired.

Reference materials

  1. Azure Monitor overview
  2. Monitor Azure SQL Database using Azure SQL Analytics (Preview)
  3. Monitoring Azure SQL Database using dynamic management views
  4. Announcing the Power BI Solution Template for Azure Activity Log Analytics
  5. New monitoring capabilities for Power BI Premium Capacities
  6. Intelligent Insights
  7. SQL Database Service: Monitoring and performance tuning

Modern Data Platform Map and Video

Last update: Dec 4, 2018

Modern Data Platform Map represents reference organizational layout of most important data pillars and services and corresponding groups of specialists in enterprises.

In the following video I make a quick overview of Microsoft Data Platform. I will provide more details in subsequent posts and videos. Please post your questions, suggestions and feedback below.

You may also check for details following data pillars and products:

Business Intelligence Solutions Decision Tree

In this article we will cover most important Business Intelligence components based on Microsoft Data Platform. One week ago there were announcements on Power BI Premium and Power BI Report Server which will require some clarification, so I decided to create another decision tree describing available Microsoft analytical modeling and visualization tools, and covering Power BI related components in more detailed way.

For the purposes of this article we will define Business Intelligence in a narrow way, as top and middle layers of BI stack, so it will include Analytical Modeling, Data Visualization, and Collaboration. We will also cover Sites and Apps integration as important part of BI functionality.

  1. Analytical Modeling solutions allow to load data from different data sources, combine data in one model and create calculations.
  2. Data Visualization and Collaboration solutions allow users to create, change, manage and share reports and dashboards built on top of analytical models or data sources.
  3. Sites and Apps Integration solutions allow to create applications of top of data sources, embed analytical resorts into applications and web sites, and create data driven workflows.

Here is the decision tree, which maps these areas to specific solutions. Below I will provide some comments on each of them.

Analytical Modeling

  • Azure Analysis Services is Azure PaaS offering built on the proven analytics engine in Microsoft SQL Server Analysis Services. Azure Analysis Services provides enterprise-grade tabular data modeling in the cloud.
  • SQL Server Analysis Services (SSAS) is a part of SQL Server which contains engines for multidimensional (OLAP) and tabular analytical models, and for data mining.
  • SQLBI DAX Studio is a tool to write, execute, and analyze DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular. It includes an Object Browser, query editing and execution, formula and measure editing, syntax highlighting and formatting, integrated tracing and query execution breakdowns.
  • Microsoft Excel is a spreadsheet application with cell-based calculations. It includes Pivot Tables, Pivot Charts and Power View for data visualization; Power Query for data transformation; Power Pivot to create in-memory tabular models and calculations. Excel is a component of Microsoft Office applications package, and is also available in Office 365 subscriptions.
  • Power BI Desktop is a visual data exploration tool for data analysis and reports creation. It allows to load multiple data sources, establish data structure, transform, create analytical tabular model, visualize and explore data in interactive way, and also publish to Power BI Service.

Visualization and collaboration

  • Power BI is a set of tools for self-service and traditional business intelligence, which uses tabular analytical models, allows to build interactive reports and dashboards, and features mobile reports, collaboration and application embedding.
  • Power BI Mobile is a set of free Windows, iOS, and Android applications allowing to view and explore personalized dashboards and reports created in Power BI Service. Also it allows users to be up-to-date with data-driven alerts.
  • Power BI Service (or powerbi.com), is a SaaS part of Power BI offering allowing to create interactive reports, build dashboards, create reports & datasets, update data with real-time, automatic and scheduled refreshes, share dashboards easily with other people in your organization, ask questions of data with Natural Language Query, stay connected to data all the time with mobile applications.
    • Power BI Free is a free version of Power BI Service intended for report authoring (personal use). Currently this service is in transition to have the same functionality as Power BI Pro, but with limited sharing and collaboration features. (This will be effective June 1st)
    • Power BI Pro is a professional version of Power BI Services intended for report authoring, sharing and collaboration. Power BI Pro is payed per user, per month.
    • Power BI Premium is dedicated capacity for large-scale BI deployments, with enhanced performance and larger data volumes, without requiring to purchase per-user licenses. Power BI Premium builds on the existing Power BI portfolio with a capacity-based licensing model that increases flexibility for how users access, share and distribute content. Power BI Premium is payed per node, per month.
  • Power BI Report Server is an on-premises server that allows the deployment and distribution of interactive Power BI reports – and traditional paginated reports – completely within the boundaries of the organization’s firewall. Power BI Report Server is available as part of Power BI Premium or with SQL EE SA.
  • SQL Server Reporting Services is a solution for creating, publishing, managing reports, and delivering reports to users in web browser, on mobile device, or as an email. Types of supported reports: “traditional” paginated reports, mobile reports (AKA DataZen), and Power BI reports (through Power BI Server of Power BI Service).

Sites and Apps development

  • Power BI Embedded is PaaS offering in Azure, which provides interactive data visualizations in customer-facing apps without the time and expense of having to build it from the ground up. In future it will be converged with the Power BI Service to deliver one API surface, a consistent set of capabilities and access to the latest features.
  • Microsoft Flow is a component of Office 365 which represents user friendly and intuitive way of creating automated workflows between applications and services to generate notifications, synchronize files, collect data, and produce other actions.
  • Microsoft PowerApps is a component of Office 365 with user friendly and intuitive interface allowing to build applications without writing code, connect to data sources and create new data, publish and use created apps on web and mobile devices. Power Apps allows business experts in the organization to create the apps they need to support their business requirements with drag and drop simplicity.
  • SharePoint in Office 365 allows you to integrate Power BI interactive reports into SharePoint web pages.
  • SharePoint Server on-premises solution also includes BI-related functionality of SharePoint Server. It includes integration with SQL Server Reporting Services, and also creating PerformancePoint dashboards.

Reference materials: