SAS Visual Analytics - Reports with datasets that need to be updated frequently

[NAVSUP_Aya]

I have been tasked with creating a Dashboard that provides visualizations of the dates and times tables within the a library have been updated. There are over 450 tables in this library and the data is just the table names, what type of table it is (classified by function) and the last date and time the information within the table has been updated. The dashboard is meant to provide insight to management at NAVSUP as to whether or not tables are being updated on time or are delayed. Since the development of the Dashboard, I have stumbled upon several obstacles that make rendering the Dashboard quite time consuming as it requires a significant amount of manual manipulation every time new datasets are imported. For example, every time I update the information in the Dashboard by importing new datasets, I must manually change the column names/labels, reassign roles to each dataset for report objects, reapply any rules and filters to report objects and remap linked report objects. As you can imagine, this takes quite a bit of time and is not particularly efficient for a Dashboard that would be useful on a daily basis. Would you be able to provide any insight or feedback as to how these processes could be automated?  Thank you!

[Madelyn_SAS]

Are you replacing the existing data source with a different data source that has different column names? If so, then that would be considered a new table and you would have to do the manual steps you describe. If the data source is just being update (new rows added, values updated) but the column names are the same, then a refresh should update the dashboard. 

[Panagiotis]

I'm not exactly sure where the issue is (the report or the data prep), but depending on what you are doing you should be able to automate it with some code.

From my limited understanding of the issue, I would

- create a program that prepares the data how I need it. Eventually create a job that will execute it daily/weekly/monthly/etc. The job would prep the data as necessary, then update the previous older table.

- create the report in Visual Analytics that would use the table above as it is updated daily/weekly/monthly/etc.

From my understanding, you are documenting all available CAS tables in a caslib and looking to check when it's been modified? Here is some CASL code with an action to identify all tables in a caslib:

cas conn;

proc cas;
    library = 'casuser';
    table.tableInfo / caslib = library;
quit;

My output with some fake tables. Notice the Created and Last Modified columns:

TableInfo gives me created date and last modified dates. You can add the results = option to save this result as a CASTable (or SAS DATA set) and execute some code to try and do any data prep you need.

Here is the code to save the table above as a CAS Table:

proc cas;
    library = 'casuser';
    table.tableInfo result = ti / caslib = library;

    * store the dictionary as a table*;
    CASTables = ti.TableInfo;
    * Save the table as a CAStable*;
    saveresult CASTables casout='listoftables' caslib=library;
quit;

Then you could use some DATA step code to manipulate that CAS table how you want it for the report. Then update the previous table that the report is using.

* Connect SAS to your caslib *;
libname casuser cas caslib = 'casuser';

* Execute data step to manipulate the table*;
data casuser.prepdata;
    set casuser.listoftables;
    * Here i'm simply converting the string date to a date value in SAS*;
    DateUpdated = datepart(input(ModTimeFormatted,ANYDTDTM.));
    format DateUpdated date9.;
run;

I am a bit confused by this part:

 I have stumbled upon several obstacles that make rendering the Dashboard quite time consuming as it requires a significant amount of manual manipulation every time new datasets are imported. For example, every time I update the information in the Dashboard by importing new datasets, I must manually change the column names/labels, reassign roles to each dataset for report objects, reapply any rules and filters to report objects and remap linked report objects. 

What do you mean by new data set is imported? Do you mean a new data set is being added to the library? 

I'm also not sure why you need to change column names/labels, reassign roles to report objects and more. Once you create a report and save it this should already be done and stored and using a CAS table. Are you creating a new report each time you update your main table?

- Peter