blom0344 Tracker

Re: Managing Audit Data in SAS Visual Analytics 7.1 and 7.2

blom0344 — Thu, 11 Feb 2016 20:01:54 GMT

Having encountered serious problems with VA version 7.1 regarding the size of the audit tables reloading parallel with regular tables (several days of unstable VA environment) , tech supported mentioned a workaround by weekly archiving data from audit and audit_entry tables to a set of archive tables. This seemed to work properly. After upgrading to version 7.3 this process was repeated (new configuration) , but the auto-archiving feature does not seems to work properly. (audit tables not populated, regular tables not purged)

We then decided to build our own small DI Studio job to read the appropriate tables (6 in total) from the EVM repository (it's a postgreSQL DB hidden on the VA server) and use the output of the Job to populate the existing VA audit table. The Job is remarkably simple , just some joins, denormalizing data and appending archived / non-archived data. 3-4 million audit records are processed within 10 minutes and we run the job weekly . EVM autoload is disabled , so VA restarts are no longer a risk with multiple autoloading processes getting in each other's way.

The job was build with DI Studio 4.6 requiring PostgreSQL drivers installed, a DSN configured, ODBC library defined and using the proper account to access the data. The job package I can share 🙂

Cheers!

Re: Visual Analytics Refresh Report

blom0344 — Tue, 17 Feb 2015 07:43:35 GMT

Hi, what seems to work is the combination is the direct link URL + Auto hot key tool. (AutoHotkey: macro and automation Windows scripting language)

From our test experiences you can set a certain interval and the screen with the report is refreshed with new data without having te reenter credentials. We tried this with a 30 seconds script and it seems to work.

It is still a workaround considering your requirement, but perhaps it is good enough..

Cheers

Re: Getting started with SAS Visual Analytics 7.1 Audit Reports for Administrators

blom0344 — Tue, 10 Feb 2015 19:16:33 GMT

I did not modify the autoload.sas file, just used credentials for the windows task with sufficient (?) rights within metadata. SAS does not specify in any detail what sufficient rights ought to be, but in my case I use my own account (both local admin on the server and admin within metadata) If I use a local server admin account without metadata rights I get the same errors and missing values for directories.

My gut feeling would be that modifying the .sas file should not be mandatory for a working solution..

Re: 6 hidden SAS Data Management Platform features you may not know about

blom0344 — Wed, 04 Feb 2015 19:23:21 GMT

Can you confirm that Data Management Studio used a 'real' ETL pipeline engine as opposed to DI Studio which basically just creates base code that is handled sequentially?

Re: Getting started with SAS Visual Analytics 7.1 Audit Reports for Administrators

blom0344 — Fri, 30 Jan 2015 21:57:28 GMT

okay, I'll check this new input out ..

Re: Getting started with SAS Visual Analytics 7.1 Audit Reports for Administrators

blom0344 — Wed, 28 Jan 2015 19:55:32 GMT

Hi Anna,

I actually have a working solution now , so opening a case with technical support does not seem warranted. I would want a bit more detail / clarification

Example:

" Log on your SAS Visual Analytics Root node as the SAS Installation User "

Root node seems to address a Unix setup, but how do you intend to run the task on a windows box. The account running the task NEEDS to have the right metadata permissions, the windows installer account does not.

I'm very glad you published the article though, cause without it i would still be searching all other documents trying to tie the various pieces together !

Cheers,

Ties Blom, Netherlands , Gelre Hospitals

Re: Getting started with SAS Visual Analytics 7.1 Audit Reports for Administrators

blom0344 — Fri, 23 Jan 2015 16:09:36 GMT

I've executed the first part of the 'Getting Started' part of the the article and the good news is that it worked 'as designed' ..

The bad news is, in my case , is that I've only been able to get data from the audit RDBMS just once

The windows task ran as designed within 15 minutes with me being logged on the the remote desktop and using my own credentials (admin rights both for server and in metadata)

I basically changed 2 things:

1. Set the account for the task to the sas installer user

2. Set the frequency of the task to once a day for a specific time

The task runs as scheduled without errors, but no new data is generated, the originally generated audit_visualanalytics.sas7bdat file is not replaced or appended

This file does indeed store all kinds of audit related data from 8-1-2015 (audit enabled) up till 21-1-2015 and the usage report shows a LOT of interesting stuff

Any ideas?

[update - 26 jan 2015]:

Today I changed the credentials back to my own (instead of the sas installer identity) after realizing that the installer account only has rights on the windows server and is not associated with the SAS metadata. . According to the logging generated I finally get :

(A) the readout from the RDBMS (PostgreSQL) EVM datamart and

(B) a working autoload mechanism that does indeed append to the LASR table 'audit_visual analytics' .

The logs show that actually 2 files audit_visualanalytics.sas7bdat are generated. The first one writes to EVDMLA folder (identified as DB.audit_visualanalytics) and a second one is created in EVDMLA\Append folder. The first one is incremently build, whereas the second one only stores record to be appended to the LASR table in VA

Perhaps the Article is simply focussed on the 'getting started' bit , but it seems that a little more additional information may be in order. Like which (type of ) credential to use to run the system task and the append mechanism itself

Re: Need sas Editor

blom0344 — Thu, 22 Jan 2015 13:17:50 GMT

AFAIK the SAS editor (prior to version 9.4?) only runs on a server and not from a client. The icon you display would indicate version 9.3 and older versions. The newest incarnation as editor runs as a web application. the Student version within VMWare does seem to need a pretty highend desk/lap top to run with any kind of performance

Re: display used datasource within a VA Dashboard

blom0344 — Fri, 16 Jan 2015 19:40:57 GMT

Rick, thanks for the crystal clear reponse. We'll look forward to this type of functionality!

Re: VA use ; how to monitor and report on Dashboard usage

blom0344 — Fri, 16 Jan 2015 07:02:25 GMT

Thanks Michelle ! We'll digest the new published article and see whether we will get things working overhere..

Cheers!

Re: VA use ; how to monitor and report on Dashboard usage

blom0344 — Thu, 15 Jan 2015 12:32:34 GMT

So far enabling auditing within the Management Console and restarting the webserver application service generates the table/file 'audit_visualanalytics.sas7bdat' just once in :

D:\SAS\Config\Lev1\AppData\SASVisualAnalytics\VisualAnalyticsAdministrator\AutoLoad\EVDMLA\Append

This table should be the input for the same table within the LASR server , feeding the canned administrator report:

Administrator Overview in the Usage folder (Products --> SAS Visual Analytics Administrator --> Reports --> Usage)

I would like to populate the table with usage data, but I have found no document that describes the process.

APM and ACM auditing through Environment Manager seems to focus solely on system(resource) auditing and monitoring

Any ideas?

display used datasource within a VA Dashboard

blom0344 — Thu, 15 Jan 2015 12:12:42 GMT

Does anyone know a method to display the datasource(s) used for a report/dashboard within the reporting object itself. Suppose I use a LASR table 'dim_customer' and I want to list this tablename within the dashboard itself?

Re: VA use ; how to monitor and report on Dashboard usage

blom0344 — Thu, 08 Jan 2015 19:17:25 GMT

It's been a month and progress regarding enabling auditing is mighty slow. With SAS version 9.4 / VA 7.1 it looks like all components are somehow in place or ready to be implemented or configured. So far I've been able to:

1. enable auditing within Management Console

2. Learned about dropping zones and autoload facilities

3. Acces Environment Manager by means of the proper URL (only by using the service account, not by adding the proper groups to my account, mind you)

4. Tracked down my beloved Postgresql database server and the PgAdmin tool

5. Found out that the EVS datamart stores 2 audit tables and 2 archive audit tables

6. That reading the audit tables from PG at a certain interval and dumping the contents in the append part of the autoload 'zone' is the startpoint to automatically load the LASR table(s) in order to feed the admin report lurking within the VA caves.

It's thus a matter to link the dots and I hope to present the full working solution in the community asap

Cheers..

Re: unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 20:59:02 GMT

@Tom, thanks for your reply. My code was merely a random example. The actual code does require the Proc SQL, so another SAS expert suggested the select into option. Thanx also for the examples offered !

Re: Sql Insert vs Data Step Efficiency Question

blom0344 — Sat, 13 Dec 2014 20:55:49 GMT

Yes, you are right. My idea was to point to the use of set operators. I wasn't sure SAS supports union all, so I sticked to the union

Re: unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 18:57:55 GMT

Okay,let me clarify, I put up some code at home , based on the student version on VMWARE and the available tables. (As mentioned)

The actual program I'm working on I cannot access from home, hence the example(s) from VMWARE.

The actual idea is to store the timestamp from the beginning of the process (it takes about 3 minutes to run from begin to end) and then add the timestamp for reporting purposes to the resulting reporting table. (at the end)

The concept of applying a format is not easily adopted after working 20 year with each and every possible relational database, but I think that Cynthia really made it very clear. Thanks for your feedback !

Re: unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 18:30:48 GMT

My example is simply some testcode to check differences and since it is executed within the same program , there is little reason for a difference of 11592 seconds. The value itself is not stored as the datetime() value, but some sort of rounded figure. But why?

I find SAS quite exhausting at times, but that may be due to working with relational databases for 20 years

Calling the start_time variable as you define it does not work @start_time is not recognized, so perhaps the entire code needs to be within a macro?

Re: unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 18:23:20 GMT

using @datetime_var yields an error message:

ERROR 200-322: The symbol is not recognized and will be ignored

The code I'm using is not wrapped up within a macro, perhaps I need to wrap it up in there?

put(datetime(),is8601dt.) as tstamp does work the datetime value as an integer stored as a character can be used within a variable and returns the correct value

Re: Sql Insert vs Data Step Efficiency Question

blom0344 — Sat, 13 Dec 2014 16:54:19 GMT

What is the CPU time of:

proc sql;

create table3 as

select t.* from table1 t

union

select v.* from table2 v;

quit;

since this closely resembles building a table from scratch as the datastep does..

unable to store datetime value in a macro variable

blom0344 — Sat, 13 Dec 2014 16:23:10 GMT

I'm trying to store the datetime() value into a macro variable for use within a little program: (example with the baseball table of the VMware student edition )

proc sql;
create table work.start_falnr as
select max(nhits) as max_hits,
round(datetime()) as process_start_integer,
put(datetime(),is8601dt.) as tstamp,
datetime() as process_start
from sashelp.baseball;
quit;

proc sql noprint;
select max_hits,process_start_integer,process_start,tstamp into
:max_hits,:process1,:process2,:tstamp
from work.start_falnr;
quit;

proc sql;
create table work.output as
select process_start_integer,&max_hits,&process1,&process2,&tstamp from work.start_falnr;
quit;

The actual exact value of process_start_integer is 1734088402., but the values written to the output table are 1734100000 and 1734100000 resp. for process1 and process2

Why can I not store the exact value this way?

The tstamp value (put(datetime(),is8601dt.) ) yields an error message in the last proc sql ...