Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hello Team,
I need to load very huge amount of data in power bi from SQL server. Data rang is starting from 2012 to till now. So, there is a massive amount of data even if I consider only for single month data e.g. December, 2020. I need to load the data and just need to display based on filter value provide by the end user. No transformation is required on the data.
1) So can i make the dataset at runtime based on parameter selection? as it will reduce the size of dataset runtime, just we are doing in SSRS, In SSRSS we apply the parameter and when press the submit button it will generate the report based on the parameter supply. If not, then what the other alternatives to minimize the dataset.?
2) What the suggestions to use Import mode or Direct query? There is size consideration will be there for both the options.
3) I am a Power BI Pro user, and my system ram is 16 GB. Should I upgrade the Power BI Pro to Power BI Premium capacity? What the other alternatives if I want to stay only with Power BI Pro?
Thank You
Abhishek
Hello @Anonymous ,
how much is a massive amount of data for you?
1) Something kind of similar is possible. Take a look at the dynamic M parameters:
https://www.youtube.com/watch?v=hrPbf06aF9o
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters
2) In general you have more possibilities and a better user experience with the import mode. But you also have limitations of I guess 1 GB file size in the cloud and 10 GB in premium capacity.
If you don't need any transformation you could give the direct query connection a try. But be aware that you need a fast relational database underneath as every click in the report will trigger a new query. If you have multiple user working on the same report it can be a huge load for the database.
3) Your Power BI subscription doesn't have any impact on the local RAM. Your bottleneck could be the possible file size of 1 GB in Pro and 10 GB per file in Premium capacity. Be aware we talk about file size, when it's saved and compressed.
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
Best regards
Denis
Hello @selimovd ,
Thank You for your valuable feedback and guidance.
I have a Banking Transaction table, If I consider only One month data even if there are more than 9 Million data, in few cases its above to that figure. So instead of loading all the year record starting from 2012 to till date, I want to take inputs from user that, for which year you want to see the reports. If user set 2019 then accordingly Dataset will be prepared and load the data instead of loading all the years data.
Suppose if there is no any other alternatives and I must load all the data from 2012 onwards, then what should be the best option to use 1) Import Query or 2) Direct Query? Please suggest.
M query or Parameters works with Desktop version file but when we published the report it might be not ask the report parameter, I guess.
Eagerly waiting for your reply, Denis.
Thank You, Thank You very much Denis.
Regards
AbhishekP
Hello @Anonymous ,
ok, that's quite a bit of data.
My feeling tells me that might be too much to load, even with the dynamic M parameters.
I personally would try to use an aggregation table in Power BI together with direct query.
That is again another approach that I didn't mention before 😉
You would aggregate your table to a lot smaller data, e.g. by bank location and month. This aggregated table will be imported to the Power BI file.
Whenever the result can be calculated with the aggregate table it will be used. If you would drill down into more details, e.g. by transaction or by day, then the query will be sent to the data source as direct query.
Check the video from Guy In a Cube, they demonstrate how you could do that:
Configure Power BI Aggregations - YouTube
If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️
Best regards
Denis
Blog: whatthefact.bi
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
72 | |
62 | |
52 | |
48 |
User | Count |
---|---|
208 | |
89 | |
61 | |
59 | |
57 |