Logical view primary keys

[dagnaw...@freddiemac.com]

HI,

Do you guys know how to get rid of primary key indicator in logical
view of a table without actually relating the attribute to the primary
key?

For example, if I have month and quarter in a lookup table and I don't
relate the two, in the logical table view, I see both as primary keys.
If I assign a parent-child relationship, then month only shows as
primary key.

Is there any way to change this, to say month is the only primary key
but it's not related to quarter attribute?

This is just an example. I basically want to use two fact tables for
an attribute and depending on a filter, want the attribute to use one
of the fact tables. Long story but please let me know if there's a way
to do this.

[Caio Gouveia]

Hi Dagnaw,

Yes...MicroStrategy always uses the lowest level attribute on the
dimension as the primary key of the table.

If you do not make Quarter a parent of Month, then you have to un-
check the box "The key specified is the true key for the warehouse
table" in the Table editor. In that case, both Quarter and Month will
be the primary key of the (logical) table.

If you want to change that, you will have to create a new attribute
that is the lowest level of the dimension. The problem is that if you
don't have that granularity on the fact....nothing will join.
Another option is to create a new logic view of the table so Month is
only defined in one of them and Quarter in the other one.

Even though you will be able to get this to work...you will loose the
drill functionality and things will look pretty messy.
It seems to me that your problem can be fixed without changing the
parent child relationship between the attributes.
Basically all you need is to force the metric to use the correct fact
table.

For example:

Quarter Month and Units Sold in Tutorial. Take a look at the SQL below
and notice that it goes to the MNTH_CATEGORY_SLS table because it is
the lowest size table:

select a12.[QUARTER_ID] AS QUARTER_ID,
max(a13.[QUARTER_DESC]) AS QUARTER_DESC,
a11.[MONTH_ID] AS MONTH_ID,
max(a12.[MONTH_DESC]) AS MONTH_DESC,
sum(a11.[TOT_UNIT_SALES]) AS WJXBFS1
from [MNTH_CATEGORY_SLS] a11,
[LU_MONTH] a12,
[LU_QUARTER] a13
where a11.[MONTH_ID] = a12.[MONTH_ID] and
a12.[QUARTER_ID] = a13.[QUARTER_ID]
group by a12.[QUARTER_ID],
a11.[MONTH_ID]

Now...let's say you want to go to the ORDER_FACT table. Edit the Units
Sold metric and change the parameter of the SUM function. In the
FactID column select any fact that exists only on the second fact. For
Example Freight in Tutorial. The report will now go to the second fact
table:

select a12.[QUARTER_ID] AS QUARTER_ID,
max(a14.[QUARTER_DESC]) AS QUARTER_DESC,
a12.[MONTH_ID] AS MONTH_ID,
max(a13.[MONTH_DESC]) AS MONTH_DESC,
sum(a11.[QTY_SOLD]) AS WJXBFS1
from [ORDER_FACT] a11,
[LU_DAY] a12,
[LU_MONTH] a13,
[LU_QUARTER] a14
where a11.[ORDER_DATE] = a12.[DAY_DATE] and
a12.[MONTH_ID] = a13.[MONTH_ID] and
a12.[QUARTER_ID] = a14.[QUARTER_ID]
group by a12.[QUARTER_ID],
a12.[MONTH_ID]

To change the parameter of the SUM function, edit the metric and right
click on SUM. Select SUM Parameters.

Hope that helps...
Caio Gouveia
www.dmeaning.com

[dagnaw...@freddiemac.com]

Caio,

Thanks for the suggestions, all very helpful. The fact tables I'm
working with are really at the same level, one is stage table, the
other a production one. Based on a filter that points to just the
stage table, I wanted to force metric calculation from that table. No
filter would mean it would default to the production table. I had
thought using only one of the fact tables as a relate table (there's
no other related table for the 2 attributes), the filter wouldn't
work. I will try not relating the attributes and treating them both as
primary keys, shouldn't cause a problem hopefully. This is just a way
of using the appropriate table without having to create 2 versions of
everything, attributes, metrics, logical views etc.

Thanks,
Dagnaw

> > to do this.- Hide quoted text -
>
> - Show quoted text -

[BI Help]

Another option is to use a dummy metric.
Basically create a metric (eg Max(1)) and only select the staging
table.
Place that metric in the report objects but not in the template.

That should force the report to go to the stage table instead of the
regular fact.

> > - Show quoted text -- Hide quoted text -