Re: WHY

From: Nick Hodge (nick_hodgeTAKETHISOUT_at_zen.co.uk.ANDTHIS)
Date: 12/22/04 

Date: Wed, 22 Dec 2004 19:07:59 -0000

Aaron

>Importing data out of a spread*** is BROKEN, DOESN'T WORK AND IT NEVER
>HAS.

Surely you import in and export out?

Anyhow, how is it broken. The number of times I link Excel tables into
Access each week and run append, select, delete queries is immeasurable.
After that I run the data into Excel using ODBC to form an intuitive pivot
table, chart, etc.

When will you realise that interoperability and 'right tool for the job' is
key, not a blind hatred for one or the other? 

-- 
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS
<aaron_kempf@hotmail.com> wrote in message 
news:%23dvMxbF6EHA.2608@TK2MSFTNGP10.phx.gbl...
> the only thing that is 'built into a spread***' is the ability to flush
> hours down the toilet.
>
> If you're too narrow-minded to consider that doign _SOME_ of the work on 
> the
> database side makes sense.. then uh.. go ahead and write spreadsheets for
> the rest of your life.
>
> The thing that will please you nasty beancounters the most-- if you want 
> to
> push dfata into a spread***; access supports this functionality.   Once
> you get data into a database, it is EASY to pull it into Excel.
>
> Access is a 2 way street; SQL is a 2 way street.
>
> Importing data out of a spread*** is BROKEN, DOESN'T WORK AND IT NEVER
> HAS.  It is impossibly to have adequate field validation in Excel... by
> definition; it is impossible.  In Access, there are input masks and all
> sorts of tools that make it easy for a 3rd grader to have accurate,
> consistent data.
>
> You spend all of your time typign numbers from Excel; and you have this
> house of cards-- formulas on top of formulas.. there ISN"T a decent way to
> check for broken formulas.. (unless you like priting out the formulas and
> LOOKING for them GAG)
>
> Microsoft just doesn't test it or something.
>
> And about perl, shove it up your a$$; VBA is the _ONLY_ language in the
> world.  If you want to index data; store it in SQL-- if it is too slow; 
> then
> give it an OLAP interface.  It is simple simple simple stuff if you knew
> anything about databases.
>
> Do you really think that this:
> "=AND(ABS(TRANSPOSE(ConvRates)-1/ConvRates)<1E-12)"  is intuitive?
>
> I think that the SQL Statement is perfectly intuitive.. but after all of
> your bitching you guys still dont understand that I could duplicate the
> storage of your matrix in a db and it would be this:
>
> select USD from currencyconvertor where source 'GBP'
>
> I can use the exact same format you use; and it is twice as easy to get to
> this data.
>
> get out of your cubicle and get into the real world kid
>
> All I know is that companies-- AS A WHOLE-- need to hire about 2x as many
> database people as they currently do.. and then need to fire about half of
> their beancounters.
>
> Being able to automate beancounters is what the 'pc revolution' is all
> about.
>
> You guys are targets; in an ideal world-- we would have automated your job
> already.. but the sad thing is that there is this DISEASE called EXCEL and
> people think that it is ACCEPTABLE to print the same report by hand every
> month.
>
> Grow up and welcome to the future
>
>
>
> "Harlan Grove" <hrlngrv@aol.com> wrote in message
> news:Oq$$MBz5EHA.2512@TK2MSFTNGP09.phx.gbl...
>> <aaron_kempf@hotmail.com> wrote...
>> >RE:
>> >
>> >If you mean reports, then I don't disagree that databases could be more
>> >efficient in some instances. However, there's a question of how many
>> >calculations (not the simple database sort of accumulations - SUM,
>> >COUNT, AVERAGE, etc. - but rather more complex ones like the a_th
>> >percentile of a statistical distribution with parameters based on
>> >sample data) are needed to produce the results of interest.
>> >
>> >
>> >This type of person should be locked up in jail for being egocentric.
>> >
>> >SQL Server has the ability to make user defined functions.
>>
>> No, really?!
>>
>> Most useful software possesses the attributes of modularization,
>> programmable extension and the ad hoc ability to run other software.
>>
>> That said, while one could write a udf for SQL RDBMSs to calculate, say,
>> estimators and standard errors for linear models, it'd unpleasant to do
> so.
>> That sort of thing is built into modern spreadsheets but not databases.
>>
>> Now one could use a database as the storage backend for a stats package,
> but
>> it'd be the stats package that does the real work, and only after it 
>> loads
>> the data into its own data structures. I agree that databases are very
> good
>> storage and retrieval subsystems, but I remain unconvinced that the forms
>> and reporting tools provided by most databases are vastly and uniformly
>> superior to the alternative provided by spreadsheets (when properly
>> designed). But calculations using udfs leads to UI/front-end vs
>> processing/back-end interface issues.
>>
>> Consider something as simple as the currency conversion rate table
> discussed
>> before. If there were no conversion fees reflected in the conversion
> rates,
>> then one would expect, e.g., the conversion rate from US dollars to UK
>> pounds to be the reciprocal of UK pounds to US dollars. So if the foreigh
>> exchange markets were perfectly efficient, the matrix of conversion rates
>> should be characterized by the entries in its lower triangular matrix
> being
>> the reciprocals of the entries in its upper triangular matrix. In Excel
>> terms,
>>
>> =AND(ABS(TRANSPOSE(ConvRates)-1/ConvRates)<1E-12)
>>
>> where ConvRates is the N-by-N matrix of conversion rates for N 
>> currencies,
>> which are unity along the main diagonal.
>>
>> This can be done with an inner join of the table with itself swapping the
>> country fields in the second reference into the table. Something like
>>
>> SELECT (Count(*) > 0)
>> FROM (SELECT [CT1].[ConvRate] = 1/[CT2].[ConvRate]
>>   FROM CurrTbl As CT1 INNER JOIN CurrTbl AS CT2
>>   ON (CT1.ToCountry = CT2.FromCountry) AND (CT1.FromCountry =
> CT2.ToCountry)
>>   WHERE ((Abs([CT1].[ConvRate] - 1/[CT2].[ConvRate]) < 1E-12)));
>>
>> Does the latter really look clearer to you?
>>
>> >Anythign that you can do in Excel; i can do in either MDX or SQL.  And
> I'll
>> >do it once; and make a couple of DTS packages; and I am done-- I don't
> need
>> >to come back and type stuff into a spread*** in order to make a new
>> >report..
>>
>> Further to the example above, I could parametrize the table name in 
>> Excel,
>> so the formula would become
>>
>> =AND(ABS(TRANSPOSE(INDIRECT(CTN))-1/INDIRECT(CTN))<1E-12)
>>
>> How do you do that in SQL without resorting to some metalanguage or
> creating
>> temporary tables using common, reserved names?
>>
>> >I tell you this-- if spreadsheets were all-powerful. you know those
>> >supercomputers that they have?? They would be runnnig Excel.
>>
>> Who wastes money running databases on supercomputers or networked
> clusters?
>> No one in their right mind. They run hand-crafted FORTRAN or C code if
> they
>> want to get anywhere past single-digit gigaflops. Do you believe such
>> programs rely on realtime database feeds? No way. They use cached,
>> multiplexed data pipelines. And they don't waste runtime writing results
> to
>> databases but to many synchronized output streams. Possibly databases
>> populate the inputs and store the outputs eventually, but this is just
>> another example of databases being the backend storage subsystem. A 
>> useful
>> supporting role to be sure, but hardly center stage.
>>
>> >If Excel was really the best solution-- people would have Clusters of
>> >spreadsheets.  Excel 2005 Cluster Edition...
>>
>> No more than they'd have clusters of DBMSs. Neither are the right tool 
>> for
>> the task. And no one uses supercomputers of clusters for generating
> reports.
>>
>> Time for your next straw man.
>>
>> >explicit and reproducible.
>> >>They're much less so in other systems, including DBMSs.
>> >
>> >They're reproducible because you CUT AND PASTE THE FORMULAS INTO 1,000
>> >DIFFERENT CELLS.
>>
>> Yup. No one said audit trails are storage efficient or free from
> unintended
>> screw-ups. Rather, it's easier to locate such screw-ups.
>>
>> >I CAN DO THE SAME THING ON THE DATABASE SIDE-- 
>>
>> No you can't. The source tables, the definitions of the views or the
> queries
>> could be modified. If a user calls the same named stored procedure in
> March
>> and April, that user has no guarantee other than the word of their
> database
>> admin that nothing has changed other than the addition of data from the
>> month of April.
>>
>> Historically this has been addressed in mainframe reporting by including
>> checksums, tape volume IDs, record counts and other stats derived from
>> inputs along with full JCL and key procedure listings in printouts. To
>> repeat, no one said audit trails were storage efficient.
>>
>> >BUT I CHOOSE TO KEEP MY BUSINESS LOGIC IN ONE PLACE-- SO THAT IF I NEED
> TO
>> >CHANGE A REPORT: I CHANGE IT IN ONE PLACE.
>> ...
>>
>> It's the need to prove there have been no changes in that business logic
>> between report runs that's the nasty problem. It's actually pretty easy 
>> to
>> show the formulas in two different workbooks are identical or
> substantially
>> similar. As long as printouts (which could be text files) of business
> logic
>> are included in the master copies of reports, there's a true audit trail.
>>
>> As for change in one place, that is a definite advantage of databases.
>> However, using standard templates as the basis for spread*** reports
> also
>> provides centralized change management.
>>
>> In spreadsheets the key is to separate storage of user inputs from
>> calculations. [IMO, this is much easier in Lotus 123 and Quattro Pro than
>> Excel.] Then the only thing that would need to be stored would be the 
>> user
>> inputs. All the 'business logic' (dare I call it formulas and macros?)
> would
>> remain in a separate, centrally stored and maintained workbook.
>>
>> >IT IS PHYSCIALLY AND PRACTICALLY IMPOSSIBLE TO GET DATA OUT OF
>> >SPREADSHEETS.
>>
>> If you don't know how to do something, then it'll seem impossible.
>>
>> Getting information out of spreadsheets does require knowing the
> workbook's
>> filename and the work*** ranges in it where that information is 
>> located.
>> If no proper documentation of filenames or work***/range addresses
> exists,
>> it can take considerable effort to locate the information. But if we're
>> talking about standard reports, it's easy as long as a consistent layout
> has
>> been maintained (or better still, common defined names have been used to
>> identify the information of interest).
>>
>> >I SHOUD KNOW.  I HAVE WRITTEN DOZENS OF APPLICATIONS THAT CATALOG
>> >SPREADSHEETS-- BECAUSE WINDOWS INDEXING SERVICE SUX-- I BUILD A DATABASE
>> >WHERE I DO A FULLTEXT INDEX ON HUNDREDS, IF NOT THOUSANDS OF
> SPREADSHEETS.
>>
>> Windows indexing is an OS feature, no? Microsft still to blame, but not
> the
>> Excel or Office developers.
>>
>> You'd be better off using Perl to create such indices. Of course that
>> assumes you have the capability of learning anything other than SQL. An
>> associative array each entry of which is in turn a reference to an
>> associative array is a very powerful data structure for generalized text
>> search and retrieval.
>>
>> >AS IT IS; IT IS A TOTAL WASTE OF TIME THAT 80% OF YOU RECREATE THE SAME
>> >REPORT BY HAND EVERY MONTH.
>>
>> Maybe you and/or people in the company for which you work do so, but that
>> doesn't mean everyone does.
>>
>> >MDX IS GOING TO EAT YOU ALIVE, KIDS
>>
>> I'm shaking in my shoes, soiling my pants, won't sleep ever again.
>>
>>
>
>