topic Can a stored process save and restore state? Or should I use a custom task? in Developers

Can a stored process save and restore state? Or should I use a custom task?

ScottBass — Wed, 18 Sep 2019 08:27:19 GMT

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...

Re: Can a stored process save and restore state? Or should I use a custom task?

AllanBowe — Wed, 18 Sep 2019 08:52:32 GMT

For sure, it can! As for where, it depends on your UI type and use case.

If you wish to build apps on SAS you already have your 'AF alternative'. It's free (mostly), and immensely powerful, and you've probably used it before.

It's HTML5.

You can save state in users browsers through cookies or local storage. You save state at the backend through network drives (like you say) or a database.

Some resources for this approach:

Building Web Apps

Build an app in 10 minutes; https://drive.google.com/file/d/1kAbbKDYWZ1wmgLWiwTYctR4QbQzxLlWX/view

Build an app in 5 minutes: https://vimeo.com/349223867

Existing Web Apps

Data Controller - https://datacontroller.io

Metadata Explorer - https://github.com/Boemska/metanav

User Navigator - https://github.com/Boemska/user-navigator

If you'd like to get stuck into building web apps on SAS I'd be happy to talk more, just drop me a PM.

Re: Can a stored process save and restore state? Or should I use a custom task?

ScottBass — Wed, 18 Sep 2019 22:10:13 GMT

Hi @AllanBowe ,

I think this would be really cool. But...

1) How would I integrate this with EG, which is the tool my end users use?

2) How do I create this if I don't have access to a web server in my environment?

Cheers...