BusinessObjects Board

Any Designer Expert in Cincinnati

building semantic layer
#1

I have a problem with one of the universes where the measures are getting multiplied because of the 1 to many relationship between a fact table and another table. If there is any desginer expert , who I can talk to or meet in cincinnati would be of help

cravie (BOB member since 2005-04-20)

#2

Look in the designer manual (and here) for "context"s and “chasm trap” or “fan trap” - I think you can find examples of what to do…

There are lots of good BO folk in Cinci (and Dayton);if you get this working, you’ll be one, too ; )

Seriously - you seem to have the classic scenario that causes the “multiplied” answer… There used to be (hope there still is) a whole section in the manual on it, and it’s BO’s bread and better to get you past it.

Let me / us know if you don’t find an answer quickly.

Good luck,
Brent (up here in Dayton)

bdouglas :switzerland: (BOB member since 2002-08-29)

#3

Brent

Thanks a lot for the response. I had earlier submitted a message in the message board and there were couple of responses and I did try them all the suggestions , but it still not help much. I also went thru all the material regarding this in the website and it still did not help

I have also tried to use couple of suggesions from collegues here and still could not resolve it.

Can I send you my issue to your email and can you look at it and give me some suggestions.

[Moderator edit: No, please do not use email to another member for a specific problem – please post specifics in the forum topic instead.]

Any suggestions from you would be appreciated

Thanks

cravie (BOB member since 2005-04-20)

#4

I’m not sure I could fix it, I’m really more of an environment guy… I just know we a had very similar issue when we started here, and the problem was a join to 2 “parallel” tables.

The net result of that was the numeric value in the one table was being duplicated 1 time for each record in the other table. I would think setting a context path would be a start here.

I wish I were more help, keep looking and let me know how much progress you make.

B

bdouglas :switzerland: (BOB member since 2002-08-29)

#5

Hi Cravie,

Can you please be more specific on the options that you had tried to resolve this issue.It would be easier to diagnose, if you had missed anything in implementing the resolutions.

Thanks and Regards,

prithvi (BOB member since 2005-09-08)

#6

There are Business Objects partners available in Cincinnati such as Cardinal Solutions Group.

Andreas :de: (BOB member since 2002-06-20)

#7

What kind of sql is generated (one statement or multiple due to contexts ) ? Are there measures coming from two different tables ? (-> generaly gives problems and needs to be fixed with aliassing and contetxs)
You seem to know what join (which relationship) causes this effect, any possibility to alter this join so it doesn’t multiply the results ?

A bit more info could give you a solution
(earlier topic: https://bobj-board.org/t/80761)
Depending on the version you’re using you could for example create a derived table on the VW_DIM_PDT_LVL_CAS_BAS with a distinct on the join and dimension columns.

Regards,

Gerard

highandstoned :netherlands: (BOB member since 2005-08-01)

#8

…looked at your trap.unv.

The VW_DIM_PDT_LVL_CAS_BAS, it’s a view right ? Look at the sql for this view, identify the tables used for the dimension colums mentioned (Case UPC Number and Consumer UPC Number) and import that/those tables too in the universe. Then link that table directly to the fact and use it for the objects.
(also link it to the VW_DIM_PDT_LVL_CAS_BAS but used that join for the other context)

Regards,

Gerard

highandstoned :netherlands: (BOB member since 2005-08-01)

#9

Gerard

I am attaching the universe file for your review. This one does not have the view. This one still gives the problem.

1.The measure that is having the problem is Ord Lin Adj Qy in Order adjustments.

  1. The table which is causing the problem is FCT_CAS_CON which has a 1-m relationship with table FCT_ORD_ADJ_DLY_STO_CAS. For each CAS_UPC_ID in table FCT_ORD_ADJ_DLY_STO_CAS there are CAS_UPC_ID in table FCT_CAS_CON because of which the measure Ord Lin Adj Qy out of table FCT_ORD_ADJ_DLY_STO_CAS multiplies by so many times.

I have tried using a combination of aliases and context and it didn’t help. Can you please look at this universe and give your suggestions
Loss.unv (59.0 KB)

cravie (BOB member since 2005-04-20)

#10

I’m running out the door, but the first thing I notice is that you use sum() in your object definition AND you have it set to SUM in your properties tag (sum when aggregated). That’s redundant at best.

I would also be curious as to your data model - you join on that upc-id field, is that setting you up to join more than you really want at the fact level?

I’ll look again tomorrow, but maybe there is a quick fix in there… Your contexts look off, but that’s a harder thing to diagnose (especially without data / data model)…

Much luck,
Brent

bdouglas :switzerland: (BOB member since 2002-08-29)

#11

sum and sum redundant ?? That’s how it’s normally done. However, in this case you can try to remove the sum in the select statement but I don’t think that will do it. (or perhaps will do it but will give problems afterwards or performance problems)

Like Brent says, it’s difficult to do this without data/model so I’m guessing here.
You write:
FCT_CAS_CON 1:M FCT_ORD_ADJ_DLY_STO_CAS
But in your universe it’s:
FCT_CAS_CON M:1 FCT_ORD_ADJ_DLY_STO_CAS
(which explains the multiply)

The table FCT_CAS_CON is only used as a link table between FCT_ORD_ADJ_DLY_STO_CAS and DIM_PDT_LVL_CON ?
Looks like the CON_UPC_ID also should be in the FCT_ORD_ADJ_DLY_STO_CAS table

In the FCT_CAS_CON, can a CAS_UPC_ID have multiple DIFFERENT CON_UPC_ID and if so, do these DIFFERENT CON_UPC_ID lead to different dimension items in DIM_PDT_LVL_CON ?
If both answers are yes, then you’ve got a problem which needs to be solved both in the universe AND in the reports you generate from it.
(think of this: a car is painted in two colours. You want the number of cars per colour and you want a correct total number of cars…)
If your answers are yes and no, then replace the FCT_CAS_CON with a derived table like (select max(CON_UPC_ID) as CON_UPC_ID, CAS_UPC_ID from FCT_CAS_CON group by CAS_UPC_ID).
(this also works if your answers are no, no)

Regards,

Gerard

highandstoned :netherlands: (BOB member since 2005-08-01)

#12

Gerard

We are in version 5.1.9 and I wouldn’t able to do derived tables in 5.1.9

cravie (BOB member since 2005-04-20)

#13

ah…version 5…

what about my questions ?

Can you build a view on the datbase ?

Regards,

Gerard

highandstoned :netherlands: (BOB member since 2005-08-01)

#14

The idea of a derived table / view is a good one, High’s right… We live and breathe views here, would not be able to generate universes if we didn’t simplify / denormal our data.

The Many to one vs 1 to many is a good thing to look at - nice catch (I was running to class, so I only half-looked).

I figured my note on the SUM() was more a personal preference, but I had to pass it on. I really would be more concerned as to what you are returning in your SQL, and that means joins and keys in your database.

Much luck!
Brent

bdouglas :switzerland: (BOB member since 2002-08-29)

#15

Gerard Brent

The answer is Yes and Yes to both questions

Is there anyway this issue can be resolved.

If you want us a build a view, what exactly would the view have?? Any suggestions for the views?

Thanks a lot for all the time and help

cravie (BOB member since 2005-04-20)

#16

If you give folks more specific information, they can suggest better possible solutions.

Are you using an ERP system with effective-dated rows for dimensions? If so, then one should be able to figure out a View on the dimension(s) that return only the most current effective-dated row.

Else, please let us know what is causing you to have the “many” rows that are causing the multiplying problem.

Anita Craig :us: (BOB member since 2002-06-17)