cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Antiparras Regular Visitor
Regular Visitor

SQL Load huge amount of data already grouped / filtered / formatted

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. 

 

idnorequestsdatetotaldurationmsusertypetotalrequestsizereturnstatustotalresponsesize
0505.09.201814934user5type2332511980
1805.09.201811540user5type384691321914
262005.09.2018740808user1type418102012813252
343605.09.20181109771user1type51124381766411
4205.09.20182310user2type645811684
5205.09.20181237user2type55361734
62805.09.201895467user5type1357301773762
7105.09.20182411user5type66011696
8140705.09.201826959user1 0121105

 

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?

2 REPLIES 2
Highlighted
cmaz Regular Visitor
Regular Visitor

Re: SQL Load huge amount of data already grouped / filtered / formatted

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.

Antiparras Regular Visitor
Regular Visitor

Re: SQL Load huge amount of data already grouped / filtered / formatted

Thank yoiu @cmaz. 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...