With the Viya 3.5 release, CAS get a new deleteRows CAS action. This gives CAS the full compliment of table CRUD capabilities: Create (Insert/Append); Read; Update; and Delete. So let's take a look at updating CAS tables and see what we should and shouldn't be doing to maximize up-time, minimize down-time, and give our end-users the latest and greatest view of their data as quickly as possible. Awww CRUD! I stepped in the ACID! The first thing to note is that, as an analytical server, CAS is not equipped to support ACID compliant transaction processing (no transaction, commit, or rollback support). While CAS tables can be updated, data consistency must be enforced by the update process. CAS provides no functionality for maintaining data consistency itself.   For a better understanding of this topic, see this video. It talks about Hadoop, not CAS, but the concepts are the same.   With that out of the way, let's get into the CRUD... Inserts / Appends CAS has handled inserts from its earliest days but new insert functionality continues to appear. In 3.4, we got server-side appends (Appending CAS tables to other CAS tables). In 3.5, we get partitioned table appends.   Below is the CASUTIL syntax to append (insert) client-side rows into a CAS table:   proc casutil ; load data=stg.prdsaleNewRecords casout="prdsale" append ; run ;   For examples of server-side appending and much more, see this post: CAS answers to 4 common data manipulation tasks – Part 1 – APPEND Update Like a SQL Update statement, CAS' table.update action can update CAS table field values subject to a WHERE clause. Thus, it can update a single row's values by filtering on a single primary key value; Update several rows by filtering on a non-unique field value; And, update all of the table's fields by not filtering at all.   Below is the CASL syntax to update values in a CAS table:   proc cas ; table.update / set={ {var="actual",value="22222"}, {var="country",value="'FRANCE'"} }, table={ caslib="Public", name="prdsale", where="id=10" }; quit ;   CAS offers no "deltas table" update functionality. That is, we can't update our target CAS table with the values of another table like we can with base tables using DATA Step MODIFY or we can with LASR tables using IMSTAT UPDATE. Delete Similar to table.update, CAS' table.deleteRows deletes rows subject to a WHERE clause. Thus it can delete a single row by filtering on a single primary key value; Delete several rows by filtering on a non-unique field value; Or, delete all of a table's rows by not filtering at all.   Below is the CASL syntax to delete rows from a CAS table:   proc cas ; table.deleteRows / table={ caslib="DM", name="sales", where="region='EAST'" }; quit; Reading Tables During Update CAS tables are available for READ during update. Updates (inserts, updates, and deletes) are applied at the session level and are only promoted to the Global table once complete. So any READ requests during update will see the table as it was prior to the update. READ requests after the update will see the updated table.   In VA there is no cue, like the yellow triangle in VA 7.X, that the underlying table has changed. The end-user sees one version of the table for CAS actions prior to update and another version for CAS actions after the update. The results displayed in VA will not change to reflect the modified table data until a post-update CAS action is run. Below we see the behavior. In the example, a simple report is run and then a table.deleteRows action is run to remove all of the "EAST" region records.   The first image shows a VA report run prior to but viewed after the deleteRows action was run. Note that there is no visual cue that the table has changed. The report still shows the EAST region records even though they have been deleted at this point.   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   We then modify the report which triggers a new CAS action causing the report to utilize the modified table. Note that the EAST region data is now gone   WRITE Locking Concurrent table update requests are simply queued and applied in the order they arrived. So be sure to develop a robust update strategy to ensure no updates are lost.   For a full understanding of CAS table locking during update, check out Uttam Kumar's post, Concurrent data append and update to a global CAS table.     ... View more

Let's take a look at what makes CAS tables big and what makes them small and see what we can do to limit any unnecessary girth. 1. Memory Alignment (8 Byte Boundaries) CAS tables are tightly integrated with computer memory. In many respects, CAS tables are stored as memory and, as such, they share many of the attributes of computer memory. One such attribute is data structure alignment. While this topic is quite technical, it basically means that CAS tables will contain extra padding between fields.   Like LASR before it, CAS conforms to memory alignment requirements by aligning all field data along 8 byte boundaries. So, while CAS allows any length of character data (CHAR(1), CHAR(2), CHAR(3), ....) which is useful for domain constraint restrictions, the net effect of each field's length on memory and disk (CAS Disk Cache and/or SASHDAT) is always rounded up the closest multiple of 8 bytes. So, as the diagram below depicts, a char(5) will be padded with 3 bytes so that the next field can be placed on the next 8 byte boundary. Thus, the net effect of the char(5) field is 8 bytes of storage. Similarly, the net effect of a char(12) would be 16 bytes.   Example: 8 Byte Alignment Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   Growth due to memory alignment should be considered during all aspects of the CAS project from system sizing to data design. For example, splitting out an 8 byte description field to a dimension table (which would normally have a positive impact on sizing assuming a 1 byte foreign key) would have a slightly negative impact on sizing in CAS compared to simply putting the field on the fact table since you would need a minimum 8 byte foreign key on both tables to reference it.   A CHAR(3) and CHAR(3) take up as much space as a CHAR(8) and a CHAR(8) due to memory alignment.   Finally, growth due to memory alignment will occur in RAM (CAS data that is residing in active memory) as well as CAS Disk Cache and SASHDAT since these on-disk locations can act as virtual memory and thus must adhere to the server's memory alignment requirements. 2. Transcoding Multiplication Factor All CAS data is transcoded in UTF-8. UTF-8 is a variable width encoding system requiring 1 byte to store some characters and up to 4 to store others.   When source data is encoded in something other than UTF-8, the data may require more space in CAS than it did in the source system. As a turn-key solution to this potential expansion, CAS offers the NCHARMULTIPLIER parameters (Doc links: CASUTIL, System Option, CVP Libname, CAS Connectors) to define how much to expand character fields in order to avoid truncation.   The NCHARMULTIPLIER parameter can be set anywhere from 1 to 4. So, depending on setting, a CAS character field can be 1 to 4 times as big as the source field. For example, a 100GB (all character) CAS table with NCHARMULTIPLIER=1 would be 100GB (ignoring all other sizing considerations). The same table with NCHARMULTIPLIER=4 would be 400GB.   Effect of NCHARMULTIPLIER=4 on an 8 character field   If the NCHARMULTIPLIER setting is set too low for the character data (e.g. Setting it to 1 for a Double Byte Character Set), then the data could be truncated. So be careful. For a better understanding of SAS and transcoding, see the documentation, and for further understanding, see this paper. There is also a FAQ for Viya and encoding.   While SAS makes an attempt to set the multiplier if you do not specify a value, if you are setting it manually, analyze the encoding and the data and choose the lowest setting the data will allow, or, even better, analyze each field and specifically set its length based on its contents and encoding (making sure to set the NCHARMULTIPLIER to 1 since you're managing the sizing yourself). Going field-by-field will allow for the smallest possible table size.   (If you're really confused about encoding and what it all means, this post should help.)   As with memory alignment, growth due to the transcoding multiplication factor will occur in RAM as well as CAS Disk Cache and SASHDAT. 3. VARCHAR to CHAR Conversion As discussed previously, CAS contains a variable length character data type (VARCHAR) which, in certain circumstances, can save lots of space when compared to the fixed length character type (CHAR).   When server-side loading, CAS automatically maps incoming variable length character fields (e.g. REDSHIFT VARCHAR) to the CAS VARCHAR data type. However, CAS data can often be unknowingly sent to SAS (by running non-CAS-enabled procs, using non-CAS-enabled DATA Step functions, etc.) where VARCHAR fields will be converted to CHAR. This can lead to the field ultimately being stored in CAS with a CHAR data type (fixed length).   To mitigate unwanted VARCHAR to CHAR conversion, be aware that client-side processing in SAS can lead to data type changes. If non-CAS processing is required or if you're just loading CAS via a SAS client, be sure to set the VARCHARCONVERSION option. 4. Using VARCHAR When You Shouldn't The way CAS stores its variable length data types impacts how you should apply the data type. Like SAS, behind the scenes, CAS depends mainly on a fixed length data structure to store CAS tables. Variable length fields are stored in a separate data structure. While a 16 byte "pointer" links the fixed and variable length data. Thus every VARCHAR field in CAS brings with it an extra 16 bytes of overhead.   CAS Variable Length Storage Scheme   Given this overhead, you'll only want to utilize VARCHAR on long character fields that are highly variable in length like descriptions. When applied in those scenarios, VARCHAR will save lots of space. Utilize CHAR for variables under 16 bytes and analyze the lengths of the data values to see if VARCHAR makes sense for longer fields.   As with the previous considerations, growth due to sub-optimal VARHCAR usage affects memory as well as the CAS Disk Cache and SASHDAT. 5. Failover (COPIES) While more about data architecture than CAS table design, the COPIES option will definitely impact the CAS-Disk-Cache representation of the CAS table. The parameter basically specifies how many back-up copies to keep for the CAS table. Since CAS tables are spread over multiple machines, CAS spreads extra copies of the tables' pieces around too so that if one machine fails, the extra copies can be used in place of the pieces that were on the failed machine. Setting the parameter to 1 means that there will be one extra copy. Set the parameter to 2 and there will be 2 extra copies. These copies do not impact memory however. They only impact CAS Disk Cache.   CAS Disk Cache Table Blocks Being Promoted after a Node Failure   Using CAS' COPIES feature for failover is not always required. Often there is no requirement for a specific table to be up 24X7. So COPIES can be set to 0 and, if something happens, the table can simply be reloaded. Additionally other redundancy technology might already be in place such as SASHDAT on highly available HDFS. In cases like these, COPIES should be set to 0 since failover is being handled in a different way.   In general, setting COPIES to 1 doubles the size of the CAS table on disk. Setting it to 2 triples it and so on.... Therefore set it as low as possible to get the smallest CAS Disk Cache representation of your CAS table. 6. Repeated Tables While COPIES is used for failover, the REPEAT option is used for CAS Action optimization (~Query Optimization). Unlike COPIES which creates inactive copies of the CAS table for failover, REPEAT creates a full active copy of the CAS table on every CAS worker node. As mentioned, this is done for optimization. For example, joining a large table that is distributed across all of the worker nodes to a smaller table that is repeated on every node is much more optimal than joining the two when each is distributed. View more information on both the COPIES and REPEAT options.   REPEATED CAS Table   While the REPEAT option can definitely improve performance of some CAS actions, it will definitely increase the overall size impact of the CAS table since you will get n copies of the table where n is the number of worker nodes. So if you had 10 worker nodes, a repeated table would take up 10 times as much space. This impact would be on disk in the form of CAS Disk Cache as well as memory when the CAS table is in use. 7. Indexes I mention CAS indexes for completeness because they definitely increase the size of the table. However, the size impact of indexes is quite small and is a minor consideration considering the positive impact they have on performance. In general you can expect a size increase of 5% or less for unique indexes and 1% to 3% for non-unique indexes depending on cardinality. For more on CAS indexes, check out this post. 8. Compression Compression, of course, is another consideration when designing/sizing CAS tables.  The CAS implementation is very similar to the LASR implementation and our initial tests indicate you should expect a similar performance impact. View the latest info on compression in CAS and what to expect if you choose that route. 9. Auto-Partitioning and Auto-Replication While not technically part of the table sizing process, CAS data architects should be aware of the way CAS creates temporary data structures to optimize CAS actions. Particularly, when performing BY-Group operations, joins, and certain analytical actions, CAS will temporarily replicate the table in one of two ways. Depending on the situation, CAS may "auto-partition" the table or "auto-replicate" it. In auto-partitioning, CAS copies and re-organizes the CAS table according to the BY-Groups required. In auto-replication, CAS distributes full copies of the table to every CAS worker node (like a temporary REPEAT).    Data Movement for CAS Action Optimization   So, while you might save space not replicating (REPEAT-ing) your CAS table up front, it could be needlessly replicated over and over again anyway to satisfy optimization algorithms. Similarly, you should consider partitioning your CAS table upon load to avoid partitioned copies of it being created to satisfy end-user requests from VA and the like. 10. Interactions Among Sizing Considerations Finally, please note that the above considerations do not exist independent of each other. If a CAS table grows due to memory alignment, that growth will also further impact failover redundancy (COPIES), optimization redundancy (REPEAT), etc.. Conversely, increasing a character field's length from 3 bytes to 6 bytes to account for transcoding will have no net effect on sizing due to 8 byte memory alignment. So make sure to consider the complete picture when sizing.   ... View more

I recently posted an article on CAS Computed Columns and, before that, an article on CAS Views. Both of these topics hint at a larger topic in CAS and Viya: Data Abstraction. Basically, data abstraction is when end users are presented with data that isn't exactly what is stored in the underlying data tables.   Within the SAS Viya platform, there are two main tools for data abstraction: CAS Views and Visual Analytics Data Views. Both provide mechanisms for creating "virtual tables," structures that are surfaced to the end-user that look and feel like data tables but that don't really exist within CAS.   (Obviously complex analyses and reports change the data too but we're concerned here with anything that looks and acts like a database view, a BO Universe, or a SAS Information Map, basically anything that is presented to the end-user as the basis for a report or analysis.)   So, let's dive into Viya data abstraction! We'll summarize both CAS Views and VA Data Views, and, in the case of Data Views, the major components they can accommodate, to see how the two compare and when we should use one over the other. CAS Views CAS Views are virtual tables that build on another CAS table (or more rarely a SASHDAT file). They are usable in SAS code as well as all of the Viya visual interfaces which treat them just like CAS tables.   In Viya 3.4, CAS Views support complex and conditional calculated fields as well as complex filtering. Viya 3.4 CAS Views do not support joins or aggregation however. (There is a major push to add star-schema join capabilities in 3.5.)   Capabilities: Complex and conditional calculated columns; Filters   Available In: Code; All Viya web applications (VA, VS, VDMML, Decision Manager, ...)   Limitations: Lack of join or aggregation support in 3.4   Creation Example:   proc cas; table.view / name="virtualMega_corp" caslib="DM" promote=true tables={{name="mega_corp" caslib="visual" computedVars={{name="Margin"} {name="RemainingLife"}} computedVarsProgram="Margin = Revenue - ExpensesMaterial; RemainingLife = UnitLifespan - UnitLifespanLimit;" where="facilityID = 2" }}; run; quit;   For more information on CAS Views, see my previous post as well as the external documentation. VA Data Views Visual Analytics Data Views allow for common reporting constructs to be shared among Visual Analytics reports. Some of these constructs are more reporting/analytics related like hierarchies, spline effects, parameters, and interaction effects. While others are more data management related like calculated items (basically CAS computed columns), Custom Categories (basically CAS computed columns or CAS Formats), and Filters (basically WHERE clauses). Regardless, they are all presented from a reporting and analytics context.   VA Data views have no connection to CAS Views. Data Views are exclusively a Visual Analytics construct and, as such, Data Views are only accessible inside Visual Analytics itself. They are neither accessible from code nor from other Viya visual interfaces like Decision Manager.   Capabilities: Complex and conditional calculated columns; Filters; SQL Joins; Aggregations; Parameters; Custom Categories; Hierarchies; Spline Effects; Interaction Effects; ....   Available In: Visual Analytics only   Limitations: Visual Analytics only   Creation Example:   Select any image to see a larger version. Mobile users: To view the images, select the "Full" version at the bottom of the page.   For more information on Data Views, see this article. Key Components Supported by Data Views While Data Views support a number of reporting/analytics constructs, below we'll dive in more to some of Data Management-like components which compare more to traditional DBMS View capabilities including: Calculated Items Aggregated Data Sources "Data Source Join" Calculated Items While they manifest in generated code as CAS Action computed columns or DATA Step assignment code, Visual Analytics Calculated Items are created inside the VA User Interface. Calculated Items are created within a report definition and only exist within that report. Thus, without the use of VA Data Views, they cannot be shared among other reports. They offer equivalent capabilities as CAS View Computed Columns.   Capabilities: Complex and conditional calculated columns   Available In: Visual Analytics only   Limitations: Visual Analytics only   Creation Example:     VA Aggregated Data Sources Like Calculated Items, Visual Analytics Aggregated Data Sources are created through the Visual Analytics User Interface. As the name would suggest, they are used to pre-aggregate detailed data prior to report execution. Pre-aggregation is useful for combining different levels of aggregation in the same report.   While Data Views are logical constructs, Aggregated Data Sources manifest as global CAS tables. So when an Aggregated Data Source is created, either by itself or as part of a Data View, a new global CAS table is created to house that aggregation. These tables are stored in the report user's CASUSER directory and only exist for the duration of the report creation or report execution exercise however. So they are temporary and thus operate like views. Due to their temporary nature, they are not really usable outside of Visual Analytics. (You could manually identify them in the CASLib and utilize them elsewhere but this would be a hack). So they are usable only inside Visual Analytics and are not usable in either code or by the other Viya user interfaces.   Capabilities: Aggregation   Available In: Visual Analytics only   Limitations: Visual Analytics only; Materialized table paradigm   Creation Example:     For more information on Data Source Joins, see this post.   "Data Source Join" The "Data Source Join" functionality allows the report builder to join-in additional tables as the name would suggest.   It works similarly to Aggregated Data Sources in that the join result is materialized as temporary global CAS table in the report user's CASUSER CASlib. So, as with aggregated data sources, you can't really use these join results in other analyses. Additionally, since join products could be several times larger than the original CAS table, users must be conscience of the size of the join product and account for it in the CAS Disk Cache. Joining could also negatively impact performance if the tables are large and the join process is not optimized.   Capabilities: SQL join multiple tables   Available In: Visual Analytics only   Limitations: Visual Analytics only; Performance and sizing concerns (materialized table paradigm)   Creation Example:     For more information on the VA "Data Source Join" functionality, see this post. Conclusion In SAS Viya 3.4, Data Views offer considerably more functionality than CAS views. While CAS views really only support computed columns and filtering, Data Views support aggregation and join capabilities, along with computed columns and filtering. However, Data Views suffer from a lack of universality since they are only available to Visual Analytics. Additionally Data Views support aggregation and joining via Visual Analytics components that create temporary materialized tables which can bring sizing, performance, and increased I/O concerns. Finally we are anticipating Star Schema join capabilities and increased robustness in CAS Views with the 3.5 release. ... View more

When I last looked at CAS programming, I mentioned that, while much more powerful than SAS, CAS still looked and felt like SAS. In that post, I was talking about DATA Step which is, of course, the heart of SAS programming. In this post, let's look at something completely new -- CAS Actions. In particular, let's focus on the COMPUTEDVARS and COMPUTEDVARSPROGRAM parameters which define the action's computed columns / computed variables.   So, even though the CAS Action paradigm is new, is it still grounded in the SAS concepts we all know and love? (hint, it is....)   CAS Action Overview CAS Actions are the finest grain way to call CAS. They instruct CAS to perform one and only one action, for example: table.loadTable -- Loads a table into CAS regression.glmScore - Executes a generalized linear regression model optimization.solveLP - Solves a linear program clustering.kClus - Perform K-Means clustering In many ways, CAS Actions are similar to SAS procedures (PROCs) in that both generally perform some well defined processing algorithm on input data and produce some kind of output, possibly table(s) and/or listing files (reports). However, SAS procedures also play a role in CAS as certain PROCs are CAS-enabled meaning that they trigger CAS actions to read and process CAS data. In general a CAS-enabled PROC will call one or more CAS actions to perform its CAS processing.   CAS Action Syntax CAS Actions can be called from SAS (Proc CAS), Python, REST, as well as a few other languages. While everything we talk about in this post applies to all of these platforms, we'll use SAS here.   Calling CAS actions from SAS is done using PROC CAS while the language used inside PROC CAS is called CASL. Below is an example of the table.partition action.   proc cas; table.partition / table={name="mega_corp" caslib="visual" computedVars={{name="Margin"} {name="RemainingLife"}} computedVarsProgram="Margin = Revenue - ExpensesMaterial; RemainingLife = UnitLifespan - UnitLifespanLimit;" } casout={name="MGTest" caslib="casuser" replace=true compress=false }; run; quit;   Parameter Syntax Within the PROC CAS block, the desired CAS action is stated (table.partition) and then various optional and required parameters for the action are set. Parameters are generally: Value assignments (e.g. replace=TRUE) Lists of values(e.g. computedVars={...}) Lists of parameters (e.g. casOut={...}) Commas between parameters and list items are optional and lists are enclosed in braces ({}).   Computed Variables Syntax As shown in the example above, we created two variables, margin remainingLife These are listed within the COMPUTEDVARS parameter. The only required attribute for a computed column within this list parameter is the name. However you can define many more attributes for your new column including format, label, length, and precision.   Each variable created in COMPUTEDVARS must be defined in the COMPUTEDVARSPROGRAM parameter. This parameter must be set to a string containing DATA Step code. Generally, the computed variables will be defined using assignment statements. If-Then-Else blocks are also allowed. So conditional logic is possible.   Use Case 1: Materializing Calculations as Columns As in the example above, we can use this functionality to create new columns from calculations on other columns (as well as constants, macro variables, etc.). Any CAS action that reads a CAS table and writes it to a new location (Partition, Index, Shuffle, etc.) can be used to do this.   In the example we used the partition action with no GROUP BY instructions so that it would simply write out a copy of the input CAS table with the new columns included. The new columns are calculated by the CAS action and materialized on the target CAS table.   Use Case 2: Performing Calculations on the Fly for a Specific Analysis While you can use computed variables to create new materialized columns on a target CAS table, you can also use them to perform one-off calculations for a specific analysis. For example, you might create new columns for a more advanced summary analysis as shown below.   proc cas; simple.summary / table={name="mega_corp" caslib="visual" computedVars={{name="Margin"} {name="RemainingLife"}} computedVarsProgram="Margin = Revenue - ExpensesMaterial; RemainingLife = UnitLifespan - UnitLifespanLimit;" groupBy={"unit"} } subset={"mean"} inputs={"profit" "revenue" "Margin" "RemainingLife"}; run; quit;   In this example, the same two fields as before, margin and remainingLife, are created but they will not be stored anywhere. They simply exist for the life of the CAS action and are only used in enhancing the output summary report.   NB: Visual Analytics Calculated Items actually manifest using CAS computed variables as shown in this use case. So they are not stored with the data. Their logic is simply applied when the CAS action for the VA report is run.   NB2: Visual Analytics Aggregated Measures do not manifest as CAS computed variables. Aggregated measures are generally computed after the CAS actions have completed.   Use Case 3: Creating Virtual Fields Associated with a CAS Table CAS computed columns can also be defined when creating a CAS view. While the partition action (or any other action that creates an output CAS table) will materialize any computedVars variables, the table.View action will create a virtual table that references both the materialized columns from the input CAS table and combines them with the calculated columns (computedVars) from the view definition only at query time. An example is below.   proc cas; table.view / name="virtualMega_corp" tables={{name="mega_corp" caslib="visual" computedVars={{name="Margin"} {name="RemainingLife"}} computedVarsProgram="Margin = Revenue - ExpensesMaterial; RemainingLife = UnitLifespan - UnitLifespanLimit;" }}; run; quit;   CAS Views have several advantages over fully materialized CAS tables in that they can save space since some calculated columns can be quite large. Also they offer advantages over temporary "on-the-fly" calculated columns in that the logic only needs to be created once.   Read my previous article to learn more about CAS views. ... View more

You're working late, responding to a request for information (RFI). Having just answered 42,893 questions (that's what it feels like, right?) and you look to the next one on the list:   RFI Question #42894: Does the data storage solution support binary large objects (Blobs)? Short answer: Yes Long answer: Keep reading...   The VARBINARY Data Type Though "BLOB" is common parlance, the actual CAS data type for large binary data is "VARBINARY." Theoretically the VARBINARY data type could hold any type of "file" data, e.g. an audio file, an image, a pdf, an archive file, an MP4, whatever. However, in Viya 3.4, you'll only be able to get certain kinds data into it.   Image BLOBs With the 3.3 release, Viya introduced a set of image processing capabilities. Included was the loadimages CAS action. This action loads image files (e.g. jpg, png, dicom) from path CASLibs. So you'll need to transfer or mount your image files to the CAS controller to get them into CAS.   Using the loadImages CAS action looks like this:   proc cas; image.loadImages / caslib=”CASUSER” path=”list.txt” decode=TRUE pathIsList=TRUE casout={caslib=”CASUSER” name=”imageTable”, replication=0, replace=true}; run; quit;   The PATH parameter can point to a file that lists the images (as shown) or it can be left blank and CAS will load any image files it finds in the CASLib DataSource location.   Once loaded, the image blobs are placed into the _image_ field inside the target CAS table:     Audio BLOBs With the 3.4 release, Viya introduced a set of audio processing capabilities. Like the imaging functionality, this audio package contains its own load CAS action, loadAudio. Again the action uses only path CASlibs. So, like with image files, you'll need to transfer or mount your files to the CAS controller to get them into CAS.   Using the loadAudio CAS action looks similar to the loadImages action:   proc cas; audio.loadAudio / caslib=”CASUSER” path=”list.txt” casout={caslib=”CASUSER” name=”audioTable”, replication=0, replace=true}; run; quit;   Once loaded, the audio blobs are placed into a VARBINARY field inside the target CAS table like with images.   Non-Image, Non-Audio Blobs? What about other file types, e.g. PDFs, xls, doc, ...? Can we load those?   Here, again, the short answer is yes but with some reservations.   Again, the longer answer is below.   Loaded "Documents" are saved as VARCHAR In Viya 3.4, the loadTable CAS Action can load complex text document formats like PDFs, Word docs, and PPTs when used with the FileType="DOCUMENT" option. (This manifests in the User Interface as the Documents Directory Import)   You might think these would come in as BLOBs (VARBINARY fields). However, they are actually converted to text and stored as VARCHAR. This is great for text analytics but if your goal is to keep the document as is, then this will not help you. As any desktop app will tell you, you lose formatting and document metadata when you convert a document to text.     loadImages and loadAudio won't load non-Image and non-Audio files Also, you can't bring in non-Image and non-Audio files with the respective loadImage or loadAudio CAS actions. These actions simply ignore any file types that don't meet their input requirements.   No Connector support for BLOBs In Viya 3.4, no connector supports BLOBs -- images, audio, or otherwise. So if you want to bring in audio or image files from a database, you'll have to stage them on the CAS controller file system and load them using either the loadImage or loadAudio CAS actions.   Working with BLOBs in CAS Only a limited set of CAS actions support VARBINARY columns in Viya 3.4 -- essentially only the imagining, audio, BioMedImage, as well as the ASTORE action set, and possibly a few more.   The majority of CAS actions, procedures, and DATA Step do not support tables with VARBINARY fields and will error in various ways if they encounter them.   The Wider Story: Viya 3.4 Support for Binary Data While CAS only offers limited support for BLOBs, Viya 3.4 (as a whole) offers a considerable amount of functionality for binary data. Considering CAS' BLOB capabilities along with Visual Analytics embedded content capabilities as well as CAS' document conversion capabilities, Viya 3.4's binary data support is robust.   Here are just some of the ways, Viya 3.4 can utilize binary (file) data. Many of which have been mentioned already: Visual Analytics Embedded links (to externally hosted BLOB content) Visual Analytics Embedded Images CAS Image Processing CAS Biomedical Image Processing CAS Audio Processing Word, PPT, PDF, etc. document conversion to text (UI link) (Code link) So, while you might not be able to load all BLOB files into CAS, between Visual Analytics and CAS, Viya can meet most business requirements around binary data. ... View more

SAS Decision Manager 5.2 is fully integrated into the Viya stack with rich business rule functionality as well as custom code and analytic model integration. However, the most interesting aspect of the release has to be Data Grids.   If you're thinking "Data Grids? Huh?" then let me attempt to explain....   "Flat" Decisions and Rule Sets SAS Decision Manager publishes data services called "Decisions" or "Rule Sets." These manifest as REST or batch services with a simple, "flat" input data and output data vectors. In other words, a Decision/Rule Set expects an input row of data and outputs a row of data. For example, a customer churn decision might look like this:     Beyond Flat Services Services with flat input vectors and flat output vectors are great. They can probably handle the majority of an organization's needs. However, most modern service architectures are based on some hierarchical message format, usually JSON or XML. What would something like this look like? If we extend the service above to include a nested address and score structure (so the customer can have any number of addresses and any number of scores), it might look like this:     So an input customer record might have 3 addresses (three rows in the nested "Customer Address" table) and the two scores (lifetime and recent value) would manifest as 2 rows in the nested "Value Scores" table.   Data Grids So what are Decision Manager Data Grids? They are the nested tables. In the service above, the Customer Address structure is a data grid and the input and output Score structures are also data grids.     Data Grids in Decision Manager The Data Grids concept manifests in Decision Manager as a Data Type. For example, if we registered the above input data structure in Decision Manager, it would look like this:     Structuring the sub-table data to fit inside a single Decision Manager Data Grid field is done using JSON. For example, some data inside the above customerAddress data grid might look like this;   Sample Data Grid Data   Processing Data Grids So Data Grids are basically nested JSON table structures stored within fields. How can we use that data in our decisions? Normal SAS functions won't be able to get to the address field within the customerAddress data grid.   To utilize the data within data grids, Decision Manager offers an entire library of functions to parse and process data grids. As an example, below is a function call to return the primary address of a customer from the sample customerAddress above.     There are also functions to count rows, sum amounts, calculate complex statistics, and even construct data grids for output.   Creating Data Grids Decision Manager Decisions and Rule Sets can be deployed as REST services CAS-enabled DS2 packages (programs that run in CAS) In-Database DS2 pacakges (programs that run in-database). While modern REST applications will often format their data as JSON automatically, the other deployment destinations are generally not equipped to easily nest JSON data structures inside table fields. To accommodate this, Decision Manager is delivered with two auto-call macros that can assist with creating data grids.   The %DCM_SERIALIZEGRID macro "serializes" table data into the required Data Grid JSON format. While the %DCM_MERGESERIALIZEDGRIDS macro merges tables that contain serialized data grid content with normal scalar data tables.   Even if you don't plan on deploying to CAS or an In-Database destination, you'll likely still need to use these macros during testing since Decision Manager tests are run in CAS. ... View more

In a previous post, I gave some specific strategies for optimizing Visual Analytics reports by optimizing the underlying CAS tables. Let's take another look at this topic, but this time let's work from a specific example with Viya 3.4.   In that article, I listed a number of techniques for optimizing CAS tables including:   Partitioning / Sorting Reducing Table Width (record length): Using VARCHAR Using User Defined Formats Using Repeated Tables (Of course now CAS also offers indexes to optimize WHERE operations)   In this post, let's look at two of these techniques -- reducing table width with VARCHAR and table partitioning -- to see the relative impact of each in a real situation.   To test these two techniques, we'll perform the same CAS action against different versions of the same CAS table where we've either applied or not applied these techniques.   The Input Tables To get a sense of how much each strategy affects performance, we'll start with three extra large versions of the MegaCorp table. Each of our expanded MegaCorp tables has 46 columns and 7,961,290 rows but they each utilize different storage options. The three versions include:   A wide version that uses long CHAR variables     A thin version that uses VARCHAR variables     A thin version partitioned by the Unit variable     The thin table contains all of the information contained in the wide table but with less padding due to the use of VARCHAR data types. Thus the thin table is only 40% the size of the wide table ( (3732011500 + 292734220) / 98210500000 ). The partitioned table is a copy of the thin table but is partitioned by the Unit field. In our test, we'll group by Unit to see how much pre-partitioning helps in this case.   Comparing CAS' performance against these tables will give us an idea of how much shrinking record width and/or partitioning impact performance.   The Test: Simple Summary CAS Action From bar charts to line graphs, many VA objects utilize the Simple.Summary CAS action. To see this, we can request query diagnostics in VA by pressing cntrl-alt-q. In the example below, we see the Simple.Summary action that produced this VA bar chart.   Bar Chart issuing a Summary CAS Action   To simulate a VA report object and gauge its performance on our input tables, we use the following CAS action. It essentially aggregates the input table by the Unit field producing average values for two measure fields:   proc cas; simple.summary / table={name=”(((Input Table)))” caslib=”dm” groupby={“unit”}} subSet = {“MEAN”} inputs = {“Profit” “Revenue”}; run;   The Results: Executing the above CAS action multiple times against the three CAS tables gave the following results (Please note that these results were achieved on under-sized virtual hardware.  So it is impossible to derive any overall performance information from them.  We are just looking at relative performance here, while using the same hardware) :   Input CAS Table Average CPU Time (sec) % Improvement Wide MegaCorp 98.9   Thin MegaCorp 14.67 85% Thin, Paritioned MegaCorp 13.02 11.2%   Results Analysis The first thing that jumps out from the test results is a huge performance enhancements from decreasing the table's record length. In this test, the reduction was accomplished using VARCHAR in place of long CHAR fields but it can also be done using custom formats (substituting short codes for long descriptions) and/or removing unnecessary fields. While the size reduction in this test was quite significant (60%), even modest size reductions can result in improved performance.   While not as dramatic as record-width reduction, pre-partitioning also leads to a fairly significant reduction in CPU time proving this is an effective strategy for reducing CAS CPU burden and delivering results faster.   Further Discussion While CAS utilizes sophisticated multi-machine big data processing techniques, you can still coax better performance out of it by optimizing the storage options of your CAS tables.   The above results confirm our previous findings that minimizing record length in CAS is critical for optimal performance. So, where appropriate, utilize VARCHAR in place of CHAR, replace long character fields with short codes (and/or utilize custom formats), and remove unnecessary fields.   Likewise, where appropriate, pre-partition your data on common GROUP-BY variables. Good candidate variables will be used often for grouping with a medium cardinality -- not too many values (e.g. Using a unique key would give you as many partitions as rows) but not too few values either(e.g. 4 values for a 10,000,0000 row table would result in too few partitions).     ... View more

Are you new to CAS? Or, maybe you just want to understand it better? Here are 6 key data concepts:   Loaded Tables: CAS is not only an analytic and data transformation engine. It is also a data server. With a few exceptions, you need to load your data into CAS for CAS to analyze or process it.     DataSource Tables: CAS utilizes a two storage area paradigm. Actual CAS tables are temporary, "in-memory" data structures. To provide data persistence, CAS tables are "backed" by a redundant data store. This data store, called the "DataSource" in the CASLib statement, can be one of many formats including files on disk (e.g. SASHDAT, SAS7BDAT, CSV, etc.) as well as 3rd party data providers like Oracle, SQL Server, and Hadoop. When tables exist in the DataSource but have not been loaded in CAS, they are called "unloaded."     Session Scoped Tables: CAS is a session-based server. To utilize CAS, you must first create a CAS session and all the work you do occurs within that session. By default, any tables you create are session-scoped tables, meaning that they are only available to your session. Other CAS users and applications will not see your CAS tables. They are "loaded" CAS tables but are only visible within the session that created them.   Session scoped CAS tables are useful for data processing, ad-hoc analysis, and any other use case that only requires a single CAS session access the table.     Global Scoped Tables: While CAS creates session-scoped tables by default, you can also promote CAS tables to global scope. Global scoped CAS tables can be seen and utilized by any CAS session (assuming appropriate authorization)   Global scoped CAS tables are useful for end-user applications like Visual Analytics and Visual Statistics that requires the table be available to multiple users.     Server Side Loading: CAS performs various functions including predictive analytics, text analytics, and data transformation. It can also load data to itself. When CAS accesses external data and loads that data to itself, it is called "Server Side Loading." The server, CAS, loads CAS.   Server side loading can take a few different forms including: Serial loading Parallel Loading Multi-Node Data Transfer Cross Loading   In each case, CAS accesses data in some defined CAS Data Source and loads the data into CAS.   Client Side Loading: When CAS is loaded by something other than itself, we call that "client side loading."   In client side loading, some external application -- either base SAS, Python, LUA, REST, Java, or R -- starts a CAS session and uses CAS actions to load data from the client into CAS.   As the diagram below shows, the data is loaded only into CAS ("in-memory") and is not loaded to the defined data source. If you want the data loaded to the defined data source, you'll have to issue a SAVE CAS action.   ... View more

Have you heard the term, "cross loading?" It's an important concept in CAS.   The Dual Nature of CASLibs   CAS Libraries (CASLibs) utilize two separate areas:   A temporary in-memory space to hold CAS tables. A permanent "dataSource" to source and/or backup those CAS tables. As a temporary (in-memory) data server, CAS utilizes the dataSource (as its designated in the CASLib statement), as a permanent, enduring data copy. When the CAS server stops, the in-memory data disappears but the dataSource copy remains. The dataSource can be any number of physical data servers including connector sources like SQL Server, Oracle, and Redshift as well as "platform" sources like linux PATH files, DNFS, and HDFS.   The graphic below depicts this dual nature showing both the temporary, in-memory location as well as the permanent "dataSource" location together in a single construct with data moving back and forth (loading and saving) between the two locations:   Platform and Connector CASLibs   When the Data You Need isn't in the DataSource This setup works as designed when utilizing tables from the CASLib's defined dataSource. For example, when the CAS table needed in VA is identical to a Teradata table in the Teradata CASLib dataSource, you simply load the Teradata table into CAS. You can even just push a button in Environment Manager.   What if, however, you want to load tables from an Oracle database to your Teradata CASLib (cross load)? Well, CAS can do that too. The graphic below depicts cross-loading from a platform CASLib to a connector CASLib and vice-versa:   Cross Loading   But why would you do this? Why not just create an Oracle CASLib and load your Oracle data there? Let's look at some scenarios to better understand cross-loading.   Scenario 1: Multi-Source Data Mart In this scenario, you need to load data from numerous, disparate data sources (say Oracle, Hadoop, and an ODBC source) and use CAS' massively parallel data processing capabilities to combine and transform the data into a single-table CAS data mart. We've decided to back our data mart with SASHDAT on DNFS. So our data mart will exist in a DNFS CASLib.   To access the remote data, we create connector CASLibs to each data source. Each CASLib contains, not only the connection to the remote data but also the in-memory logical location as well (even though we don't plan on using the connector CASLibs for in-memory data).   We could, of course, load each source to its own CASLib in-memory space but this creates an extra step and extra I/O since we still need to get the data into the target DNFS CASLib. It's more optimal to load it from the source directly into its final location, transforming it on the way. As the graphic below illustrates, this scenario results in three separate cross loads.   Multi-Source Data Mart Load   Scenario 2: Power-User CASLib In this scenario, we are not motivated by a desired data model but by end-user privileges. Here we have a power-user who has access to every data source in the organization. Our power user, Jill, creates ad-hoc reports and analyses as well as analyzing data to identify issues. To facilitate her initiatives, Jill has her own CASLib. Currently her company has built an Oracle CASLib to access their ERP system, an Hadoop CASLib to access their data lake, as well as a PATH CASLib to access their SAS Data Warehouse. Jill has rights to read from each of the data sources but does not have rights to write into any of the CASLib's in-memory spaces as they are used for production reporting. Given these restrictions, she loads data from the other CASLib's dataSources directly into her own CASLib as shown below.   Power User CASLib   These two examples are certainly not meant to be exhaustive. They are just meant to give you an idea of why cross-loading is possible.   How to Code a Cross Load Motivations aside, coding a cross load is quite easy. The CASUTIL LOAD statement has two CASlib parameters, INCASLIB and OUTCASLIB to facilitate reading from one CASLib dataSource into another CASLib in-memory space. A graphic below shows a cross load running in SAS Studio. Cross Load Code   Concluding Thoughts The dataSource concept in CAS facilitates two related but separate purposes, data backup for in-memory data as well as data access. The way CAS was built, being able to utilize almost any data server as its permanent data store, these two concepts are so interdependent and intertwined that they were implemented together. They manifest exactly the same way after all. Both are simply connection information to some data/file server.   With these two concepts both implemented in the CASLib dataSource, however, we must be prepared to accept and understand cross loading. ... View more

From DBMSs to base SAS to Hadoop Hive, views offer powerful functionality and now CAS is in on the act! In Viya 3.3, CAS' View capabilities extend beyond the session level so that certain CAS Views can be used in VA 8.2.   Let's illustrate CAS views with two use cases -- a simple one, using CAS views as a semantic layer, and an advanced one, using CAS views to transpose a table.   . . Use Case 1:....Semantic Layer   Like SAS Information Maps, views can be used to add end-user "sematics" to data. What are semantics? Think "end-user context." To meet all requirements, you might need to create your CAS data in a less refined format than your end-users are used to seeing it. With views, you can add the context to this "raw" CAS data table after it's loaded.   As a simple example, maybe you store all datetimes as GMT/UTC so that transaction times from different parts of the world are all easily comparable. However your executive team in New York wants to see everything in Eastern time. So you build an "Executive" CAS view that transforms the GMT datetimes to Eastern time.   Here's a (non-DST compliant) version of what such a view might look like.      Simple CAS View    For more information on the table.view CAS action, see the documentation. .   Use Case 2:....Transposing Tables   In Viya 3.3, CAS Views can go well beyond simple field transformations too. To illustrate the power available, let's effectively transpose a CAS table with a view.   Why would we ever do this? Because some VA objects need their tables "long and thin" with generic measure fields whose exact meaning is determined by categorical variables (e.g. waterfall chart) while others need their tables "short and wide" with lots of very specific measure fields (e.g. correlation matrix). With a CAS view that transposes our CAS table, we can satisfy both configurations with the same CAS table saving disk/memory space as well as eliminating the need to maintain and synchronize two versions of the same data. .   CAS GL Table   For this example, we'll "transpose" a general ledger (GL) CAS table. GL tables are great for reporting/analysis because they usually contain ALL of the transaction data. They are fashioned like normal transaction tables but instead of facilitating a single transaction type (orders, touch points, shipments), they facilitate them all using a GL Account field which functions something like a transaction type.   For this example, here is our GL table. Again, it's a transaction table. So each record represents a transaction.     Sample General Ledger Table  Transpose it with a CAS View As is, this table would make a great waterfall chart but the correlation matrix (and other VA/VS objects) require their input CAS table to have the categories of measures strung out as individual columns like this: .   Transposed General Ledger Table  To get our GL table from its raw "long and thin" format into this "short and wide" format, we use the table.view CAS action as shown below: .     WideGL CAS View     The view defines the 4 columns shown above (productRevenue, fixedCosts, etc.) as the amount field (amt) times an INDEX function. The function will return either a 1 or a 0 depending on the text in the account name field (accountNm). If the text is "Product Revenue" then the index function in the productRevenue equation is set to 1 and, consequently, the value of productRevenue is set to the amount field (1 * amt). All the other calculated variables are set to 0 because the text does not match (0 * amt). If the text is "Fixed Costs" then the index function in the fixedCosts equation is set to 1 and, consequently, the value of fixedCosts is set to the amount field (1 * amt). Again, all of the other calculated variables are set to 0 because the text does not match. And so on until the entire table is effectively transposed....   When viewed through SAS Studio, the resulting CAS view looks like this:      WideGL CAS View (SAS Studio)    While not as pretty as the perfectly transposed and reduced table above, it works because VA automatically sums the columns. In fact, it is even better because it maintains the proper number of transactions (records). .   Report and Analyze in VA   Combining the CAS GL table with the CAS wideGL view, we can produce both a waterfall chart as well as a correlation matrix (as well as many other VA/VS objects) from our GL data from only one physical CAS table.     VA Report Combining GL Table and WideGL View    Further Considerations The two examples above should give you an idea of the syntax as well as some ideas for what CAS views can do. Additionally, here are some notes that should be considered when using CAS views.   The varlist parameter is required. Without the varlist parameter, CAS Views will not function in VA 8.2. CAS Views can be created on either CAS Tables or the CASLib DataSource. If built on a DataSource, the required data is loaded into CAS at VA/CAS action execution and dropped after the VA/CAS action is finished. The code in the computedVarsProgram is DATA Step ... View more

There are quite a few notable data management related  enhancements in CAS for Viya 3.3. One such improvement is the new CAS table index functionality. Let's look at it here so we can better understand how and when to utilize it. . Creating CAS Indexes Currently CAS indexes can only be created via CAS Actions. Any action that can create a CAS table (includes a CASOUT option) can also index that table. As an example, the loadTable action is shown below. Proc cas; Session mySession; table.loadTable / path="prdsale.sas7bdat" casOut={name="prdsaleInd" indexVars={"key"} replace=True}; run;   Additionally there is a dedicated Index action within the Table action set. Proc cas; Session mySession; table.index / table="prdsale" casOut={name="prdsaleInd" indexVars={"key"} replace=True}; run;              NB:     In this release, neither CASUTIL nor SPRE (DATA Step, Procedures, etc.) support CAS index creation.   So, client-side sources will have to be loaded and then indexed in a two step process. . Using CAS Indexes In the 3.3 release of Viya, CAS indexes are only used by subsetting WHERE filters. Below shows the same WHERE filter applied to the same 9 million row CAS table with and without indexes. Note that the indexed version is an order of magnitude faster than the un-indexed version.    While not classic B-Tree indexes, CAS indexes are similarly best used on high to medium cardinality fields (fields with lots of unique values). There should always be a measureable reduction in CPU time when using a WHERE filter to subset on such indexed CAS Table column(s). Such CPU time reductions when using WHERE on very large CAS Tables are very important when multiple CAS Sessions are each running resource-intensive CAS Actions. Measures of Elapsed Time reduction depend on one or more the following: How busy is the SMP machine or CAS MPP nodes running the action using a WHERE filter? How much of the indexed table is already RAM-resident? Is there enough RAM for the table to all fit in memory? How many CAS Workers are assigned to the Session running the CAS Action using a WHERE filter? What is the distribution of CAS Table Blocks among these MPP workers handling the query? What is the total time expended on WHERE filtering versus the remaining analytics computations, etc. that the corresponding CAS Action does? . NB:.... In Viya 3.3, CAS indexes are not used in join optimization, for BY-Group processing, or by other process algorithms (e.g Transpose, Machine Learning). They are only used in sub-setting operations. So, they should only be applied when sub-setting is expected. For BY-Group optimization, utilize CAS partitioning and ordering instead of CAS indexes. . Physical Manifestation Unlike other implementations, CAS indexes are stored inside their respective tables.  There are no separate index files (e.g. no .sashbndx files). While it's not easy to see how CAS tables physically manifest, if you did go looking for the indexes, you would just see more data blocks. Similarly, when saved to SASHDAT, indexes manifest within the SASHDAT table. So again, there will be no separate index files. . Size Considerations Since we expect to see more data blocks, it should be no surprise that CAS indexes increase the overall size of CAS tables. As with any index implementation, CAS indexes improve data retrieval speed at the expense of storage space.   The size of the indexes depends mainly on field size and cardinality (the number of unique values) as well as the number of rows in the table.   For reference, here are the tableDetails output from two different indexing strategies. The first shows a single index on a unique, numeric field on a 9 million row table.  The second one shows a non-unique index on a character field on the same table. Indexing the unique field consumes about 117MB while indexing the non-unique field (48 unique values) only takes 49MB. While every situation is different, this should give you an idea of the order of magnitude expected.        ... View more