Recommended approach to populate Tables with Associations

[quiroga]

Hi All:

We are currently investigating how to perform the following operation in WFA:

a) Need to populate two tables we've created under a new schema.

Table A

Table B

  where Table A contains a couple of FKs referencing the primary key of Table B.

We have seen that we can:

1. Create a datasource that reads a CSV file and populate things that way (powershell only it seems)

The issue in this case is that we would need a way to first create the records we need in Table B (one CSV File perhaps). Then use another file to create the entries we need in Table A using the ids from the records previously created.

We won't be the ones actually creating these things but the customers, so we wanted to find an easier way for the user to perform the operation.

We have thought of perhaps creating a perl script (Command) that reads in an XML file or input arguments where the user can perform the operation within the WFA GUI. However, in order to do this, the user would need to provide DB credentials (I think) so we can connect and execute the SQL statements necessary.

We are looking for a recommendation, easy way to handle this case. Any help, pointers, guidance will be highly appreciated.

Thanks in advance.

Alfredo

[sinhaa]

Hello Alfredo,

     I need some information from you.

@ a) Need to populate two tables we've created under a new schema.

---------

What is your source of data here? Are you trying acquire some Data-base in WFA (into a new schema with tables tables A & B)? Or are you looking to build WFA schema/tables A & B y as a result of some execution output?

@

We have seen that we can:

1. Create a datasource that reads a CSV file and populate things that way (powershell only it seems)

-------

A data source type with method=SCRIPT (Power Shell) will execute the PoSH script, creates its own .csv files and acquire that Data into your tables(dictionary items). I don't think there is a direct way where any Datasource type will read directly Data from the User provided .csv files.

@ We have thought of perhaps creating a perl script (Command) that reads in an XML file or input arguments where the user can perform the operation within the WFA GUI. However, in order to do this, the user would need to provide DB credentials (I think) so we can connect and execute the SQL statements necessary.

----------------

I can't get what are you trying to do here. That's why I need need to know what is the Source of Data that you are trying to acquire. You said SQL statements, so it looks like you are trying to acquire some Database. Is this correct?

But you also said perl scripts to read XML files/Input. So its confusing me.

So I need to know what is the source of your Data? Is it some Data-base?

warm regards,

Abhishek

WFA

NetApp Inc.

[quiroga]

Hi Abhishek:

Appreciate the response. See inline below:

What is your source of data here? Are you trying acquire some Data-base in WFA (into a new schema with tables tables A & B)? Or are you looking to build WFA schema/tables A & B y as a result of some execution output?

Yes, it's a new schema We've created. We currently have 4 tables. We would like to have the ability to populate with user input 3 out of the 4 tables we now have.

I can't get what are you trying to do here. That's why I need need to know what is the Source of Data that you are trying to acquire. You said SQL statements, so it looks like you are trying to acquire some Database. Is this correct?

But you also said perl scripts to read XML files/Input. So its confusing me.

So I need to know what is the source of your Data? Is it some Data-base?

The idea is to use an XML schema/file/template we have created that the user can fill out. We would then:

- Read the XML in.

- Parse it.

- Extract the fields and populate the respective tables we have. If IDs are passed in we will update the records. If no IDs are passed in we will handle it as a brand new insert.

The only way We've seen to perform the above steps is to use the CSV approach with the powershell based datasource. In our case as previously mentioned the data to be added is based on a relational model so it would be pretty tedious for the user to do it all using CSV and in multiple steps. So we are basically looking for a different alternative if there is one besides doing what we plan on doing which is writing a perl script that will take in the XML as a WFA command, we will ask for credentials to the DB and then take the XML and persist it to the DB.

Do we have any other options to accomplish something like what I've just described?

Thank you in advance for the help.

Alfredo

[rle]

Hi Abhishek,

To add more information.

We would like to get a set of data into WFA.  It seems we have 3 choices:

1. Add  data to the playground database or WFA database in a separate schema

2. Use a file as a data source

3. Use an SQL server as a data source.

Adding data to WFA requires WFA credentials as far as we can tell, which we don't have.

We have seen a CSV file used as a data source. Video Link : 3351. It uses a script in Powershell to assist in obtaining the data.  Instead of using a CSV file, we would like to use an XML file, but don't understand how to write the script to obtain the data.  I believe that this is what Alfredo is talking about.

We don't want a separate SQL database and server.

Thank-you,

   - Rick -

[sinhaa]

Hello Rick & Alfredo,

@ It uses a script in Powershell to assist in obtaining the data.  Instead of using a CSV file, we would like to use an XML file, but don't understand how to write the script to obtain the data.  I believe that this is what Alfredo is talking about.

------

This video tell about using an existing .CSV file to create a new Data source type to acquire in a new scheme table in WFA. But you DON'T have to have an existing .CSV file. You can create a .CSV file by your PoSH script which will read data from the XML file. I'll take the below example:

1. A new Dictionary item "users" in a new scheme "people" with following attributes, first_name, last_name

2. My XML file is Users.xml with the below content:

<Users>

  <User>

          <id>1</id>

    <FirstName>Abhishek</FirstName>

          <LastName>Sinha</LastName>

  </User>

  <User>

    <id>2</id>

    <FirstName>Rick</FirstName>

          <LastName>Ehrhart</LastName>

  </User>

</Users>

2. Add a new data source type (e.g. name XML Datat source), method=Script and in Script, you can write a powershell code that will read your XML file and create a new .CSV file when you acquire a DataSource of Type 'XML Data source" and populate your table users.

I'm attaching a Datasource type that will do it. Its script is comments at all steps to help you understand what is it doing. Its very simple.

3. Execution => Data Sources => Add a new Data source of type XML Datasource. Provide Some filler Host Name and save it. It should succeed and will acquire the XML data into you new schema.

4. For every table, you'll need one .CSV file. So accordingly modify your PoSH script.