How to Combine Multiple Data Sources in Power BI Composite Models with Fabric

You can use Composite Models in Power BI with Fabric to join data from different places for better analysis. Many companies have trouble matching data when sources have different details, like daily sales and monthly marketing spend. Data systems can make mistakes or cause security problems. Composite Models help you fix these problems by letting you model, protect, and study your data in one spot. You can make good dashboards and choose better actions.

Key Takeaways

• Composite Models let you use data from many places in one Power BI report. You can mix imported data and live data. This helps you get better answers from your data.

• Using Microsoft Fabric with Composite Models helps teams work together. It can handle lots of data. It also adds smart tools to your reports.

• Set up your workspace with care. Keep dataflows and sources close together. Use the best storage modes to make things fast and keep data fresh.

• Link tables the right way with regular or limited relationships. Pick Import or DirectQuery modes based on how often your data changes.

• Make your data model simple and safe. Remove columns you do not need. Use star schemas. Add row-level security to keep important data safe.

Composite Models Overview

What Are Composite Models

Composite Models let you use data from many places in one Power BI report. You can use data you bring in and data you connect to live at the same time. This means you can use both DirectQuery and import modes together. You can also link tables from different sources, even if they store data in different ways.

• Here is what you can do:

  • Mix data from SQL databases, Excel files, and cloud sources.

  • Make links between these sources, even many-to-many links.

  • Use calculated tables and measures with both imported and live data.

Tip: Composite Models let you mix big company data with local spreadsheet data. Old models cannot do this easily.

Old data models only let you use one kind of connection. Composite Models give you more choices and control.

Benefits for Data Integration

Composite Models make it easier and stronger to put your data together. Here are some important things you can do:

1. Make big models that teams can share and build on.

2. Connect live to Power BI datasets and Azure Analysis Services for real-time data.

3. Mix many sources, like Excel and SQL, into one model.

4. Add to master datasets without losing updates, so you keep one true source.

5. Use row-level security from the main model on new data.

6. Work with big datasets and lots of users quickly.

Groups using Composite Models get faster answers, less data stuck in silos, and better growth. Many say they save time and make choices faster, so they get their money’s worth in a few months.

Composite Models in Fabric

Microsoft Fabric makes Composite Models even better. Fabric helps with every step, from bringing in data to using advanced analytics. You can use Fabric to link Power BI to big and different data sources, work with real-time data, and use AI tools.

With Fabric, you can grow your Composite Models for huge data and many users. You also get better teamwork and smarter analytics than with just Power BI.

Getting Started

Environment Setup

You must get your workspace ready before you mix data from different places. First, check that you can use both Power BI and Microsoft Fabric. Put all your data sources in one workspace. Dataflows help you keep things neat and tidy. With dataflows, you can refresh all your data at once. This saves time and keeps your data up to date.

If you use data from your company’s servers and the cloud, set up the on-premises data gateway. This tool lets you pull data from both places in one query. You can then make your Composite Models with fewer problems. Power BI dashboards can show visuals from many datasets. This helps you see everything about your business.

Tip: Keep your dataflows and sources in the same workspace. This makes updates simple and helps you handle your data better.

Connecting Data Sources

Power BI can connect to many kinds of data sources in Fabric. These include warehouses, lakehouses, Excel files, and cloud databases. To connect to a lakehouse or warehouse SQL analytics endpoint in Fabric, do these steps:

1. Open Power BI and look at the Data pane. You will see all the warehouses by workspace.

2. Pick the warehouse or lakehouse SQL analytics endpoint you want.

3. Choose the tables or data objects you need for your report.

4. Load the data. Pick Import or DirectQuery mode, based on how you want to use the data.

Power BI works with these endpoints right away. You do not need to type in a SQL connection string. This makes things fast and easy.

Note: Import mode brings the data into Power BI. DirectQuery keeps the data where it is and checks it live.

Adding More Sources

You can add more data sources to your Composite Models to make your reports better. Power BI lets you mix data from many places. You can add team goals like budgets and forecasts. You can also bring in master data, like customer lists, to match with your sales data. If you have old systems not yet in your main data setup, you can use them too.

Here are some types of data you can add:

• Other Power BI datasets

• Imported tables from files or databases

• DirectQuery sources, such as SQL databases

You can use both Import and DirectQuery storage modes in one model. This gives you more options. But Direct Lake datasets do not work with Composite Models.

Tip: Connect to each data source by itself. Build your reports, then add visuals to dashboards for a full view.

When you do these steps, you can bring together many types of data. You get one model that helps you see more and make better choices.

Managing Data and Relationships

Table Relationships

When you use data from different places, you must connect your tables. This helps your reports make sense. Power BI lets you link tables, even if they come from different sources. You can set up regular relationships for tables from the same place. These are fast and use special tools to help your reports run quickly.

For tables from different sources, Power BI uses limited or "weak" relationships. These do not join the data before you run your report. Power BI puts the data together when you ask for it. This way gives you more choices. You can link tables from a SQL database and an Excel file without moving the data.

You can also work with many-to-many relationships. In the past, you needed a bridge table to connect tables without unique values. Now, you can link two tables directly, even if both have repeating values. This makes your model easier to use and understand. Power BI will warn you if you set up a many-to-many relationship, so you know what to expect.

Here are some tips for managing relationships:

