(URGENT) Cross Join in MSTR Report SQL

[manojkrghosh]

I have created a MSTR report from a single table only. The report is basically very simple. The Database it is connecting to is Teradata V"R4. But when I checked the generated SQL, it is showing a surprise Cross Join. The SQL is given below...

select a11.YEAR_NO YEAR_NO,
a11.WEEK_NO WEEK_NO,
a11.STORE_CD STORE_CD,
a11.DEPARTMENT_CD DEPARTMENT_CD,
a11.REGION_CD REGION_CD,
sum(a11.DIFF_AMT_CST) WJXBFS1,
sum(a11.DIFF_AMT_RTE) WJXBFS2,
sum(a11.JOURNAL_AMT_CST) WJXBFS3,
sum(a11.JOURNAL_AMT_RTE) WJXBFS4
from SSA_JOURNAL_OVERRIDE a11
cross join SSA_JOURNAL_OVERRIDE a12
group by a11.YEAR_NO,
a11.WEEK_NO,
a11.STORE_CD,
a11.DEPARTMENT_CD,
a11.REGION_CD

I want to remove this "Cross Join". Can anybody help me out in this regard. I shall be thankful to you...

Regards,

Manoj

 

[OV]

Most likely you need to specify some relationships, probably many to many between your attributes that appear in the query. Do that and then update the schema.
OV

 

[manojkrghosh]

I have created the relationships among attributes as you said...but after creating relationships and updating the schema, at the time of report execution, it is showing an error as below...

SQLEngine got an Exception from DFC: [DFCENGINE] Engine Logic:
Fact does not exist at a level that can support the requested analysis. Fact: "Diff Amt Cst". Level: "Department Cd, Region Cd, Store Cd, Week No, Year No".


Please suggest what to do...I got stuck at this point now...please help me out...

 

[nlim]

go to "diff amt cst" fact, and edit it.

Make sure the expression is DIFF_AMT_CST, and the source table SSA_JOURNAL_OVERRIDE is checked.

update schema, make sure that the box for "recalculate fact entry level" is checked.

then try to create simple report with year and diff_amt_cst on it.

 

[OV]

If your field naming means what it says than I would define a one to many relationship between year_no and week_no. Then a one to many between Region_Cd and store_CD, a one to many between Region_Cd and Department_CD. Then a one to many between store_Cd and Department_CD. I assume that stores fall within a region and departments fall within a store. Finally define a many to many relationship between department_cd and week_cd.
Update schema.
Then start building the report by adding one attribute at a time.
OV

 

[manojkrghosh]

Do know how to define Fact Level, because after modification suggested by you, it is now showing Fact Level for a Fact as Department CD only....

 

[OV]

Did you get this problem resolved?

 

[manojkrghosh]

No not really the way you have suggested. But I found some other way to solve that. Anyway, thanks a lot for your help....

thanks...