Building models with SAS Enterprise Miner, SAS Factory Miner, SAS Visual Data Mining and Machine Learning or just with programming

Using ODBC on Enterprise Miner

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Using ODBC on Enterprise Miner

I am newer to Enterprise Miner and need to use a SQL Server database to pull data. Is it possible to connect an ODBC database to enterprise miner? I have tried using data source, file import, and a SAS code node to no avail. I have proper SAS code which seems to execute properly on the EM server, but this does not create a dataset for subsequent analysis.

James


Accepted Solutions
Solution
‎05-16-2014 02:09 PM
Contributor
Posts: 29

Re: Using ODBC on Enterprise Miner

So the best method for resolving the issue is to

1. edit \SAS\EMiner\Lev1\SASApp\appserver_autoexec_usermods.sas to include a library such as minstag on the server machine

2. create a sas code node to pull data from the sql server

3. then use data source to prepare the data

Thank you again for your help.

View solution in original post


All Replies
Trusted Advisor
Posts: 3,215

Re: Using ODBC on Enterprise Miner

Your EM is running on a Server. Wanting SQL-server to be connected you need the 1/ SAS/ACCESS ODBC license 2/ software and 3/ the connection being configured.
The first two should be part of the installers job. the third is cooperation with the DBA of the SQL-server.
As Eminer has an interface optimized for mining the mentioned parts are more easier to do with EGuide.
Doe it work well you van copy the things of Eguide to Eminer or when it is the WS-server being adjusted by your sas-admin they will share that. 

---->-- ja karman --<-----
Contributor
Posts: 29

Re: Using ODBC on Enterprise Miner

I'm confused as to why EMiner wouldn't be optimized to mine from a SQL Server or Hadoop database. Seems rather counterintuitive.

I have the SAS/Access ODBC license and the software is installed and the odbc connection is configured and verified to work in SAS 9.4, EG, and EM. I am also the system admin. I have been able to create a temp table with a SAS Code node. However, the work directory here is different than the work directory used in Data Sources?

James

Trusted Advisor
Posts: 3,215

Re: Using ODBC on Enterprise Miner

When you see the data you can add that to your Miner project. The data should already have been prepared in a way suitable for mining.
With the preparation of the data, Eguide usage (coding) can be helpful.

The SAS-work is not shared between SAS sessions it is designed to be temporary. As parallel execution is possible all processes have their own SASwork in that case.

Do not see Miner as a coding environment it meant to do mining in a graphical node approach.
Those nodes are needing a lot of registration and intermediate files. You can find those on the OS level. (browse them).

There is a sample with sample data (donor) you could use to work on and get the approaches. SAS Enterprise Miner

When you have you input-data ready for mining you add them as resource (see sample) 

---->-- ja karman --<-----
Contributor
Posts: 29

Re: Using ODBC on Enterprise Miner

I can't see the data as it is created in a separate work folder which (as you said) is available only to that node. I tried to make a permanent library but this is not viewable on data sources as it points to a local directory while data sources points to directories on the server. Is there documentation on preparing remotely accessed data (on an external DBMS) so it is suitable for mining and available as a data source?

James

Trusted Advisor
Posts: 3,215

Re: Using ODBC on Enterprise Miner

The miner docs is assuming someone has done that and he is knowing that.
As you did something and store that in saswork, why:
- did not have stored in the SQL-server using a dedicated schema (minestag)
- use a mountpoint-filesystem eg /data/bulinexx/miner/minstag (unix) or other location (windows) where you save that data as a directory - libname minstag.

Use the autoexec_usermods of the appserver or the autoexec of your minerproject the do the libname.

You will get a permanent storage that should be reviewed for regular cleanups. But your processes are able to access that (shared). 

---->-- ja karman --<-----
Solution
‎05-16-2014 02:09 PM
Contributor
Posts: 29

Re: Using ODBC on Enterprise Miner

So the best method for resolving the issue is to

1. edit \SAS\EMiner\Lev1\SASApp\appserver_autoexec_usermods.sas to include a library such as minstag on the server machine

2. create a sas code node to pull data from the sql server

3. then use data source to prepare the data

Thank you again for your help.

Trusted Advisor
Posts: 3,215

Re: Using ODBC on Enterprise Miner

To autoexec within Eminer is a little bit hidden. In one of the first-screens left side, at the  project property, there is something as startup-code.
Getting there to the content is accessing the dots fiels (elisses) to open and change.


If you review the sample approach, there is a step "Create Library" doing that it will probably needed to execute more often.
The startup/autoexec is to make life easier as a service. You can do that later when needed.

By the way the miner help is online present when using that client. A lot is to find in that.

Missed your last response....
As soon you have a libname defined you will see the data in that becoming visiable.
Within eminer you onlye need to add the table from that to your project.

---->-- ja karman --<-----
Contributor
Posts: 29

Re: Using ODBC on Enterprise Miner

I edited my response instead of adding another when I understood your suggestion. Please see above.

Trusted Advisor
Posts: 3,215

Re: Using ODBC on Enterprise Miner

I did that also

---->-- ja karman --<-----
Contributor
Posts: 29

Re: Using ODBC on Enterprise Miner

Thank you for your time. I couldn't find other documentation on ODBC within miner.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 1985 views
  • 3 likes
  • 2 in conversation