## Metric giving cross join???

## Metric giving cross join???

(OP)

I have a fact named "Number of Days" which contains the number of days in a given period on a Time lookup table.

I want to create a metric on this fact that simply gives me the total number of days in a period.

If the report contains:

Attribute: Year

Metric: Number of Days

Filter: Year In List (2003)

I should get one record with 365 as the metric value.

If the report contains:

Attribute: Year

Product (there are 5)

Metric: Number of Days

Filter: Year In List (2003)

I should get 5 records with 365 in each row.

If the report contains:

Attribute: Year

Product

Metric: Number of Days

Filter: Year In List (2003)

Product In List (Deposit)

I should get one record with 365 in the row.

Instead, for each of the product ones, I get records with a multiple of 365 in each row, based on the number of records in my Product lookup table for that product. It is cross-joining with the lookup table and multiplying my result!

The metric is defined as Sum([Number of Days]) with ReportLevel-standard-standard and Product-ignore-none.

Removing the Product attribute from the template removes the cross join, even if there is a product filter on the report. Therefore, the Ignore setting must be working.

But why does it Group By product when I explicitly tell it not to?

How do I do this? Help!

I want to create a metric on this fact that simply gives me the total number of days in a period.

If the report contains:

Attribute: Year

Metric: Number of Days

Filter: Year In List (2003)

I should get one record with 365 as the metric value.

If the report contains:

Attribute: Year

Product (there are 5)

Metric: Number of Days

Filter: Year In List (2003)

I should get 5 records with 365 in each row.

If the report contains:

Attribute: Year

Product

Metric: Number of Days

Filter: Year In List (2003)

Product In List (Deposit)

I should get one record with 365 in the row.

Instead, for each of the product ones, I get records with a multiple of 365 in each row, based on the number of records in my Product lookup table for that product. It is cross-joining with the lookup table and multiplying my result!

The metric is defined as Sum([Number of Days]) with ReportLevel-standard-standard and Product-ignore-none.

Removing the Product attribute from the template removes the cross join, even if there is a product filter on the report. Therefore, the Ignore setting must be working.

But why does it Group By product when I explicitly tell it not to?

How do I do this? Help!

## RE: Metric giving cross join???

Chael

## RE: Metric giving cross join???

I kept the same metric formula and level settings. Around this metric formula, I used a Max function with ReportLevel-standard-standard. Now the formula looks like Max(Sum(Number of Days)).

It does exactly what I want, except with an extra pass: the first pass calculates the Sum(Number of Days) at the Time level I want, ignoring the Product filter and attribute. A second pass simply selects all from the first pass, with no aggregation function, but with the Year filter and no Product filter. In essence, this second pass is a copy of the first.

This second pass (one record in the above examples) is then cross joined with my Product table to give the correct 365 days for each product record. This is the SQL I was aiming for from the beginning.

I guess I just need someone to more fully explain to me the level settings for metrics. I honestly thought I fully understood the metric settings and their effect on the generated SQL. I don't understand why the Sum function at ReportLevel, Product-ignore-none would cross join to Product in order to Group By; while a Max function around the Sum function at ReportLevel with no ignore-none settings would ignore the Product filter and not cross-join to Group By.

Does anyone have this information? The product documentation and the CBIC course doesn't fully explain the interaction of the level settings on a metric, esp. if the metric contains aggregations based on other aggregations.

## RE: Metric giving cross join???

I don't quite understand your comments.

The Number of Days fact's entry level is Month and only Month. Year is a parent of Month with a one-to-many.

What I wanted to create was a metric that gave me the total number of days at the level of the Time attribute on the template filtered only by Time-related attribute qualifications. It should ignore non-Time filters and not try to group by non-Time attributes.

Example:

Attribute: Month

Product

Metric: Number of Days

Sales

Filter: Year = 2003

Product = Widget

I would expect the following SQL passes:

One:

Select Sum(Sales)

From SalesFact

Where Year = 2003 and Product=Widget

Group By Month, Product

Two (ignores non-Time filter and attribute):

Select Sum(Number of Days)

From TimeLookup

Where Year = 2003

Group By Month

Three (consolidate)

Select Month, Product, Sales, Number of Days

From Pass1 Join Pass2 On Month

Per my previous post, I was able to find the solution, but I don't understand why it works. I thought the Sum(Number of Days) with ReportLevel-standard-standard and Product-ignore-none would give me the desired passes. Does anyone have a reference that I can go to that explains this?

BTW, I am running 7.2.2.

## RE: Metric giving cross join???

z3, nlim -- any explanations?

## RE: Metric giving cross join???

LUYear - numberdays

LUMonth - numberdays

then if you remove the product level dimensionality setting in your metric and leave it as basic reportlevel, you should be fine. I tried it with the tutorial demo project and it seems to behave this way.

Here's my reasoning, the fact itself has no entry level to the product attribute. ie. the engine cannot find a way to group by product. So if you have just report level, the sql will do the cross join automatically.

When you add the product ignore none, the engine tries to find the relationship BEFORE ignoring it. This must really confuse it somehow...

best of luck