BusinessObjects Board

On having multiple links between DPs

building reports desktopintelligence
#1

Greetings all,

I’ve been stumped by this issue for awhile, and now am humbly seeking your wisdom.

The issue is: I have database with a log of “incidents”, or “defects” occuring in the infrastructure. It’s a DP called DP_Issues. I am trying to tie the “incidents” to the possible “causes”, which are described in another database. Let’s call it DP_Causes.

The “incident” can be characterized by one of the two dimensions – either Dim_1, which is the preferred, but not always available, or Dim_2, which is less reliable, but can be used to second-guess the “cause”.

On the “causes” side, I also have these dimensions. For any given “cause”, both would be available.


DP_Issues: Dim_1, Dim_2, Defect
DP_Causes: Dim_1, Dim_2, Cause

I need to produce a report that will list:


Cause, Defect, Count(Defect)

The way I’d like to do that is for any given Dim_1 in DP_Issues to try checking if there is Dim_1 with the corresponding value in DP_Causes, and using the associated Cause value; and if not, then trying to make a match between Dim_2’s.

I am runnin BO 6.51PS2. Naturally, it’s not possible link two DPs twice, so I can’t link DP_Issues:Dim_1 to DP_Causes:Dim_1 and DP_Issues:Dim_2 to DP_Causes:Dim_2 at the same time.

A logical way out of that would be to create TWO copies of DP_Causes, and to link DP_Issues:Dim_1 to DP_Causes1:Dim_1 while linking DP_Issues:Dim_2 to DP_Causes2:Dim_2. And that works, right up to the part when I try to construct a variable that makes use of this:


<AnyCause> = If(IsNull(<Dim_1(DP_Issues)>) Then <Cause(DP_Causes2)> Else <Cause (DP_Causes1)>

What happens is that BO tells me that these two variables are not compatible. There fact that the linkage between them is a two-hop is apparently putting BO off.

I studied the Search results for all imaginable keywords, and haven’t found anything resembling my issue. Can anyone recommend a workaround, or a new way altogether to look at my problem?

Many thanks,
Val.

gorilla800lbs :us: (BOB member since 2007-08-08)

#2

Hi Val,

Ideally, you would sort this at an ETL level…

You would be better off resolving this at run time using CASE statements in the Universe level.

Unfortunately, I don’t think you will have any joy solving this on the report… :?

Mak 1 :uk: (BOB member since 2005-01-06)

#3

Hi,

In your variable check to see that the objects (from the 3 data providers) are either linked (synch’d) or details associated to the linked dimension.

If they are, and you still have the issue, try this: move your data providers to free hand sql. Create the details associated to the linked dimension in the data manager (cube). Then create your variables.

The following link may help or not : Solving the Mystery of Data Provider Synchronization

Regards,

Steve

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

#4

Thanks for the responses!!

Yes, sorry, I forgot to make it clear initially. I am using free-form SQL to bring the data in from two different Sybase ASE 12.5 databases.

The variables – Cause (DP_Causes1) and Cause (DP_Causes2) in my case – are of course defined as Details and are subordinated to the Dimensions that are used as the links to DP_Issues. Still, Cause (DP_Causes1) and Cause (DP_Causes2) continue to be “incompatible”.

I think the issue here is in this “two degrees of separation” for the link between them. Somehow it’s not link-y enough.

It’s an interesting challenge to solve, and I’d love to ponder it for some longer, but there’s a client breathing inpatiently into the back of my neck…

gorilla800lbs :us: (BOB member since 2007-08-08)

#5

Sorry, I should have taken the time read this a bit more clearly earlier, CASE statement what was I thinking… :oops: :slight_smile:

Still, it certainly looks like a bit tricky to solve .

Have you tried creating another variable, testing your nulls first, thus returning a 1, sorry if my syntax is wrong I’m in Webi mode… :mrgreen:

If(IsNull(<Dim_1(DP_Issues)>) then 1 else 0))

as .

Then filtering on your 1s and applying your second bit of logic to that…?

I think your ‘Two degrees of Seperation’ is the key to why this may not be able to be done…

Mak 1 :uk: (BOB member since 2005-01-06)