degenerated dimensions

[brmstr]

Hi all,

I'm trying to implement a degenerated dim. such as an invoice number, where you don't have a dimension table but just a single field which can easily be stored in the fact table only.

I tried to define it as an attribute, but it joins the fact table against itself ... any ideas what is wrong here?

thx!

 

[Z3]

I remotely remember there was a bug about a single attribute alone on a table, but I don't remember the details. Could you give us some more info?

The SQL engine requires fact to worker properly. Also may VLDB settings can affect SQL. Without detail I don't know what exactly was your problem.

 

[brmstr]

Hi Z3,

thanks for your reply!

some more details on the attribute which is causing the problem:
- it's based on a CHAR column in the fact table (and some aggregates)
- no other lookup table there ...!
- no relationships defined, not even to a set of dimension keys in the fact table (I tried but it didn't work either)
- VLDB parms are standard, except for role playing has been activated, but I don't think this is the problem
- we're using 7.2.3

to me it looks like MSTR always needs to join against a lookup table, and in this case the fact table is it, as there's no other lookup table defined / available ...

So I believe I can get around it by creating a lookup table for a single attribute - but that's just a workaround...

I'd still like to understand what causes this unnecessary join

 

[reisw]

We do something similar and have had no problems here. What does your query look like?

 

[brmstr]

Hi reisw,

here is a sample query:

select a12.CUSTOMER CUSTOMER,
a11.DEGDIM_INVOICE INVOICE_NUMBER,
sum(a11.TOTAL_SALES) WJXBFS1
from FACT_SALES a11,
DIM_CUSTOMER a12,
FACT_SALES a13
where a11.DIMKEY_CUSTOMER = a12.DIMKEY_CUSTOMER and
a11.DEGDIM_INVOICE = a13.DEGDIM_INVOICE and
a11.DIMKEY_PRODUCT = a13.DIMKEY_PRODUCT and
a11.DIMKEY_WAREHOUSE = a13.DIMKEY_WAREHOUSE
group by a12.CUSTOMER,
a11.DEGDIM_INVOICE


1 fact table FACT_SALES, 3 dimension tables (DIM_CUSTOMER, DIM_PRODUCT, DIM_WAREHOUSE) only one of them is used in the query and one degenerated dimension DEGDIM_INVOICE with the invoice number directly stored in the fact table (so the attribut definition uses this table as the lookup table).

As you can see, instead of pulling everything from the fact table once, MSTR joins it again for the retrieval of the DEGDIM_INVOICE.

 

[reisw]

It looks like from your query that Product and Warehouse are also defined on the same Fact table (A13). Is that correct? Do you have separate lookup tables for those two attributes? Do you by any chance have an alias defined for your Fact_sales table? If you do that could be why the table is joining to itself. If you do change it so that it uses the original fact table and then you shouldn't have a problem.

 

[brmstr]

Yes, there is a relation to product and warehouse via their dimension key in the same fact table. But as you alrady assumed: we do have seperate lookup tables for them.

Since none of the attribute for product and warehouse are not used in the query I would not expect to see those DimKey's in there.

I double checked for an alias defition on the fact_sales table: no - there is none. The attribute (and all other objects) are directly pointing to that one and only fact table.

Is there anything else we need to keep in mind when defining a degenerated dimension (attribute) in a fact table?

 

[reisw]

I was wondering about the Product and warehouse in the query also. It doesn't look like you are displaying them in the report so I am wondering why they are showing up in the query. Have you made any relationship changes recently? make sure you update the schema. Also, I know i have seen instances where I will make a change to an object and delete all of the caches and the sql doesn't change. I have to disconnect and reconnect to make sure it re-generates new sql. You could also try unchecking the option on the table that says "The key defined is the true key for the warehouse table" and see if that changes anything.

 

[brmstr]

reisw,

First of all: Thanks a lot for your hints .

I tried to unchecked the table option "The key defined is the true key for the warehouse table". No change to the query - even after purging the cache and logging off and on again.

Just to be sure again: DEGDIM_INVOICE is created as an attribute on FACT_SALES. Since it is part of the PK of the table FACT_SALES, I did not specify any children/parents for this attribute - left it just: as is. Lookup table is the fact table itself (this becomes interesting when adding an aggregate table for the fact table, e.g. FACT_SALES_MONTHLY - which of those two should then be used for the lookup).

When using only the attribute DEGDIM_INVOICE in a report, everything works fine. But when the first metric is added (pointing back to a fact defined on the table), the self join is executed.

Is this the wrong way of defining degenerated dimensions (dimensions that only have one attribute, which is then for simplicity put directly in the fact table)? Do you always need to create a lookup table in MSTR for all attributes used in fact tables?

Thanks again.

 

[chael]

To reisw's point, update the schema...I can't remember if you have to update the schema after checking or unchecking the true key setting, but it would help.

One question, though: why don't you set invoice as a child of customer? The relationship seems perfectly logical (M:1). It doesn't matter if your fact table is the lookup table for invoice, as invoice a pk on the fact table anyway. I think defining this relationship would alleviate the self-join, no?

 

[Z3]

Let's be straight:
This is the SQL you want, right?

select a11.DIMKEY_CUSTOMER,
max(a12.CUSTOMER) CUSTOMER,
a11.DEGDIM_INVOICE INVOICE_NUMBER,
sum(a11.TOTAL_SALES) WJXBFS1
from FACT_SALES a11,
DIM_CUSTOMER a12
where a11.DIMKEY_CUSTOMER = a12.DIMKEY_CUSTOMER
group by a11.DEGDIM_INVOICE ,
a11.DIMKEY_CUSTOMER

This should be the default SQL that MicroStrategy generates according to the schema you described. When you got error SQL in your case I would:
1) recreate the report de novo and see if the SQL is correct;
2) Update schema with 3 boxes checked;
3) Check the right corner on your report editor, if it says "OLAP Services" - this is the expected bahavior (bug!). Log a support case and recreate the report de novo. By default, the OLAP Service should not come up unless you play with the report object browser. Once it's there I have not figure out a way to get rid of it.

If you still having problems, send your md compressed to support.

 

[brmstr]

Z3,

thanks for your help, yes this looks like the SQL I want... And indeed, OLAP services seems to be the problem, I'll check with support ...

Thanks a lot for your answer!