Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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.
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...
Hi @Anonymous ,
if the date table comes from the SQL, filters can be transferred to the SQL server.
Take a look at this...
https://docs.microsoft.com/en-us/power-bi/desktop-composite-models#performance-implications
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |