How to acquire Non-English European characters in WFA using script based acquistion: Solution

[sinhaa]

The Script based Data Source Acquisition is an extremely powerful Data Acquisition method provided by WFA which allows users to acquire Data from just about any source and get them in WFA MySQL Database. However the provided mechanism can only acquire  English characters and can be a serious limitation as not everyone uses English for communication and work.

Keeping internationalization in mind, I'm providing method of how to acquire West European, Scandinavian, German, French and Other non-English letters in WFA.

The Background story:

Its important to understand why is this limitation for only English Characters exists in WFA. The limitation of not being able to use non-english letters is due to the method of indirect insertion of data into WFA DB for a Script based Data Source acquisition. I need to first write all data rows in a .CSV file in Byte Encoding and then WFA dumps these rows into the Data base tables. This is a basic requirement that data be written in Byte encoding only. But these non-English characters need UTF8 encoding to be visible as they are. But WFA can't acquire from  a UTF8 encoded .CSV file and throws all sorts of errors.

But do I really need WFA to do data insertion in DB tables for me? WFA restricts write access to all schemes (canned and even custom) to itself, and hence you can't make SQL INSERT/UPDATE/DELETE SQL statement directly into any scheme except for the only provided 'playground'. So the limitation.

WFA4.0 allows you to create writable custom schemes where the default WFA DB user has full privileges on custom schemes of a given type. I can execute INSERT/UPDATE/DELETE queries into them. But now I have another problem. WFA4.0 prevents me from creating DataSource type on those writable schemes.

So what do I do now. I want to be able to INSERT Data myself.

The Solution Logic:

I create a proxy scheme and make WFA believe that the Data Source Acquisition script is filling Data into this proxy scheme, but in my Acquistion script, I'm using INSERT query to fill data directly into my writabe scheme.

One-time prerequisite steps:

1. You need at least WFA 4.0. It’s not mandatory but the older version do not create new DB-user writable schemes. So if you want to use WFA older than 4.0RC1, you can only create your dictionary on ‘playground’ scheme. This is how it’s going to work for the old WFA.
2. Set a login-path for your WFA Mysql DB so that you don’t need to send clear-text password on the CLI when executing my SQL queries. Below are the steps to get this done
   1. Create a new mysql login-path for wfa
      1. Open a cmd prompt as “Administrator” on WFA and do the following.
      2. C:\Program Files\NetApp\WFA\mysql\bin>mysql_config_editor set --login-path=wfa --host=localhost --user=wfa --password 

                    Enter password:

1. Enter ‘Wfa123’ as password.
2. Test that this worked fine by executing a query on the CLI as below.

 C:\Program Files\NetApp\WFA\mysql\bin>mysql --login-path=wfa --user=wfa -e "show databases"

1. Change properties of NetApp WFA Server service to start it as any Administrator account and not LocalSystem (Default). Restart service to take effect.

1. Add a new command in WFA with the below code and Test. If it passes, your WFA is fine to proceed with next step. Else you need to correct them.

##BEGIN CODE

$pwd = Get-Location
$cmd1="show databases"
$pwd = Get-Location
$mysqlcmd = $pwd.Path+"\..\..\..\..\mysql\bin\mysql.exe"
$wfaService=(Get-WmiObject -Class Win32_service -filter "name='NA_WFA_SRV'").StartName
Get-WfaLogger -Info -Message $("WFA service account: " + $wfaService)

if ( $wfaService -eq "LocalSystem" )
{
throw("NetApp WFA Server service is running as LocalSystem. Restart it using an Administrator account and try again.")
}

# Default credentials
$MySQLAdminUserName = 'wfa'
$MySQLAdminPassword = 'Wfa123'
$MySQLDatabase = 'cm_storage'
$MySQLHost = "localhost"
$MySQLLoginPath='wfa'

$output = & "$mysqlcmd" "--login-path=$MySQLLoginPath" "--user=$MySQLAdminUserName" "--host=$MySQLHost" "-B" "-f" "-e$cmd1" "$MySQLDatabase"
$output | % { Get-WfaLogger -Info -Message $_ }

##END Code

Go to WFA->Designer-> Data Source Designer -> Schemes, Create a scheme named proxy of Type Data Source Acquisition. You can choose any other name too if you don't like proxy. This is just a proxy scheme, no data ever gets filled here. No need to create any dictionary on it.

That’s all.

Next Steps, the real thing

In my example lets assume, I need to acquire some data about football players from Scandinavian countries. This is my real scheme where I want to acquire data. Names of people from Scandinavian countries often have some special characters. I need them as they are in WFA DB as I get those names from a powershell script.

1. Go to WFA->Designer-> Data Source Designer -> Schemes , Create a new scheme of type Other. In my example I chose name ‘scandinavia’ of type: Other . This is NOT the default type, so ensure you select it correctly from the drop-down. This is going to be my scheme or database to keep my data

1. I create some dictionary on this scheme Scandinavia.
2. Add a new Data Source Type
   1. Provide to identify your real scheme: I gave  "DST for Scandinavian Footballers"
   2. Method: script, language PowerShell
   3. Select scheme ‘proxy’ from the drop-down list. My real scheme ‘scandinavia’ will NOT appear in the available list. Why? WFA wants to restrict Script Based acquisition for schemes of type ‘Other’ and this is what our schemes type is. I had to do this as I don’t want WFA to fill data into my scheme. I’ll do it myself from my PowerShell script.
   4. Now just write Powershell code as you did to get data.
   5. DON’T create any .CSV files. No need to write anything on them. Since my proxy has no dictionary, WFA won't look for it.
   6. So when your row is ready prepare your SQL INSERT statement and execute them using mysql.exe
3. That’s all folks.
4. Add a new Data Source for this Data Source Type. When acquistion suceeds , your Data in the purest form will get acquired into you real scheme.

Use the attached complete example:

You can Import the sample Data Source Type attached here.

I also have given a simple workflow which is easy execute and will help you understand the flow.

The Example

To try the example provided. I assume all the one-time prerequisites have been covered.

1. On a WFA4.0 , Import the file : WFA4_0_DataSourceType_Scandinavian_Football_Players.dar

2. Import the file: WFA4.0_Workflow_to_Print_a_Footballer_Name_sinhaa.dar

3. Add a new Data Source for : Scandinavian Football Players -1.0.0 ( POWERSHELL)

4. Acquire it.

After acquisition, WFA scheme scandinavia , table football_players has all the correct names.

5. Goto workflow and execute the workflow: Print a footballer Name. The user-input is very simple dorp-down list.

6. The proxy scheme can be used to fill any number of real schemes i.e. you don't need separate proxy for every real scheme.

Have fun!!

sinhaa