Creating SAS Web Reports from an Oracle data source

SAS Visual Analytics
Misleading screenshot of SAS Visual Analytics – but it looks much sexier than Web Report Studio, doesn’t it? 🙂

Here’s a sparse set of instructions for the steps you need to take to configure SAS to access data from an Oracle data store and surface it through SAS Web Report Studio (as an example).

Note that once it’s in a SAS format this can be used in any SAS product, and therefore means you can do all sorts of analytics on it. Hence the VA screenshot.

Caveats – Please note the purpose of this is not to break down every area step by step, but more to highlight the various areas that you’ll need to configure to get it all working together. See this as an orchestrator’s guide – I’ll try to document the areas that are necessary for a full orchestra, but I won’t tell you how to play each instrument! As such, these steps assume a working Oracle database setup, as well as the necessary SAS servers ready and waiting to be used.

I might go into more detail for each particular step in later posts.

Management Console

  1. Open Management Console

SAS User

First off choose a SAS user who will access the Oracle data (SAS Demo User).

  1. Go to User Manager
  2. Right click a SAS user
  3. Go to Properties > Accounts
  4. Click “New”
  5. Create a new Authentication Domain called “MyAuthDomain” and enter your Oracle RDBMS main user’s login details (e.g. myUser)
  6. You’ve now tied a SAS user to an Oracle user for a specific domain

Server

Next we create a server to represent where all the data is coming from (i.e. your MIE machine)

  1. Right click Server Manager
  2. Select “New Server”
  3. Select “Oracle Server”
  4. Enter a name (myserver)
  5. Set the “Associated Machine” to your SAS machine
  6. Set “Path” to the string you would use to connect to it if you were using mysql
  7. In other words, <ORACLE MACHINE>:<PORT>/<SERVICE NAME>
  8. e.g. ‘myoraclemachine.domain:1521/myservicename’
  9. Important – use single quotes around the whole thing
  10. Change Authentication Domain to “MyAuthDomain”

New Library

Now create a Library to represent a certain subset of your data – it’s like a logical grouping, and the library will be a lot more publicly accessible so the name must be good.

  1. Expand “Data Library Manager”
  2. Right Click “Libraries”
  3. Select “New Library”
  4. Select “Oracle Library”
  5. Choose a name (mytbls)
  6. Set the location to /Shared Data/<new folder of your choice> (create a new folder for your tables)
  7. Choose “SASApp”
  8. Set “Libref” to what your publically accessible name should be – it can only be 8 characters!
  9. Set “Database Server” to your server name (myserver)
  10. Set “Connection” to myserver (should be there already)

Register Library Tables

All you’ve done is create an empty library, pointing at your server. You need to actually choose the tables you want to use in this library.

  1. Click “Libraries”
  2. Right click your new Library (mytbls)
  3. Select “Register Tables”
  4. A dialog will appear telling you to log into your SAS machine… enter your SAS user details, NOT your oracle user details (always keep a close eye on which machine it’s asking you to log in to – other times it will ask for oracle credentials)
  5. Choose whatever tables you want to register in here

SAS Information Map Studio

Next we need to set up an Information Map to expose the view of the table(s) we want to use in reports.

  1. Open Information Map Studio
  2. Expand SASApp in the left hand side column to see your server
  3. Expand your server
  4. Choose any table or tables you want to use in your Information Map
  5. Choose any fields from those tables
  6. IMPORTANT – SAS will by default set the width of those fields to 32kb characters. Give these a more sensible limit otherwise this will break Web Report Studio (and potentially other things)
  7. Add any filters you want on those fields
  8. Make any Joins between tables you need
  9. etc
  10. Save your Information Map to Shared Data

SAS Web Report Studio

Now we have our data view exposed, we can build reports off of it.

  1. Open Web Report Studio – http://mysasmachine.domain:8080/SASWebReportStudio/
  2. Open the Report Wizard
  3. Select the Information Map you created as your data source
  4. etc etc
  5. Save the report