The Heap™ – Consultancy ©®

gbn

6:37 AM

@ConcernedOfTunbridgeWells I don't much SO either

Morning all

ypercube

Morning

ypercube

7:01 AM

@gbn: Why Oracle cannot handle this kind of subqueries?: sql-fiddle, test-1

gbn

missing group by?

teh subquery is non-aggregated so a GROUP BY is needed

ypercube

It works fine in SQL-Server, Postgres, MySQL

gbn

MySQL allows such stuff anyway

Postgres allows no GROUP BY but stricter

dunno why SQL Server allows it

ypercube

Why not? The MAX(t.Datex) is refering to the external query (Group By all rows)

gbn

because of the non-aggregated subquery

do you need cross-platform?

I'd consider using OVER clause if not MySQL

ypercube

7:14 AM

Check this version

I guess the issue is that the parser cannot undertand that MAX(t.Datex) is refering to the grouping in the external query.

gbn

I wouldn't have expected SQL Server to like it. But hey, keep on learning

I don't normally use subqueries in the SELECT clause though. I view them as too RBAR. And see them a lot my non-SQL types

ypercube

It's funny that only Oracle and SQLite reject it :)

It was for a question at SO. I couldn't find a way without a subquery, either in the SELECT list or in the FROM list.

Jack Douglas

12:51 PM

@ypercube my instinct would be to nest and have the subquery in the outer query.

@gbn can be slow, can be a good deal more readable in some cases IMO

gbn

@JackDouglas my brain sees them as more complex

Jack Douglas

If you are trying to guarantee 1 row output per 1 row in table A, this is harder to see at a glance with outer joins etc

