"SQL manipulation
iason
New Altair Community Member
What would you suggest as an optimal workflow to analyze customer data from an SQL database?
There is no analysis server available. Only sql server, excel, rapidminer and any other free tools you might suggest. I might also have access to a Crystal Reports license.
The case is as follows:
There are around 15 tables with appropriate relations, containing useful and useless information.
Naturally, some aggregations and joins will be needed. ie drop the customer id, but not before joining with the table containing the socioeconomic data. Also supplier data are mostly not needed but margins, annual revenue and payment terms per supplier might be. Those are just random examples.
Should I de-normalize the data using Excel and SQL scripting and then import one big flat file to Rapidminer, or is there some way to connect to the db and do all the operations within RM?
Would a reporting tool (such as Crystal) be helpful?
Would you suggest any other software to manipulate the data, join tables, drop unwanted attributes etc before importing to RM? (preferably a free or low cost one since budget is tight)
There is no analysis server available. Only sql server, excel, rapidminer and any other free tools you might suggest. I might also have access to a Crystal Reports license.
The case is as follows:
There are around 15 tables with appropriate relations, containing useful and useless information.
Naturally, some aggregations and joins will be needed. ie drop the customer id, but not before joining with the table containing the socioeconomic data. Also supplier data are mostly not needed but margins, annual revenue and payment terms per supplier might be. Those are just random examples.
Should I de-normalize the data using Excel and SQL scripting and then import one big flat file to Rapidminer, or is there some way to connect to the db and do all the operations within RM?
Would a reporting tool (such as Crystal) be helpful?
Would you suggest any other software to manipulate the data, join tables, drop unwanted attributes etc before importing to RM? (preferably a free or low cost one since budget is tight)
0
Answers
-
I would personally do it all in SQL, preferably using a View. You could consider writing your view (sql query) in Microsoft Query, which is part of Excel first.
RapidMiner can talk directly to MS SQL Server, as well as many others.
Here's a video on connecting RapidMiner to SQL Server using integrated security: http://www.youtube.com/watch?v=Dig0A1oC3Z0
Views: http://odetocode.com/articles/299.aspx
MS Query: http://www.youtube.com/watch?v=I1qvqMiGyhc0