Imagine having your Data Model ready and everyone loves it.
Then you get this question all of a sudden.
“I have made this excel file and I would like to use and combine it with the Sales Dataset in PowerBI.”
The next thing you do is analyze the request and for some reason, maybe you don’t want to flood the Datawarehouse with Excel Files or maybe your ETL team is too busy with requests, but you have to say no.
The business user is quite disappointed because it would really have solved that time-consuming task he/she had to do every week.
What if I told you that all the above can be avoided with a very simple solution?
Composite Models are the answer!
These models are a way to seamlessly combining different data sources in PowerBI.
With composite models you can connect to your Azure Analysis Service (AAS) model or Power BI dataset and enrich them even further by connecting to a second model, adding flat files, or even just the ability to build measures on top of your AAS model.
This is all super exciting for both business users and BI developers as it will create even more analytical opportunities.
Now, I know what you are thinking.
Let’s take a look at this feature!
I love your eagerness but before we dive into it, you should know that this feature has the following Limitations.
The following Live Connect multi-dimensional sources can’t be used with composite models:
- SAP HANA
- SAP Business Warehouse
- SQL Server Analysis Services
- Power BI datasets
- Azure Analysis Services
As of December 2020, DirectQuery for PBI and Analysis Services is available in Public Preview.
If you would like to use Composite models with PBI Datasets and AAS you should enable this feature.
Now, with that out of the way, let’s get to the exciting part!
In our Example, we will be using a live connection to a PBI Dataset and an Imported Excel File.
First of all we will be connecting to the PBI Dataset we want to use.
Once this is done and you are all ready and eager to add a different source to the model, or to just add your own calculated columns, you will need to go to Transform Data.
You will be prompted with the above information.
As stated previously a DirectQuery Connection is required. This is the reason the DirectQuery for PBI Datasets and Analysis Services was required.
It will also notify you that this change is permanent. You cannot change this back once performed and saved. This is very important to keep in mind.
When we are ready to continue we can click on the Add to Local Model button and a DirectQuery Connection will be made.
You know it will be successful when you see the following text in the bottom right corner.
Now we can do quite a few more things more we couldn’t before to the already existing model.
For example, if you previously right clicked on a table you got the below options.
However, now we can actually create new Columns along with renaming/hiding Tables, Measures and Attributes and create relationships.
This last one will be very handy when adding different sources to your model.
Now we can actually add a different source file to our model. This can go from Excel or Sharepoint files to different PBI Datasets.
Once you add another data source you will once again be prompted with a message.
Of course, when combining different data sources through DirectQuery there is always the possibility that data can be included in queries sent from one data source to the other.
Always be certain of the sources you are pulling your data from.
Once you are sure this is no issue for you, we can continue.
Both models will now exist separately from one another and can be joined through a relationship you define if required.
Different visuals can now be built using data from both (Or more) sources.
All of this is amazing and a great leap forward for both developers and advanced business users.
PowerBI is a tool that keeps improving and each iteration brings in more capabilities to deliver high-quality dashboards to the end-user in a short amount of time.
If you are interested in getting to know more about Composite Models or other Microsoft BI-related aspects you can always contact Lytix.