my brain sees everything as complex :-(

ConcernedOfTunbridgeWells

I'm complex because I have an imaginary component. This also means I can eat quiche.

swasheck

2:15 PM

@jcolebrand I dont think so.

jcolebrand

kk

swasheck

3:38 PM

2

Q: Not able to attach database file without transaction log file

I lost the transaction log file. I tried re-attaching the data file without the log file, to create a new log file, SQL Server is giving error:

►

DTest

haha

swasheck

I've just been looking for a way to work Stephen into the conversation.

JNK

3:59 PM

hai guys

swasheck

Hello

DTest

4:12 PM

hi @jnk

ConcernedOfTunbridgeWells

4:53 PM

@JNK Long time no see.

Ben Brocka

Long time no...be in this room

swasheck

5:26 PM

@ConcernedOfTunbridgeWells my god ... it's full of stars

1

A: 2NF decomposition + database normalization

There's a little aphorism that goes: The key (1NF) The whole key (2NF) And nothing but the key (3NF) . . . So help me Codd In your example we can assume 1NF to begin with as the relational structure doesn't imply any repeating groups within the row (i.e. no D1, D2, D3 etc.). R = {a, b, ...

ConcernedOfTunbridgeWells

@swasheck When I did my database paper at university I looked at relational synthesis and thought - kind of irrelevant, so I didn't swot it up. Turned out there was a question wirth 25% of the final exam on it. sad-trombine schadenfreude

swasheck

@ConcernedOfTunbridgeWells Bummer. Yet the lesson clearly made an impact on you. Plus, the aphorism is something that I'd never heard.

ConcernedOfTunbridgeWells

@swasheck I saw it in the first Guru's Guide book IIRC,

ConcernedOfTunbridgeWells

5:43 PM

Now, if I did one on regular expressions, that really would be full of stars.

swasheck

@ConcernedOfTunbridgeWells Truly

ConcernedOfTunbridgeWells

Apropos of nothing at all, my Codd number is 2. I know someone who used to work for Codd & Date when they had a consulting firm.

swasheck

@ConcernedOfTunbridgeWells that clearly legitimates your lofty status here on DBA.SE

ConcernedOfTunbridgeWells

Context

swasheck

@ConcernedOfTunbridgeWells So its like an intelligent version of the "Six Degrees of Kevin Bacon"

ConcernedOfTunbridgeWells

5:48 PM

@swasheck Absolutely. Not that anybody important would actually have anything to do with me.

@swasheck Sort of. Paul Erdos was quite famous for travelling around, staying with people and then doing big jam sessions with them. He published an ungodly number of papers this way and quite a lot of mathematicians knew him personally in this manner.

DTest

@ConcernedOfTunbridgeWells Hmm, does that mean i can claim Codd number 3...or does internet chat acquaintance not count?

ConcernedOfTunbridgeWells

@DTest Has about as much relevance as my having worked with someone who knew Codd once.

DTest

Yay, though I'll refrain from adding that to my Resume.

ConcernedOfTunbridgeWells

@DTest I don't mention it on mine.

ConcernedOfTunbridgeWells curriculum vitae, April 2012
Career Highlights:
o Worked with a guy who knew Edgar Codd once
. . .

DTest

They'd probably ask you for the contact info of the guy. Loss of job!

ConcernedOfTunbridgeWells

5:52 PM

@DTest Maybe. He's quite a successful B.I. consultant now, and has been doing relational databases since early versions of DB/2.

DTest

@ConcernedOfTunbridgeWells I'm just beginning to delve into B.I. chapter 1 of 'The Data Warehouse Toolkit (2nd ed)'

something to read while archiving something like 3 years worth of tracking data for my employer

ConcernedOfTunbridgeWells

BRB

DTest

yeah going to have a good laugh, no doubt ;)

jcolebrand

thedailybeast.com/articles/2012/04/09/…

DTest

more importantly, why didn't I think of it first

jcolebrand

5:58 PM

Wait, what's the overall benefit for Facebook to buy Instagram? I hope they keep my grams off the book.

DTest

"We plan on keeping features like the ability to post to other social networks, the ability to not share your Instagrams on Facebook if you want, and the ability to have followers and follow people separately from your friends on Facebook."

but the benefit is that they now have access to that data..and information is money!

swasheck

@DTest @ConcernedOfTunbridgeWells just laughs at us behind our backs, which is nice compared to what happens to others.

@jcolebrand Glad I never got hooked on that app.

DTest

To me, the ROI would be very small, but then when you're worth that much, any tenth of a percent is worth it.

jcolebrand

@swasheck it's not too bad. I've heard some people say "oh, it's like a bad version of tumblr" which it was never intended to be a tumblog replacement.

swasheck

@jcolebrand Yeah. I thought that it was all about doing neat things to your pictures. I just don't have any pictures.

ConcernedOfTunbridgeWells

6:09 PM

@DTest Well, I've been doing business intelligence for about 10 years now and I've yet to see it in the wild.

My job is about 90% data monkey work, and occasionally I get to do something that makes a difference.

A friend of mine coined a good phrase to describe the phenomenon: 'condemned to mediocrity.'

swasheck

@ConcernedOfTunbridgeWells define "makes a difference"

Also. Someone just told me about coffee that has been processed by the colon of a bird

ConcernedOfTunbridgeWells

So, I tell myself 'at least as a contractor I get paid to deal with the bullshit' and then I remember: 'Oh, yeah, joint account. Bugger.'

Fairly low standards:
'Produces something that markedly improves on the status quo and achieves a significant fraction of its original objectives.'

DTest

I would like to imagine going from no use of the archived data to some use is a lot like going from a HDD to a SSD in terms of 'making a difference'

ConcernedOfTunbridgeWells

Bearing in mind that the companies who hire contractors for this are ones where the internal culture prevents their own I.T. function from implementing something effective and where B.I. doesn't have enough priority within the business to bring in a consulting firm with actual brand recognition.

@DTest It's all rather context sensitive. Perhaps more like putting a SSD on a machine where you're still connecting to it over a RS232 cable.

Very, very subject to the 'maximum pace of the slowest member' principle.

DTest

I definitely can see that

ConcernedOfTunbridgeWells

6:19 PM

Like any integration project, data warehouses tend to be very political as you have to put your fingers in pies. Often the source data isn't sufficiently joined up in the first place to achieve everything you might want to do.

Plus, you often have to deal with reference data maintained out in desktop systems, which may or may not be of very good quality.

Still, if you can get useful stats out of your archived data then you're probably going to achieve something useful.

What options have you got for a platform?

DTest

7:31 PM

@ConcernedOfTunbridgeWells Sorry ran away for lunch without telling anyone! No idea on platforms yet, as I said literally just began researching it. So any suggestions would be appreciated.

ConcernedOfTunbridgeWells

@DTest SQL Server comes with a passably good set of reporting and ETL tooling and an OLAP server.

DTest

I don't know the budget I'll have (very small company), but I plan to make the best damned presentation when this research is done that it'll be 'Do this or stagnate, kthxbai.'

ConcernedOfTunbridgeWells

@DTest If you can go 'bleeding edge' then SQL Server B.I. edition is about 8,500 USD per server + CALs and has most of the useful features from Enterprise Edition. The main draw card for EE is partitioning if you have really big data volumes.

DTest

Yeah, SQL Server ... it's high on my list, but quite aside from the cost, try convincing a mac shop to purchase anything microsoft related :(

ConcernedOfTunbridgeWells

@DTest How much data do you have?

Realistically, I think 2012 B.I. edition is by far the best bang for buck on the market for (say) <100GB of data.

DTest

7:35 PM

not much...~60GB from my OLTP source.

ConcernedOfTunbridgeWells

number of rows?

DTest

which is the first source I'll be looking at doing anything with

~700mil

ConcernedOfTunbridgeWells

You might be able to do an open-source one with PostgreSQL and (say) Pentaho.

PostgreSQL has table partitioning, but no parallel query facility. Mondrian is hybrid OLAP server that caches aggregations in memory.

DTest

Postgres was on the list to look into, didn't know how well they fared in BI tools.

ConcernedOfTunbridgeWells

Anything else will be way, way more expensive.

DTest

7:39 PM

I'm prepared to spend it if I need to. Can't say my boss is though :)

ConcernedOfTunbridgeWells

$8,500 is cheaper than the server you'll put it on, and definitely cheaper than any significant development effort.

DTest

how does that licensing work (I know I could look it up)...one-time, annually, other?

ConcernedOfTunbridgeWells

Large table scans on PosrgreSQL might be slow with the lack of a parallel query.

@DTest From the MS licensing blurb, that's the up-front cost to buy a licence. B.I. edition is licensed by Server + CALs, so you have to pay extra per seat (about 200 USD per seat IIRC)

With B.I. edition, I guess the entry point is about $15,000 to 20,000 USD for a server including hardware and software (obviously depending on the spec of the hardware).

DTest

That's really not bad

huge leap from $0 in software, but probably worth it.

ConcernedOfTunbridgeWells

For your data volumes I'd guess you could get away with B.I. edition. Probably cheaper to throw hardware at it than to purchase Enterprise Edition.

12

A: Sql Server 2012 data warehousing and different versions

Business Intelligence Edition Business Intelligence edition has some useful features, like Master Data Services and non-additive aggregations (i.e. anything but sum/count). EE has partitioning and the rest of the large database features. The EE features are mostly relevant to users with large ...

DTest

7:45 PM

I'm glancing over this licensing pdf...I love how on the licenses based on number of cores, the minimum is 4 cores per processor

ConcernedOfTunbridgeWells

@DTest That's for Enterprise Edition.

DTest

yeah, i know.

ConcernedOfTunbridgeWells

Way, way more expensive than B.I. edition.

B.I. edition has basic table partitioning support (3 partitions max), so you can do some partitioning tricks to archive off old data.

(i.e. shift partition boundaries then swap out the old partition). At least that should work on B.I. edition. Haven't tested it.

DTest

I know what I've said about my current volumes, but a) about 40% of that data is from the last 3 months (yay growth) and b) I have high expectations of exponential growth within the next 2 years

ConcernedOfTunbridgeWells

And cubes have unlimited partitioning support and unlimited resource usage on B.I. edition, and all the features of EE.

@DTest I guess you can upgrade to EE if you need to, although that would be a lot more expensive.

So, realistically you need to capacity plan for something an order of magnitude bigger than you have now.

DTest

7:48 PM

The last thing I want to do is recommend the expense if we don't need it. Loads of research

@ConcernedOfTunbridgeWells precisely

ConcernedOfTunbridgeWells

Which would suggest EE.

DTest

haha

ConcernedOfTunbridgeWells

I doubt any of the open-source systems will cope with that data volume.

DTest

any experience with oracle?

ConcernedOfTunbridgeWells

MySQL only does nested loops joins.

@DTest A bit, although it's way dearer than SQL Server.

And all the front-end tooling is extra.

DTest

7:50 PM

good times

ConcernedOfTunbridgeWells

For your volumes you could look into Greenplum.

For 7 billion rows of something fairly narrow SQL Server is still probably the best bang for buck, even if you have to go to EE.

PostgreSQL doesn't do parallel queries, so it will probably be too slow. MySQL only does nested loops joins IIRC. @gbn will be able to fill you in on the joys of trying to do a data warehouse on top of MySQL.

DTest

haha

Yeah, just from my own experiences, I've about 98% scrapped the idea of using MySQL for this.

plus, it's a great opportunity to learn new techs

ConcernedOfTunbridgeWells

SQL Server's main claim to fame is that you get ETL and reporting tools bundled with the DB server. Getting these from a third party could be quite expensive.

Greenplum is probably good for your data volumes, but iyou would need a third party reporting layer. It's more expensive than SQL Server ($16,000 per core vs. $6,000 per core for EE).

Oracle and DB/2 are even more expensive.

For anything but SQL Server, all the surrounding tools like reporting and ETL are extras.

DTest

that's making a good case for SQL Server

ypercube

@COTW: MariaDB 5.3 has added a few more options besides Nested Loops to the optimizer.

DTest

7:59 PM

if postgres is too slow (with the no-parallel-query limitation), sql server looks to be best choice based on this convo

ConcernedOfTunbridgeWells

DB/2 is about 30,000 USD per core, Oracle EE is about $47,500 per core list.

@ypercube Yes, now you remind me, it does hash joins as well now, doesn't it.

ypercube

Yes.

ConcernedOfTunbridgeWells

@DTest Overall, I think the pricing and the bundled tooling make a very strong argument for SQL Server. Oracle is losing quite a lot of B.I. customers to Microsoft.

A two-socket server like a DL380 has truckloads of computing power. With (say) 8 cores you'd be up for about $50,000 for the licensing plus the cost of the hardware for EE. You could use Windows Server SE for a machine with up to 32GB of RAM, over 32GB, Windows Server EE starts at about $4,000 USD (with 25 cals) + about $40/CAL after that.

The 16 internal disks on a DL380 (or 24 on a DL370) would be fast enough for hundreds of GB system ($15k maybe). If you needed more performance, an external 25-way 2.5" disk array would probably be about $10K.

Maybe $30-35K for a well specced server with B.I. edition, $70-80K for a server with EE, plus whatever your running costs are.

Possibly less than that, but I think you will want to get the fastest disk subsystem you can.

DTest

Yup, that is going to be a tough pill to swallow for my company. We also host a private 'cloud' at rackspace (basically leasing the hardware), so I'm not sure what they charge yet.

ConcernedOfTunbridgeWells

(I'm assuming a TB-ish size but that's really a 'how long is a piece of string' question without seeing the data up close).

@DTest Cloud servers have really crap I/O performance. 'B.I. in the cloud' is snake oil.

I saw someone quote 10-20MB/sec for Amazon EC2.

DTest

8:13 PM

no see, it's dedicated hardware for the db. that's what I meant by it's basically leasing hardware from them.

the application layer is virtualized, but the db is dedicated.

ConcernedOfTunbridgeWells

a. Do you know the spec of the hardware?
b. How much control do you have over the configuration?

This will almost certainly perform poorly on a data warehouse workload unless you can tune the disks correctly.

BIG WARNING FLAG. DANGER WILL ROBINSON

DTest

haha

ConcernedOfTunbridgeWells

Benchmark:

DTest

I've got complete control over the configuration, spec for the data warehouse hardware hasn't even come up.

ConcernedOfTunbridgeWells

Load up a table with (say) 1-2TB of data. Can be any old crap - just write something that bulk loads the data into the table.

DTest

8:16 PM

the only thing I don't have is 'physical' access to the server, but I've got a datacenter monkey to do my bidding.

swasheck

@DTest I'm fighting the netmonkeys, right now, over a SAN

ConcernedOfTunbridgeWells

Time a table scan from a cold cache.

This is how long your reports will take to run.

swasheck

so, are you going to be on a SAN? what is it?

ConcernedOfTunbridgeWells

Rule of thumb: you need 3x the disks (dedicated) on a SAN tuned for a general purpose workload to get the same performance as a SAN specifically tuned for a data warehouse system.

swasheck

indeed

yet this product was not purchased with any sort of structured data in mind

ConcernedOfTunbridgeWells

8:18 PM

BIG WARNING FLAG: I/O NOT TUNED FOR THE WORKLOAD IS REALLY SLOW

swasheck

and the mandate is clear "Make it work anyway"

DTest

@swasheck no idea yet, again, this is months/a year down the road so the speccing of the system hasn't even come up.

@ConcernedOfTunbridgeWells will do that :)

but not soon™

swasheck

@DTest as an organization, what is your underlying storage?

ConcernedOfTunbridgeWells

@DTest download.microsoft.com/download/E/3/0/… download.microsoft.com/download/9/9/0/… www-03.ibm.com/systems/x/os/windows/fasttrack.html

Reference specifications for a DW system with a few TB of data.

DTest

@ConcernedOfTunbridgeWells very nice, thanks

ConcernedOfTunbridgeWells

8:22 PM

@DTest Take a look at the SQL Server fast track data warehouse page. Note that the DELL system is actually direct-attach SAS even though they talk about a SAN (although the array does have local RAID rather than host-based RAID on ther server which will be a bit cheaper and faster).

Off the record, I've heard EMC sales reps say that many of their data warehouse customers use direct attach storage for their DW systems as SANs are too slow.

TBH I'm tired of fighting this battle with management who don't understand it.

The charlatans sweet talk them into buying virtualisation environments and say you can run anything on it. Then they try to shoehorn TB-sized data warehouse systems onto something that actual DB vendors don't recommend.

swasheck

@ConcernedOfTunbridgeWells The "Executive Summary" and "Sales Summary" have done untold amounts of damage to the underlying data architecture here

and I suspect many other places

ConcernedOfTunbridgeWells

@swasheck Enterprise software vendors are really one of the biggest problems in enterprise I.T.

DTest

@swasheck 3x146 SAS 15k drives in a raid 5 (barf) <--- learned after setup, so not possible to change

did I mention small data?

swasheck

@DTest I refer you to a previous statement by @ConcernedOfTunbridgeWells here

That is if performance is critical

ConcernedOfTunbridgeWells

If you go with B.I. edition on slow hardware,
a. Use incremental loads
b. Use cubes to speed up reporting. They materialise aggregates, and the native data format is fairly compact. Less I/O intensive than a database.

swasheck

8:28 PM

unless you want to put your faith in Twitter

@ConcernedOfTunbridgeWells Cubes on a separate instance, right?

DTest

Tuning a SAN is not in my current skillset. Didn't say I wouldn't do it. I do have the option of adding SANs to our setup 'in the future'

ConcernedOfTunbridgeWells

@swasheck Doesn't have to be, unless the database server is busy.

@DTest Are you using data off a shared SAN managed by the vendor or do you have your own?

DTest

we don't ... have a SAN. currently attached drives

ConcernedOfTunbridgeWells

If you just run overnight ETL on the data warehouse and occasional reports with most of the traffic coming off the cubes they can live on the DB server just fine.

swasheck

@ConcernedOfTunbridgeWells nice.