• Use regular relationships for tables from the same source group.

• Use limited relationships for tables from different sources.

• Try the USERELATIONSHIP DAX function to turn on inactive relationships in your calculations.

• Use TREATAS in DAX to make virtual relationships for advanced needs.

• Turn on "Assume Referential Integrity" for DirectQuery tables if you know your data is clean. This can make your reports faster.

Note: Many-to-many relationships let you filter both tables, but some DAX functions like RELATED will not work as you expect.

Storage Modes

Power BI gives you three main ways to store and use your data: Import, DirectQuery, and Direct Lake. Each way works differently and has its own strengths.

Import mode brings your data into Power BI and keeps it in memory. This makes your reports run fast, but you need to refresh the data to keep it up to date. DirectQuery leaves the data in the source and checks it live. This gives you real-time data, but your reports may run slower, especially if the source is busy. Direct Lake is new and lets you read data straight from the data lake. It gives you almost real-time data with better speed than DirectQuery, but it has some limits, like fewer options for calculated columns.

You can mix Import and DirectQuery in one model. This lets you use Import for old data and DirectQuery for new data. You get fast reports and fresh data at the same time. Dual mode tables can switch between Import and DirectQuery, so Power BI can pick the best way to run your queries.

Tip: Use Import mode for tables you do not need to update often. Use DirectQuery for tables that change all the time. Mixing modes gives you the best of both worlds, but it can make your model more complex.

Combined Measures

You can make measures and calculations that use data from different sources. This helps you answer questions that need information from more than one place. For example, you might want to compare sales from your SQL database with budget data from an Excel file.

To do this, you can write DAX measures that pull data from both tables. You can also use DAX functions like TREATAS to apply filters across tables, even if they come from different sources. This lets you build strong reports that show the full picture.

Keep in mind some limits:

• How fast your data sources are will affect performance.

• Calculated tables and columns work best with Import mode.

• Some features, like QuickInsights, do not work with DirectQuery.

• Cross-filtering between Import and DirectQuery tables may not always work.

• If you rename a dataset or workspace, your model may break.

Alert: Making calculations across sources can slow down your reports if your data sources are not set up well. Test your model to make sure it runs well.

When you use Composite Models, you can bring together data from many places and create measures that help you make better decisions. Always plan your relationships and storage modes to get the best performance and flexibility.

Best Practices

Performance Tips

You can make your Power BI reports run faster by following some easy steps. First, choose the best storage mode for each table. Use Import mode for tables that do not change much. Use DirectQuery for tables that need live updates. Sometimes, you can use both in one model to get good speed and fresh data.

• Use aggregations to make big tables smaller. This helps Power BI answer questions more quickly.

• Keep your model simple. Take out columns you do not need and do not add too many relationships.

• Build your data model using a star schema. This makes your data easier to read and helps your reports run faster.

• Filter out extra data before it gets into Power BI.

• Use measures instead of calculated columns when you can.

• Combine tables with similar data to keep things neat.

Tip: Tools like DAX Studio can show you which parts of your model use the most memory. You can use this to make your model even faster.

Security and Governance

It is very important to keep your data safe. Power BI and Fabric give you many ways to protect your data.

• Data is kept safe with encryption when stored, using Microsoft-managed keys. You can also use your own keys for more control.

• Row-Level Security (RLS) and Object-Level Security (OLS) help you decide who can see what data.

• Use secure sign-in methods like Microsoft Entra and strong passwords.

• Make workspaces for each team and only give people the access they need.

• Turn on audit logs and set sensitivity labels to watch and protect your data.

• Use gateways to connect safely to on-premises data.

Note: Always check who can see your data. Take away permissions from people who do not need them anymore.

Common Pitfalls

Many people have the same problems when using composite models.

• Users cannot see data if they do not have the right permissions on the source datasets.

• Sharing reports without giving the right workspace access causes errors.

• Not reusing dataflows or shared datasets means more work and more mistakes.

• Forgetting to use themes and templates makes reports look messy.

To avoid these problems, always check permissions before sharing. Build dataflows and datasets you can use again. Use themes so your reports all look the same.

---

You can use Power BI and Fabric to join data from many places. Pick the best storage mode for each table you use. Make sure you set up relationships between tables the right way. Keep your model easy to understand and not too big. This helps your reports run fast and work with both new and old data. Try making your own models to learn more. You can also use Tabular Editor or look at Microsoft guides for help.

To get better, practice with sample datasets and check out training tools to build your skills.

FAQ

How do you refresh data in a composite model?

You can refresh imported tables by clicking the "Refresh" button. DirectQuery tables update on their own when you look at the report. Set up scheduled refresh if you want updates to happen often.

Can you use Direct Lake with composite models?

Note: Power BI does not let you use Direct Lake with composite models. You can only use Import and DirectQuery modes together.

What happens if you change a data source?

If you change a data source, some links or visuals might stop working. Always check your model after you make changes. Update your connections and test your visuals to make sure they work.

How do you secure data from different sources?

You can use Row-Level Security (RLS) and Object-Level Security (OLS) in Power BI. Give users roles to control what they can see. Use workspaces and permissions to decide who can view or edit your data.

Why does my report run slow with composite models?

Big tables, too many links, or using DirectQuery can slow things down. Try Import mode for data that does not change much. Take out columns you do not need and keep your model simple for better speed.