enable cross join on a metric

[mgordas]

Hi!

I haver 3 tables on my model:

PERSON_TABLE
-------------
PERSON_ID
CONTRACT_DATE

EXPED_TABLE
---------------
EXPED_ID
EXPED_TYPE_ID
EXPED_CLASS_ID
EXPED_DATE

EXPED_TYPE_TABLE
-------------
EXPED_TYPE_ID
EXPED_TYPE_DESC

We need to create a report like:

ATRIBUTTE:exped_type_desc
METRIC(compound metric):
(Num Exped (Class=1 & Exped_Month=January)) /(Num Persons (Contr_Month=January))

Dimensionality for metric Num Persons is: Report level-standard-standar and
Exped_type_desc-ignore-none

Report generate SQL like next:

-- calculate Num Exped
CREATE TABLE1
SELECT EXPED_TYPE_ID,
COUNT(DISTINCT EXPED_ID) AS NUM_EXP
FROM EXPED_TABLE
WHERE EXPED_CLASS_ID = 1
AND MONTH(EXPED_DATE)='JANUARY'
GROUP BY EXPED_TYPE_ID

-- calculate Num Persons
CREATE TABLE 2
SELECT COUNT(DISTINCT PERSON_ID) AS NUM_PERS
FROM PERSON_TABLE
WHERE MONTH(CONTR_DATE)='JANUARY'

-- join everything
SELECT EXPED_TYPE_DESC,
NUM_EXP/NUM_PERS
FROM TABLE1 A,
TABLE2 B,
EXPED_TYPE_TABLE C
WHERE A.EXPED_TYPE_ID= C.EXPED_TYPE_ID

On 3rd query appears a cross join because there is not relationship between
EXPED_TYPE_TABLE & PERSON_TABLE. I can´t enable cross join on my project and users
want to use this metric with every atribute from the project.
It´s possible to create a non-real relationship to not use cross-join on my report?

Thanks

 

[nlim]

in this particular case, you get only one value for the num_per; that's why you want to get rid of the cross join. I don't think you can do that in MSTR.

Out of curiosity, why isn't the exped_id related to person_id. Seems like that would be logical...

 

[Z3]

One way to get ride of the cross join is to use OLAP Services. You can insert a metric that does M1/M2, without doing it in the database which requires a cross join.

Second way to do this would be to use applysimple to do some SQL engineering.

 

[FLB]

The VLDB setting regarding cross joins allows you to differentiate between cross-joins between temp tables and cross-joins between main tables (and temp tables).
It is usually safe to enable cross-join between temp tables (if you don't think it is, put a limit on the intermediate table size)

HTH,
FLB