I'm mantaining and mining a little db of about 700Mb, which contains the sales results of the region we administer, 89 branches with about 300 salesmans. We here work mainly in Excel 2003 for reporting and analysis of the data, me "developing" specialized reports, around a collection of vba functions that collect specific data from the db gathered in a xla.
Well, the story is: in the company there is now a complex scheme for ranking the branches nationwide revolving around 13 concepts, that I'm monitoring almost daily (and storing the results in my db). I already succeded in replicating the scheme, with vba programming and of course MySQL, but my model has some flaws that make it a little unstable, mainly because the logic to create the "complex results" are in every client computer (a copy of my xla), so the first one that calls for data in the day, is the one that calculates the outcome for everybody and saves the result in a special table.
I'm evaluating if moving the algorithm for it to some stored proc's would make more reliable the model, and if it would make it faster also.
If somebody wants, I'll post here examples of the querys and code (some of them are a little large) and of course would try to clarify every aspect of it. (I didn't want to do a too large post no one would care to read...)
I don't know that posting a large amount of information would be bad. If anything it should help us answer your query better. By all means, post as much as you think is applicable and will help us understand your situation better.
I was just trying not to scare people out with a big initial mess... here it goes.
Each of the 13 concepts in the productivity scheme is more or less measured this way:
1. The sales for that product, for each branch are recorded in a monthly basis, for the full branch. One month, one number.
2. We have 5 kinds of salesman, the branch manager, the premier one, A's and B's and a small business executive we call pyme (we are a bank). Each product involves a different subset of the sales personnel.
3. The personnel involved with the product we call it "hands". The branches doesn't have the same hands (number of salesmans): some have 1 Premier, some more and others doesn't, for instance.
4. So to keep the competition "balanced", the sales for each product are divided with the hands of each branch, the result of that we call "Indicator".
5. Every product has some weight in the scheme, as Credit cards doesn't weight the same as a House loan. So after that, to sum all the products in a "coherent" weighted score, they Indicator is "transformed" to a weighted score. That is accomplished assigning a mobile scale, using 2 standard deviations, giving 0 score to -2 stddev, 5 to average, and 10 to +2stddev.
To calculate that, I keep a table with the results of the branches of every product called "Promedios" (averages in spanish); the hands of the branches in a a table "TripulacionTOP" (crew), and the rules for what goes where in a table called "ParametrosTOP" (parameters). I follow this steps to do the calculations:
Firts, I build a query that outputs the indicator for a product for the whole set of branches, in descending order and save it to a table "PosicionesTOP". Next I take that table and obtain the sttddev, and the max and min values that give score, to have the scale handy for the product and be able to project future scores or something.
All of that is made with VBA code, in a .XLA library of functions I made, along with code to access my db and extract data directly in Excel reports. Here is a sample of the query I build to generate the first step:
SELECT cve_reg AS xcve,
sum (promedio) / (SELECT sum ((trip - pyme - premier * .4))
FROM tripulaciontop t
WHERE cve = xcve
AND nivel = 1
AND t.corte = '2007-03-31') AS indicador
sucursales USING (cve_suc)
zonas USING (cve_zon)
WHERE corte IN ('2007-01-31', '2007-02-28', '2007-03-31', '2007-04-30', '2007-05-07')
AND producto = 'Co'
AND segmento = 'Pa'
GROUP BY xcve
ORDER BY indicador DESC;
I'll would send or post the VBA code that constructs the query if anybody is interested. The real matter here for me is that I've never programmed a stored proc, and I really don't know what should be in the db and what is to keep in the client side, and if a view would be somewhere in the solution. Thanks.