Connecting to Data

Power BI may very well be one of the most aptly named tools ever developed by Microsoft, giving analysts and developers a powerful business intelligence and analytics playground while still packaging it in a surprisingly lightweight application. Using Microsoft Power BI, the processes of data discovery, modeling, visualization, and sharing are made elegantly simple using a single product. These processes are so commonplace when developing Power BI solutions that this book has adopted sections that follow this pattern. However, from your perspective, the really exciting thing may be that development problems that would have previously taken you weeks to solve in a corporate BI solution can now be accomplished in only hours.

Using the Power BI Desktop application enables you to define your data discovery and data preparation steps, organize your data model, and design engaging data visualizations based on your reports. In this chapter, the development environment...

Getting started

Power BI is a Software as a Service (SaaS) offering in the Azure cloud and, as such, the Microsoft product team follows a strategy of cloud first as they develop and add new features to the product. Power BI is also one of five members of Microsoft’s Power Platform:

Figure 2.1: Microsoft’s Power Platform suite of tools

Each of the tools within the Power Platform solves distinctly different problems, but what they have in common is who their core user audience is intended to be. The Power Platform and all the tools included within it are designed and built so business users can solve and design solutions on their own rather than relying solely on their IT department. All five tools within the Power Platform leverage Microsoft’s cloud architecture and allow for seamless integration between the different tools. Leveraging the features of each Power Platform tool (Power BI, Power Apps, Power Pages, Power Automate, and Power Virtual Agents...

Importing data

Choosing to import data, which is the most common option and default behavior, means that Power BI will physically extract rows of data from the selected source and store it in an in-memory storage engine within Power BI. Power BI Desktop uses a special method for storing data, known as xVelocity, which is an in-memory technology that not only increases the performance of your query results but can also highly compress the amount of space taken up by your Power BI solution. In some cases, the compression that takes place can even lower the required disk space by up to one-tenth of the original data source size. This data compression occurs automatically, meaning there is no required configuration step you must do to receive this benefit. The xVelocity engine uses a local unseen instance of SQL Server Analysis Services (SSAS) to provide these in-memory capabilities.

There are consequences to using the Import option within Power BI that you should also consider....

DirectQuery

Many of you have likely been trying to envision how you may implement these data imports in your environment. You may have asked yourself questions such as the following:

• If data imported into Power BI uses an in-memory technology, has my company provided me with a machine that has enough memory to handle this?
• Am I really going to import my source table with tens of billions of rows into memory?
• How do I handle the requirement of displaying results in real time from the source?

These are all excellent questions that would have many negative answers if the only way to connect to your data was by importing your source into Power BI. Fortunately, there is another way. Using DirectQuery, Power BI allows you to connect directly to a data source so that no data is imported or copied into Power BI Desktop.

Why is this a good thing? Consider the questions that were asked at the beginning of this section. Since no data is imported to Power BI...

Composite models

Occasionally, you may find it helpful for your data model to take a hybrid approach regarding how it stores data. For example, you want sales transactions to be displayed in near real time on your dashboard, so you set your SalesTransaction table to use DirectQuery. However, your Product table rarely has values that are added or changed. Having values that do not change often makes it a great candidate for the imported data storage method to take advantage of the performance benefits.

This describes a perfect scenario for utilizing a feature called composite models. Composite models allow a single Power BI solution to include both DirectQuery and import table connections within one data model. From the Power BI developer’s perspective, you can take advantage of the best parts of each data storage mode within your design.

Another effective use case for composite models is found when using the feature called aggregations. Leveraging aggregations...

Live connection

The basic concept of live connection is very similar to that of DirectQuery. Just like DirectQuery, when you use a live connection, no data is actually imported into Power BI. Instead, your solution points directly to the underlying data source and leverages the Power BI Desktop simply as a data visualization tool. So, if these two things are so similar, why give them different names? The answer is that even though the basic concept is the same, DirectQuery and live connection vary greatly.

One difference that should quickly be noticeable is the query performance experience. It was mentioned in a previous section that DirectQuery can often have poor performance, depending on the data source type. With live connection, you generally will not have any performance problems because it is only supported by the following types of data sources:

• SQL Server Analysis Services database
• Azure Analysis Services database
• Power BI datasets

The...

Choosing a data connection method

Now that you have learned about the three different ways to connect to your data, you are left wondering which option is best for you. It’s fair to say that the choice you make will really depend on the requirements of each individual project you have.

To summarize, some of the considerations that were mentioned in this chapter are listed in the following table:

...

Consideration	Import Data	DirectQuery	Live connection
Best performance	Yes	No	Yes
Best design experience	Yes	No	No

Summary

Often, Power BI developers have questions concerning various data sources and what unique challenges come with each connection. The great news is once you have established a connection to your data source, regardless of which one it is, everything after that point typically follows that same pattern of steps. The point is don’t let any one data source overly intimidate you. Once you connect to it, the rest of your Power BI solution design will follow the same processes mentioned earlier in this chapter: data discovery, data modeling, data visualization, and sharing.

Power BI provides users with a variety of methods for connecting to data sources with natively built-in data connectors. The connector you choose for your solution will depend on where your data is located. Once you’ve connected to a data source, you can decide on what type of query mode best suits your needs. Some connectors allow for little to no latency in your results with options like DirectQuery...