BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jplarios
Quartz | Level 8


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!

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20

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

Data never sleeps

View solution in original post

11 REPLIES 11
LinusH
Tourmaline | Level 20

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.

Data never sleeps
jplarios
Quartz | Level 8

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
Tourmaline | Level 20

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

Data never sleeps
jplarios
Quartz | Level 8

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
Tourmaline | Level 20

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?

Data never sleeps
jplarios
Quartz | Level 8

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

thank you for all your help

LinusH
Tourmaline | Level 20

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

Data never sleeps
jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
jplarios
Quartz | Level 8

or more like a ROLAP maybe?

jakarman
Barite | Level 11

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.

---->-- ja karman --<-----
jplarios
Quartz | Level 8

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 11 replies
  • 1854 views
  • 2 likes
  • 3 in conversation