×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

7.2.2 - Drills across a M2M - aghghghghghgh!!!!!!

7.2.2 - Drills across a M2M - aghghghghghgh!!!!!!

7.2.2 - Drills across a M2M - aghghghghghgh!!!!!!

(OP)
Hi guys,

I have been struggling with a wiered problem (which I think is an undocumented bug in MSTR 7.2!) - but can't get MSTR Support to look at it as 7.2.2 is no longer supported :( (please no 'upgrade to 7.x/8' replies - we are moving towards 8, but have various projects on various versions from 5.1 up!).

Any way problem is this. Take this hierarchy, where 'A' prefixed attributes represent the main path, and 'B' prefix is an offshoot:

[B1]     [A1]
1:M      1:M
[B2]     [A2]
1:M      1:M
[B3]     [A3]
1:M      1:M
[B4] M:M [A4]   Two paths merge here on a Many to Many
         1:M
         [A5]
         1:M
         etc

There is a relationship table for each of the B prefixed attributes against A4 directly (i.e. each level of the B branch has its own relational mapping table Many to Many against A4) as well as the standard 1 to Many Parent/Child Look Up (using a hybrid start-flake schema - snow flake builsd on descent into a star at lowest level). B4 just has the Many to Many against A4. Phew!

OK, so under 7i (unlike 6 and before) if I run a report at B4 and drill to A4 I get SQL something like (ignoring description lookups for simplification):

[Prompt - filtered for Week End of 2005-08-27, Coubtry of GBR (UK) and B4 Id of 111]

select Ref1.B4_ID  B4_ID,
    Fact1.A4_ID  A4_ID,
    sum(Fact1.AFact)  AFACT    
from Fact_Table Fact1
    join B4ToA4RefTable Ref1
      on  Fact1.A4_ID = Ref1.A4_ID
    join B4LookUpTable LookUp1
      on  Ref1.B4_ID = LookUp1.B4_ID      
where (Ref1.B4_ID in (111)
 and Fact1.A_WKEND_ID = '2005-08-27'
 and Fact1.A_COUNTRY_ID in ('GBR'))
group by Ref1.B4_ID,
    Fact1.A4_ID
    
Ok, so far so good. Now instead of running at B4 and drilling to A4, run at B3 and drill to A4. This still looks OK (as follows):

[Prompt - filtered for Week End of 2005-08-27, Coubtry of GBR (UK) and B3 Id of 222]

select Ref1.B3_ID  B3_ID,
    Fact1.A_SC_ID  A_SC_ID,
    sum(Fact1.AFact)  AFACT
from Fact_Table Fact1
    join B3ToA4RefTable Ref1
      on  Fact1.A_SC_ID = Ref1.A_SC_ID
    join B3LookUpTable LookUp1
      on  Ref1.B3_ID = LookUp1.B3_ID
where (Ref1.B3_ID in (222)
 and Fact1.A_WKEND_ID = '2005-08-27'
 and Fact1.A_COUNTRY_ID in ('GBR'))
group by Ref1.B3_ID,
    Fact1.A_SC_ID
    
No the problem (got there at last - sorry for the length of post). OK, now from B2 to A4 - in theory this should be pretty much identical to the last drilled run (B3->A4), but using the B2ToA4RefTable and B2LookUpTable:

[Prompt - filtered for Week End of 2005-08-27, Coubtry of GBR (UK) and B2 Id of 333]

select LookUp1.A2_ID  A2_ID,
    Fact1.A4_ID  A4_ID,
    sum(Fact1.AFact)  AFACT
from Fact_Table Fact1
    join B4ToA4RefTable Ref1
      on  Fact1.A4_ID = Ref1.A4_ID
    join B4LookUpTable LookUp1
      on  Ref1.B4_ID = LookUp1.B4_ID
where (
 (
   exists
   (
     select *
     from B3ToA4RefTable Ex_Ref1
        join B3LookUpTable Ex_LookUp1
          on  Ex_Ref1.B3_ID = Ex_LookUp1.B3_ID
        where Ex_LookUp1.A2_ID in (333)
        and    Ex_LookUp1.A2_ID = LookUp1.A2_ID
        and    Ex_Ref1.A4_ID = Fact1.A4_ID
    )
   )
 and Fact1.A_WKEND_ID = '2005-08-27'
 and Fact1.A_COUNTRY_ID in ('GBR')
)
group by LookUp1.A2_ID,
    Fact1.A4_ID

OK, MSTR 7i differs here from MSTR6 etc in that it uses the horrible Exists clause. This in itself is an efficency problem, but if you look at the query, it is using a join in the Exists calsue between the B3 look up table and the B3->A4 ref table. It is not using the dedicated B2->A4 ref table, which is all that it needs. I can find no reason for this and can not aseem to model it out in any way. I have even tried heavily skewing the LookUpTables to be undesireable (LTS) as opposed to using the Ref table at the correct level. It makes no difference. There are no VLDB setting outside of the out-of-the-box set up for this. I have defined the drill ref tables at each B level to A4 in the ID form of each. The table structure is correct.

MSTR documentation only talks about the M2M relationships when the M2M attribute is at the highest level, so it does not cover this. The MSTR Knowledge base does not mention it neither does a search here. Anyone have an idea? Please?

Cheers Guys,

Wolf
PS: Sorry for the horrendiously long post :)  

RE: 7.2.2 - Drills across a M2M - aghghghghghgh!!!!!!

From the model point of view, is B4 the parent of A4 or is A4 the parent of B4?

RE: 7.2.2 - Drills across a M2M - aghghghghghgh!!!!!!

Might be going down the wrong route with this but what's the DSS Star Join VLDB setting for the report / project?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close