20.08.2021
Authors:

Data Integration From Multiple Sources in Power BI

Depending on how mature your business is, you might find it necessary to set up a data storage tool. The most common motivation to implement a database is the need to put in order the constantly growing number of business-relevant information from a large number of sources.

A well-designed database ensures coherence and integrity of the information it contains, but requires inputs consistent with the adopted database model.

With the cost or function criterion in mind, you can choose from several database engines, including:

  • Microsoft SQL Server,
  • Oracle,
  • PostgreSQL
  • MySQL

However, not all data in an organization are always stored in the database. Even though the company may have many information management systems in place, it is sometimes more convenient and faster to save some data as a file, e.g. as an Excel spreadsheet or PDF file. Saved in this way, data may be large and important enough to complement those stored in the database. You may find that the data contain similar information, but the structure and nature of the place where they sit are different. Transforming data each time so that they have a common model can be too time-consuming and not efficient enough, which may hinder the processing or finding information.

Power BI as a data integration tool

The strength of Power BI lies in the ability to connect with data from various sources. As an advanced tool for building interactive analyzes, Power BI helps create a common data model by integrating data from both local and cloud-based sources.

Power BI data categories

Below are the available data source types divided by specific categories in Power BI. Microsoft is constantly expanding data sources, so you’ll often see data sources marked as beta or preview.

Power BI data sources

 This extensive tool for building interactive analyses allows you to create a common data model and aggregate data from many sources (including):

Power BI Power Query

Connection to data sources is established using Power BI’s Power Query. Power Query is a data preparation tool where the following processes take place: data extraction, transformation, and loading (ETL).

By integrating various data sources, Power BI can be a data warehouse where all data transformations are common, regardless of any limitations of the original data source.

Data transformations in Power Query do not interfere with the original structure – the source remains unaffected. This feature is important, especially when your sources use different underlying technologies or data structures.

Currently, there are two ways to work with Power Query in Power BI, both providing an almost identical environment:

Power Query Online – used for integrations such as Power BI dataflows, Microsoft Power Platform dataflows, Azure Data Factory dataflows, and many more.

Power Query Desktop – used for integrations such as Power BI Desktop and Query for Excel.

Summing up – by aggregating data from many sources, Power BI can produce reports with a broader scope compared to a single database. This will allow you to prepare accurate and consistent business analyzes.

Authentication data for data sources are stored in the data model and are automatically used to establish a connection when one data load procedure is called. As a result, data that were previously available from multiple sources can be easily merged and put in a common dataset that can be processed in the Power BI cloud or saved locally on the device using the Power BI Desktop tool.

Our Experts
/ Knowledge Shared

Czego nauczyła nas luka log4j2?
23.12.2021

What We All Learned From Log4J2

Cloud Infrastructure

On Thursday, December 9, a few minutes before midnight, information regarding a serious vulnerability in the Log4J2 library hit the IT world. The preliminary information announced in the subsequent morning drew a very bleak picture. The attack vector itself is similar to the ‘format string’ (specific for C applications) popular in the 90s and...

12.10.2021

A Single Source of Truth / A Key Way To Eliminate Technical Debt

E-Commerce

Repetition, inaccuracy and inconsistencies are issues every business wants to avoid. They’re also typical signs of poorly chosen – or poorly used – technologies. When used right, good technology can eliminate unnecessary repetition, ensure greater consistency and generally improve results over all. At the core of this is often the concept of “A...

05.10.2021

Personalization in E-Commerce / The Basics

E-Commerce

When talking face to face, personalization often happens at unconscious level. Interacts are smooth, adapted on the fly to suit the customer’s needs. Shop assistants can guide people to where they need to be and, when it comes to homing in on exactly what someone wants, it’s the fundamental secret to any good salesperson. But online? We must adapt....

Expert Knowledge
For Your Business

As you can see, we've gained a lot of knowledge over the years - and we love to share! Let's talk about how we can help you.

Contact us

<dialogue.opened>