Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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...