New Power BI Features
Those of you who are familiar with Microsoft Power BI may know that Microsoft releases new features every month. August has just seen an exciting update – a significant improvement in regard to Data Sources and Data Modelling.
There are three different ways to source the data in Power BI:
- Import the data into Power BI Cache
- Direct Query
- Live connection (connects to the existing Data Sets, SSAS, etc)
In previous versions, creating a single model from multiple sources of data including the ones from the SQL databases was only achievable by importing into Power BI Cache. It was impossible to combine the data accessed either by multiple Direct Queries or even the Data from the single direct Query and imported Data.
If you were using Direct Query to connect to the database, then your model could contain only the data that was returned by the direct query itself. This was a significant limitation.
Though importing the data is still the preferred way for Power BI desktop especially in case of the limited data sizes, in some cases it is absolutely impossible.
Latest Power BI Release
The latest release of Power BI introduces the new feature called composite models. In fact, there are three new related features that were introduced in the Power BI:
- Composite Models
- Many-to-Many Relationships
- Storage Model
Composite Models allow you to create Power BI model (such as a single .pbix Power BI Desktop file) that either combines data from one or more DirectQuery sources, and/or combines data from DirectQuery sources and import data.
For example if you have your enterprise sales data in the database and your local department management data in your spreadsheet you can create a single model that combines these two different sources of data into a single Power BI model by directly linking you data imported from the spreadsheet with the data that was defined by the Direct Query. The only restriction on your model is that the data that is coming from different types of sources will always have many-to-many relationships with each other.
Many-to-Many Relationships is the second new feature introduced in Power BI. Previously if you had many-to-many relationship between two tables you had to define an intermediary table which had a one-to-many relationship with both original tables. Now you can define many-to-many relationship directly. Power Bi will create an intermediary table behind the scenes, but it does not need to be included in the model.
Storage Model allows you to specify which visuals require a query to back-end data sources, and those that do not require it are imported even if based on DirectQuery. Previously, even simple visuals like slicers, initiated queries sent to back-end sources. The Storage Model has three values import, direct query and dual. If you need to preserve one-to-many relationship between dimension tables (that come from the same data source as direct query tables) and main data tables, then dimension tables are set as dual storage model which then can have a one-to-many relationship with both imported and direct query tables.
Those updates significantly improve the capabilities of Power BI and we are looking forward to introduce them within our next projects.