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

Author

Referenced dimension causes fact table to be filtered

Peter Kenyon

2005-11-24, 8:23 pm

Hi,

One of the dimensions in my cube is a reference dimension ("Territory"),
which is linked to the fact table through an intermediate dimension
("Customers"). This is causing the fact table to be queried using an inner
join when it is processed, and as a result many rows are filtered out of the
fact table.

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]
)

)

Here, [dbo].[customers] is the name of the dimension table for the
intermediate dimension.

In this cube, some fact table rows will have their customerID field null and
some rows from the Customers dimension table have their territoryID field
null. I have enabled UnknownMember for both dimensions, but as you can see
the query issued by SSAS still excludes many rows when processing.

What do I need to do to prevent this? I'm sure there is a setting or
property which does what I want, but I haven't been able to find it in BOL.

Thanks,

Peter

Milind

2005-11-25, 1:23 pm

In AS2000, I am not aware of any such property. One way to do this is
to add an unknown entry in the dimension table (or view) and do an
isnull on the fact table/view referencing this column.

HTH

Milind

Peter Kenyon

2005-11-25, 1:23 pm

"Milind" <milind.bhabal@gmail.com> wrote in message
news:1132941503.625689.319770@g44g2000cwa.googlegroups.com...
> In AS2000, I am not aware of any such property. One way to do this is
> to add an unknown entry in the dimension table (or view) and do an
> isnull on the fact table/view referencing this column.
>
> HTH
>
> Milind

Sorry, I forgot to make clear in my post that we are using SSAS2005.

Peter

Akshai Mirchandani [MS]

2005-11-29, 8:24 pm

Make the reference dimension unmaterialized... That will cause a perf
slowdown at query time because the lookups will now need to happen on the
fly. But it should prevent the reference dimension tables from causing facts
to be eliminated.

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.no.spam@paradise.net.nz> wrote in message
news:ukB4q4e8FHA.3804@TK2MSFTNGP12.phx.gbl...
>
> "Milind" <milind.bhabal@gmail.com> wrote in message
> news:1132941503.625689.319770@g44g2000cwa.googlegroups.com...
>
> Sorry, I forgot to make clear in my post that we are using SSAS2005.
>
> Peter
>

Sponsored Links