February 12, 2022 at 7:19 pm
I have a 120gig database.
I want to analyse it with Power BI. (It's way to Large)
This is data from a machine at a sampling rate of 0.5 sec.
To do my analysis, 1 min to 15 min sampling rate would be ok.
How can I make a query to do this? Or what’s keywords should I search for to find information?
Since the disk is full, it would be great to delete the non desire value.
TagIndex: 135 values
Millitm: it seems to increment 10 at each half second.
Let me know if I should adress it in a more appropriate forum. Help would be appreciate.
My sql skill level is so low that I don't even know what to seach for in Forums. (but I find it very interesting)
February 13, 2022 at 9:32 am
Hi Fred,
I think any query with TOP clausule (with PERCENT modifier) should help extracting a limited bunch of data suitable for Power BI analisys.
Even more, by combining TOP with the apropiate WHERE you'll get relativelly few significant records. From my experience, often big databases mantains heavy tables because of historical regs. Consieder retrieving, for exemple, only "last year" information (any "timeline" division).
Best wishes.
RS.
February 13, 2022 at 4:43 pm
You could filter on TaqIndex... something like
WHERE TaqIndex % 30 = 0, and then you'd only pull a small fraction of the records. Instead of 30, try a larger number... that'll decrease the number of records returned.
Also, there's a HUGE difference between "normal"/standard storage and Columnstore, which is what tabular models use. If you don't have a lot of unique values in your data, it will compress pretty well. (So separating datetime values into a date column and a separate time column will aid in compression).
February 14, 2022 at 8:09 pm
The sample rate is at roughly a half second. It's hard to tell what the actual data is from the graphic you posted but are they actually recording the time? If so, they might be a way to actually pre-summarize some data like taking the average by Millitm and TagIndex for know periods of time.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply