| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-28 : 09:10:44
|
| Integration services can be good if you want to manipulate data in the datastream.It caters for utf8.It can be quick to write fairly efficient code - but people tend to overcomplicate.Reporting services - handy for deliverinug reports from the server and allowing users to export to their own format.Analysis services - handy for delivering data aggregated at different levels efficiently.Also can be used by report producers without knowing too much about the underlying technology - but this is dangerous as they will then try to design the underlying database for enhancements thinking they know what they are doing.All I belive are used inappropriately just because people have access to them. SSIS is probably the worst for this - as soon as people see etl they think it has to all be done in SSIS and goodbye to timescales and maintainability.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
fparker
Starting Member
27 Posts |
Posted - 2010-12-28 : 10:26:23
|
| resume boosters--f |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-28 : 23:27:43
|
| To stay up to date with the "latest" technology.PBUH |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-01-03 : 15:12:12
|
quote:
Originally posted by X002548
maybe they can come up with a technology that forces developer to understand normal forms BEFORE they "design" a database
I've said it before and i'll say it again. The internet age has produced an entire generation of programmers who have no idea how computers work. Point and click development tools means you can design applications without understanding the first thing about it. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2011-01-03 : 17:09:52
|
quote:
Originally posted by X002548
maybe they can come up with a technology that forces developer to understand normal forms BEFORE they "design" a database
Is that statement in the correct thread? I guess I'm not sure what you are refereeing to. I agree with you, but the three tools you listed have little or nothing to do with relational database design. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 04:34:14
|
"as soon as people see etl they think it has to all be done in SSIS and goodbye to timescales and maintainability."+100 to that ...Although I don't know SSIS, back in the days of SQL2000 and DTS we had clients who's database guys spent all morning, every day, sorting out what had gone wrong on the overnight data transfers.If they had written some robust code with decent logging they would have been able to identify the problems quickly AND program against them for the future ... and probably only have to do that once a month after the first week or two.All IMHO as usual, of course."SQL Server Reporting Services"I know nothing about that either ... but from my viewing platform  I reckon if it is installed to replace Crystal Reports then its a fine upgrade & improvement  |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-04 : 06:43:39
|
| Holy smokes. Have you guys never heard of Business Intelligence? Data warehouses? OLAP cubes? Pie- and line charts?Business Intelligence is probably the primary focus (or at least on of them) of Microsofts strategy for SQL Server in the future. Saying that IS/AS/RS are resume boosters is probably the biggest pile of sh** I have heard in a very good while.- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-04 : 06:51:51
|
| Have you looked at what have been updated between 2008 and 2008 R2? Almost all of it is related to business intelligence:http://msdn.microsoft.com/en-us/library/ff714063.aspx- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-04 : 06:58:38
|
quote:
Originally posted by Kristen
Although I don't know SSIS, back in the days of SQL2000 and DTS we had clients who's database guys spent all morning, every day, sorting out what had gone wrong on the overnight data transfers.If they had written some robust code with decent logging they would have been able to identify the problems quickly AND program against them for the future ... and probably only have to do that once a month after the first week or two.
It's possible to write crap code in any language, tool or framework.I have a bunch of SSIS packages importing data from 3 different systems, one with very dirty data. The majority of data errors it handles automatically, what it can't gets routed into an error table, I get notifications after each run as to how many rows went to error and why, how many rows successfully processed. If the package fails, identifying causes of failure is trivial with all of the logging, most of it using the built-in logging components. Failures result in mail being sent (from the SSIS package, not SQL agent)Yes, you have to design the packages properly to be able to do that, but that's true with any development.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 08:20:08
|
| "It's possible to write crap code in any language, tool or framework"I absolutely agree. But it did seem that DTS encouraged people to just connect Database-A Table-A to Database-B Table-B and fire data between (which is fair enough) but when the data in some column was not suitable to insert into Table-B the methods of resolving it seem tortuous. I still think SQL's errors when that happens leave a lot to be desired (or I don't know how to get the Verbose mode I need). I mean ... surely SQL must have the whole SOURCE row at its disposal when making the INSERT or UPDATE in such circumstances so that it could give us a Soup-to-Nuts description of which row, from the many millions being processed, was at fault? The alternative methods of diagnosis are rather time consuming.Maybe SSIS is streets better out-of-the-box? or is that just that you are using it properly, and naive people will still just "connect Database-A Table-A to Database-B Table-B" and get into exactly the same mess they used to? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 08:23:04
|
quote:
Originally posted by Lumbago
Have you looked at what have been updated between 2008 and 2008 R2? Almost all of it is related to business intelligence:http://msdn.microsoft.com/en-us/library/ff714063.aspx
Well, to answer you earlier question, no I don't much about Business Intelligence, so maybe many/all topics on that page come under the BI umbrella, but the only direct reference to it I could find was "Introducing Business Intelligence Development Studio" - which is presumably not what you meant? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-01-04 : 08:29:54
|
DTS was terrible. Was a major pain to work with.quote:
Maybe SSIS is streets better out-of-the-box? or is that just that you are using it properly, and naive people will still just "connect Database-A Table-A to Database-B Table-B" and get into exactly the same mess they used to?
Yes.Both are true. One thing about SSIS is it is very, very picky about data types, far more so than SQL. If there's a data type mismatch connecting a source to destination, it will tell you when you try to set the connection up. So you'll have to add a type conversion or derived column transform. Now it's possible that the conversion will fail at runtime, but that, like every other data component has an error output, so it's trivial to set up a data flow where the rows that will convert go into the destination table and the ones that won't go elsewhere. Another table, a flat file, an excel speradsheet, etc. 4 tasks and maybe 20 minutes work (including testing). Adding in verbose logging (to file or table) another minute or two.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 08:52:16
|
| Thanks Gail, useful info for me."DTS was terrible. Was a major pain to work with."Well you say that - it did provide me with a chuckle once. Big Multinational Client spent a FORTUNE on a database tool that allowed them to easily move data from Database-A to Database-B. And then DTS came out. Included with the product. "Free" so to speak I never thought their hugely expensive purchase was particularly shrewd but I hadn't factored in the enjoyment of the investment being rendered obsolete inside a few months |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-01-04 : 09:20:10
|
quote:
Well, to answer you earlier question, no I don't much about Business Intelligence, so maybe many/all topics on that page come under the BI umbrella, but the only direct reference to it I could find was "Introducing Business Intelligence Development Studio" - which is presumably not what you meant?
All of SSIS, SSRS and SSAS are created to cater Business Intelligence needs and they are all a part of Business Intelligence Development Studio. So you are right, that was not what I meant :)I'm probably overdoing it a little (for the sake of discussion) by saying this but for a database professional to know next to nothing about business intelligence is almost as bad as saying "I'm actually a really good chef I just don't know much about fish."- LumbagoMy blog (yes, I have a blog now! just not that much content yet) -> www.thefirstsql.com |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2011-01-04 : 10:36:50
|
quote:
Originally posted by GilaMonsterIf there's a data type mismatch connecting a source to destination, it will tell you when you try to set the connection up.
Assuming that NOTHING changes in the futureBcp the data into columns that are all varchar(max) null. In extreme cases bcp it in to a single column table and write code that has substring offsets to check the data.Then audit everythingThen do your "loads"MOOAnd BI...what the hell is BI?A silver bullet?Anything I have seen over lo the many years is that SOMEONE wants SOMETHING that doesn't fit a GUI Wizard development (Well, and this is also BEFORE there was such a thing as well).So what do these GUI Wizard "developers" do?They build a ROCKET SHIP because you can't get there from hereMOO AgainLearn Database 101 and learn how to codeAll these things can be done, and more efficiently, and cleaner, and safer with code.MOO ^ 3Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspxhttp://weblogs.sqlteam.com/brettk/http://brettkaiser.blogspot.com/ |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 10:44:56
|
| In a lot of case I would agree but for instance>> Bcp the data into columns that are all varchar(max) nullbcp no longer supports UTF8 but SSIS does.It's as well to know the facilities available and how to use these products so you know what is the best for whatever you need to do.I would try to steer away from the sort of setup that Gail is suggesting due to the maintenance issues an inflexibility involved in adding control to packages - but I've seen a lot of companies go down this road considering the data manipulation an application rather than a database issue (and usually end up in trouble - especially when they try to get inexperienced saaff involved).==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-01-04 : 11:03:54
|
quote:
Originally posted by Lumbago
I'm probably overdoing it a little (for the sake of discussion) by saying this but for a database professional to know next to nothing about business intelligence is almost as bad as saying "I'm actually a really good chef I just don't know much about fish."
No, I hear you, but its not something I feel I need.I see folk here posting errors from things (either ASP.NET daignostic errors, or errors that look much the same from tools such as SSMS I presume).Looks much like a stack dump.To interpret them I assume it would help to understand the objects involved - which are the things being called on the stack.It seems a whole pile more complex than just getting an error telling you what went wrong. Of course if the application is not looking out for things (well, perhaps "that thing in particular") going wrong then it can't report on it in a way that will help.So I assume you just get the output from a generic TRY ... CATCHAnd I also assumme that many/most? of the people using this stuff have no clue about the Nuts & Bolts of the stuff they are using, and thus they can't interpret the stack dump - they stuff it into Google and hope someone already had that problem, or they ask the question in places like this.I've worked with people like that - normally I'm called in to sort things out. To my mind these tools are enabling the Point-and-CLickers to put applications together. Perhaps to put them together quickly (CERTAINLY to put them together with way more presnetationaly prettiness than was possible, in the same amount of devemoment time, than in times gone by).But the APPs are fragile. They maybe don't scale. And the people who have written them don't know how to sort them out.We have a framework that allows CRUD stuff to be built. We have things that allow a Report to be produced - a Form for the Criteria for the report, and the Results of the report. Its all pretty much Point & Click (except that we expect the developer to write the SQL for the actual query so its sensibly optimised for the job at hand). I suppose it takes an hour or two to write a nice CRUD routine for a new table, and about the same for a Report. Possibly could be done in an hour - certainly could be done in an hour if I spent some time adding some nice "functions for developer" to the tools we use.The results are all consistent within our framework (and migrateable to any future frame upgrades we come up with).The results are immune to any database changes (well, something things like renaming a column will tell you that you have to rename the TAG on these pages, and any others that reference them, but adding a column, or changing its type or size, will be accommodated automatically).When things go wrong we get messages that are easy to interpret. If ever they aren't we improve the core system so that, next time, there will be a helpful error message.So far I don't see the need to put some String and Gum "B.I." into my applications I'm with Brett on this one  , and the folk around me who arrived as GUI-clickers would agree (not that they have a choice, but they ARE still here!) |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-01-04 : 11:33:51
|
Haven't had a good rant in a while. This is meant with the greatest affection:quote:
Bcp the data into columns that are all varchar(max) null. In extreme cases bcp it in to a single column table and write code that has substring offsets to check the data.
I've done my share and more of this kind of thing, but until you demo a bcp/substring solution that can:1. Handle DB2, SQL Server, and Excel data equally2. Integrate with SAP in both directions3. Alert DBAs of bad data or failed loadsI'm gonna look at SSIS for that kind of stuff. I had to deal with all these at my last job, and the only other option was BizTalk. (Please tell me you think BizTalk is better than SSIS, I could use the laugh) I ended up quitting before I had to get too involved with it. I'm not even gonna mention the FoxPro data.quote:
And BI...what the hell is BI? A silver bullet? Anything I have seen over lo the many years is that SOMEONE wants SOMETHING that doesn't fit a GUI Wizard development (Well, and this is also BEFORE there was such a thing as well). So what do these GUI Wizard "developers" do? They build a ROCKET SHIP because you can't get there from here. MOO Again. Learn Database 101 and learn how to code. All these things can be done, and more efficiently, and cleaner, and safer with code.
See my earlier statement about SAP integration. None of the major ERP packages follow good coding or database design practices. You'll have to hack/baling wire/chewing gum something together no matter which one you use, but you definitely don't want to go with bcp/parsing/audit/load to do it. You can't even use the SAP DB schema directly, nor would you want to.I love ya Brett, really I do, but you not only sound like Celko on this one, you're starting to sound like the punchcard programmers he rants about. I'll just say you're lucky you CAN get by with bcp. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-01-04 : 11:51:12
|
| I'd go with ssis for something like that (especially as I've not found another way to get data from db2 to sql server and back again without changing the data).But I'd just use a package for the transfer - might use other packages to do other things but I'd try not to put data manipulation in that same package that does teh transfer - I'd rather do that either before or after. Means that the transfer method (or source) can be changed without affecting the rest of the processing - also if transfer works (usually the long bit) then the rest of the processing can be rerun on error easily without transferring again.Also in the last job we had a multi-million pound db2 server (actually 48 of them) and a 10,000 pound sql server.The code was so bad on db2 that often it couldn't cope but if it was free it could do things blindingly fast.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Next Page
|
Hey, can anyone give me a good reason...