BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jchapman
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Jchapman
Calcite | Level 5

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

10 REPLIES 10
jakarman
Barite | Level 11

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 --<-----
Jchapman
Calcite | Level 5

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

jakarman
Barite | Level 11

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 --<-----
Jchapman
Calcite | Level 5

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

jakarman
Barite | Level 11

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 --<-----
Jchapman
Calcite | Level 5

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.

jakarman
Barite | Level 11

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 --<-----
Jchapman
Calcite | Level 5

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

jakarman
Barite | Level 11

I did that also

---->-- ja karman --<-----
Jchapman
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to choose a machine learning algorithm

Use this tutorial as a handy guide to weigh the pros and cons of these commonly used machine learning algorithms.

Find more tutorials on the SAS Users YouTube channel.

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