Date table in Direct query

[Anonymous]

Hi Team,

My Source is SQL server, pulling as a direct query. But when i create a date table in Power BI then it is getting change to Mixed Storage mode. 

Could you please let me know if there will be any disadvantages if i use Mixed mode ? or is there any alternative to create a date table in direct query.

Thanks,

Shubham

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Please kindly refer to the official document for more details of Mixed mode:

https://docs.microsoft.com/en-us/power-bi/desktop-storage-mode 

https://docs.microsoft.com/en-us/power-bi/desktop-composite-models 

Composite models have some security implications. A query sent to one data source can include data values that have been retrieved from another source. 

Consequently, information that's stored in the spreadsheet is now included in a query that's sent to the relational database. If this information is confidential, you should consider the security implications. In particular, consider the following points:

• Any administrator of the database who can view traces or audit logs could view this information, even without permissions to the data in its original source. In this example, the administrator would need permissions to the Excel file.

• The encryption settings for each source should be considered. You want to avoid retrieving information from one source by an encrypted connection and then inadvertently including it in a query that's sent to another source by an unencrypted connection.

Or is there any alternative to create a date table in direct query.

That is currently not possible. You should try and create date table in your source to make it accessible in Direct Query Mode.

[amitchandak]

I personally used the date dimension from SQL Server itself. Refer:https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-1-Time-Intell...

But this good video to understand the advantage of mixed-mode: https://community.powerbi.com/t5/MBAS-Gallery/Aggregation-and-composite-model-Microsoft-Power-BI-fas...

[mwegener]

Hi @Anonymous ,

if the date table comes from the SQL, filters can be transferred to the SQL server.

[mwegener]

Take a look at this...

https://docs.microsoft.com/en-us/power-bi/desktop-composite-models#performance-implications

[parry2k]

@Anonymous no issue with mixed mode, it is all good. if you don't want mixed mode, you should create the date dimension in sql and pull it from there. you can surely use mixed mode.

Would appreciate Kudos 🙂 if my solution helped.