BusinessObjects Board

Multiple Measures

building semantic layer
#1

Hi,

I am in some trouble with multiple measures selection. I am having two tables (A & B) joined as 1:1. I have one measure in table A (MA1) and two measures + one dimension from table B (MB1, MB2, DB1).

Multiple measures in parameters is not selected:
Now, when I select MA1 (from table A) and MB1, DB1 (from table B), I get correct results. When I add MB2 with this, I get wrong results.

Multiple measure in parameters is selected:
Not getting correct results by selecting MA1 and MB1, DB1 (although getting seperate SQL queries).

I want a report where I have MA1, MB1, MB2 and DB2 (one measure from one table and two measures + one dimension from second table).
I am smelling a chasm/fan trap here. Please spare some time to help me :frowning:

Thanks.
P.S.: Ideally, I wanted to have all measures coming from single table/context which is not in this case (considering the universe/model)

Smith85 (BOB member since 2009-12-10)

#2

Allthough you specify 1:1 for the join cardinality, this does not have any effect on the results. It’s only needed to detect contexts.
I suspect that the real join is more like 1:N or M:N.

Create an alias for table B, join this alias to table B and A.
Create separate contexts for both joins.
Create the dimension object from the alias, the measures from table A and B.

If you need more help, I suggest giving examples for the data in the tables and describing the wrong results.

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

#3

Thanks for the quick reply. I am little confused in the contexts here :frowning: Currently, I have two tables A & B. I created alias for table B (B’).
Now, the joins created:

  1. A & B’ (Context 1)
  2. A & B (Context 2)

The objects I select on webi:

  1. Dimension from B’
  2. One measure from A + Two measures from B

But, when I run the report, I get two different tables (and I suppose thats because I now have two different queries). By wrong results I meant that the count information is not correct when I view the report containing three measures (two measures and one dimension from one table + one measure from other table) with multiple measure selection option not ticked and without any alias/contexts.

Could you please help me out with the contexts here… :cry: And thank you so much for your time…

Smith85 (BOB member since 2009-12-10)

#4

Which is meant to be the way the tables sit?
As highsandstoned mentioned, it’s probably not a 1:1.
Are there other tables joined to either of these?

If you have A -< B and measures from both, create an alias of A, A_Fact
take your measures from A_Fact, any dims from A and anything that should be from B, from B.

Your problem is that a fact from A has no relation to a dimension from B.

I cannot relate my batch shipping cost measure from A directly to any given order line reference number in B for example.

#5

if you get two tables in webi, there’s something wrong. Did you alter the universe and saved and exported it back to the repository ?

In reading your post again, you created:

but it should be

  1. A & B’ (Context 1)

  2. B & B’ (Context 2)
    Then measures from A and B, dimension objects only from B’

  3. as MarkP suggested that there’s no real relation between A and B ? I do hope that’s not the case here ?

  4. as asked previously, give some examples of the data in the tables and the results expected/retrieved.

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

#6

The model is something like this:

Table A and Table B joined on Universe (Table A.Column = Table B.Column).
Table A:
Dimension Object - A1
Measure Object - Count(distinct(A1)) - Let’s name it MA1

Table B:
Dimension Object - B1
Measure Object - Count (B1) - Let’s name it MB1
Measure Object - Oracle Extract function used to compute a value (I have made this as Measure) - Let’s name it MB2

Now, I want to create a report where I have :

B1, MB1, MB2, MA1 (These should be grouped by B1 dimension). As of now, the cardinality stands out to be 1:1.

When I run B1, MB1, MA1 I get correct results, but when I add MB2 I end up with incorrect records.

I am sure I am doing something horrible here… :cry:

Smith85 (BOB member since 2009-12-10)

#7

What does that mean? What does it do? Does it do it on the fly rather than being an actual table value? Does it belong to table B? Can it be pre-calculated?

Telling us what A and B are may help - e.g. region and sales

#8
  1. the cardinality setting in the universe does not have ANY effect on results produced!

Is this really a measure ? extract function returns a date ? You classified the other measures with aggregate functions, is there any aggregate function for this measure ? If not, it will be in the group by clause!!! (thus actually behaving like a dimension.

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

#9

Yes, I was pondering upon the same issue. This object is calculating the difference between start datetime and end datetime (and the result is the time difference in minutes, which should be aggregated on report layer to have sum functionality on this column). Also, the results should only be grouped by one dimension (of second table).

I guess I should just have a look at the design once more (just to make sure we have the right objects at the universe and report level (variables)) :hb:

Thank you so much Mark & highandstoned, really appreciate your time and efforts :). May be, I well post a reply after some introspection of the design :stuck_out_tongue:

Smith85 (BOB member since 2009-12-10)

#10

I believe creating the object for extract function (time difference) on report level would solve the problem as the report will get grouped by on only one dimension (with both measures from different tables). The idea is not bad (although, I loose slight re-usability here)…

Once again, Thank you Mark and highandstoned :slight_smile:

P.S.: I will post one final analysis of the problem (with the solution once I am done with it, guess it will help people with similar issue in future).

Smith85 (BOB member since 2009-12-10)

#11

Can this time difference be calculated at the time that the database is populated rather than at report run time?

#12

I wish we could do that Mark, seems slightly tough now to have this change :oops: Creating it at the report level should solve the problem (however I am skeptical about the performance of the report). I am still exploring for better solutions if any…

Smith85 (BOB member since 2009-12-10)

#13

As long as this time difference is calculated between two columns in the same table, I don’t see what has to do with his problem ?
(with regards to incorrect results, not to performance 8) )
As long as you do something like sum(dateend-datestart) and not just dateend-datestart.

I do worry about the count(distinct). That could easily lead to false results.

Some general rules:

  1. create measures with aggregate functions in the select
  2. don’t create measures and dimensions from one universe table, use aliasses
  3. don’t create objects from a table, alias every table (so you would have something like TableA, DimensionAliasTableA, MeasureAliasTableA)
  4. read the designer faq

BTW: I still have no clue about the data in your tables, so I guess MarkP and I are still a bit clueless about the actual situation… :?

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

#14

I figured out my mistake (and please don’t boil me in hot water for this :stuck_out_tongue: ). The extract function was written without the sum part. I rectified it sum(extract()) and it is working fine now.

Time for a coffee after that stupid blunder :wink: (and of course Cheers to Mark and highandstoned :smiley: )

Smith85 (BOB member since 2009-12-10)