90% of top roles seek PL-300 Power BI Data Analyst skills

In today's rapidly evolving, data-driven world, the ability to transform raw data into actionable insights is no longer just a desirable skill—it's a critical imperative. Businesses across every sector are actively seeking professionals who can harness the power of data to make informed decisions, optimize operations, and drive growth. It's no surprise then that an astounding 90% of top roles in data analytics and business intelligence specifically seek proficiency in tools like Microsoft Power BI, a market leader in data visualization and business intelligence.

At the heart of validating this crucial skill set is the PL-300 Power BI Data Analyst certification. This credential from Microsoft signifies a professional's expertise in designing, building, and deploying scalable data analysis solutions using Power BI. If you're looking to elevate your career, demonstrate your expertise, and align yourself with the demands of leading organizations, understanding the value and pathway to obtaining this certification is paramount.

The Growing Demand for PL-300 Power BI Data Analyst Expertise

The digital transformation sweeping through industries has placed data at the forefront of strategic decision-making. Organizations are collecting vast amounts of data, but without skilled data analysts, this data remains an untapped resource. This is where the Microsoft Power BI Data Analyst plays a pivotal role, translating complex datasets into clear, compelling narratives that drive business action.

The demand for professionals with these skills is not just high; it's intensifying. Companies are actively seeking individuals who can not only manage and analyze data but also effectively communicate insights. The PL-300 Power BI Data Analyst certification directly addresses this need, offering a benchmark for employers to identify qualified candidates who can hit the ground running and deliver tangible value.

What is the Microsoft Certified - Power BI Data Analyst Associate Certification?

The Microsoft Certified - Power BI Data Analyst Associate certification is a prestigious credential offered by Microsoft, designed to validate the skills and knowledge required to perform the duties of a data analyst using Power BI. The associated exam, known as PL-300, measures a candidate's ability to prepare data, model data, visualize and analyze data, and deploy and maintain assets in Power BI.

This certification, sometimes referred to by its short-name as the MCA Data Analyst or simply the Power BI Data Analyst certification, focuses on practical, real-world skills. It covers the end-to-end process of working with data in the Microsoft Power Platform environment, specifically leveraging Microsoft Power BI. It's a testament to your capability in turning raw data into rich, interactive dashboards and reports. For a deeper dive into the specific topics and objectives covered in the exam, you can review the PL-300 Power BI Data Analyst exam syllabus.

Is the PL-300 Power BI Data Analyst Certification Right for You? (Role Fit)

The Microsoft Certified - Power BI Data Analyst Associate certification is ideal for a broad range of professionals looking to validate or advance their data analytics skills. If you are passionate about data, have a knack for problem-solving, and enjoy transforming complex information into understandable insights, this certification is likely a perfect fit for your career trajectory.

Who Should Pursue the MCA Data Analyst Certification?

• Data Analysts: Professionals already working in data analysis roles who want to solidify their Power BI expertise and gain formal recognition.
• Business Intelligence Developers: Individuals focused on creating and maintaining BI solutions, seeking to leverage Power BI's advanced capabilities.
• Report Developers: Those primarily responsible for designing and building reports and dashboards across various business functions.
• Business Users with Data Responsibilities: Professionals in marketing, sales, finance, or operations who regularly work with data and need to build compelling reports and visualizations for their teams.
• Aspiring Data Professionals: Individuals looking to enter the data analytics field and need a strong foundational certification to demonstrate proficiency.

The Microsoft Certified Power BI Data Analyst Associate requirements are primarily a solid understanding of data concepts and practical experience with Power BI. This certification is career-aligned, designed to equip you with the skills highly sought after in today's job market.

Core Responsibilities of a Power BI Data Analyst

A certified Power BI Data Analyst plays a crucial role within an organization. Their responsibilities typically include:

• Connecting to and transforming data from diverse sources.
• Developing scalable and robust data models.
• Creating interactive and insightful dashboards and reports.
• Implementing business logic through DAX (Data Analysis Expressions).
• Collaborating with stakeholders to define reporting requirements.
• Ensuring data accuracy, integrity, and security within Power BI.
• Optimizing report and data model performance.

