Create View as Data Source in SAS Visual Analytics 7.4

[ANON4]

Hello all.  I have two tables in memory on the "Public LASR Analytic Server" that I want to use in one report object in SAS VA.  I know that a report object can only reference a single data source.  Since the tables are already in memory, I thought that I could join the tables and create a view instead of creating an output table and use the view as my data source for the report object.  I've tried using Visual Data Builder in VA to create this view by creating a "New Data Query".  Even though I select "Create an SQL Query View" on the Properties tab of the data builder, an output table is created instead of a view.

In my research of the problem, I found the following statement about the LASR Server:

These libraries types do not support views. Selecting the check box applies to the work table, rather than the output table or staging table.  If the source tables and the output tables are in the same SAS LASR Analytic Server library, then the check box is not enabled, and the work table must be a physical table.

http://support.sas.com/documentation/cdl/en/vaug/68648/HTML/default/viewer.htm#p0ci6glvfbc1kin1ipn3u...

Okay, so I guess I found the problem but is there a workaround?  Can I save the view in a different library type where VA can consume the data?  It looks like all of the tables that are currently being consumed by VA are on the "Public LASR Analytic Server" (except for EVDMLA).  Thanks.

[SASKiwi]

The short answer is VA 7.4 reports can only use LASR data sources so views are not an option. Joining the two tables into one table before loading into LASR is the best approach.

[ANON4]

Thanks for the reply. I feel like I’m asking the same thing that the user is asking in this post:

https://communities.sas.com/t5/SAS-Visual-Analytics/using-the-VA-data-preparation-tool-data-query-to...

And related to this post:

https://communities.sas.com/t5/SAS-Visual-Analytics/Executing-a-SQL-query-from-the-Data-Preparation-...

Joining the two tables and uploading it to the LASR server creates a lot of duplicated data in a data structure that I would only currently need for one report. The reason I haven’t joined these two tables already is that it’s a many-to-many join and the dataset size becomes prohibitively large.

[SASKiwi]

How many rows is prohibitively large? Consider summarising one or both of the tables before joining. Unless you want to display detail rows in VA keeping the lowest level of detail in your LASR data isn't necessary. 

[ANON4]

I need detail rows.

When I created this post, I was expecting to find out how to use a view based off what I read here:

https://communities.sas.com/t5/SAS-Visual-Analytics/using-the-VA-data-preparation-tool-data-query-to...

Is the information in this post incorrect or was the affirmative response related to a newer version of VA? Did I misunderstand the poster’s question or the responders responses?

[SASKiwi]

I now think the answer I gave in that post is incorrect, as it is quite clear you can't create views in a LASR library. You can create a view in a "normal SAS library" that references LASR tables, but you can't use a normal SAS library in a VA report.

I suggest you might want to re-think your problem. You can't display millions of rows in a VA list table anyway, the default maximum is less than 100K from memory.

We have summary reports that work off tables with 30M rows - that's why I asked how many rows is prohibitive for you. 

[ANON4]

Thanks for your help. I never intended to display millions of rows. The user would be required to filter the table based on term which does not reach the SAS VA display limit for list tables. When I wrote that the table is prohibitively large, I mean that it doesn’t make sense to create a third table using two large tables in a many-to-many join because this just creates a very large dataset that is taking up space in memory for one visualization on one report. Since all of the data is already in memory, a good solution would’ve been the use of a view, if possible. I run into this situation frequently and I usually just end up creating a stored process for the end users. This is not the solution I want but it seems like the best solution.

[SASKiwi]

A star schema might be another way to do this, but you need to be very careful with performance of these

[ANON4]

Thank you. I am taking a look at that. What I have learned so far about using the star schema in this situation:


1) Creating a star schema creates a view that seems to be available for use on the LASR server.

2) After the view is created using Visual Data Builder, if you open the "Administrator" section in SAS VA, the LASR Tables tab shows the view but it looks like any other LASR table. The reported size of this table in VA is the size it would be if a physical table actually existed which is what this note says is expected behavior. (http://support.sas.com/documentation/cdl/en/vaag/69958/HTML/default/viewer.htm#p0dh2d3grknfnun11snwr...) This was confusing to me so I searched the server for an output table by the assigned name with a sas7bdat extension but it appears that, in fact, a physical table does not exist even though the Administrator tab would lead you to believe otherwise. Oddly, I could not find a view by the assigned name either (sas7bvew extension).

3) I was still suspicious about the memory consumption of the view/table produced by the schema so I ran a query of the LASR server from SAS EG. The EG reported "In-Memory Size" and "Uncompressed Size" of the view/table is close, but not the same as, the table size reported in the VA administrator section. The EG reported "Table Allocated Memory" is about 1/6 the size of the EG reported "In-Memory Size", "Uncompressed Size", and the size reported in VA.

4) I need to test the performance to see if this method makes sense.

5) I need to study more about the LASR server memory. I think this a source of confusion for me...

Thanks for your help.

[SASKiwi]

@ANON4  - Thanks for the feedback. I haven't used a star schema so it is good to get a new user's perspective.