Day: September 10, 2019

For this example, add a Table in Direct Query mode from SQL Server. Here I’m using my AdventureWorks DB from my local database

Add another file (any Excel file you have) Direct Query is not available for Excel. Before Composite models was available, you couldn’t add Excel if your other table was direct query. Everything had to be the same import mode

Close and Apply

Now Composite model is available you can mix storage models. However you need to be aware that the Direct Query will have actual data within the query (SQL) rather than all the querying being done within Power BI

You can hover over the tables to see what table storage is set for each table

Note that we can see the data for Excel which can only be Imported data

However we cant see the data in the data pane for the Direct Query Fact table

We can go to the model view and connect the tables together

To show this I’m going to add Dim date from the SQL Data base as an Imported table (Remember the fact is a direct query)

A composite model is one where tables have different Storage types. there are now three storage types.

• import – the Best way to use Power BI because everything is cached in memory
• Direct Query – As above Power BI will query your data source and your measures and calculated columns will be created within SQL if you are connecting to a SQL database
• Dual – can be both Import or Direct query. We will look at this in more detail later

live Query is only available using analysis services as a data source. In this case you can only use SSAS and no other data source.