Combine Datasets In Power Bi

In the April update of Power BI Desktop, we released the ability to connect to datasets in the Power BI service. This feature allows you to create new reports off existing datasets you’ve already published to the cloud. You can leverage this feature to better collaborate and reduce duplicate efforts across your team.

  1. Since you want to combine data from multiple data sources, it is better to use Power BI Desktop to create reports and publish them to Service. Power BI Desktop provides advanced data exploration, shaping, modeling, and report creation comparing with Power BI Service.
  2. Are you copying your Power BI Dataset or PBIX multiple times? Here are 3 reasons why you shouldn't be doing that.Christopher Wagner Article: https://www.link.

Model once, create many reports from that model.

Many Power BI users tell us that data cleaning and data prep tasks take up 70% of their time when creating a new report. Now, you can do the data modeling once and then create new reports on top of that same model, while still remaining in Power BI Desktop.

Easier data refresh management.

Combine Datasets In Power Bi Using

Aug 28, 2020 Just because you can merge all the tables using Power Query, it doesn’t mean you should do it. The process of data modeling is not based on CAD DOs, it is based on SHOULD DOs. Power BI modeling starts with a pen and paper This is very important that you start your modeling by a pen and paper. Bonus Video part of www.LearnPowerBI.com training program.In this video I explain how you can use Power BI to combine data from multiple excel files.

Every time you publish a new dataset to the service, you need to make sure data refresh is scheduled and the credentials are set up. If there are issues with refresh such as a password change, you’ll need to go and update that on all your datasets using that source. In addition, the more datasets you have with scheduled refresh on that source, the more load that source will incur when refreshes take place. If your reports are all leveraging the same dataset, this only needs to be managed once.

By: Matt Wollner In my previous post, I showed how you can use Microsoft Power BI Desktop to pull data from the web and use it to create simple charts.Also, in another previous post, we laid out the steps for sharing Power BI reports.

Divide up work between your team.

Instead of everyone in your team needing to do data modeling and report authoring, you can divide up the tasks. One person can be responsible for doing the data prep and managing the dataset. Others on the team can focus on report authoring and visualizations.

Leverage content packs as a starting point for your reports.

Not only can you connect to datasets you or your teammates have created, you can also connect to datasets created through content packs. For example, you can connect to a service content pack that you’ve set up such as Dynamics or Quickbooks and leverage that dataset in Power BI Desktop. In addition, you can make a copy of any Organization Apps you have installed to create new reports from those datasets in Desktop.

Getting Started

To get started with this feature, you’ll first need to enable the preview option in Power BI Desktop. Navigate to File > Options and settings > Options > Preview features and enable Power BI Service Live Connection. You’ll need to restart Desktop in order for the option to take effect.

When you create a new Power BI report, you’ll see the Power BI service option under Online Services.

From there, you’ll get a list of your workspaces. Expand each workspace to see the list of datasets you can use to create reports. You may not see every dataset that is available to you in the Power BI service, the datasets you’ll see listed are:

  • Datasets you have edit access to. These include datasets you’ve created or datasets you have edit access to in a shared workspace. Datasets created from service content packs should show up in this list as well.
  • Datasets coming from data sources via import, DirectQuery or Push. We do not yet support SSAS data sources via this method, but it is on our roadmap to support this as well.
  • Datasets from Organization Apps once you’ve made a copy. In order to get edit access to a dataset in an org app, you first need to make a copy.

Once you’ve connected to a dataset, you’ll get a read-only view of the model. The data view, relationship view and all modeling actions will be disabled. Once you’ve created the report, you can publish it up to the Power BI service to pin to a dashboard and share with others.

For more details about how to use the feature, step by step instructions and any current limitations, check out the documentation.

Tips & Tricks

When you download a report that is live connected to a dataset on the service, it will remain live connected when you open it in Desktop. This means you have read-only access to the model.

What if you need to make changes to the original dataset? In your list of datasets for any workspace, you’ll see the Download .pbix option in the context menu under the “…”.

What if you don’t remember which dataset you were using for this report? Using the new Related Content Pane in the Power BI service, you can find the dataset and quickly download the PBIX that contains the model.

Please give the public preview a try and share your feedback. As always, if you have ideas for new capabilities you’d like to see, post them on our User Voice forum.

In Nov 2018, we released composite models, which allow you to seamlessly add multiple DirectQuery sources and imported data to your models. We have heard your feedback that you would like to do this with sources you previously live connected to, like Power BI datasets and Azure Analysis Services sources.

We are excited to announce that DirectQuery for Power BI datasets and Azure Analysis Services is now in preview! This long-awaited addition allows report authors to use DirectQuery to connect to Azure Analysis Services or Power BI Datasets and, if they want to, combine it other DirectQuery and imported data.

