BookmarkSubscribeRSS Feed
jwhite
Quartz | Level 8

DI Studio 4.4

SAS 9.3

I'm a new SAS user.

I'm using a 'Create Table and Metadata' node to load the Metadata from a SAS program's definition of table. I have created parameters in the parameters tab of this node, and these parameters are being used in the options tab for the 'Repository Name', 'Table Name', and 'Metadata Folder'.

However, what I would like is for the user to be prompted to provide these values with the node is executed. Currently, there is no prompt. I can 'test prompts' in the parameters tab, and that pop-up window is what i want to see when a user executes. This means that currently only the default values of the parameters are being used.

So, is there as way to have prompts 'pop-up' the inputs to the user when they run a DI Studio job? How is this done?

Thanks!

11 REPLIES 11
jakarman
Barite | Level 11

A DI studio environment is designed to build fixed type of jobs as of ETL processing.
You can used generated code to be used with Stored Processes. Stored processed can be associated with prompts.
You can test with the default settings (essential being macro-vars) as batch jobs and verfiy them more afterwards.

r     

---->-- ja karman --<-----
jwhite
Quartz | Level 8

So, the 'Create Table and Metadata' has parameters, and a prompt, that can be tested, but we can't get the prompt to pop-up for the user when executed? This isn't part of a larger job, but just one note in the job. This feels more like a quick utility as it were as opposed to a job with a series of processes.

CT&M.png

jakarman
Barite | Level 11

DI is not the tool normally being delivered to users. Just one for developing code.
Delivering code with SP usage could be with AMO (Eguide / portal-web report are others)
Just as example for AMO using SP:  http://support.sas.com/resources/papers/proceedings12/036-2012.pdf

---->-- ja karman --<-----
jwhite
Quartz | Level 8

I'll tackle that document this weekend, thanks.

However, I guess I'm using the wrong term when I say 'users'. I mean the developer. For example, I, as an ETL developer would just like to run this node to upload a tables metadata, and would love to have a prompt pop-up so I can fill in the correct names and paths, rather than have to open up the node and type it all in, or (as was the case prior to this point) have one of these mini-jobs for each table in.

Thanks!

Patrick
Opal | Level 21

The way I'm normally registering table metadata is either by creating the physical data first (this can be tables without any rows) and then select "register tables" for a whole library - or I simply create the table metadata on the go when I build the flow. Using the "create table" node you can for example register the source table against this node, propagate & map the columns as required to a work table target,then right open the green work table (properties window and give it the name you want, select the library it needs to reside (so a permanent library) and the metadata folder you want to save it to.

As for your question: No, DIS won't prompt you when running a job interactively. The picture you've posted shows that you're using a "user written transformation" and that you can pass in parameters as macro variables. There are 2 related "work-arounds" I can think of:

- You could create a loop job and pass in the parameters as table to the inner job which then creates the table metadata (and the outer job could load the control table with all the table names and so on from some csv file).

- You could also have the "inner" job only. You then deploy the job and call it from EG (%include) and define the prompts in EG (though creating and populating the macro vars used in your included job).

So the general idea is: Pass macro variables to the "user written transformation" and populate these macro variables before you call the job/transformation creating and registering the table.

jakarman
Barite | Level 11

Ok jwhite you are a new developer (not user).
You are normally starting at a point knowing the structure of the data coming in. Having that data in SAS or other DBMS type there is already a lot of metadata. The register tables is the action to do see Patrick.
admin. It is commonly no task for the developer. Sometimes there are other tools being used for data modeling. There are options to import that to SAS metadata. That is bringing the question why would you want to develop a libname task? not knowing/having the input not knowing the output, that is outside the ETL (Extract Transform Load) actions. 
  

---->-- ja karman --<-----
jwhite
Quartz | Level 8

Patrick, Jaap, thanks for the responses!

Patrick, how can I determine that the 'Create Table and Metadata' node is a 'user written transformation'? under the basic properties 'User Written = No' and there doesn't appear to be user written code I can skip to in the 'Code' tab of the node.

Also, when you mention creating the physical data first, then selecting 'Register Tables...' (that seems easy enough), how are you creating the physical data? With a SAS program denoting the columns (variables) and formats?

I'm going to be attending a DI:Studio class here shortly, so hopefully this will become more clear.

Thanks again!

LinusH
Tourmaline | Level 20

By looking at the icon in your PrtSc, it looks like a Use Written.

Also, if you can check out the actual transformation to your Project repository - then it's proven.

Unfortunately, there is no tight click on meta data table, and Create Physical Table... option in DI Studio.

As I mentioned in an earlier post, the tables are created for you if they are placed in a job, and is created the first time you execute the job.

On rare occasions when you wish to create them manually, create a temp job, place your meta data table there, add a Table Loader, link them in a "circle", then go to the Code tab, mark the data step, and potentially proc datasets step, and hit F3. Not optimal, but for rare occasions...

Data never sleeps
JohnJPS
Quartz | Level 8

Can we summarize this briefly to state that, even during development, one should not expect to be prompted to supply "user prompts" when running a job directly in DI Studio? For instance, I've created a simple job that has a test prompt (in the job properties)... when I run it, right from DI, there is no prompt displayed: it just uses the default and runs with it. I assume that's normal, but is there a way I can ever supply the prompt manually while testing in DI Studio, (without needing to modify the default and/or deploy/integrate with a testing tool)? Thanks!

DaveR_SAS
SAS Employee

If you would like an intro to DI Studio jobs, you might check out the brief videos at the bottom of the DI Studio doc page: http://support.sas.com/documentation/onlinedoc/etls/index.html

You might also want to look at the topics about generated transformations in the DI Studio user guide: 

http://support.sas.com/documentation/cdl/en/etlug/66819/HTML/default/viewer.htm#n0iu33h8k5gxhon0za9f...

LinusH
Tourmaline | Level 20
Not in DI Studio, AFAIK.
Untested, if you deploy as a stored process you might get promoted. But then, running a stored process is not ideal testing environment...
Data never sleeps

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 4425 views
  • 0 likes
  • 6 in conversation