Oracle Business Intelligence

Products Banner

OBIEE (12.2.1.4) I need an alternative to using a "union" in an analysis

Received Response
395
Views
17
Comments

This is my first post, hopefully I explain myself thoroughly. I'm using OBIEE 12.2.1.4.

This is a scenario which I've made up.  My real life scenario is very similar to this. I'm having issues using "unions" in my analyis and getting a correct Grand Total %.

So here goes....

I have a database table with Attempts and Completions for time intervals of "week1" through "week10".

I have a dashboard prompt which is a multi-selectable choice list in which you can choose one or more weeks (week1 through week10), represented by a Presentation Variable called PVWeekGroupA.

I have another similar dashboard prompt which is a multi-selectable choice list in which you can choose one or more weeks (week1 through week10), represented by a Presentation Variable called PVWeekGroupB.

The values in these presentation variables can overlap, i.e., you can have "week1" in PVWeekGroupA and PVWeekGroupB.

In the dashboard prompt for Week Group A, I choose week1, week2 and week3.

In the dashboard prompt for Week Group B, I choose week4, week5, and week6.

Jones completed 85 of 130 in weeks 1 through 3 (Week Group A), and 50 of 74 in weeks 4, 5, and 6 (Week Group B). For the season, he completed 145 of 270 (noted by All Weeks in the Week column)

Smith completed 150 of 200 in weeks 1 through 3 (Week Group A), and didn’t play in weeks 4 through 6 (Week Group B). For the season, he completed 180 of 250 (noted by All Weeks in the Week column)

Green didn’t play in weeks 1 through 3 (Week Group A), and completed 30 of 45 in weeks 4 through 6 (Week Group B).  For the season, he completed 120 of 190 (noted by All Weeks in the Week column)

The output should look like this:

Qtr 1

Qtr 2

Qtr 3

Qtr 4

Grand Total

Name

Week

Att

Cmp

%

Att

Cmp

%

Att

Cmp

%

Att

Cmp

%

Att

Cmp

%

Jones

All Weeks

70

35

50%

60

30

50%

65

35

54%

75

45

60%

270

145

54%

Jones

Week Grp A

20

12

60%

25

15

60%

35

22

63%

50

36

72%

130

85

65%

Jones

Week Grp B

22

15

68%

23

15

65%

19

14

74%

10

6

60%

74

50

68%

Smith

All Weeks

100

85

85%

50

30

60%

40

25

63%

60

40

66%

250

180

72%

Smith

Week Grp A

50

30

60%

50

40

80%

50

40

80%

50

40

80%

200

150

75%

Green

All Weeks

45

30

67%

45

30

67%

45

30

67%

55

30

55%

190

120

63%

Green

Week Grp B

10

7

70%

15

7

47%

10

8

80%

10

8

80%

45

30

67%

I can get this to work by using “unions” in the analysis. I need Grand Total %, and from what I experienced and read about, there is an OBIEE bug when trying to get that Grand Total % while using unions in the analysis.

So I’d like a solution where I don’t need to use “unions” in the analysis.

If you'd need more information, please let me know.

Thanks,

Tom

Tagged:

Answers

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    Before abandoning the union query you built, I'm curious about whether it was a two-leg union or a three-leg union.

    In analogous situations, I've union-ed one query for Grp A, one for Grp B, and a third for "All Weeks".

    The "All Weeks" query contains its own calculations on the whole time period, so the row percentages are correct.

    Alternatively, you can turn to column filters for a non-union query.

    In that case you'll have 3 separate columns for GrpA, GrpB, and All Weeks X 3 (for attempts, completions, and %)

    Grp A column formula would look like : FILTER(Count(table.Attempts) Using table.Weeks IN (@{GrpA}{1,2,3}) This may need added apostrophes, depending on the data types.  It may also need a sum rather than a count, depending on your data.

    You can figure out the other 8 columns from there.

    Jerry

  • Tom D
    Tom D ✭✭✭

    Jerry,

    This would be a 3-leg union.  All Weeks w/ Grp A w/ Grp B.  The "All Weeks" Grand Totals for Att. and Cmp. are correct, but the Grand Total % is incorrect every time.

    It seems to always take the % that is in the very first Att/Cmp/% group.  In my example, it shows 50% for Jones, 85% for Smith, and 67% for Green.

    The same is true for Week Grp A and Week Grp B Grand Total %.

    I would like to use column filters, but my users don't want to see those "extra" columns.  They want to see "All Weeks", "Week Grp A", and "Week Grp B" in the same column.

    Any ideas on how to do this in the column formula?

    Thanks for your response,

    Tom

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    Hi Tom,

    Can you provide more information about your data? You've described your table as covering weeks 1-10, but you're results show quarters 1-4.

    Are Quarter and Week coming from a structured time dimension?

    You've described your desired results in detail, but not what you're working with.

    Jerry

  • Tom D
    Tom D ✭✭✭

    Jerry,

    Think in terms of a football game for my example.

    There are 4 quarters per game, 1 game per week. In my example, week is the only element in a structured time dimension.

    Let me know if this is not clear.

    Tom

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    I had assumed the "Qtrs" were calendar quarter, so you can see how unclear it was for me.

    Starting over,

    If the whole example is a football season, then I have to assume a single record looks something like:

    Week, Qtr, Name, Attempts, Completions.

    Because your GrpA and GrpB can overlap, I believe it has to be a union of independent queries.

    My own practice is to place an identifier column in the first position of each union "leg".

    So, the Grp A query starts with a text column with the formula of 'Group A', and is filtered on table.Weeks IN (@{PVWeekGroupA}['@']{'week1','week2','week3'})

    the Grp B query starts with a text column with the formula of 'Group B', and is filtered on table.Weeks IN (@{PVWeekGroupB}['@']{'week4','week5','week6'})

    the 'All Weeks' query has no filter on the Weeks column.

    Start with the All Weeks query to ensure your calculation produce the correct percentages. Use a pivot table to get the desired result format.

  • Hi Tom,

    if I understand correctly you have to calculate a sort of an attribute "week", not filtered measures.

    With the filtered measures solution, you will not be able to obtain exaclty the same layout you show in your example.

    You say that "the values in these presentation variables can overlap, i.e., you can have "week1" in PVWeekGroupA and PVWeekGroupB", I do not see any other solution of using union query if you want exactly this layout and you permit the overllapping of the 2 groups.

    Regards

    Massimo

  • Tom D
    Tom D ✭✭✭

    Jerry,

    I started with the All Weeks query (no unions) and the Grand Total %'s were correct.

    I added a union with GroupA and the Grand Total %'s are incorrect, as I suspected. I used the filter that you mentioned above. (table.Weeks IN (@{PVWeekGroupA}['@']{'week1','week2','week3'}) ).

    The percentage total for Week 1 (and Week's 2 and 3, for that matter) is correct.  It's only the Grand Total % that is incorrect.  BTW, the Grand Total Attempts and Completes are correct.

    In the '%' Column formula, i have:

    percent.PNG

    My pivot table aggregation rule for the '%' is as follows:

    pivot.PNG

    So, I'm at a loss.

    There are several bugs that I'm aware of:

    OBIEE Union Query Bugs.PNG

    Any other suggestions are appreciated!

    Tom

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    If the separate queries in the union are independent of each other, the "All Weeks" percentages shouldn't have changed.

    The main purpose of the text label column that I mentioned  is to separate the results.  So, if column 1 (let's call it Bin) contained values of 'All weeks',

    'GrpA', and 'GrpB' in the 3 queries, you use Name, and then Bin to define the rows in the pivot table.

    When you now mention "Grand Totals" are you referring to "All Weeks", or another bottom line for the whole table?

    If you are expecting a bottom line with the correct percentages, you would add a fourth query to the union that included all names and all weeks.

    Copy and paste the 'All Weeks' query, and in the in the name column, replace the formula with the text 'Grand Totals'. (Turn off any totals in the pivot table)

    Use conditional formatting (When Name='Grand Totals', etc.) to make it look like a totals line.

  • Tom D
    Tom D ✭✭✭

    Ultimately, I would like a "Grand Totals" for name="Jones" (and the other Names), for All Weeks and each Week Group. (you can see this in my original post). I would have to select "Column Totals"  in the pivot table.

    And i would like a bottom line "Grand Totals" for each "Qtr". I was able to get that working thanks to your previous post (with the conditional formatting, etc). Thank you.

    And I would like a Grand Totals for each Name.  So there would be a Grand Totals line below the Jones entries, below the Smith entries and below the Green entries.

    See below:

    Qtr 1

    Qtr 2

    Qtr 3

    Qtr 4

    Grand Total

    Name

    Week

    Att

    Cmp

    %

    Att

    Cmp

    %

    Att

    Cmp

    %

    Att

    Cmp

    %

    Att

    Cmp

    %

    Jones

    All Weeks

    70

    35

    50%

    60

    30

    50%

    65

    35

    54%

    75

    45

    60%

    270

    145

    54%

    Jones

    Week Grp A

    20

    12

    60%

    25

    15

    60%

    35

    22

    63%

    50

    36

    72%

    130

    85

    65%

    Jones

    Week Grp B

    22

    15

    68%

    23

    15

    65%

    19

    14

    74%

    10

    6

    60%

    74

    50

    68%

    Jones Totals

    70

    35

    50%

    60

    30

    50%

    65

    35

    54%

    75

    45

    60%

    270

    145

    54%

    Smith

    All Weeks

    100

    85

    85%

    50

    30

    60%

    50

    40

    80%

    60

    40

    66%

    250

    180

    72%

    Smith

    Week Grp A

    50

    30

    60%

    50

    30

    80%

    40

    25

    63%

    50

    40

    80%

    200

    150

    75%

    Smith Totals

    100

    85

    85%

    50

    30

    60%

    50

    40

    63%

    60

    40

    66%

    250

    180

    72%

    Green

    All Weeks

    45

    30

    67%

    45

    30

    67%

    45

    30

    67%

    55

    30

    55%

    190

    120

    63%

    Green

    Week Grp B

    10

    7

    70%

    15

    7

    47%

    10

    8

    80%

    10

    8

    80%

    45

    30

    67%

    Green Totals

    45

    30

    67%

    45

    30

    67%

    45

    30

    67%

    55

    30

    55%

    190

    120

    63%

    Grand Totals

    215

    150

    70%

    155

    90

    58%

    160

    105

    66%

    190

    115

    61%

    710

    445

    63%

  • Tom D
    Tom D ✭✭✭

    Massimo,

    How about if I do not permit the overlapping of the groups?  Can I still use the unions and get the Grand Total %'s working correctly/

    Thanks,

    Tom

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    I'm a bit confused again.  How do the "All Weeks" rows differ from the Name Totals?  It seems redundant.  If it's necessary, you can repeat the 'All Weeks' query, and put a formula in the Name column like: "Table"."Name"||' Total'.  You'll have to do more conditional formatting, and possibly add a column to keep them sorting in the right order.

    Signing off for the day.  Cheers!

  • Tom D
    Tom D ✭✭✭

    I realize that it seems redundant. Just know that I can also select individual weeks instead of "All Weeks".  Probably should have mentioned that earlier, but didn't want to complicate matters....

    I hope that makes it clear why I need the Name totals.

    I'll work on what you mentioned....

  • Tom D
    Tom D ✭✭✭

    I got the Name Totals and Grand Totals lines working and in their correct places on the pivot table. 

    Only thing I can't get right is the pivot table Grand Totals % column on the right (right-most column). The Atts. and Cmps. are correct.

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    An option:

    In your pivot table try un-checking the totals you are using to create your Grand Totals columns, and in its place,

    Click the edit icon for  Qtrs, and then "Create a calculated item".  in the dialog box, put 'Qtr1'+'Qtr2'+'Qtr3+'Qtr4'.  You can name it Grand total.

    This may produce better results.

  • Tom D
    Tom D ✭✭✭

    Not giving correct results.  Here are the 1st two rows of the Grand Total columns:

    GT.PNG

    I'm really struggling here. What did I do wrong?

  • Jerry Casey
    Jerry Casey ✭✭✭✭✭

    Did you change the position of your Measure labels?

    pastedImage_0.png

    Also, My last advice may not work.  The calculated Item will just total the percentages, not recalculate them.

    In this simple example, the Column totals are correct:

    pastedImage_1.png

    But in your case, the complexity of dealing with unions is showing its ugly head.

    Another approach gets into the real strength of unions.

    You may have noticed the "Add a Result Column" option:

    pastedImage_2.pngIf you click on one of the "legs", then click on "Result Column", you'll see it.

    This allows you to do calculations at the header level.

    All formulas use column references saw_0, saw_1,saw_2, etc, starting on the left.

    You can construct an alternate Percentage column there and see how that works.

    Not knowing your query, lets say your column order is BIN, Week, Qtr, Name, Attempts, Completions

    Percent would be 100*SUM(saw_5 by saw_0,saw_1,saw_2,saw_3)/SUM(saw_4 by saw_0,saw_1,saw_2,saw_3)

    The BIN and name changes that you've created on the "Totals" queries will still work with this method.

    Replace your old % column with this new one.

    I know this is getting into the weeds, but your requirements are extremely complex/convoluted.

    I'd have talked your customer into a simpler presentation, if I could.

  • Tom D
    Tom D ✭✭✭

    Jerry,

    I got something to work with that last approach!

    I appreciate your diligence here.  Much appreciated!

    Tom