Beginner Query SQL
-
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)
- This topic was modified 2 years, 7 months ago by FredC.
-
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.
-
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).
-
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
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply