Duplication of data when Dimension and measure combined

building semantic layer
1

I have a report that includes Dimensions and measures, however as soon as I include a Dimension the results get duplicated.

For example the correct data should be,1999 DIP Typ

ID 1999 DIP’s 1999 CSA
DIP03 50,000 10000
DIP03 50,000 12000
DIP08 43,750
DIP08 43,750

but as soon as I add the dimension date I get the following,

ID OPTION DT 1999 DIP’s 1999 CSA
DIP03 01/10/03 50,000 10000
DIP03 01/10/04 50,000 12000
DIP08 01/10/05 43,750 12000
DIP08 01/10/06 43,750 12000

as you can see it adds two additional 12,000 for 1999 CSA that are not correct. How can I resolve this ?. :wah: :wah: :wah: :wah: :wah: :wah: :wah:

Rich :uk: (BOB member since 2002-10-04)

2

You may want to check your joins or the SQL generated to get more idea!

Copy and paste both SQL in the query analyzer and see whether you get the same result!

JShih

JShih (BOB member since 2002-08-15)

3

Hi Rich

Copy and paste the SQL we might able to help you

gowthaman (BOB member since 2002-08-15)

4

Time for us to start guessing.

Do you have multiple data providers in this report? I’m guessing yes.

Have you linked ALL dimensions? I’m guessing no.

Have you put multicube() around your measure? Try it and see what happens.

If none of the above are true, I’m just really bad at guessing. It would help to have a few more details.

Steve Krandel :us: (BOB member since 2002-06-25)

5

Answers as follows,

  1. I have 6 DP, the first lists all employee and there details (around 30k rows), the other 6 relate to Bonus types for example Options, Retention bonus. The nature of these Bonus is that an employee may be award more than one Retention bonus on the same date ie 01/Mar/2000 with each of these having a different payment date. I used a DP for each bonus type to overcome the duplication problem as the universe doesnt have contexts applied; it didnt work :frowning: .

2). No Ive not linked all Dimensions in the DPS as the only dimension that is in all DPs is EMPID, so Ive linked on this

  1. No I have not put ‘Multicube’ around my measures, Ive not used this before what does it do ? (Will try it though).

Thanks

Rich :uk: (BOB member since 2002-10-04)

6

Thanks Steve :cheers: :mrgreen: ,

Mutlicube did the trick

Rich :uk: (BOB member since 2002-10-04)

7

Steve,

the Multicube worked fine on Measures but Ive got my Dimension which are dates are mutlipling, how do you get round this ?

For example I now see,

Date Amount Date2 Amount 2
01 Jan 01 1000 01 Jan 01 500000
01 Jan 01 2000 01 Jan 01

so the measure of amount os correct, but it pads out Date 2 with 01/Jan/01 :roll_eyes:

Rich :uk: (BOB member since 2002-10-04)

8

Can’t you just hide the date2 column?

Steve Krandel :us: (BOB member since 2002-06-25)

9

Unfortantly no I cant hide,

there are lots of Bonus types and this info can vary from Bonus to Bonus.

Is there anyway you can do that multicube function on details ?, Ive spent the day trying to getting it working but cant

Rich :uk: (BOB member since 2002-10-04)

10

You have to link all dimensions that are in common. Those that aren’t in common need to be details or not in the report.

Details don’t rollup the way dimensions do.

What you’re describing tells me that you have multiple records for 1-Jan-01. That’s what’s supposed to happen. You might need to change the column definiton for date2 to make it a max or min of date2.

The other option is to create breaks and fold them up so that you only see the summaries.

Steve Krandel :us: (BOB member since 2002-06-25)