topic Re: How Does SAS VA resolve Bridge tables? in SAS Visual Analytics

How Does SAS VA resolve Bridge tables?

jplarios — Tue, 09 Sep 2014 15:22:35 GMT

Hello,

So if there is a many to many between a fact and a dimension- how does SAS resolve when wanting to sum up the measures dynamically without double counting due to the many to many between the measure and the attribute?

Thank you!

Re: How Does SAS VA resolve Bridge tables?

LinusH — Tue, 09 Sep 2014 16:25:09 GMT

Can you describe in more detail fanout the M-M relationship, what causes it and why you need it?

Generally speaking (not VA specific) I always try to have 1-M relationships between dimensions and facts. Bridge tables and other "non-standard" models is usually to complicated for users to consume.

Re: How Does SAS VA resolve Bridge tables?

jplarios — Tue, 09 Sep 2014 16:56:24 GMT

thanks Bridge tables or if viewed as objects are standard phenomena in data. Can I surface such model in SAS VA via a SAS Information map's intelligent query builder (http://support.sas.com/resources/papers/proceedings12/033-2012.pdf)? It seems that an Information Map's intelligent query builder can achieve this; I am not able through the data builder.

Re: How Does SAS VA resolve Bridge tables?

LinusH — Tue, 09 Sep 2014 17:14:15 GMT

I haven't tried it myself, but according to the documentation you are able to import information map logic so it becomes data builder SQL logic.

But from what I try to understand, that is not your issue. You still need to resolve the potential M-M joins, and my guess is that you need change the model in VA, and the use data builder logic to do the transformation from your original model.

Letting us understand more about your actual requirements would help us to help you...

Re: How Does SAS VA resolve Bridge tables?

jplarios — Tue, 09 Sep 2014 17:39:51 GMT

Linus,

ok so if I had a table with the following:

TIME ID CAT1 CAT2

2004 1234 1 1

2004 1234 2 1

I want to count the number of IDs grouped by CAT1 or CAT 2 which should be 1 for each respectively and have a total of 1. A dynamic visualization where the user can group by any attribute like examplewould look like:

Cat1 Cat 2

Total 1 1

Gran Total 1 1

Thank you for all your help

Re: How Does SAS VA resolve Bridge tables?

LinusH — Tue, 09 Sep 2014 19:23:51 GMT

Just to be clear, are CAT1 and CAT2 two different values in the same dimension?

Are the values 1 and 2 codes for something or measures?

Re: How Does SAS VA resolve Bridge tables?

jplarios — Wed, 10 Sep 2014 11:43:51 GMT

cat1 and cat2 are the dimensional attribute values they can be red =1 blue =2

thank you for all your help

Re: How Does SAS VA resolve Bridge tables?

jakarman — Wed, 10 Sep 2014 13:58:21 GMT

I think VA looks more like: OLAP cube - Wikipedia, the free encyclopedia without the difficult cube building process as that is done in memory.

Bridge tables are from the table - relationship modeling done with tools like DI.

Re: How Does SAS VA resolve Bridge tables?

LinusH — Wed, 10 Sep 2014 15:52:55 GMT

Ok, it seems that you have a factless fact table.

One thing you could try is to use distinct count as an aggregated measure. What do you think?

SAS(R) Visual Analytics 6.2: User's Guide

Re: How Does SAS VA resolve Bridge tables?

jplarios — Wed, 10 Sep 2014 15:56:02 GMT

or more like a ROLAP maybe?

Re: How Does SAS VA resolve Bridge tables?

jakarman — Wed, 10 Sep 2014 16:33:32 GMT

Not really molap rolap holap are of old times http://www2.sas.com/proceedings/sugi25/25/dw/25p133.pdf

We are going into a next one. In memory analytics removing the constraints of a RDBMS or slow processing by hierarchial tables. No external storage anymore. Fast enough no need for preparation anymore.

Remember lotus 123a as of 1985 it all started with spreadsheets from downloaded sequential files. Then needing a dwh a datamart precalculated reports and now back to the future.

Re: How Does SAS VA resolve Bridge tables?

jplarios — Wed, 10 Sep 2014 16:59:45 GMT

wow- so no need to clean up or data prep. This takes abou 60 to 80% of the analytical/visual piece. wow - reduced to 0 %. at last no more code. all the data integrated in memory. can't wait.