topic Re: Keeping LASR Tables updated when source Oracle tables update in SAS Data Management

Keeping LASR Tables updated when source Oracle tables update

Djones4822 — Fri, 07 Jul 2017 15:49:40 GMT

We have an Oracle OLAP database that contains all historical data garthered through our daily transactional processings and is updated every morning. We've also built a suite of reports that use the oracle tables as source tables.

What we're noticing is that the LASR tables do not update automatically each morning, we've found that we need to manually open up the administration view and unload/reload each table.

Is there a process or a system of processes we can use that will keep the LASR tables up to date with the Oracle tables?

Re: Keeping LASR Tables updated when source Oracle tables update

LinusH — Fri, 07 Jul 2017 19:38:04 GMT

You should be able to script this by using PROC IMSTAT, which could schedule.

Re: Keeping LASR Tables updated when source Oracle tables update

Djones4822 — Fri, 07 Jul 2017 20:18:36 GMT

I've come across that procedure, but I wasn't sure where to place that. Do I schedule that in the Enterprise Guide?

Re: Keeping LASR Tables updated when source Oracle tables update

LinusH — Sat, 08 Jul 2017 03:54:57 GMT

Perhaps you could, but that is far from optimal, since that is on a client.
It's bettervti do it from the Management Console's Scheduling Manager (requires that you have stored the program as a .sas file first).
But given that you probably want to execute it right after the Oracle update you should consider how to build that dependency. Perhaps you could use an external scheduler that you organisation uses?

Re: Keeping LASR Tables updated when source Oracle tables update

SASKiwi — Sat, 08 Jul 2017 21:58:32 GMT

You can also use other usual processes to update LASR tables like PROC DATASETS and DATA steps. The trick here is to make sure that the table metadata also remains in synch for which I use PROC METALIB.

Re: Keeping LASR Tables updated when source Oracle tables update

Djones4822 — Tue, 11 Jul 2017 14:20:42 GMT

This seems to be it.

We're using Python to do control the ETL - is it possible for me to call SAS script from the command prompt? I know that I could do that typically with SAS installed locally on my machine, but with SAS VA I'm unsure.

I don't suppose you or anyone has any insight into that?

Thanks so much for your replies!

Re: Keeping LASR Tables updated when source Oracle tables update

Patrick — Tue, 11 Jul 2017 22:34:56 GMT

@Djones4822

Yes, you can call SAS in batch mode. That's what every single scheduler does so it's BAU.

To call SAS from a command prompt: You need the prompt on the machine where the SAS server sits (i.e. via Putty for a Unix/Linux distribution).

If I'm not mistaken then a VA instance also comes with the related SAS Compute modules licensed and installed and I believe PROC IMSTAT should be available.

http://support.sas.com/documentation/onlinedoc/va/7.3/en/vaicg.pdf

Re: Keeping LASR Tables updated when source Oracle tables update

Djones4822 — Wed, 12 Jul 2017 13:05:01 GMT

@Patrick that's great info, thanks so much - but it looks like that link is broken?

Re: Keeping LASR Tables updated when source Oracle tables update

AnnaBrown — Wed, 12 Jul 2017 13:32:27 GMT

Try this link, Djones4822 (without the http://).

support.sas.com/documentation/onlinedoc/va/7.3/en/vaicg.pdf