05-16-2014 12:00 PM
DI Studio 4.4
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?
05-16-2014 01:26 PM
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.
05-16-2014 03:00 PM
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.
05-16-2014 03:32 PM
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
05-16-2014 03:50 PM
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.
05-16-2014 09:53 PM
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.
05-17-2014 02:46 AM
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.
05-19-2014 08:38 AM
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 DItudio class here shortly, so hopefully this will become more clear.
05-19-2014 10:44 AM
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...
05-19-2014 12:10 PM
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: