How Does SAS VA resolve Bridge tables?

[jplarios]

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!

[LinusH]

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.

[jplarios]

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.

[LinusH]

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...

[jplarios]

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

[LinusH]

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?

[jplarios]

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

thank you for all your help

[LinusH · Accepted solution]

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

[jakarman]

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.

[jplarios]

or more like a ROLAP maybe?

[jakarman]

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.

[jplarios]

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.