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.
I've been reading several posts in different forums but none of them hits what I'm looking for.
I've got a table in a SQL server for server transactions with millions of records that I would like to get in Power BI.
I need to do some statistics so I want to group the data by some columns, extract a server name from a XML look-alike string (with delimiters) and get a count of requests and sum of request & response sizes and time to get them.
Here's an example of the desired result. Notice that norequest will be the original number of rows processed.
id | norequests | date | totaldurationms | user | type | totalrequestsize | returnstatus | totalresponsesize |
0 | 5 | 05.09.2018 | 14934 | user5 | type2 | 3325 | 1 | 1980 |
1 | 8 | 05.09.2018 | 11540 | user5 | type3 | 8469 | 1 | 321914 |
2 | 620 | 05.09.2018 | 740808 | user1 | type4 | 181020 | 1 | 2813252 |
3 | 436 | 05.09.2018 | 1109771 | user1 | type5 | 112438 | 1 | 766411 |
4 | 2 | 05.09.2018 | 2310 | user2 | type6 | 458 | 1 | 1684 |
5 | 2 | 05.09.2018 | 1237 | user2 | type5 | 536 | 1 | 734 |
6 | 28 | 05.09.2018 | 95467 | user5 | type1 | 35730 | 1 | 773762 |
7 | 1 | 05.09.2018 | 2411 | user5 | type6 | 601 | 1 | 696 |
8 | 1407 | 05.09.2018 | 26959 | user1 | 0 | 1 | 21105 |
While I've got all clear in SQL, it takes AGES to run this in Power BI, either in the loading process or afterwards, so my question is:
Any practises to load just the data I want (already grouped, counted, etc.) in a quick manner?
Hi - If you create a view on top of the table and then attempt to load the data, you may notice faster response.
Also, I suggest that you take a look at this thread and check memory usage via Windows task manager -> Resource monitor.
Thank yoiu @Anonymous. Actually Power BI is grabbing data from a view, but it takes too long to access the data anyway. I did read that thread about the memory usage before and it doesn't apply to this case, as memory is far from being a limitation here.
I think I'll need to rethink the SQL view and maybe even the scope if I don't find a suitable solution...
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.