TL,DR; In EG: Should I use a stored process or a custom task if I want to initialize a UI to the last saved state?
We have a business process where an end user runs an EG project, and has to either:
- Create a brand new Project (row in a SQL Server table)
- Create a new Service (row in a SQL Server table) owned by an existing project. A service is always bound to a project.
- Create both a new Project and Service at the same time (first execution of a new project)
- Search for an existing Project and Service (re-run a service after an error) (there's another table for batch executions bound to a service)
Right now we're using macro variables, SQL passthrough processing, and the process is error prone.
The current table structure is flat. Very simplified dummy data:
data operational;
length Project Service $20;
input Project Service;
datalines;
Project1 ServiceA
Project1 ServiceB
Project1 ServiceC
Project2 ServiceD
Project2 ServiceE
Project3 ServiceF
Project3 ServiceG
Project3 ServiceH
Project3 ServiceI
;
run;
I'm working on splitting the table into separate normalized tables using FK's. Again very simplified example:
data Project;
length ProjectID 8 Project $20;
input Project;
ProjectID+1;
datalines;
Project1
Project2
Project3
;
run;
data Service;
length ProjectID ServiceID 8 Service $10;
input ProjectID Service;
ServiceID+1;
datalines;
1 ServiceA
1 ServiceB
1 ServiceC
2 ServiceD
2 ServiceE
3 ServiceF
3 ServiceG
3 ServiceH
3 ServiceI
;
run;
proc sql;
create view vwOperational as
select
a.ProjectID
,ServiceID
,Project
,Service
from
Project a
join
Service b
on
a.ProjectID=b.ProjectID
;
quit;
That's probably overkill on the description of the business process. But, in summary, they need to either create a row or select an existing data, and IMO some sort of UI is the best approach and least error prone for the end user.
This is fairly simple drilldown processing in a stored process (at least for the select part). However, in the past, I could never get a stored process to save and restore state. IOW, I can create a UI where the user drills down to a row, but the next time he/she has to select all over again. There's no way to initialize the stored process to the last saved state. For example, by setting a macro variable, and have the stored process pre-selected to that macro variable.
I'm thinking the best approach may be a custom task which would display the UI, allow the user to create a new project and/or service, select an existing project/service, and save the state so the next time the user runs the project they don't have to select again. I'll have to work out the best approach to save state (XML/JSON), and store this in a place (UNC path) accessible to both the custom task and the SAS workspace server. I'd like the option for the user to not have to display the UI, in which case some macro variables would be set from the previous state (i.e. read the XML/JSON).
What I really want is SAS/AF for EG ;-). But I guess you can say that's analogous to a custom task?
Apologies if this is cryptic. I can provide more details if needed.
Your thoughts? How would you approach this?
Thanks...
Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
