Power BI Datasets as part of Composite Model?

[AlexisOlson]

In Power BI Desktop, I can connect to a Power BI Service Live Connection just fine, however, I then have zero flexibility at all to add even a single calculated column or load in any additional data.

From what I heard at the Power BI World Tour in Dallas, I thought this connection would work just like a DirectQuery that can be used in a composite model, but this seems not to be the case, at least currently. Am I missing something?

Is this a Premium-only feature? Is this part of the next update (or otherwise in the near future) or is it not even a planned feature at this time?

[v-frfei-msft · Accepted solution]

Hi @AlexisOlson,

As the instruction of connecting to the dataset in power BI service using Power BI desktop, that is a kind of live connection.

As we know, There are more limitations for live connection in place. It doesn’t work against all data sources. Current list can be seen here https://powerbi.microsoft.com/en-us/documentation/powerbi-refresh-data/#live-connections-and-directq...

You are also limited to just one data source/database you selected. You can’t combine data from multiple data sources anymore. If you are connected to SQL Database, you can still create logical relationships between objects from that database as well as measures and calculated columns. When you are connected to SQL Server Analysis Services, you are limited just to report layout and even can’t make calculated columns or measures. When connected to SSAS, you could also reach some licensing limitations. Power BI issues DAX queries against SSAS and this isn’t supported in Standard edition of SQL Server before SQL Server 2016. When using live connection, users have to have access to underlying data source. This means you can’t share outside of your organization or publically. Other way around, when you set up security once, all reports using live connection to the same source have the same security model applied automatically. Similarly, you can prepare your data model carefully with all measures and calculated columns on data source side. All reports from the same data source can benefit of it. Refresh frequency is unlimited. Power BI simply shows data as they are in database. However, be careful using live connection against highly transactional OLTP systems. If you suffer from performance problems now, analytical workloads from Power BI won’t help it. Consider enabling features on data source side like Operational Analytics in SQL Server 2016.

Regards,

Frank

[LivioLanzo]

Hello @AlexisOlson

currently to a Power BI datasets you can only connect live and it works like when you connect live to SSAS, Power BI becomes only a visualization tool. 

Composite models work with Direct Query and Import only