ConcernedOfTunbridgeWells

8:32 PM

@DTest Stick with direct attach storage on the DB server if possible. It's much, much faster, at least 10x the price/performance of SAN based storage.

@swasheck You can set them to share memory nicely, and with EE you can do procvessor affinity if you want.

swasheck

@ConcernedOfTunbridgeWells At this stage of the game, my problem is getting there. I inherited a "warehouse server" running EE with basically a bunch of replicated data

ConcernedOfTunbridgeWells

Also, remember you don't need hot failover for a data warehouse. A 24 hour restore-from backup DR strategy is fine for almost all reporting systems. Don't buy into more SLA than you really need.

swasheck

and reports hanging off tthose

@ConcernedOfTunbridgeWells and don't commit to more SLA than you've bought

ConcernedOfTunbridgeWells

@swasheck You could build an ETL process onto another DB on the same server if you want to set up a data mart. This might stretch your platform further as the queries would be more efficient.

Try and keep the reports running directly off the replicated server to operational ones that really need up-to-date data. Move all the rest off onto a data mart (which could be a cube and some views to populate it if that's your only option).

swasheck

@ConcernedOfTunbridgeWells That's my plan, but I'm about 3 months into this gig and there's a parallel Data Warehouse "proof of concept" project

a shadow project

that I just discovered :)

ConcernedOfTunbridgeWells

8:36 PM

@swasheck Do you have large data volumes?

swasheck

@ConcernedOfTunbridgeWells yes ... computing by as we speak

ConcernedOfTunbridgeWells

@swasheck Better go now. Good luck with your attempts to bring intelligence to the business.

swasheck

actually not that much

200 million

rows

ConcernedOfTunbridgeWells

@Dtest - rule of thumb with a data warehouse server:
a. As many disks as you can afford
b. Large stripe size on RAID volumes (256K or more)
c. Direct attach storage if possible
d. Memory is good, but a your canonical query is a table scan which will hit each block of your 1TB tabe exactly once, which means that you have no locality of reference whatsoever, and thus caching doesn't help much. The old blocks will get flushed, so the next time you go to do your query there won't be anything it can use left in the cache.

Ergo, any arguments about memory, or (better yet) SAN controller caches are basically codswallop.

Also, SQL Server uses FILE_FLAG_WRITETHROUGH, which forces I/O operations to complete to disk before the call returns. It also requires SAN vendors to honour these semantics in order to get SQL Server certification for their kit, so controller cache does nothing to help write performance either.

DTest

@ConcernedOfTunbridgeWells thanks, been very helpful!

@ConcernedOfTunbridgeWells good to know

ConcernedOfTunbridgeWells

8:45 PM

Therefore, writing log entries to a shared disk volume is like writing random access data.

DTest

:D

ConcernedOfTunbridgeWells

TL; DR - most arguments about SAN performance (controller caching, lots of disks etc.) are basically incorrect when it comes to data warehouse workloads.

swasheck

@DTest we're standing on the shoulders of giants here

ConcernedOfTunbridgeWells

@swasheck I may be a fat bastard, but 'giant' is kind of overstating it :)

swasheck

@ConcernedOfTunbridgeWells GET IN MAH BELLAY

ConcernedOfTunbridgeWells

8:48 PM

@swasheck repost - google Alexei Sayle 'who's that fat bastard'

Anyway, really must go.

DTest

Thanks again, and yeah @swasheck we really are :)

ConcernedOfTunbridgeWells

9:08 PM

@Dtest - It's all about speciality. I should think most of the people who work as DBAs here will actually know much more about managing a production server than I do. I've had to deal with underperforming DW plaforms on about 5 gigs on the trot now, so DW platform architecture is something of a hot button.

I've also had to do quite a lot of homework on the subject, and there's about 3/4 of fuckall written about it on anything accessible by google. As fair as I can tell it's a bit of a dirty little secret amongst storage vendors. There's been a fair bit of detective work going into my views on the subject.

Time for tubby bye bye.

Transcript for

The Heap™ – Consultancy ©®