Repost - referenced dimension causes fact table to be filtered

[Peter Kenyon]

Hi,

I posted this question before, but it was not done under my MSDN email alias
so I'll try again. Apologies for the double-post.

On our SSAS2005 installation, the fact table for one of the cubes (sales) is
being queried in a way which excludes rows if the key to one of the
dimensions (customers) is null. An example is below:

SELECT [dbo_sales].[quantity] AS [dbo_salesquantity0_0],[dbo_sales].[sales]
AS [dbo_salessales0_1],[dbo_sales].[cost] AS
[dbo_salescost0_2],[dbo_sales].[scenarioID] AS
[dbo_salesscenarioID0_3],[dbo_sales].[custID] AS
[dbo_salescustID0_4],[dbo_sales].[periodID] AS
[dbo_salesperiodID0_5],[dbo_sales].[srepID] AS
[dbo_salessrepID0_6],[dbo_sales].[prodID] AS
[dbo_salesprodID0_7],[dbo_customers_6].[terrID] AS [dbo_customersterrID2_0]
FROM [dbo].[sales] AS [dbo_sales],[dbo].[customers] AS [dbo_customers_6]
WHERE
(

(
[dbo_sales].[custID] = [dbo_customers_6].[custID]
)
)

The where clause for this query excludes fact table rows with a null for
their customerID field. However, there are many rows like this in the fact
table, so this is no good. If the query used a left outer join rather than
an inner join it would be OK.

The problem only occurs when a third dimension (territory) is included in
the cube. Territory is a referenced dimension, linked to the fact table
through Customers. If this dimension is taken out of the cube, the problem
goes away.

The Unknown member is enabled and KeyErrorAction is set to ConvertToUnknown
for both Customers and Territory. Can anybody tell me what else I need to do
to solve this problem? I'm assuming there is some other setting I need to
make, but I haven't been able to find anything in BOL.

Thanks,

Peter

[Jéjé]

"fill the blank"

fill your tables in your database with unknown members to insure that you
never have a null value.
your model become more clean and easier to use, specially if you want to
create SQL reports in the future.

"Peter Kenyon" <p.ke...@newsgroups.nospam> wrote in message
news:e8D$lvu8FH...@TK2MSFTNGP12.phx.gbl...

[Peter Kenyon]

"Jéjé" <will...@BBBhotmailAAA.com> wrote in message
news:O$EQQq18F...@TK2MSFTNGP12.phx.gbl...

> "fill the blank"
>
> fill your tables in your database with unknown members to insure that you
> never have a null value.
> your model become more clean and easier to use, specially if you want to
> create SQL reports in the future.
>

I could do that. It's what I would have to do if were were using SSAS2000. I
suppose another alternative would be to use a Named Query as the cube's fact
table, make the query a left outer join between the real fact table and the
customers table, and treat both Customers and Territory as star dimensions
instead of snowflake dimensions. But I would prefer to avoid doing that if
there is some setting I can make which will fix the problem.

If there isn't such a setting, then I would suggest that this is a design
problem. Obviously SSAS2005 is meant to be able to handle missing and null
keys and in most situations it does this very well. However, I would really
like to hear a definitive answer from Microsoft.
In a situation like what we have, where some keys in the fact table to a
dimension are null, and that dimension is also used to join a snowflake
dimension to the fact table, what is the expected behaviour? Can I change it
so that fact table rows aren't filtered?

Thanks,

Peter

[Akshai Mirchandani [MS]]

I posted a reply to your original post -- basically turning off the
Materialized option on the reference dimension should help.

I do agree that this is a bit of a flaw in materialized reference dimensions
and I have filed a DCR to solve it -- unfortunately it isn't an easy change.

Thanks,
Akshai
--
This posting is provided "AS IS" with no warranties, and confers no rights
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

"Peter Kenyon" <p.ke...@newsgroups.nospam> wrote in message

news:%23VGO704...@TK2MSFTNGP15.phx.gbl...