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
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
