Getting one row per linked dimension

Hi all,

I have 2 DP from different universes, linked together on SSN. One DP provides detail about a program, while the other DP uses the SSN to find all other programs the person is enrolled in.
The DPs are as follows

DP1 ( SSN, A, B, C, D)
DP2 (SSN, Program Id, Program Desc)

There exists a One to Many relationship; ie one person can be enrolled in many programs.

When i link the DPs, and filter out null values(on some cols), I get one row back per SSN and for all those SSN’s where I should get multiple rows back, the Program Id and Program Desc fields are empty.
I have linked the DPs, followed the “Bickerton’s Presentation” and everything else seems to be working fine.
Please let me know what am i missing. Thanks alot for your help.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

Hi Kashif,

Are you linked dimensions at the the lowest level of detail? My guess is no, because one SSN can have multiple programs. Can you add program id (I believe this object is at the lowest level of detail) of to both queries and link on this?

Secondly, what is your filter logic?

Regards,

Steve Bickerton


SteveBickerton :canada: (BOB member since 2002-08-15)

Hi Steve,

Lucky me that I got the presentation man 8)
Steve, i have not linked dimensions on program id. The only common dimension between the 2 DPs is SSN; hence i cant add program id to the second DP and link on it.(unless i ask for an ETL change).

The filter logic is to avoid null values. The first DP has a column called “Case Number” which is unique. The firse DP returns 450 rows and these are the 450 rows for which i need the program-id (from the second DP). I have used

Not( IsNull)

to avoid null values as first DP is the one which decides how many records should i get back.

I hope I was able to explain the scenario.

Thanks for your help.

Kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

Kashif,
Are you sure (from the backend) that these SSNs have One/Multiple program ids associated to them. Just trying to understand if the SSNs really have Programs associated. Try to add the SSN# from the second DP and see if it shows values along with the null Program id and description.


Ashokkumar (BOB member since 2004-05-20)

Ashok,

Thanks for your reply. Yes I am sure that these SSN’s have multiple program ids associated with em.
The other problem that I am having now is when I try to display the results from 2 DPs in one table, it converts everything into Structure view…and even if i press the view Structure toggle again; does not change it back to normal. :reallymad:

Any suggestions.
Also, it is running very very slow as I have thousand of rows from one DP.

Thanks again.

kashif


Kashif Saeed :pakistan: (BOB member since 2004-06-02)

Also, I am getting an un handeld exception.

Unhandled Exception
Code: c0000005
Description: EXCEPTION_ACCESS_VIOLATION

Seems like everything is happening in one report :slight_smile:


Kashif Saeed :pakistan: (BOB member since 2004-06-02)