February 8, 2008 at 4:39 am
dbDever (2/7/2008)[hr
I'm curious as to why you can't check them as they're inserted. I used to manage an SQL2K warehouse that took nightly loads of production data from a cobol db we had to crack. Due to the size of the db, it had to be indexed for reporting needs. We line item failed packages through active x scripts in dts with a fail cap of 200 (I think) per table. When I left that position that db was at about 750GB and the nightly inserts were costing in the neighborhood of 2 1/2 hours on a very good stand alone server that had separate partitions for temp db/log/data.
On the failed items, we would write the failure to a log and investigate it (which was usually a data type issue because the cobol system did a terrible job of data type checking) and we'd pick up the change the following evening.
...
It might be a flaw in my character, but in a situation where I am capturing some other system's data, I think I am ahead of the game having a complete (but potentially flawed) copy of that systems data, as opposed to having an incomplete (and likely inconsistent) copy because my constraints were "better" than the source system.
Another admission: My opinion of this whole "ETL" fad is that it simply perpetuates and institutionalizes stove pipe systems and batch interfaces.
February 8, 2008 at 11:28 am
Jim Russell (2/8/2008)
Matt Miller (2/7/2008)
Sure the constraints help a system build the diagram, but that is not the same as automating joins. (Nor could it be.)
I guess I am no longer sure what you then mean by automating joins. If you mean - pick 2 tables in the external product in question, and the link is automatically put in for you based on the constraints, then yes - we ARE talking about that, and there ARE products who read that info.
Business Objects, Reporting services, Visio, Crystal reports - all read the schema, and "reverse engineer" the relationships in. And - they don't just guess, since I've seen them pick the right combination even when the fields aren't named the same. And since BO and SSRS both allow you to create reports - they use the relationships they detected.
I tend to have both (diagrams and constraints), so I wasn't quite sure which was enhancing the auto-joining as you call it. Of course - drawing the diagram from the diagram screen results in the constraints being built, and the constraints being hand-coded in can automatically be picked up as part of a diagram, so I'm thinking they're essentially the same thing in the background (just a prettier graphical interface on the diagram side).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 11, 2008 at 5:44 am
Matt Miller (2/8/2008)
Jim Russell (2/8/2008)
Matt Miller (2/7/2008)
Sure the constraints help a system build the diagram, but that is not the same as automating joins. (Nor could it be.)
I guess I am no longer sure what you then mean by automating joins. If you mean - pick 2 tables in the external product in question, and the link is automatically put in for you based on the constraints, then yes - we ARE talking about that, and there ARE products who read that info.
...
And these products claim they can guess if I need an inner or outer join, and what the join relation should be? (On second thought, I guess with FK constraints they can assume an inner "=" join, but that leaves a lot of relationships out.)
February 11, 2008 at 7:43 am
(I'll skip the quoting since we're now up to quoting each other 4 levels deep....:))
No - of course not - it wouldn't know what kind of join you want. But it still is a fairly decent timesaver for it to pick up the fields involved in a typical join on each side, especially if your FK and PK don't share the same name (they can't always share the name).
One of the big reasons for using this kind of functionality is to give some limited capabilities to your end-users to generate their own reports, so it helps them to not grind your server to a halt by creating a join without any join criteria....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply