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.
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?
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.
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.
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.
Any suggestions.
Also, it is running very very slow as I have thousand of rows from one DP.