PLEASE READ IF YOU PROGRAM: Help Continue Visual Basic

[Jay Petrulis]

ok i will. just a coupel of dlookups (or subqueries) and a couple of
cartesians there's not a damn thing i cant do

You've got to be kidding. All these posts with specific questions for
you, plus explicit requests to provide details and this is what you
reply? Wow!

This should be quite embarrassing, yet you keep going. You are in a
deep hole and still you keep digging.

 

[aaron.kempf]

haha im not in a hole

im not the one that spends week in and week out building the same damn
spreadsheet

you guys are in a hole

i can do this; i just have a fulltime job and a bunch of contracting
thigns on the side; and 3 kids to deal with at home.. i dont have a ton
of time to look into this; but i will try this weekend (in between
clients)

i haven't ever had any math in the db world that is even challenging; i
mean.. sum this sum that

none of it is slightly challenging

i took 3 semesters of college calc 15 years ago; i know i can do this

all i know is that subqueries and views and user-defined functions are
more powerful than copying and pasting formulas around.

i know it and you guys are crazy for not seeing the light

 

[Harlan Grove]

(e-mail address removed) wrote...

haha im not in a hole

Quite so. You *are* a hole, an metaphysical absence of anything
remotely associated with intellect. Then there are the holes in your
head, more and larger than those normal humans possess.

i can do this; i just have a fulltime job and a bunch of contracting
thigns on the side; and 3 kids to deal with at home.. i dont have a ton
of time to look into this; but i will try this weekend (in between
clients)

Excuses. I already gave you an out: start off by showing code which you
must *already* have to convert a table of, say, 4 records and 4 fields
of double precision floating point numbers into a VBA array perhaps
named SourceArray, then a stub function call like

ResultArray = MatrixInverseStillToBeWritten(SourceArray)

then create a new DBMS table from ResultArray. Someone who claims to be
such an expert at Access, DBMSs in general and VBA must have written
such procedures before. Do you lack access to the code?

You've claimed that you can do any calculations in Access or VBA that I
can do in Excel, and you've claimed you can do them faster. It seems
the only small chink in this edifice of calculation proficiency you've
proclaimed for yourself is writing the code needed to reinvent Excel's
existing functionality. So if you're as good at VBA as you've claimed
you are, but you can't provide a working solution after a whole week,
what chance would anyone who's never programmed in BASIC or any other
procedural or OO language have writing code to get Access to do what
Excel can do out of the box?

You don't see any defects in your arguments to date?!

i haven't ever had any math in the db world that is even challenging; i
mean.. sum this sum that

Yup. That's what databases are good at: summing, counting,
categorizing. They're not really meant for linear algegraic operations.
Neither is VBA. [If only Microsoft would add the MAT statements from
PowerBASIC or TrueBASIC.]

none of it is slightly challenging

i took 3 semesters of college calc 15 years ago; i know i can do this

Oh, certainly. Why anyone can write robust numerical code. They just
won't take as long as you seem to need.

All you need to do is translate existing code into VBA. However, since
you apparently don't know any other languages, and there's a dearth of
VB[A] matrix inversion routines on the web, you're screwed. So much for
VB[A] being the only language you need to know.

Wassa madda, you couldn't even figure out the BASICA code to which I
posted a link in

http://groups.google.com/group/microsoft.public.access/msg/f81e6c06cb50add0

?!

all i know is that subqueries and views and user-defined functions are
more powerful than copying and pasting formulas around.

For counting, summing and categorizing, sure. For inverting matrices or
generating all permutations of sets of distinct tokens, no way. No
single software tool or language is any more universally appropriate
for any & all applications than any single hand-held tool is
universally appropriate for any & all woodworking tasks. You know how
to use a hammer, and you think your hammer is a better saw,
screwdriver, drill, . . .

i know it and you guys are crazy for not seeing the light

The light you see is the train coming at you in the dark tunnel into
which you've blundered.

 

[aaron.kempf]

ALL MATH IS SUMMING, CATEGORIZING DIPSHIT

i mean seriously here.. is your math 'magic math'

are you harry potter?

friggin idiot

I can generate permutations easier than you. it's called a CARTESIAN.

I'm not the one that sees that one tool fits the need for everything.

you're the spreadsheet dork that uses spreadsheets for EVERYTHING
you sit around and build the same damn XLS week in and week out.

I'm not the one that uses a one-size fits all mentality.

YOU ARE HARLAN.

and when stuff doesn't fit your peachy little app you run out and use
PERL and PYTHON?

fucking idiot use VBA / VBS / VB6

 

[aaron.kempf]

and you REALLY think that arrays are that complex?

i mean-- seriously harlan.. what are you looking for 'yes, you are
right.. databases can't handle arrays'

fucking idiot.

spreadsheets can't handle arrays buddy

databases handle 2-dimensional stuff just fine.. and everything you've
ever touched is 2-dimensional by definition..

if you really want the heavy math; you do OLAP
i mean. .it's got a lot more functions that you spreadsheet dorks are
familiar with

 

[Harlan Grove]

(e-mail address removed) wrote...

ALL MATH IS SUMMING, CATEGORIZING DIPSHIT

Not completely inaccurate. Using sign change, subtraction can be
achieved via summing. Multiplication of a multiplicand by an integer
multiplier is just repeated addition, so can also be achieved via
summing. Division can also be reduced to summing, but that gets messy.
Once you have the 4 basic operations, you can use power series to
approximate exponentiation and logarithms, and there you have all the
operations you need.

Heck, read a book on Goedel's proof and you can see that addition can
be reduced to counting.

It's just grossly inefficient.

i mean seriously here.. is your math 'magic math'

....

Nope. But what I know how to do is obviously outside your competence.

I can generate permutations easier than you. it's called a CARTESIAN.

SHOW US. Yes, if you have n distinct tokens, you just need to take the
n-fold cartesian product of that set and weed out all nodes in which
one or more of the tokens is repeated, so reducing the n^n nodes to the
n! permutations.

Again, it's just grossly inefficient.

I'm not the one that sees that one tool fits the need for everything.
No?

you're the spreadsheet dork that uses spreadsheets for EVERYTHING
you sit around and build the same damn XLS week in and week out.

So how do I use Excel to run my Perl code?

So far in all the threads in which we've, er, discussed the relative
merits of Excel and Access I've posted more SQL than you've posted SQL
or VBA. That going to change any time in this millenium?

I'm not the one that uses a one-size fits all mentality.

Gee, I use databases when they make the most sense (to me). I use
spreadsheets when they make the most sense. I use commandline and batch
tools when they make the most sense. I'll admit that I have access to
more commandline tools than most Excel or Access users, so that's O/T
in these newsgroups (other than to perforate your arguments).

You're the one who keeps on ranting ACCESS, ACCESS, ACCESS.

and when stuff doesn't fit your peachy little app you run out and use
PERL and PYTHON?

For things like text processing and transforamtion, yup, I use Perl (or
sed or awk when I'm lazy). I also use batch files when they make the
most sense to me.

fucking idiot use VBA / VBS / VB6

Why would I want to become as hopelessly a single trick pony as you?
Since I already know VBA (and don't have VB6), what possible benefit is
there not to learn any other language?

 

[Harlan Grove]

(e-mail address removed) wrote...

and you REALLY think that arrays are that complex?

Apparently they're sufficiently complicated that you haven't been able
to whip up the requested VBA procedure in a week's time. So, no,
they're not complex for me, but there's no evidence to suggest that
they're not too complex for you.

i mean-- seriously harlan.. what are you looking for 'yes, you are
right.. databases can't handle arrays'

No. I'm pretty sure that a competent programmer could handle arrays in
databases. But I'm not at all convinced you can.

spreadsheets can't handle arrays buddy

Really?! I inverted the array I posted in my challence to you last week
before I posted. Seems like Excel can handle that bit of array
processing. You don't seem to be able to, but just because Aaron is
either to ignorant or too stupid to manage it in Access or VBA doesn't
mean that there aren't thousands of high school kids that could.

databases handle 2-dimensional stuff just fine.. and everything you've
ever touched is 2-dimensional by definition..

Sure. Whatever you say.

if you really want the heavy math; you do OLAP

If you (1) have access to OLAP software, and (2) want to waste the time
needed to set up your data in OLAP. And I didn't happen to see any
mention of a matrix inversion function in Microsoft online
documentation for MDX. Care to provide a link?

i mean. .it's got a lot more functions that you spreadsheet dorks are
familiar with

And you're not familiar with.

Yes, I'm sure it can do a lot. I'm just not convinced you can do
anything more than rant. You haven't exactly demonstrated any other
competence.

 

[aaron.kempf]

I can generate permutations easier than you. it's called a CARTESIAN.


SHOW US. Yes, if you have n distinct tokens, you just need to take the
n-fold cartesian product of that set and weed out all nodes in which
one or more of the tokens is repeated, so reducing the n^n nodes to the

n! permutations.

ok.

1) you have a table called N1 that has the numbers from 1 to 100
2) you have a table called N2 that has the numbers from 1 to 100

