Could anyone recommend an interactive application for mass data analysis?
-
Hi, everybody I need to conduct a large amount of data analysis on database. Could anyone recommend an interactive application for data analysis? The requirements are: 1. Able to cope with the unexpected requirement rapidly. 2. Able to perform further computations on results interactively (base on the mass data). 3. Easy to confront even a large amount of complex computations What would you great expert recommend? Thanks in advance.
Microsoft Excel and Sql Server. Those are the most-used all-purpose tools to do what you want. You can adapt using VBScript if Excel if those requirements change. Most data-mining applications are written to mine for specific stuff.
Bastard Programmer from Hell :suss:
-
Microsoft Excel and Sql Server. Those are the most-used all-purpose tools to do what you want. You can adapt using VBScript if Excel if those requirements change. Most data-mining applications are written to mine for specific stuff.
Bastard Programmer from Hell :suss:
Thank you for reply. SQl lacks stepwise computation ablility, so It is not a ideal solution. What I mean about stepwise is: 1. In excel, I can write number "10" in A1 cell, then formula "=A1*5" in A2 cell. then formula "=A2+2" in A3 cell. A1->A2->A3, It's stepwise. 2. When I change 10 to 9 in A1, then result in A2 and A3 will changed automatic. 3. an example: a. to select out the 10 categories of best sellers b. as a further computation on the basis of result from a., to select out the top 20 products from each category, c. as a further comparison with that of the last year based on the result from a. let's call SQL_A,SQL_B,SQL_C as SQL statements for a,b,c. when I changed SQL_A (for example change 10 categories to 9 categories ), I must change SQL_B and SQL_C. why? because SQL_B and SQL_C is based on SQL_A. that is, SQL_B may like: with SQL_A as A selet....... Excel has another problem, It is too simple to process mass data analysis. for example, to compute the product whose annual sales values are all among the top 100. data structure( sales table's fields): productID, time, value . the sql solution is:
WITH sales1 AS (
SELECT productID, YEAR(time) AS year, SUM(value) AS value1
FROM sales
GROUP BY productID, YEAR(time)
)
SELECT productID
FROM (
SELECT productID
FROM (
SELECT productID,RANK() OVER(PARTITION BY year ORDER BY value1 DESC) rankorder
FROM sales1 ) T1
WHERE rankorder<=100) T2
GROUP BY productID
HAVING COUNT(*)=(SELECT COUNT(DISTINCT year ) FROM sales1)above code is hard to write in Excel. So what I want is some like SQL + Excel, do you have some idea?
-
Thank you for reply. SQl lacks stepwise computation ablility, so It is not a ideal solution. What I mean about stepwise is: 1. In excel, I can write number "10" in A1 cell, then formula "=A1*5" in A2 cell. then formula "=A2+2" in A3 cell. A1->A2->A3, It's stepwise. 2. When I change 10 to 9 in A1, then result in A2 and A3 will changed automatic. 3. an example: a. to select out the 10 categories of best sellers b. as a further computation on the basis of result from a., to select out the top 20 products from each category, c. as a further comparison with that of the last year based on the result from a. let's call SQL_A,SQL_B,SQL_C as SQL statements for a,b,c. when I changed SQL_A (for example change 10 categories to 9 categories ), I must change SQL_B and SQL_C. why? because SQL_B and SQL_C is based on SQL_A. that is, SQL_B may like: with SQL_A as A selet....... Excel has another problem, It is too simple to process mass data analysis. for example, to compute the product whose annual sales values are all among the top 100. data structure( sales table's fields): productID, time, value . the sql solution is:
WITH sales1 AS (
SELECT productID, YEAR(time) AS year, SUM(value) AS value1
FROM sales
GROUP BY productID, YEAR(time)
)
SELECT productID
FROM (
SELECT productID
FROM (
SELECT productID,RANK() OVER(PARTITION BY year ORDER BY value1 DESC) rankorder
FROM sales1 ) T1
WHERE rankorder<=100) T2
GROUP BY productID
HAVING COUNT(*)=(SELECT COUNT(DISTINCT year ) FROM sales1)above code is hard to write in Excel. So what I want is some like SQL + Excel, do you have some idea?
bestbird7788 wrote:
So what I want is some like SQL + Excel, do you have some idea?
Use Sql and Excel.
bestbird7788 wrote:
above code is hard to write in Excel.
It is. I'd suggest writing it in Sql, as a stored procedure.
bestbird7788 wrote:
Excel has another problem, It is too simple to process mass data analysis.
It's one of the most advanced spreadsheets that I know. Alternatively, you could check out DMTL[^].
Bastard Programmer from Hell :suss:
-
Hi, everybody I need to conduct a large amount of data analysis on database. Could anyone recommend an interactive application for data analysis? The requirements are: 1. Able to cope with the unexpected requirement rapidly. 2. Able to perform further computations on results interactively (base on the mass data). 3. Easy to confront even a large amount of complex computations What would you great expert recommend? Thanks in advance.
bestbird7788 wrote:
What would you great expert recommend?
That you adjust your mind set. You seem to think that you have something that is easy yet solves anything. Which isn't possible. The reason programming languages exist is because complex(=many steps) problems can only be solved using complex implementations. So what you can do is one of the following 1. Find an existing application (like excel) and limit your problems to ones that work in that. 2. Use a programming language and solve anything. 3. Use a mix of 1 and 2 based on the needs of the problem. Obviously there are disadvantages to all but that does cover the possibilities.
-
bestbird7788 wrote:
So what I want is some like SQL + Excel, do you have some idea?
Use Sql and Excel.
bestbird7788 wrote:
above code is hard to write in Excel.
It is. I'd suggest writing it in Sql, as a stored procedure.
bestbird7788 wrote:
Excel has another problem, It is too simple to process mass data analysis.
It's one of the most advanced spreadsheets that I know. Alternatively, you could check out DMTL[^].
Bastard Programmer from Hell :suss:
you are so funny :-D I mean SQL+Excel in one tools, not one of them or both of them. I will check DMTL, It seems like what I want. but I'm not sure does DMTL have stepwise ablility like excel? It seems just a code library. I should take a deeper research. BTW, does DMTL support computing data with multiple dataset, just as join statement in SQL. Under my impression, other data mining tools can only work on one dataset. thank you in advance!
-
bestbird7788 wrote:
What would you great expert recommend?
That you adjust your mind set. You seem to think that you have something that is easy yet solves anything. Which isn't possible. The reason programming languages exist is because complex(=many steps) problems can only be solved using complex implementations. So what you can do is one of the following 1. Find an existing application (like excel) and limit your problems to ones that work in that. 2. Use a programming language and solve anything. 3. Use a mix of 1 and 2 based on the needs of the problem. Obviously there are disadvantages to all but that does cover the possibilities.
Thank you for your advice. I know you are right, there is no easy way to solve my problem. I think SQL is more easy than programming languages like VB and C++ on common mass data computation, so I imagined if there is a tool that is more easy than SQL on multiple steps computation of mass data. So I posted this post and wanted someone who knows more than me. DMTL maybe a good choice, I will make a research on it.
-
you are so funny :-D I mean SQL+Excel in one tools, not one of them or both of them. I will check DMTL, It seems like what I want. but I'm not sure does DMTL have stepwise ablility like excel? It seems just a code library. I should take a deeper research. BTW, does DMTL support computing data with multiple dataset, just as join statement in SQL. Under my impression, other data mining tools can only work on one dataset. thank you in advance!
bestbird7788 wrote:
you are so funny :-D
I was not joking.
bestbird7788 wrote:
I mean SQL+Excel in one tools, not one of them or both of them.
You can use Sql Server to prepare the dataset (and yes, it can operate on the previous record), and Excel to visualize that data.
bestbird7788 wrote:
I'm not sure does DMTL
Neither do I. Never needed anything beyond the two tools I already named.
Bastard Programmer from Hell :suss:
-
Hi, everybody I need to conduct a large amount of data analysis on database. Could anyone recommend an interactive application for data analysis? The requirements are: 1. Able to cope with the unexpected requirement rapidly. 2. Able to perform further computations on results interactively (base on the mass data). 3. Easy to confront even a large amount of complex computations What would you great expert recommend? Thanks in advance.
If money is no issue, you can always get Qlikview[^].
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
-
bestbird7788 wrote:
you are so funny :-D
I was not joking.
bestbird7788 wrote:
I mean SQL+Excel in one tools, not one of them or both of them.
You can use Sql Server to prepare the dataset (and yes, it can operate on the previous record), and Excel to visualize that data.
bestbird7788 wrote:
I'm not sure does DMTL
Neither do I. Never needed anything beyond the two tools I already named.
Bastard Programmer from Hell :suss:
hi,thank you for your suggestion. this line: You can use Sql Server to prepare the dataset (and yes, it can operate on the previous record), and Excel to visualize that data. ------------------------------------ look at this scene: 1. I use sql to get some data, filled them in Excel, called it dataset1. 2. In Excel, I made some computation, then I got dataset2. 3. I need join the dataset2 with a table in database to generate dataset3. how to do? creat a new table in database, fill dataset2 to database? and run join statment? then fill dataset3 to Excel? No, It's a big trouble. so I need a tools like SQL+Excel.
-
If money is no issue, you can always get Qlikview[^].
Light moves faster than sound. That is why some people appear bright, until you hear them speak. List of common misconceptions
thank you I will check it. And I have 3 quesiton about Qlikview ,and I will appreciate if you give me some tips. 1.can Qlikview solve the problem above:to compute the product whose annual sales values are all among the top 100. MSSQL data structure( sales table's fields): productID, time, value SQL solution is as below: --------------------------------------------------------- WITH sales1 AS ( SELECT productID, YEAR(time) AS year, SUM(value) AS value1 FROM sales GROUP BY productID, YEAR(time) ) SELECT productID FROM ( SELECT productID FROM ( SELECT productID,RANK() OVER(PARTITION BY year ORDER BY value1 DESC) rankorder FROM sales1 ) T1 WHERE rankorder<=100) T2 GROUP BY productID HAVING COUNT(*)=(SELECT COUNT(DISTINCT year ) FROM sales1) 2.does Qlikview have stepwise ability. What I mean is like: step1. filter data. step2. group data on step1 . step3. filter data again on step2. the most important is: step4. sorting data on the result of step1 as a sub step. It's like what Excel does. 3.dees Qlikview support multiple datasets computation without SQL. what I mean is like: join statements in SQL . thank you for your reply.
-
hi,thank you for your suggestion. this line: You can use Sql Server to prepare the dataset (and yes, it can operate on the previous record), and Excel to visualize that data. ------------------------------------ look at this scene: 1. I use sql to get some data, filled them in Excel, called it dataset1. 2. In Excel, I made some computation, then I got dataset2. 3. I need join the dataset2 with a table in database to generate dataset3. how to do? creat a new table in database, fill dataset2 to database? and run join statment? then fill dataset3 to Excel? No, It's a big trouble. so I need a tools like SQL+Excel.
bestbird7788 wrote:
how to do?
Create a linked server to both Excell files, and do a SELECT with a UNION into your Sql Server.
bestbird7788 wrote:
No, It's a big trouble.
so I need a tools like SQL+Excel.Good luck :)
Bastard Programmer from Hell :suss: