Degenerate dimensions

[Patterson]

We have a degenerate dimension on several fact tables, and we don't
really want to create a dimension table for it. As a result, we are
just letting one of the fact tables that contains the key be listed as
the lookup (since MSTR requires that it have a lookup table), and we
are only using the ID form of the key. However, anytime we run a
report off of a fact table other than the one listed as the lookup,
MicroStrategy tries to join to that fact table. Is there any way
around this other than creating a dimension table for it (VLDB
settings, etc).

Any help would be appreciated.

Thanks,
Andrew

[japerron]

Check attribute join type and vldb properties for the report. You do
not want any outer joins.

Good luck

[Marvin Mayorga]

Hey Andrew,

 

In case you are talking about the attribute, then I'm wondering why you would want to try to avoid the join to the fact(otherwise defined in MSTR as the LU) if the purpose of the join is to relate the attributes on the report.

 

Here's my current understanding of a Degenerate dimension table.  Say you have a dimension that only has Zip Code and Customer Number.  Say there are 1 million Zip Codes and Zip Code and Customer Number exist in the fact table with a 1:1 relationship.  There really wouldn't be a need for a Dimension table for Zip Code and Customer Number, you could just use the fact table.  Therefore this would be your Degenerate dimension table because then you would have two or two tables with a million rows rather than just 1.

 

If this holds true, your Dimension table would have just as many rows to join to as your Fact table does.  If this is the case there really isn't anything you could do but filter on the subset that you are joining on right?

 

I think I may be getting ahead of myself.  If the above holds true for your case, how about the VLDB property that applies the filter all the way down through the joins? Located under Query Optimizations and Apply Filter Options.

 

Later,

Marvin

Patterson <andrew_p...@yahoo.com> wrote:

[Marvin Mayorga]

Hey Andrew,  if you're attempting to avoid the join, I take it that it's because the invoice number attribute exists in other tables which would make the join to the fact\LU obsolete.  Are these tables are ready checked as source tables?  Meaning the Fact could be defined as the LU but there could be other tables defined as source tables.  MSTR would attempt to hit these tables instead of the fact\LU if thier logical table size were smaller than the Fact\LU. 

 

Does this help or am I still missing the point?

 

Later,
Marvin

 

Andrew Patterson <andrew_p...@yahoo.com> wrote:

 

Marvin,

Thanks for the quick response.  We have several fact tables that have a degenerate dimension, like invoice number for example, that the end users will want to drill to.  However, since it is a degenerate dimension, we really didn't want to create a dimension/LU table for it.  However, the attribute definition requires that you assign a LU table to the attribute.  In doing so, we have picked one of the fact tables to check as the lookup table in the attribute editor.  Even though we are only using the ID form of the attribute, every time we run a report against a table that isn't the one checked, the sql joins to the table that was set as the lookup.  We really would prefer not to have to create a lookup table for the degenerate key, but we haven't been able to come up with any other solution.

 

Thanks again for your help.

 

-Andrew

Marvin Mayorga <mayorg...@yahoo.com> wrote:

[japerron]

Andrew,

The SQL engine normally doesn't do unnecessary joins to the LU
(regardless of whether it's a true LU, a Fact, or a Relationship
table). Having said that, let;s summarize:

1) You have an attribute "invoice Number". The attribute only has an ID
form, hence the degenerate dimension
2) The attribute appears on several fact tables. You have set Fact1 as
the LU for the attribute
3) You are finding that reports that should hit other fact tables (say
fact2 or fact3) are still joining to Fact1.

Here are some questions:
1) Is there any additional metric, custom group, etc in the report that
may be causing this? (i.e. what happens if you leave all attributes and
remove all metrics except one only defined in Fact1)
2) Is there any additional attribute (like a parent of Invoice Number,
where the relationship is defined in the Fact1 table) in the report
that may be causing this? (i.e. what happens if you remove all
attributes except invoice number and remove all metrics except one only
defined in Fact1)

Hope this helps