These responsibilities highlight the comprehensive nature of the PL-300 exam topics and the versatile skill set a certified professional brings to the table.

Deep Dive into the PL-300 Exam Syllabus

A thorough understanding of the PL-300 exam syllabus is the cornerstone of effective preparation. The exam is structured into four main functional groups, each contributing a specific percentage to your overall score. This breakdown helps candidates focus their study efforts strategically.

Prepare the Data (25-30%)

This section tests your ability to connect to various data sources and transform raw data into a clean, usable format. It's about ensuring data quality and readiness for analysis.

• Get data from different data sources (databases, files, online services).
• Clean, transform, and load data using Power Query Editor.
• Handle data inconsistencies, missing values, and errors effectively.
• Shape and combine data from multiple tables and sources.

Model the Data (25-30%)

Data modeling is critical for creating efficient and powerful Power BI reports. This objective group focuses on designing effective data models and creating calculated measures.

• Design and develop data models, including setting up relationships and cardinality.
• Create calculated columns and measures using DAX functions for Power BI Data Analyst exam.
• Optimize model performance and handle complex relationships.
• Manage parameters and implement role-playing dimensions.

Visualize and Analyze the Data (25-30%)

This section is all about turning your clean, modeled data into compelling visual stories. It covers the creation of interactive reports and dashboards that convey insights clearly.

• Create reports and dashboards using various visualization types.
• Choose appropriate visualizations to effectively communicate insights.
• Apply filters, slicers, and drill-through capabilities.
• Perform advanced analysis, such as trend analysis, forecasting, and anomaly detection.
• Design engaging and user-friendly report layouts.

Manage and Secure Power BI (15-20%)

Beyond creation, a data analyst must also understand how to manage and secure Power BI assets. This objective group ensures you can effectively publish, share, and protect your solutions.

• Manage datasets, workspaces, and apps within the Power BI service.
• Implement row-level security (RLS) and object-level security (OLS).
• Monitor usage metrics and optimize performance of Power BI content.
• Administer gateways and manage data sources.

Essential PL-300 Exam Details and Logistics

Before embarking on your study journey, it's helpful to know the practical details of the exam. Understanding the Microsoft Power BI Data Analyst certification cost, duration, and scoring can help you plan your preparation effectively.

• Exam Name: Microsoft Certified - Power BI Data Analyst Associate
• Exam Code: PL-300
• Exam Price: $165 (USD) (Note: Price may vary by region and is subject to change)
• Duration: 120 mins
• Number of Questions: 40-60 (mixture of multiple-choice, drag-and-drop, case studies)
• Passing Score: 700 / 1000

These details provide a clear target for your study efforts, helping you to strategize your time and manage expectations for the exam day.

Comprehensive PL-300 Power BI Data Analyst Study Guide and Preparation

Passing the PL-300 exam requires a structured approach and dedication. Here's a detailed Microsoft Power BI Data Analyst study guide to help you prepare effectively.

Official Microsoft Learning Resources

Microsoft provides robust resources to support your preparation:

• Official Training Course: Consider enrolling in the PL-300T00-A: Design and manage analytics solutions using Power BI. This instructor-led course covers all exam objectives in depth.
• Microsoft Learn Modules: The Microsoft Learn platform offers free, self-paced learning paths directly aligned with the PL-300 exam topics. These modules are an excellent resource for conceptual understanding and practical exercises.
• Practice Assessment: Utilize the official Microsoft Certified - Power BI Data Analyst Associate official page to access practice assessments that simulate the exam experience. This helps you identify areas for improvement before taking the actual exam.

Self-Study and Practice

Hands-on experience is non-negotiable for the Power BI Data Analyst exam preparation.

• Hands-on Power BI: Regularly work with Power BI Desktop and the Power BI service. Practice connecting to various data sources, transforming data, building data models, and creating complex reports and dashboards. Focus particularly on data modeling in Power BI for PL-300 and advanced DAX functions.
• PL-300 Practice Questions: Supplement your study with practice questions from reputable third-party providers. This helps you get accustomed to the exam format and question types, improving your chances of knowing how to pass PL-300 Microsoft exam.
• Case Studies: Work through real-world case studies to apply your knowledge in practical scenarios. This enhances your problem-solving skills, which are crucial for the exam.

Community and Peer Learning

Engaging with the Power BI community can provide valuable insights and support.

• Forums and User Groups: Participate in online forums, Power BI user groups, and communities to ask questions, share knowledge, and learn from others' experiences.
• Study Groups: Consider joining or forming a study group. Discussing complex topics with peers can clarify concepts and expose you to different problem-solving approaches. For more general tips on mastering other Microsoft certification exams, explore our guide on 7 steps to ace your Microsoft certification exams.

Benefits and Career Outlook for Microsoft Certified Power BI Data Analyst Associates

Earning the Microsoft Certified - Power BI Data Analyst Associate certification offers a multitude of benefits, from enhanced career prospects to increased earning potential and professional credibility. It's an investment in your future that pays significant dividends.

Enhanced Career Opportunities Power BI Data Analyst

With this certification, you position yourself as a sought-after expert in a highly demanding field. The Microsoft Power BI MCA Data Analyst certification is a clear signal to employers that you possess the verified skills to manage and analyze data effectively, leading to diverse and exciting career opportunities Power BI Data Analyst. Roles such as Business Intelligence Developer, Data Scientist (with additional skills), and of course, dedicated Power BI Data Analyst become more accessible.

Salary Potential and Job Growth

Professionals with in-demand certifications often command higher salaries. The Power BI Data Analyst associate salary typically reflects the value they bring to an organization through data-driven insights. According to the U.S. Bureau of Labor Statistics, the overall employment of computer and information technology occupations is projected to grow much faster than the average for all occupations, with data analysts being a key part of this growth. For specific data on job outlooks in this field, you can refer to insights from the U.S. Bureau of Labor Statistics.

Skill Validation and Credibility

The certification officially validates your technical proficiency with Power BI, providing concrete proof of your capabilities. This not only boosts your confidence but also distinguishes you in a competitive job market. It demonstrates a commitment to professional development and a mastery of crucial analytical tools.

Scheduling Your PL-300 Exam

Once you feel confident in your preparation, the next step is to schedule your exam. The Microsoft PL-300 exam is administered through Pearson VUE.