if you want to cartesian these two tables; to make every possible
combination; all you need to do is

SELECT N1.N AS N1, N2.N AS N2 FROM N1, N2

this will give you a nice little combination of every possible
combination for N1 and N2.

all you have to do is 'forget to write a join' and you get a cartesian.

which.. doesn't seem all THAT cool.

but pretend I have a years table and a months table.

Select Y.YYYY, M.MMM FROM tblYEAR Y, tblMONTH M

This would give you a nice little 'spreadsheet' of every combination of
years and months.

you could still filter it. you could still say 'give me all the months
for years 2003, 2004, 2005 by adding a simple where clause.

I'll look at the whole determinant thing this weekend; i dont have jack
shit for freetime right now; it's the oldest sons' bday.. i can't
believe he's nine already.. i mean WOW where does the time go?

-aaron

 

[Harlan Grove]

(e-mail address removed) wrote...
....

1) you have a table called N1 that has the numbers from 1 to 100
2) you have a table called N2 that has the numbers from 1 to 100

if you want to cartesian these two tables; to make every possible
combination; all you need to do is

SELECT N1.N AS N1, N2.N AS N2 FROM N1, N2

this will give you a nice little combination of every possible
combination for N1 and N2.

Which in your example gives 10000 combinations. There are only 9900
permutations of 100 distinct numbers, 9900 = 100 * 99 = 100! / 99! /
1!, or 100 choose 2. You could add a WHERE clause specifying that N1
not equal N2 to get all permutations of 2 items drawn from 100 items.

Very good. That's permutations of 2 items. How about complete
permutations, i.e., permutations of all 100 distinct numbers? That
get's messy using your approach. Do you generate the permutations
incrementally, requiring 99 different queries with the one above
converted into an INSERT query to generate a table named NT2, and the
permutations of 3 items given by

SELECT TN2.N1, TN2.N2, N1.N AS N3 FROM TN2, N1
WHERE ((TN2.N1 <> N3) AND (TN2.N2 <> N3))

Writing 99 queries in which all clauses keep growing isn't exactly
simple, but it'd work. It also isn't efficient. It's at least
O(N!*N^2). The generator formula approach I provided several months ago
is O((N+1)!*log(N)). And storagewise, I only need one cell for each
item in each of the permutations. Your approach (rather the one I've
extrapolated from your faulty 2 item beginning), even if intermediate
tables were deleted after each cartesian the final cartesian would use
TN99 to generate TN100, with TN99 having 99! records of 99 fields each
(9.24*10^157) and TN100 having 100! records of 100 fields each
(9.33*10^159).

Now all the computers in all the world at the current time couldn't
even generate all these permutations, so it's a moot point for N = 100.
FWIW, N = 12, TN11 would have a mere 439 million entries and TN12 just
5.75 billion. And it'd only take your approach 69 billion compares to
achieve it. My generator formulas would require only 6.7 billion
compares. OLAP may be good, but it's not good enough to offset that
differential.

Select Y.YYYY, M.MMM FROM tblYEAR Y, tblMONTH M

This would give you a nice little 'spreadsheet' of every combination of
years and months.

....

