Cross Join Problem

[sekar211]

I have 3 tables.

Table1:
adjid(PK)
Recnum(FK)
Totadjamt(Fact)

Table2:
Recnum(PK)
TrackNum(FK)

Table3:
Tracknum(PK)
Bill rep
Manager
Location

These are views with real time data.

I want a report with metric as sum(totaladjamt) with tracknum,billrep,manager,location as dimensions.

I created Recnum and Tracknum attribute and made the Recnum as the child of tracknum.

but when i place the tracknum and the metric on the report there is a cross join between table1 and table3 even though the relationship i have defined is correct!

do any one of u think this might be wrong?

Thank you.

 

[entaroadun]

Did you check to make sure that Recnum is attached to both Table 1 and 2 in MSTR?

 

[sekar211]

Yes i did it and as far as i know everything is fine.Today the problem got more worse as MSTR is not even executing report which contains object from any of these tables.It generates SQL but in GRID view it breaks up and says EXECUTION FAILED and if i try to run it again it says "table of the object in the report appears to be in inconsistent state".these is real time application without datawarehouse and all these are VIEWS.
when i run the same query on query analyzer it returns correct value.
MSTR is excuting report from all other views properly except these 3 views.
what do u think might be the problem?

Thank you

 

[entaroadun]

To me, the words inconsistent state always mean corrupt MD. Either your physical table or logical table objects have gone bad... When was the last time you backed your MD up?

I would call tech support. If the objects are corrupt, you shouldn't be able to get SQL.

 

[sekar211]

I think the physical table is fine as it returns records for the same SQL in Query Analyzer.It is failing only in MSTR.I guess it's MD problem.I had asked my Admin to do scanMD and he said that he did it and could fix only few issues.
so do you advise me to call tech support in this case or is there any other scans which we can do on MD?

Thank you

 

[entaroadun]

Do you have MDDoctor?

 

[sekar211]

yes i have.to run that do v have to back up the metadata? and also have to stop intelligence server?because i haven't done it before

 

[spod]

What do the server logs on the I-Server (Program Files\Common Files\MicroStrategy\Log) say when this error comes up? There maybe something more descriptive than 'inconsistent state'.

dsserrors.log and dss databaseclass.log

 

[sekar211]

I see this message repeated many times:

3248:[THR:3252][08/06/2004::11:31:20:873][DSS ObjectServer][Error][Code: 0x8004118d]This property is not available unless the template is in a Report Instance which has been processed by the Analytic Engine.

Any idea what this might be?