New SAS 9.4 Metadata Resource Templates for Google BigQuery, MongoDB, Salesforce and Snowflake

2 Likes

While there is a lot of cool stuff moving forward with SAS Viya, SAS 9.4 is still alive and continues to offer new features. SAS/ACCESS Interface to Google BigQuery, MongoDB, Salesforce and Snowflake have been released last year, either in the April or August version of SAS 9.4M6. But these SAS/ACCESS were not exposed in the SAS 9.4 metadata layer. So, although it is possible to access those databases from a pure SAS 9.4 programming perspective, it was not yet possible to leverage data from Google BigQuery, MongoDB, Salesforce and Snowflake in the SAS metadata using SAS Management Console, SAS Data Integration Studio or SAS Enterprise Guide (unless using generic metadata definitions).

Now, this is possible, and these capabilities come with the D8Y005 Hot Fix that has just been released. This Hot Fix provides, among other corrections, new Metadata Resource Templates for Google BigQuery, MongoDb, Salesforce and Snowflake.

What are SAS 9.4 Metadata Resource Templates?

From the documentation, “Resource templates are XML files that define the metadata that the SAS Management Console requests when defining a particular type of object. For example, the SAS Library resource template specifies the metadata that SAS Management Console collects when a user defines a new SAS library. In order to define a particular type of object, that object's resource template must be loaded into SAS Management Console.”

In other words, Resource Templates provide dedicated wizards to the SAS or Data Administrators (SMC or DI users) to collect database-specific information about how to connect to it from SAS.

Every database has its own terminology and its own connection options which need to be exposed to SAS. Dedicated Resource Templates make the configuration of a SAS/ACCESS server and library more contextual and more meaningful to users. They also unlock some features, bring some controlling options and nicely show at a glance the origin of the data in a DI process flow.

Where to find them?

The Resource Templates are located in the Metadata Manager plugin of SAS Management Console, under the Resource Templates folder.

Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.

After applying the hotfix, you will have to import the new Resource Templates using the Metadata Manager plugin (RMB -> Add Resource Template… -> Typical).

If the hotfix and its dependencies have been correctly applied, you should be able to find the latest resources here.

How to use them?

You will use them whenever you want to define new data sources coming from Google BigQuery, MongoDB, Salesforce and Snowflake. A library definition relies on a server definition. This is a one-time operation per server/database accessed.

You can use either SAS Management Console or SAS Data Integration Studio.

A server definition controls high-level options such as the server name, the port and the security associated with the database.

A library definition controls low-level options such as the database or the schema.

MongoDB server properties:

Google BigQuery library properties:

Snowflake Table definition:

Going further

Register tables from Salesforce in SAS DI Studio:

Database pushdown with Google BigQuery in DI Studio:

Load a SAS table in MongoDB using DI Studio:

Thanks to @EricWaldbauer for his assistance on the setup of the Resource Templates. The Hotfix D8Y005 can be found at the following address: https://tshf.sas.com/techsup/download/hotfix/HF2/D8Y.html#D8Y005

Search for more content from our group: SAS Global Enablement & Learning.