Kinda like having years listed in A2:A101 and months in B1:M1,
selecting B2:M101, typing the formula =$A2&" "&B$1, and pressing
[Ctrl]+[Enter]. Or just using nested For loops. Yes, this is an example
of what database do well (generating FULL cartesians). Generating
partial combinations, like permutations, takes more work (and fuller
understanding).

 

[aaron.kempf]

i can cartesian up to a billion records without a performance problem
in the world.

with olap i can cartesian as much as you want... billions of physical
records with sub-second response times. without a problem.

Analysis Services 2000 solved every database performance problem the
world has ever known and you guys are still stuck in Excel

AS with SQL 2005 made things about 10 times more powerful; and you guys
aren't on that train.

-Aaron

 

[Harlan Grove]

(e-mail address removed) wrote...

i can cartesian up to a billion records without a performance problem
in the world.

Fine. Cartesian up a matrix inverse.

with olap i can cartesian as much as you want... billions of physical
records with sub-second response times. without a problem.

Only if OLAP just creates dynamic referencing objects, which it could.
But that wouldn't manipulate any data. My n = 12 case would involve 11
intermediate steps to generate all 12! permutations of 12 items, and
it'd require two orders of magnitude greater number of comparisons. No
way OLAP handles over 37 billion comparison operations with sub-second
response time. The software can't magically eliminate the
CPU-boundedness.

But if joins aren't necessary, I suppose 11 separate queries aren't
necessary. Should be syntactically possible to use

SELECT T1.N AS N1, T2.N AS N2, T3.N AS N3, T4.N AS N4, T5.N AS N5, T6.N
AS N6,
T7.N AS N7, T8.N AS N8, T9.N AS N9, T10.N AS N10, T11.N AS N11, T12.N
AS T12
FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9, T T10, T
T11, T T12
WHERE ((N1<>N2) AND (N1<>N3) AND (N1<>N4) AND (N1<>N5) AND (N1<>N6)
AND (N1<>N7) AND (N1<>N8) AND (N1<>N9) AND (N1<>N10) AND (N1<>N11)
AND (N1<>N12) AND (N2<>N3) AND (N2<>N4) AND (N2<>N5) AND (N2<>N6)
AND (N2<>N7) AND (N2<>N8) AND (N2<>N9) AND (N2<>N10) AND (N2<>N11)
AND (N2<>N12) AND (N3<>N4) AND (N3<>N5) AND (N3<>N6) AND (N3<>N7)
AND (N3<>N8) AND (N3<>N9) AND (N3<>N10) AND (N3<>N11) AND (N3<>N12)
AND (N4<>N5) AND (N4<>N6) AND (N4<>N7) AND (N4<>N8) AND (N4<>N9)
AND (N4<>N10) AND (N4<>N11) AND (N4<>N12) AND (N5<>N6) AND (N5<>N7)
AND (N5<>N8) AND (N5<>N9) AND (N5<>N10) AND (N5<>N11) AND (N5<>N12)
AND (N6<>N7) AND (N6<>N8) AND (N6<>N9) AND (N6<>N10) AND (N6<>N11)
AND (N6<>N12) AND (N7<>N8) AND (N7<>N9) AND (N7<>N10) AND (N7<>N11)
AND (N7<>N12) AND (N8<>N9) AND (N8<>N10) AND (N8<>N11) AND (N8<>N12)
AND (N9<>N10) AND (N9<>N11) AND (N9<>N12) AND (N10<>N11) AND (N10<>N12)
AND (N11<>N12))

With T containing a single integer field with 12 records from 1 to 12,
see if this cartesian runs with sub-second response time.

Analysis Services 2000 solved every database performance problem the
world has ever known and you guys are still stuck in Excel

Sure it did.

AS with SQL 2005 made things about 10 times more powerful; and you guys
aren't on that train.

So it can invert matrices?

 

[Harlan Grove]

Harlan Grove wrote...
....

But if joins aren't necessary, I suppose 11 separate queries aren't
necessary. Should be syntactically possible to use

SELECT T1.N AS N1, T2.N AS N2, T3.N AS N3, T4.N AS N4, T5.N AS N5, T6.N
AS N6,
T7.N AS N7, T8.N AS N8, T9.N AS N9, T10.N AS N10, T11.N AS N11, T12.N
AS T12
FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9, T T10, T
T11, T T12
WHERE ((N1<>N2) AND (N1<>N3) AND (N1<>N4) AND (N1<>N5) AND (N1<>N6)
AND (N1<>N7) AND (N1<>N8) AND (N1<>N9) AND (N1<>N10) AND (N1<>N11)
ND (N1<>N12) AND (N2<>N3) AND (N2<>N4) AND (N2<>N5) AND (N2<>N6)

....

Poor syntax in the WHERE clause. Me bad.

I just tried this out in Access. Given T with one field named N
containing long integers from 1 to 12, Access generated the
permutations of 6 items using the query

SELECT T1.N AS N1, T2.N AS N2, T3.N AS N3, T4.N AS N4, T5.N AS N5, T6.N
AS N6
FROM T AS T1, T AS T2, T AS T3, T AS T4, T AS T5, T AS T6
WHERE ((T1.N<>T2.N) AND (T1.N<>T3.N) AND (T1.N<>T4.N) AND (T1.N<>T5.N)
AND (T1.N<>T6.N) AND (T2.N<>T3.N) AND (T2.N<>T4.N) AND (T2.N<>T5.N)
AND (T2.N<>T6.N) AND (T3.N<>T4.N) AND (T3.N<>T5.N) AND (T3.N<>T6.N)
AND (T4.N<>T5.N) AND (T4.N<>T6.N) AND (T5.N<>T6.N))
ORDER BY T1.N, T2.N, T3.N, T4.N, T5.N, T6.N;

*BUT* Access couldn't handle a similar query to generate the
permutations of 8 items using the query

SELECT T1.N AS N1, T2.N AS N2, T3.N AS N3, T4.N AS N4, T5.N AS N5, T6.N
AS N6,
T7.N AS N7, T8.N AS N8
FROM T AS T1, T AS T2, T AS T3, T AS T4, T AS T5, T AS T6, T T7, T T8
WHERE ((T1.N<>T2.N) AND (T1.N<>T3.N) AND (T1.N<>T4.N) AND (T1.N<>T5.N)
AND (T1.N<>T6.N) AND (T1.N<>T7.N) AND (T1.N<>T8.N) AND (T2.N<>T3.N)
AND (T2.N<>T4.N) AND (T2.N<>T5.N) AND (T2.N<>T6.N) AND (T2.N<>T7.N)
AND (T2.N<>T8.N) AND (T3.N<>T4.N) AND (T3.N<>T5.N) AND (T3.N<>T6.N)
AND (T3.N<>T7.N) AND (T3.N<>T8.N) AND (T4.N<>T5.N) AND (T4.N<>T6.N)
AND (T4.N<>T7.N) AND (T4.N<>T8.N) AND (T5.N<>T6.N) AND (T5.N<>T7.N)
AND (T5.N<>T8.N) AND (T6.N<>T7.N) AND (T6.N<>T8.N) AND (T7.N<>T8.N))
ORDER BY T1.N, T2.N, T3.N, T4.N, T5.N, T6.N, T7.N, T8.N;

It aborted with the message 'Not enough space on temporary disk.'

FWIW, Excel wouldn't have an easy time with that either since it'd
require 305 worksheets to hold all 20 million-odd permutations.

 

[dbahooker]

SELECT T1.N AS N1, T2.N AS N2, T3.N AS N3, T4.N AS N4, T5.N AS N5, T6.N

AS N6,
T7.N AS N7, T8.N AS N8, T9.N AS N9, T10.N AS N10, T11.N AS N11, T12.N
AS T12
FROM T T1, T T2, T T3, T T4, T T5, T T6, T T7, T T8, T T9, T T10, T
T11, T T12
WHERE N1 NOT IN (T2.N, T3.N, T4.N, T5.N, T6.N); etc

that should be a little bit easier

and re: tempspace.. I dont ever use MDB for anything in the real world;
i use MSDE-- the freeware version of SQL Sever. That way; i dont have
to rewrite shit ever-- i just throw it on a real server when I need to
and I have a single language for DB stuff and a single language for
frontend stuff. MSDE and SQL Server to say the least; these dont have
the same problems as MDB.

and the best part? MS Access has the best sproc design tools in the
world.. i mean-- anywhere; and product.. and db product; and
professional level product.. ADP against MSDE is the most rich querying
environment anywhere. I mean-- it's all drag and drop.

re:
No
way OLAP handles over 37 billion comparison operations with sub-second
response time. The software can't magically eliminate the
CPU-boundedness.

YES OH YES IT DOES KIDS
im sorry your stupid IT people chose cognos or any of those other piece
of shit apps
www.olapreport.com microsoft has by far the largest $$ of any vendor,
including oracle, ibm... MS _RULES_ the olap market. and olap is the
most important market in the world.

that is why i dont give a flying shit about IBM and Oracle they have
already become irrelevent.. I mean.. MS can do the same thing they can;
bigger and better and faster and cheaper

 

[dbahooker]

ps - just for the record

http://www.sql-server-performance.com/wp_msas_9.asp

if olap -- analysis services has 12 dimensions with one level each; and
each level has 100 members-- it doesn't actually store ANYTHING
resembling 20m records.

OLAP is a generation past relational; it is multidimensional and it can
eat your 2-dimensional matrices for lunch

 

[Harlan Grove]

(e-mail address removed) wrote...

ps - just for the record

http://www.sql-server-performance.com/wp_msas_9.asp

if olap -- analysis services has 12 dimensions with one level each; and
each level has 100 members-- it doesn't actually store ANYTHING
resembling 20m records.

?

Difficult to see how the linked article is relevant to the point you're
making.

I'll accept that OLAP relations don't result in storing anything other
than a referencing object that can be used to calculate things similar
to ad hoc indexing.

Now the problem with generating permutations is that the goal *is*
generating the permutations, which means producing records *or*
performing calculations based on what the permutations migh be. So
you'd either need to produce a file containing all 12! (480 million)
permutations (each a 12 field record) or generate them on the fly each
time you need to perform calculations based on them. Counting the
number of permutations in which at least 6 adjacent fields are in
serial ascending order (e.g., 12 1 3 4 5 6 7 8 10 2 9 11) would take
much longer than a fraction of a second unless all calculations such as
these were precalculated and cached, which would eat terabytes of
storage.

OLAP is a generation past relational; it is multidimensional and it can
eat your 2-dimensional matrices for lunch

It just can't invert them?

It's a referencing scheme. It may be an outstanding referencing scheme.
It may even include some useful functions in MDX. But it's not a
replacement for all other extant calculation software.

 

[Harlan Grove]

that should be a little bit easier

OK, but Access 2002 doesn't like N1 in the WHERE clause. It pops up a
dialog asking for the value of the N1 parameter.

and re: tempspace.. I dont ever use MDB for anything in the real world;
i use MSDE-- the freeware version of SQL Sever. That way; i dont have
to rewrite shit ever-- i just throw it on a real server when I need to
and I have a single language for DB stuff and a single language for
frontend stuff. MSDE and SQL Server to say the least; these dont have
the same problems as MDB.

....

Yes, but as I've repeatedly told you, most business users (including
me) don't have MSDE installed on their PCs, and we're unlikely to get
our IT departments to install it unless our primary job function is
database developer. MSDE may even be a free download, but my company's
IT policy makes installing nonstandard software a no-no. So I have only
Access to use, and only with MDB.

This is the point in our back and forth where it degenerates into
something like me asking 'how can I translate this Syriac text into
English?' and you responding 'learn Phonecian because the best
Phonecian to Syriac dictionary is free!'

re:
No
way OLAP handles over 37 billion comparison operations with sub-second
response time. The software can't magically eliminate the
CPU-boundedness.

YES OH YES IT DOES KIDS
im sorry your stupid IT people chose cognos or any of those other piece
of shit apps

....

Oh not it doesn't. It may create a referencing object sub-second, but
it can't perform 37 billion floating point operations sub-second if the
CPU's FPU can only crank out 1 billion FLOPS under ideal circumstances.
The software can't make hardware perform faster than it's physical
upper limit. Now it could cache calculations, but that means they'd
need to be performed when the OLAP cube is created or during
maintenance, but somewhere along the way the hardware speed must be
reconned with.

 

[aaron.kempf]

it's kinda in between.. doesn't really store them; it doesn't really
generate them on the fly.
kinda does both. and it's lighting fast.. i mean.. WOW

well technically; you can specify which percentage of aggregates to
store; and then you can store them in multiple formats; relationally or
in memory..

it's kinda crazy to think about relational -> olap -> relational
but it really really is a beautiful solution for some things. i mean..
the bottom line is that it's about 10 times more powerful than your
traditional, girly-man pivotTables.

