Metric giving cross join???

[entaroadun]

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!

 

[chael]

Make Number of Days a child of year. Make this a one:many relationship...this should help resolve the problem with the group by, as you may have it currently modeled in as a many:many, if it is modeled that way at all. To resolve the cross join you may want to put a dummy metric filter on the report so that the product and time dimensions have something to join on, i.e sales_fact <> -1 ... or something like that.

Chael

 

[entaroadun]

After playing around with the settings, I was able to get it to work:

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.

 

[entaroadun]

Chael,

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.

 

[chael]

I guess modeling number of days as an attribute doesn't do you any good here...

z3, nlim -- any explanations?

 

[nlim]

if your fact is found in the lookup table like this:

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