Thursday, June 19, 2014
Have you heard of the 7 V of Big Data. Everyone knows the 3 V, but it was kind of interesting to hear some additional ones by IBM consultants. Personally, I think the last 2 should be present in all BI/data projects, but that's just me.
Listen to the podcasts put out through "IBM Big Data & Analytics Hub Podcast"
Tuesday, June 10, 2014
I remember having this problem 8 years ago. We had to find the top 5 donor per month and send out some awards. The SQL we came up with was clunky and had lots of limitation (can only do one year at a time), then switch the where clause and go again.
Fast forward 8 years, I got a similar problem where we had to find the top 3 combination of 2 fields for every single day. And the solution is this elegant:
CAST(eff_dt AS DATE) AS "RecordDate"
, COUNT(*) AS occurance
, ROW_NUMBER() OVER (PARTITION BY CAST(eff_dt AS DATE) ORDER BY COUNT(*) DESC) RowNum
WHERE RowNum < 4
CAST(eff_dt AS DATE)
If only I had this 8 years ago. :) Life is good now!
[UPDATE 2014-08-28]: As KarKey pointed out, you'll have to put this into a derived table to use RowNum in the outer where clause. The query syntax as it is was run against a Teradata database successfully.
Wednesday, May 28, 2014
- To this date, I still haven't figure out how to "toggle" between my query windows. It seems like unless I click on that "new" button on top, whatever SQL I generate from right-click just overrides the current SQL in the window. I'm probably missing a "generate new sql in new window" setting
- The default Teradata SQL Assistant doesn't execute just the SQL query I highlighted. There is a setting I have to change first.
- I'm not really happy that the SQL assistant and SQL admin are different app. Still trying to get used to the fact that I can't quickly look up a table's keys/relationships while writing query. I have to switch between windows.
- LOVE the execution plan / explanation. I think that part is better done than MS SQL in some ways. The error messages can be better.
- I feel that Teradata .NET provider sends smaller query command over than others. I don't have any hard data to support my claim. One of my query in SSRS was passing multi-valued parameters to another query, and got error "Teradata 3577 row size or sort key size overflow". The search on this error says the solution is to cast result column into smaller data type, but I found that the problem was that the parameter passed into the where clause could not be too large.
- I wish Teradata SQL Assistant would remember the window size I just adjusted to. Every time I execute the query, the result set, query, and exec log auto re-adjust back to the default size. In SSMS, if I adjust the result set area to be smaller, it would stay like that if I execute query in the same window.
Monday, April 28, 2014
I'm getting this error if I try to install or update an app on my Sprint HTC 8XT Windows 8 phone. Wondering if anybody else is having the same issue. The other issue occured at the same time was my OneNote stopped syncing. After changing the server name of my Microsoft account, I was able to get mine syncing again, but not my wife's phone.
Following some blog posts, I tried to update my microsoft account server name to m.hotmail.com, but it always set itself to bay404-m.hotmail.com. This server allows OneNote to sync, but my wife's phone is stuck at col404-m.hotmail.com, and OneNote won't sync.
Sprint's advice was to delete the trusted device from list. Tried that and didn't see any difference. Knowing Microsoft's last gaffe with phone update (http://www.product-reviews.net/2013/02/04/windows-phone-7-8-update-data-and-battery-problems/), perhaps this is still too early in the process to expect any action?
[2014-04-29 Update] I ran "Network profile update", then re-enter that "m.hotmail.com" into server, and now it works.