it's kinda like what you do with offline cubes-- from excel generate
some client side cubes-- but it's a lot more hierarchial than that.. i
mean. .it gives Excel REAL drilldown.

i just think that it's insanity to claim that Excel is more powerful
than SQL. I mean-- TSQL is just plain faster and more powerful than
VBA. Any way you look at it.

 

[Harlan Grove]

(e-mail address removed) wrote...
....

i just think that it's insanity to claim that Excel is more powerful
than SQL. I mean-- TSQL is just plain faster and more powerful than
VBA. Any way you look at it.

But it's all referencing. It's not calculations unless they're cached,
in which case they took a lot of time to generate when the cube was
created, and it'd take a significant amount of time to regenerate if a
substantial portion of the cube changed.

Spreadsheets have a poor referencing scheme, but they handle the
calculations better aside from the standard aggregates cached in OLAP
cubes: COUNT, SUM, AVERAGE, MIN, MAX, etc. Nontrivial matrix
calculations, such as inverses and determinants (or
eigenvectors/eigenvalues) are ill suited to relational or
multidimensional referencing. There really are some operations in which
all you need for referencing is simple array indexing, and all the real
work is done in the calculations.

That's one key difference between OLAP and spreadsheets. OLAP is geared
towards data sets that retain most of their data but add to it over
time. Spreadsheets are geared towards using completely different data
sets (entries) from one calculation cycle to the next. Of course OLAP
would be quicker with old data, but generating a spreadsheet would
likely be a lot quicker than generating a new cube. They deal with very
different data domains, so it shouldn't be surprising that they differ
in form. You deal with predominantly static data. I don't. We deal with
different data, so we prefer different tools.

 

[Jay Petrulis]

it's kinda in between.. doesn't really store them; it doesn't really
generate them on the fly.
kinda does both. and it's lighting fast.. i mean.. WOW

well technically; you can specify which percentage of aggregates to
store; and then you can store them in multiple formats; relationally or
in memory..

it's kinda crazy to think about relational -> olap -> relational
but it really really is a beautiful solution for some things. i mean..
the bottom line is that it's about 10 times more powerful than your
traditional, girly-man pivotTables.

it's kinda like what you do with offline cubes-- from excel generate
some client side cubes-- but it's a lot more hierarchial than that.. i
mean. .it gives Excel REAL drilldown.

i just think that it's insanity to claim that Excel is more powerful
than SQL. I mean-- TSQL is just plain faster and more powerful than
VBA. Any way you look at it.

i mean...WOW

it's kinda like when someone asks you to do something and you don't
have a clue how to do it and you evade the question in the
non-responses.

i think its just insanity that people use Excel for tasks that it is
already designed to do. i mean ... seriously if you were going to type
a letter you would use Access. No girly man Word. Eck, gag me with a
spoon. You write the same letter every week. A database can do it a
lot faster. I can spam a million letters a day. Use a database kid.

go play in the sandbox. get real grow up and get away from that pen
and paper crap.

Aaron, any way you try to spin it, you are a clown. Stop going off on
tangents. Harlan's responses provide details and specifics in YOUR
CLAIMED AREA OF EXPERTISE while yours are just badly written idiotic
rants. You cannot respond intelligently at all.

 

[dbahooker]

hahaha good stuff man

yeah; i didn't have time to look into it; but im not scared of your
'super-duper complex matrices'

i mean.. seriously here

spreadsheets are for babies