Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Metric giving cross join???

Metric giving cross join???

Metric giving cross join???

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
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???

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.


RE: Metric giving cross join???

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.

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.

Attribute:  Month
Metric:  Number of Days
Filter:  Year = 2003
         Product = Widget

I would expect the following SQL passes:

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???

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

z3, nlim -- any explanations?

RE: Metric giving cross join???

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close