This feature is ideal for report authors who want to combine the data from their enterprise semantic model with other data they may own like an Excel spreadsheet, or who want to personalize or enrich the metadata from their enterprise semantic model.

Datasets

To enable this feature, you will need to enable the preview feature switch in the Report options dialog.

Alberto Ferrari had this to say about this feature: “We got used to monthly updates with Power BI, but not all the months are the same. Guys, the December 2020 version of Power BI is an historical milestone in the development of Business Intelligence. Historical. Milestone. I am not saying this lightly; I am old enough to have seen many things happen in the Business Intelligence world. Some were nice, some were cool… this is neither nice nor cool: this is huge: finally, can seal the marriage between self-service and corporate BI”.

Feel free to leave feedback at this Power BI Community forum post.

Using DirectQuery for Power BI datasets and Azure Analysis Services requires that your report has a local model. You can start from a live connection and add upgrade to a local model or start with a DirectQuery connection or imported data, which will automatically create a local model in your report.

To see what kind of connections are being used in your model, you can look in the status bar in the bottom right corner of PBI Desktop. If you are only connected to an Azure Analysis Services source, you may see a message like the following:

If you are connected to a PBI dataset, you will see a message that lets you know what PBI dataset you are connected to:

If you want to make customizations to the metadata of the fields in your live connected dataset, you can click “Make changes to this model” in the status bar, or you can click the button with the same name in the ribbon.

In Report View, you will see the “Make changes to this model” button in the Modeling tab of the ribbon, and in the Model View, you will see a button with the same name in the Home tab of the ribbon.

Once you click that button, a dialog will pop-up confirming that it will add a local model. Press “Add a local model” to unlock capabilities to create new columns or modify the metadata for fields from Power BI datasets or Azure Analysis Services.

When connected live to an Analysis Services source, there is no local model. To use DirectQuery for live connected sources like Power BI datasets and Azure Analysis Services, you will need to add a local model to your report. Keep in mind that when you publish a report with a local model to the service, a dataset for that local model will be published a well. This is the same behavior as when you publish a report with imported data to the service.

With this feature, you can publish a report and a dataset that is based on other Power BI datasets, which was previously not possible. Together, datasets and the datasets and models they are based on form a chain.

For example, imagine your colleague publishes a Power BI dataset called Sales and Budget that is based on an Azure Analysis Services model called Sales, and combines it with an Excel sheet called Budget.

When you publish a new report (and dataset) called Sales and Budget Europe that is based on the Sales and Budget Power BI dataset published by your colleague, making some further modifications or extensions, you are effectively adding a report and dataset to a chain of length three, which started with the Sales Azure Analysis Services model and ends with your “Sales and Budget Europe Power BI dataset. See the image below:

The chain shown above is of length three, which is the maximum length during preview of this feature. Extending further, beyond the chain length of three is currently not supported and will result in errors.

Please note that when you use this feature, you will see the following security warning.

Data may be pushed from one data source to another, which is the same security warning for combining DirectQuery and import sources in a data model (a feature that was introduced in Nov 2018).

To learn more about this behavior, please visit this article on using composite models in Power BI Desktop.

As this feature is currently in preview, we are eager to collect as much feedback as we can. Here are some things you can try to explore this feature for yourself:

  • Connecting to data from various sources: Import (such as files), Power BI datasets, Azure Analysis Services
  • Creating relationships between different data sources
  • Writing measures that use fields from different data sources
  • Creating new columns for tables from Power BI datasets of Azure Analysis Services
  • Creating visuals that use columns from different data sources

Beginning with the April 2021 version of Power BI Desktop, you can also connect to a perspective when making a DirectQuery connection to an Azure Analysis Services model, if a perspective is available.
Please leave feedback at this Power BI Community forum post.

Here are a couple of things that you might run into that is good to know:

  • If you refresh your data sources and there are errors with conflicting field/table names, Power BI will resolve the errors for you.
  • Users need ‘Build’ permissions on all datasets in the chain to access a report that leverages this feature.
  • To build reports in the Power BI service on a composite model that is based on another dataset all credentials will have to be set. On the refresh credential settings page, for Azure Analysis Services sources, the following error will show even though the credentials have been set:
    As this is confusing and incorrect, this is something we will take care of soon.
  • To be able to make a DirectQuery connection to a Power BI dataset your tenant needs to have ‘Allow XMLA Endpoints and Analyze in Excel with on-premises datasets‘ enabled. For premium capacities, the “XMLA endpoint” should be set to “Read-only” or “Read/Write”.
  • If using a classic workspace in combination with this feature it is not sufficient to set permissions on the dataset itself. For classic workspaces, all users accessing reports that leverage this feature must be members of the workspace, Consider upgrading classic workspaces to new workspaces to avoid this situation.
  • RLS rules will be applied on the source on which they are defined but will not be applied to any other datasets in the model. RLS defined in the report will not be applied to remote sources, and RLS set on remote sources will not be applied to other data sources.
  • KPIs, date tables, row level security, and translations will not be imported from the source in this preview build. We plan to add support for these properties when making this feature generally available. You can still create display folders in the local model.
  • You may see some unexpected behavior when using a date hierarchy. To resolve this issue, use a date column instead. After adding a date hierarchy to a visual, you can switch to a date column by clicking on the down arrow in the field name and clicking on the name of that field instead of using “Date Hierarchy”:

