MS SQL Server OLAP - Repost - referenced dimension causes fact table to be filtered

Author

Repost - referenced dimension causes fact table to be filtered

Peter Kenyon

2005-11-26, 8:39 pm

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 & #91;dbo_salesquantit
y0_0],[dbo_sales].[sales]
AS & #91;dbo_salessales0_
1],[dbo_sales].[cost] AS
& #91;dbo_salescost0_2
],[dbo_sales].[scenarioID] AS
& #91;dbo_salesscenari
oID0_3],[dbo_sales].[custID] AS
& #91;dbo_salescustID0
_4],[dbo_sales].[periodID] AS
& #91;dbo_salesperiodI
D0_5],[dbo_sales].[srepID] AS
& #91;dbo_salessrepID0
_6],[dbo_sales].[prodID] AS
& #91;dbo_salesprodID0
_7],& #91;dbo_customers_6]
.[terrID] AS & #91;dbo_customerster
rID2_0]
FROM [dbo].[sales] AS [dbo_sales],[dbo].[customers] AS & #91;dbo_customers_6]

WHERE
(

(
[dbo_sales].[custID] = & #91;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é

2005-11-27, 9:23 am

"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.kenyon@newsgroups.nospam> wrote in message
news:e8D$lvu8FHA.1188@TK2MSFTNGP12.phx.gbl...
> 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
> & #91;dbo_salesquantit
y0_0],[dbo_sales].[sales]
> AS & #91;dbo_salessales0_
1],[dbo_sales].[cost] AS
> & #91;dbo_salescost0_2
],[dbo_sales].[scenarioID] AS
> & #91;dbo_salesscenari
oID0_3],[dbo_sales].[custID] AS
> & #91;dbo_salescustID0
_4],[dbo_sales].[periodID] AS
> & #91;dbo_salesperiodI
D0_5],[dbo_sales].[srepID] AS
> & #91;dbo_salessrepID0
_6],[dbo_sales].[prodID] AS
> & #91;dbo_salesprodID0
_7],& #91;dbo_customers_6]
.[terrID] AS
> & #91;dbo_customerster
rID2_0]
> FROM [dbo].[sales] AS [dbo_sales],[dbo].[customers] AS & #91;dbo_customers_6]

> WHERE
> (
>
> (
> [dbo_sales].[custID] = & #91;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
>
>

Peter Kenyon

2005-11-27, 8:24 pm

"Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
news:O$EQQq18FHA.3132@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]

2005-11-29, 8:24 pm

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.kenyon@newsgroups.nospam> wrote in message
news:%23VGO7048FHA.132@TK2MSFTNGP15.phx.gbl...
>
>
> "Jéjé" < willgart@BBBhotmailA
AA.com> wrote in message
> news:O$EQQq18FHA.3132@TK2MSFTNGP12.phx.gbl...
>
> 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
>
>
>
>
>

Sponsored Links