• Visit the official Microsoft certification page for PL-300.
• Click on the "Schedule exam" button, which will redirect you to the Pearson VUE website.
• Follow the prompts to create an account (if you don't have one), select your preferred testing center or online proctored option, and choose a date and time that works for you.
• Ensure you review all instructions provided by Pearson VUE regarding online proctoring requirements or in-person testing center guidelines. You can schedule your exam through Pearson VUE directly.

The Microsoft Power BI Data Analyst Certification Roadmap

Earning your Microsoft Certified - Power BI Data Analyst Associate certification is a significant milestone, but it can also be a stepping stone in a broader professional development journey. For many, this certification forms a crucial part of their Power BI Data Analyst certification roadmap, laying the groundwork for more advanced roles and specializations.

After achieving the PL-300, you might consider pursuing other Microsoft certifications that complement your skills. For instance, certifications in Azure Data Fundamentals (DP-900) or Azure Data Engineer Associate (DP-203) could be logical next steps, deepening your understanding of data platforms and engineering practices. The skills gained from PL-300 are highly transferable and foundational for various data-centric careers, allowing you to branch into roles focused on machine learning, advanced analytics, or even data governance within the Microsoft ecosystem.

Conclusion: Elevate Your Career with PL-300

The statistic is clear: 90% of top roles actively seek professionals with Power BI Data Analyst skills. The PL-300 Power BI Data Analyst certification is your definitive pathway to not only meeting but exceeding these expectations. It's more than just a piece of paper; it's a validation of your ability to transform complex data into strategic business advantages, a skill invaluable in today's competitive landscape.

By investing in the Microsoft Certified Power BI Data Analyst Associate training and successfully passing the PL-300 exam, you unlock a world of enhanced career opportunities, competitive salaries, and undisputed professional credibility. Whether you're an experienced data analyst or aspiring to enter the field, this certification provides the solid foundation needed for sustained growth and success. Don't just keep pace with the data revolution; lead it. Start your journey towards becoming a certified Microsoft Power BI Data Analyst Associate today and redefine your career trajectory. For further insights into boosting your career with Microsoft certifications, check out leveraging Microsoft certifications for career advancement.

Frequently Asked Questions About the PL-300 Certification

1. What is the PL-300 certification?

The PL-300 is the exam required to earn the Microsoft Certified - Power BI Data Analyst Associate certification. It validates a candidate's skills in designing, building, and deploying scalable data analysis solutions using Microsoft Power BI.

2. How long is the PL-300 certification valid?

Microsoft certifications, including the PL-300, typically remain valid for one year. You must renew your certification annually to maintain its active status, usually by passing a free online assessment on Microsoft Learn.

3. What are the prerequisites for taking the PL-300 exam?

While there are no formal prerequisites in terms of other certifications, Microsoft recommends candidates have a solid understanding of data concepts, experience working with data in Power BI, and knowledge of data transformation, data modeling, and data visualization techniques.

4. What kind of job roles can I get with the PL-300 certification?

The PL-300 certification prepares you for roles such as Data Analyst, Business Intelligence Developer, Report Developer, and other positions where creating and managing data insights with Power BI is a core responsibility. It significantly boosts your employability in data-centric roles.

5. Is the PL-300 exam difficult?

The difficulty of the PL-300 exam is subjective and depends on your prior experience and preparation. It is considered a challenging associate-level exam, requiring both theoretical knowledge and practical application skills in Power BI. Thorough preparation using official resources and hands-on practice is key to success.

Continue reading

New in Stream Analytics: Machine Learning, online scaling, custom code, and more

Azure Stream Analytics is a fully managed Platform as a Service (PaaS) that supports thousands of mission-critical customer applications powered by real-time insights. Out-of-the-box integration with numerous other Azure services enables developers and data engineers to build high-performance, hot-path data pipelines within minutes. The key tenets of Stream Analytics include Ease of use, Developer productivity, and Enterprise readiness. Today, we're announcing several new features that further enhance these key tenets. Let's take a closer look at these features:

Preview Features

Rollout of these preview features begins November 4th, 2019. Worldwide availability to follow in the weeks after.

Also Read: 70-745: Microsoft Implementing a Software-Defined Datacenter

Online scaling

In the past, changing Streaming Units (SUs) allocated for a Stream Analytics job required users to stop and restart. This resulted in extra overhead and latency, even though it was done without any data loss.

With online scaling capability, users will no longer be required to stop their job if they need to change the SU allocation. Users can increase or decrease the SU capacity of a running job without having to stop it. This builds on the customer promise of long-running mission-critical pipelines that Stream Analytics offers today.

Change SUs on a Stream Analytics job while it is running.

C# custom de-serializers

Azure Stream Analytics has always supported input events in JSON, CSV, or AVRO data formats out of the box. However, millions of IoT devices are often programmed to generate data in other formats to encode structured data in a more efficient yet extensible format.

With our current innovations, developers can now leverage the power of Azure Stream Analytics to process data in Protobuf, XML, or any custom format. You can now implement custom de-serializers in C#, which can then be used to de-serialize events received by Azure Stream Analytics.

Extensibility with C# custom code

Azure Stream Analytics traditionally offered SQL language for performing transformations and computations over streams of events. Though there are many powerful built-in functions in the currently supported SQL language, there are instances where a SQL-like language doesn't provide enough flexibility or tooling to tackle complex scenarios.

Developers creating Stream Analytics modules in the cloud or on IoT Edge can now write or reuse custom C# functions and invoke them right in the query through User Defined Functions. This enables scenarios such as complex math calculations, importing custom ML models using ML.NET, and programming custom data imputation logic. Full-fidelity authoring experience is made available in Visual Studio for these functions.

Managed Identity authentication with Power BI

Dynamic dashboarding experience with Power BI is one of the key scenarios that Stream Analytics helps operationalize for thousands of customers worldwide.

Azure Stream Analytics now offers full support for Managed Identity based authentication with Power BI for dynamic dashboarding experience. This helps customers align better with their organizational security goals, deploy their hot-path pipelines using Visual Studio CI/CD tooling, and enables long-running jobs as users will no longer be required to change passwords every 90 days.

While this new feature is going to be immediately available, customers will continue to have the option of using the Azure Active Directory User-based authentication model.

Stream Analytics on Azure Stack

Azure Stream Analytics is supported on Azure Stack via IoT Edge runtime. This enables scenarios where customers are constrained by compliance or other reasons from moving data to the cloud, but at the same time wish to leverage Azure technologies to deliver a hybrid data analytics solution at the Edge.

Rolling out as a preview option beginning January 2020, this will offer customers the ability to analyze ingress data from Event Hubs or IoT Hub on Azure Stack, and egress the results to a blob storage or SQL database on the same.

Debug query steps in Visual Studio

We've heard a lot of user feedback about the challenge of debugging the intermediate row set defined in a WITH statement in Azure Stream Analytics query. Users can now easily preview the intermediate row set on a data diagram when doing local testing in Azure Stream Analytics tools for Visual Studio. This feature can greatly help users to breakdown their query and see the result step-by-step when fixing the code.

Local testing with live data in Visual Studio Code

When developing an Azure Stream Analytics job, developers have expressed a need to connect to live input to visualize the results. This is now available in Azure Stream Analytics tools for Visual Studio Code, a lightweight, free, and cross-platform editor. Developers can test their query against live data on their local machine before submitting the job to Azure. Each testing iteration takes less than two to three seconds on average, resulting in a very efficient development process.

Live Data Testing feature in Visual Studio Code

Private preview for Azure Machine Learning

Real-time scoring with custom Machine Learning models

Azure Stream Analytics now supports high-performance, real-time scoring by leveraging custom pre-trained Machine Learning models managed by the Azure Machine Learning service, and hosted in Azure Kubernetes Service (AKS) or Azure Container Instances (ACI), using a workflow that requires users to write absolutely no code.

Users can build custom models by using any popular python libraries such as Scikit-learn, PyTorch, TensorFlow, and more to train their models anywhere, including Azure Databricks, Azure Machine Learning Compute, and HD Insight. Once deployed in Azure Kubernetes Service or Azure Container Instances clusters, users can use Azure Stream Analytics to surface all endpoints within the job itself. Users simply navigate to the functions blade within an Azure Stream Analytics job, pick the Azure Machine Learning function option, and tie it to one of the deployments in the Azure Machine Learning workspace.

Advanced configurations, such as the number of parallel requests sent to Azure Machine Learning endpoint, will be offered to maximize the performance.

Continue reading

Data models within Azure Analysis Services and Power BI

In a world where self-service and speed of delivery are key priorities in any reporting BI solution, the importance of creating a comprehensive and performant data semantic model layer is sometimes overlooked.

I have seen quite a few occurrences where the relational data store such as Azure SQL Database and Azure SQL Data Warehouse are well structured, the reporting tier is well presented whether that is SQL Server reporting services or Power BI, but still, the performance is not as expected.

Before we drill down to the data semantic model, I always advise that understanding your data and how you want to present and report on it is key. By creating a report that takes the end consumer through a data journey is the difference between a good and a bad report. Report designing should take into account who is consuming and what they want to achieve out of the report. For example, if you have a small number of consumers who need to view a lower level of hierarchical data with additional measures or KPIs, then it may not be suitable to visualize this on the first page. As the majority of consumers may want to view a more aggregated view of the data. This example could lead to the first page of the report taking longer to return the data, thus giving a perception of a slow running report to the majority of consumers. To achieve a better experience, we could take the consumers through a data journey to ensure that the detail level does not impact the higher-level data points.

Setting the correct service level agreements and performance expectation is key. Setting an SLA for less than two seconds may be achievable if the data is two million rows. But would this still be met if it was two billion rows? There are many factors in understanding what is achievable, and the data set size is one of them. Network, compute, architecture patterns, data hierarchies, measures, KPIs, consumer device, consumer location, and real-time vs batch reporting are other impacts that can affect the perception of performance to the end consumer.

However, creating and/or optimizing your data semantic model layer will have a drastic impact on the overall performance.

The basics

The question I sometimes get is, with more computing power and the use of Azure, why can I not just report directly from my data lake or operational SQL Server? The answer is that reporting from data is very different from writing and reading data in an online transaction processing (OLTP) approach.

Dimensional modeling developed by Kimball has now been a data warehouse proven methodology and widely used for the last 20 plus years. The ideology behind the dimensional modeling is to be able to generate interactive reporting where consumers can retrieve calculations, aggregate data, and show business KPIs.

Due to creating dimensional models within a star or snowflake schema, you have the ability to retrieve the data in a more performant way due to the schema being designed for retrieval and reads rather than reads and writes, which are commonly associated with an OLTP database design.

In a star or snowflake schema, you have a fact table and many dimension tables. The fact table contains the foreign keys relating them to the dimension tables along with metrics and KPIs. The dimension tables contain the attributes associated with that particular dimension table. An example of this could be a date dimension table that contains month, day, and year as the attributes.

Creating dimensional modeling for data warehousing such as SQL Server or Azure SQL Data Warehouse will assist in what you are trying to achieve out of a reporting solution. Again, traditional warehouses have been deployed widely over the last 20 years. Even with this approach, creating a data semantic model on top of the warehouse can improve performance as well as things like improving concurrency and even adding an additional layer of security between the end consumers and the source warehouse data. SQL Server Analysis Services and now Azure Analysis Services have been designed for this purpose. We can essentially serve the required data from the warehouse into a model that can then be consumed as below.

Traditionally, the architecture was exactly that. Ingest data from the warehouse into the cube sitting on an instance of Analysis Services, process the cube, and then serve to SQL Server Reporting Services, Excel, and more. The landscape of data ingestion has changed over the last five to ten years with the adoption of big data and the end consumers wanting to consume a wider range of data sources, whether that is SQL, Spark, CSV, JSON, or others.

Modern BI reporting now needs to ingest, mash, clean and then present this data. Therefore, the data semantic layer needs to be agile but also delivering on performance. However, the principles of designing a model that aligns to dimensional modeling are still key.

In a world of data services in Azure, Analysis Services and Power BI are good candidates for building data semantic models on top of a data warehousing dimensional modeling. The fundamental principles of these services have formed the foundations for Microsoft BI solutions historically, even though they have evolved and now use modern in-memory architecture and allow agility for self-service.

Power BI and Analysis Services Tabular Model

SQL Server Analysis Services Tabular model, Azure Analysis Services, and Power BI share the same underlining fundamentals and principles. They are all built using the tabular model which was first released on SQL Server in 2012.

SQL Server Analysis Services Multi Dimension is a different architecture and is set at the server configuration section at the install point.

The Analysis Service Tabular model (in Power BI) is built on the columnar in-memory architecture, which forms the VertiPaq engine.

At processing time, the rows of data are converted into columns, encoded, and compressed allowing more data to be stored. Due to the data being stored in memory the analytical reporting delivers high performance, versus retrieving data from disk-based systems. The purpose of in-memory tabular models is to minimize read times for reporting purposes. Again, understanding the consumer reporting behavior is key, tabular models are designed to retrieve a small number of columns. The balance here is that when you retrieve a high number of columns, the engine needs to sort back into rows of data and decompress, which impacts compute.

Best practices for data modeling

The best practices below are some of the key observations I have seen over the last several years, particularly when creating data semantic models in SQL Server Analysis Services, Azure Analysis Services, or Power BI.

◈ Create a dimension model star and/or snowflake, even if you are ingesting data from different sources.

◈ Ensure that you create integer surrogate keys on dimension tables. Natural keys are not best practice and can cause issues if you need to change them at a later date. Natural keys are generally strings, so larger in size and can perform poorly when joining to other tables. The key point in regards to performance with tabular models is that natural keys are not optimal for compression. The process with natural keys is that they are:

    ◈ Encoded, hash/dictionary encoding.

    ◈ Foreign keys encoded on the fact table relating to the dimension table, again hash/dictionary encoding.

    ◈ Build the relationships.

◈ This has an impact on performance and reduces the available memory for data as a proportion, which will be needed for the dictionary encoding.

◈ Only bring into the model the integer surrogate keys or value encoding and exclude any natural keys from the dimension tables.

◈ Only bring into the model the foreign keys or integer surrogate keys on the fact table from the dimension tables.

◈ Only bring columns into your model that are required for analysis, this may be excluding columns that are not needed or filter on data to only bring the data in that is being analyzed.

◈ Reduce cardinality so that the values uniqueness can be reduced, allowing for much greater compression.

◈ Add a date dimension into your model.

◈ Ideally, we should run calculations at the compute layer if possible.

The best practices noted above have all been used in part or collectively to improve the performance for the consumer experience. Once the data semantic models have been created to align with best practices, then performance expectations can be gauged and aligned with SLA’s. The key focus on the best practices above is to ensure that we utilize the VertiPaq in-memory architecture. A large part of this is to ensure that data can be compressed as much as possible so that we can store more data within the model but also so that we can report upon the data in an efficient way.

Continue reading

Structured streaming with Azure Databricks into Power BI & Cosmos DB

In this blog we’ll discuss the concept of Structured Streaming and how a data ingestion path can be built using Azure Databricks to enable the streaming of data in near-real-time. We’ll touch on some of the analysis capabilities which can be called from directly within Databricks utilising the Text Analytics API and also discuss how Databricks can be connected directly into Power BI for further analysis and reporting. As a final step we cover how streamed data can be sent from Databricks to Cosmos DB as the persistent storage.

Structured streaming is a stream processing engine which allows express computation to be applied on streaming data (e.g. a Twitter feed). In this sense it is very similar to the way in which batch computation is executed on a static dataset. Computation is performed incrementally via the Spark SQL engine which updates the result as a continuous process as the streaming data flows in.

The above architecture illustrates a possible flow on how Databricks can be used directly as an ingestion path to stream data from Twitter (via Event Hubs to act as a buffer), call the Text Analytics API in Cognitive Services to apply intelligence to the data and then finally send the data directly to Power BI and Cosmos DB.

The concept of structured streaming

All data which arrives from the data stream is treated as an unbounded input table. For each new data within the data stream, a new row is appended to the unbounded input table. The entirety of the input isn’t stored, but the end result is equivalent to retaining the entire input and executing a batch job.

The input table allows us to define a query on itself, just as if it were a static table, which will compute a final result table written to an output sink. This batch-like query is automatically converted by Spark into a streaming execution plan via a process called incremental execution.

Incremental execution is where Spark natively calculates the state required to update the result every time a record arrives. We are able to utilize built in triggers to specify when to update the results. For each trigger that fires, Spark looks for new data within the input table and updates the result on an incremental basis.

Queries on the input table will generate the result table. For every trigger interval (e.g. every three seconds) new rows are appended to the input table, which through the process of Incremental Execution, update the result table. Each time the result table is updated, the changed results are written as an output.

The output defines what gets written to external storage, whether this be directly into the Databricks file system, or in our example CosmosDB.

To implement this within Azure Databricks the incoming stream function is called to initiate the StreamingDataFrame based on a given input (in this example Twitter data). The stream is then processed and written as parquet format to internal Databricks file storage as shown in the below code snippet:

val streamingDataFrame = incomingStream.selectExpr("cast (body as string) AS Content")

.withColumn("body", toSentiment(%code%nbsp;"Content"))

import org.apache.spark.sql.streaming.Trigger.ProcessingTime

val result = streamingDataFrame

.writeStream.format("parquet")

.option("path", "/mnt/Data")

.option("checkpointLocation", "/mnt/sample/check")

.start()

Mounting file systems within Databricks (CosmosDB)

Several different file systems can be mounted directly within Databricks such as Blob Storage, Data Lake Store and even SQL Data Warehouse. In this blog we’ll explore the connectivity capabilities between Databricks and Cosmos DB.

Fast connectivity between Apache Spark and Azure Cosmos DB accelerates the ability to solve fast moving Data Sciences problems where data can be quickly persisted and retrieved using Azure Cosmos DB. With the Spark to Cosmos DB connector, it’s possible to solve IoT scenarios, update columns when performing analytics, push-down predicate filtering, and perform advanced analytics against fast changing data against a geo-replicated managed document store with guaranteed SLAs for consistency, availability, low latency, and throughput.

◈ From within Databricks, a connection is made from the Spark master node to Cosmos DB gateway node to get the partition information from Cosmos.

◈ The partition information is translated back to the Spark master node and distributed amongst the worker nodes.

◈ That information is translated back to Spark and distributed amongst the worker nodes.

◈ This allows the Spark worker nodes to interact directly to the Cosmos DB partitions when a query comes in. The worked nodes are able to extract the data that is needed and bring the data back to the Spark partitions within the Spark worker nodes.

Communication between Spark and Cosmos DB is significantly faster because the data movement is between the Spark worker nodes and the Cosmos DB data nodes.

Using the Azure Cosmos DB Spark connector (currently in preview) it is possible to connect directly into a Cosmos DB storage account from within Databricks, enabling Cosmos DB to act as an input source or output sink for Spark jobs as shown in the code snippet below:

import com.microsoft.azure.cosmosdb.spark.CosmosDBSpark

import com.microsoft.azure.cosmosdb.spark.config.Config

val writeConfig = Config(Map("Endpoint, MasterKey, Database, PreferredRegions, Collection, WritingBatchSize"))

import org.apache.spark.sql.SaveMode

sentimentdata.write.mode(SaveMode.Overwrite).cosmosDB(writeConfig)

Connecting Databricks to PowerBI

Microsoft Power BI is a business analytics service that provides interactive visualizations with self-service business intelligence capabilities, enabling end users to create reports and dashboards by themselves without having to depend on information technology staff or database administrators.

Azure Databricks can be used as a direct data source with Power BI, which enables the performance and technology advantages of Azure Databricks to be brought beyond data scientists and data engineers to all business users.

Power BI Desktop can be connected directly to an Azure Databricks cluster using the built-in Spark connector (Currently in preview). The connector enables the use of DirectQuery to offload processing to Databricks, which is great when you have a large amount of data that you don’t want to load into Power BI or when you want to perform near real-time analysis as discussed throughout this blog post.

This connector utilises JDBC/ODBC connection via DirectQuery, enabling the use of a live connection into the mounted file store for the streaming data entering via Databricks. From Databricks we can set a schedule (e.g. every 5 seconds) to write the streamed data into the file store and from Power BI pull this down regularly to obtain a near-real time stream of data.

From within Power BI, various analytics and visualisations can be applied to the streamed dataset bringing it to life!

Want to have a go at building this architecture out? For more examples of Databricks see the official Azure documentation:

◈ Perform ETL operations in Databricks.

◈ Structured Streaming in Databricks.

◈ Stream Data from HDInsight Kafka.

Continue reading