Mysql with Rapidanalytics

TrustUnCamer

can i have a tutorial link, showing how to use Mysql with Rapidanalytics.

Marco_Boeck

Hi,

I am not quite sure what the question is - do you want to run your RapidAnalytics server on a Mysql db? Or do you want to use the mysql db in RapidMiner processes?

Regards,
Marco

TrustUnCamer

I installed Rapidanalytics, MySQL Database and RapidMiner.
I made the connection: between the Rapidanalytics Server and
Mysql database,Rapidanalytics and RapidMiner like in
"RapidAnalytics 1.2 User and Installation Manual".
Everything works great, thank you for your good work.
My problem is the management of the three system:
How to insert data from a file in the database?
How to read and manipulate them in Rapidanalytics?
Do you have a tutorial explaining this?

I thank you in advance
Cauchy

wessel

This very much depends on what you want to achieve?

Why do you need to store your data in the database?
Why not use repositories as your data storage?

In this case you use SQL connector in a rapid miner process.
Load your data.
Do operations on your data.
And use the store operator to store results in your repository.
Results can be models, trees, graphs, reports, and yes, results can also be data.

You can, with some effort, get data results back into an SQL database.
But you should think twice before doing this.
First make sure a repository storage is not better suited for what you actually want to achieve.

Best regards,

Wessel

wessel

You should ask a member of the Rapid Miner team what is the recommended way to get data from inside Rapid Miner to an SQL database.
I use the Execute SQL operator to create a table.
Then I call an CSV To SQL stored procedure in my database installation.

Best regards,

Wessel

TrustUnCamer

Here the goal I want to achieve.
I have 6 Excel file (Jan, Feb, Mar, Apr, May, Jun) each having colone (Mon, KID, PID, okNetto, Einb, Geb). I would like to have a technics or methode, how I can analyze eg "okNetto" or "KID´s with okNetto" of different Excel file together.

thanks
Cauchy

MacPhotoBiker

From the case you describe, it sounds this should be rather done in RapidMiner than in RapidAnalytics:

To read an Excel file, use the "Read Excel" operator.

For several files, use several operators.

In order to get them into one single table, you can use several SQL operators, check out "Data Transformation -> Set Operations".

From what I read in your comment, most likely you want to use the "Append" operator which allows you to append 2 or more excel files, creating one single table (I assume that Excel tables need to have the same columns, which according to your post they do.)

Then, to write your data into a database, use the "Write Database" operator.

The process would look something like this: (Just copy the code, create a new process in RapidMiner, switch to the "XML" tab, post the code there, then click on the "Process" tab.)

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<process version="5.3.013">
  <context>
    <input/>
    <output/>
    <macros/>
  </context>
  <operator activated="true" class="process" compatibility="5.3.013" expanded="true" name="Process">
    <process expanded="true">
      <operator activated="true" class="read_excel" compatibility="5.3.013" expanded="true" height="60" name="Read Excel" width="90" x="112" y="30">
        <list key="annotations"/>
        <list key="data_set_meta_data_information"/>
      </operator>
      <operator activated="true" class="read_excel" compatibility="5.3.013" expanded="true" height="60" name="Read Excel (2)" width="90" x="112" y="120">
        <list key="annotations"/>
        <list key="data_set_meta_data_information"/>
      </operator>
      <operator activated="true" class="read_excel" compatibility="5.3.013" expanded="true" height="60" name="Read Excel (3)" width="90" x="112" y="210">
        <list key="annotations"/>
        <list key="data_set_meta_data_information"/>
      </operator>
      <operator activated="true" class="read_excel" compatibility="5.3.013" expanded="true" height="60" name="Read Excel (4)" width="90" x="112" y="480">
        <list key="annotations"/>
        <list key="data_set_meta_data_information"/>
      </operator>
      <operator activated="true" class="read_excel" compatibility="5.3.013" expanded="true" height="60" name="Read Excel (5)" width="90" x="112" y="300">
        <list key="annotations"/>
        <list key="data_set_meta_data_information"/>
      </operator>
      <operator activated="true" class="read_excel" compatibility="5.3.013" expanded="true" height="60" name="Read Excel (6)" width="90" x="112" y="390">
        <list key="annotations"/>
        <list key="data_set_meta_data_information"/>
      </operator>
      <operator activated="true" class="append" compatibility="5.3.013" expanded="true" height="166" name="Append" width="90" x="514" y="210"/>
      <operator activated="true" class="write_database" compatibility="5.3.013" expanded="true" height="60" name="Write Database" width="90" x="715" y="210"/>
      <connect from_op="Read Excel" from_port="output" to_op="Append" to_port="example set 1"/>
      <connect from_op="Read Excel (2)" from_port="output" to_op="Append" to_port="example set 2"/>
      <connect from_op="Read Excel (3)" from_port="output" to_op="Append" to_port="example set 3"/>
      <connect from_op="Read Excel (4)" from_port="output" to_op="Append" to_port="example set 6"/>
      <connect from_op="Read Excel (5)" from_port="output" to_op="Append" to_port="example set 4"/>
      <connect from_op="Read Excel (6)" from_port="output" to_op="Append" to_port="example set 5"/>
      <connect from_op="Append" from_port="merged set" to_op="Write Database" to_port="input"/>
      <connect from_op="Write Database" from_port="through" to_port="result 1"/>
      <portSpacing port="source_input 1" spacing="0"/>
      <portSpacing port="sink_result 1" spacing="0"/>
      <portSpacing port="sink_result 2" spacing="0"/>
    </process>
  </operator>
</process>

TrustUnCamer

Thanks for the great help, everything worked great.
I also wanted to test RapidAnalytics to dynamically create report
but apparently I need an Enterprise version.

thanks
Cauchy

MacPhotoBiker

Glad I could help.

I'm not sure what you mean by "dynamically". Usually (afaik) the term "dynamically" refers to the fact that a report (like tables, charts etc.) is upated in the moment you open the report.

If so, the CE (Community Edition) of RM and RA perfectly allow this. I created a management dashboard using mostly flash charts and some tables as provided by RA. The ETL process I designed in RM, then in RA I exported these processes to web services, and via the provided iframe link I embed the charts into an html page. Whenever the page is accessed, the charts are created based on the real data from a MySQL database. If this were not the case, then RA would be pretty useless, because it would be far too much work to only create a simple static chart, that would be better done in Excel. But since it's indeed perfectly dynamic (as per my definition above), I have to say this is the most amazing tool I have seen in a long time.

Note: If by "dynamically" you mean drill down charts, then yes, you would need the Enterprise Edition. But as per your comments I actually believe you rather refer to the scenario I mentioned above.

If you need help on this, just ask here in the forum, but try to provide as much information as you can, and be precise in your requirements. For example, rather than asking how to make "a dynamic report", describe exactly what you try to achieve.