This behavior will be addressed later in the preview.
For more information on using date columns versus date hierarchies, visit this article.

  • You may see some less useful error messages when trying to use AI features with a model that has a DirectQuery connection to Azure Analysis Services. We plan to address them soon.
  • Using ALLSELECTED with a DirectQuery source results in incomplete results.
  • Filters / Relationships:
    • A filter that is applied from a data source to a table from another DirectQuery source can only be set on a single column. This might be supported in the future.
    • Cross-filtering two tables in a DirectQuery source by filtering them with a table outside of the source is a bad design and is not supported.
    • A filter can only touch a table once. Applying the same filter to a table twice, through one of more tables outside of the DirectQuery source, is not supported.
  • During the preview, the maximum length of a chain of models is three. Extending further, beyond the chain length of three is currently not supported and will results in errors. We are considering increasing the maximum chain length in the future.
  • Using third party tools, a “discourage chaining” flag can be set on a model to prevent a chain from being created or extended. To set it, look for the DiscourageCompositeModels property on a Model. We have plans to add this functionality to dataset settings in the Power BI service.
  • As with all DirectQuery connections, the connection to a Power BI dataset will not be shown in Power Query.
  • Parameters for database and server names are disabled. There are plans to support parameters in the future.
  • Defining RLS on tables from a remote source is not supported. There are no plans to support this in the future.
  • Using the following sources as a DirectQuery source is not supported:
    • SQL Server Analysis Services (SSAS). We plan to support SSAS Tabular models in the next major release of SQL Server. Support for SSAS Multidimensional models is not planned.
    • SAP HANA.
    • SAP Business Warehouse.
    • Real-time datasets.
  • Using DirectQuery on datasets that connect to SSAS using a live connection is not supported.
  • Using DirectQuery on datasets from “My workspace” is not supported. This will be resolved shortly after public preview.
  • Using Power BI Embedded with datasets that include a DirectQuery connection to a Power BI datasets or Azure Analysis Services model is not yet supported. We plan to support this as soon as possible.
  • Format strings on columns and measures from a remote source are not imported to the composite model. We are looking for your feedback to prioritize this.
  • Calculation groups on remote sources are not supported, with undefined query results.
  • For now, calculated tables are not supported in the Service when using this feature.
  • Sort by column is not supported at this time. This will be added soon.
  • APR (automatic page refresh) is only supported for some scenarios depending on the data source type. You can find more information in the APR documentation.

Any model with a DirectQuery connection to a Power BI dataset or to Azure Analysis Services must be published in the same tenant, which is especially important when accessing a Power BI dataset or an Azure Analysis Services model using B2B guest identities, as depicted in the following diagram. See Guest users who can edit and manage content to find the tenant URL for publishing.

Consider the following diagram. The numbered steps in the diagram are described in paragraphs that follow. Please note that this information applies to Power BI datasets as well.

In the diagram, Ash works with Contoso and is accessing data provided by Fabrikam. Using Power Bi Desktop, Ash creates a DirectQuery connection to an Azure Analysis Services model that is hosted in Fabrikam’s tenant.

To authenticate, Ash uses a B2B Guest user identity (step 1 in the diagram).

Power Bi Merge Dataset

If the report is published to Contoso’s Power BI Service (step 2), the dataset published in the Contoso tenant cannot successfully authenticate against Fabrikam’s Azure Analysis Services model (step 3). As a result, the report will not work.

In this scenario, since the Azure Analysis Services model used is hosted in Fabrikam’s tenant, the report also must be published in Fabrikam’s tenant.

After successful publication in Fabrikam’s tenant (step 4) the dataset can successfully access the Azure Analysis Services model (step 5) and the report will work properly.

This is the first update of many additional features to come. Here is a sneak preview of what is coming:

  • Settings for limiting who can use DirectQuery for Power BI datasets and Azure Analysis Services in the Power BI service.
  • More support for capabilities including parameters, display folders, KPI’s, date tables, and translations.
  • Integrations with Data Lineage, Data Protection, and Deployment Pipelines.

Please take the preview for a spin and feel free to leave feedback at this Power BI Community forum post. We are looking forward to your feedback!