Dimension Attribute that has 13.5million members

The DW and cubes I design all use the Kimball method which I find works really well for me.
I'm using SSSAS 2005 standard.

However, I have come across a problem with a new cube I have just completed. It's quite basic in that there is 1 fact table and 6 dimensions.

The Fact table contains about 13.5 million records.

Of the 6 dimensions 1 of them is a copy of the Fact table. This dimension contains all 13.5million members which have a unique serial number identifying the product sold from the ERP system.

The end user requirement is to be able to select a serial number from the Dimension table and view if or when it was sold from the Fact table.

I decided as part of the ETL process to create a separate Fact table and Dimension table containing the same data rather than allowing BIDS to create the Dimension from the Fact.

Every table has a SK and the joins to the Fact table are via the SK.

My problem occurs when I try to run this simple query:
select
[Measures].[Activated] on columns,
[Serial Number].[Serial No] on rows
from [SerialFact]

The query fails to return a result as it either causes the server to run out of memory or it loses the connection.
I have tested this query on a server with 4GB and 8Gb of memory and it behaves the same way.

I've tried to improve performance by creating aggregations but this hasn't helped.

Any suggestions would be appreciated.

I'm not in a position to upgrade the Server with more memory or move to a 64 bit server.

Thanks in advance.

Having a one-to-one relationship between the dimension and the fact row defeats the purpose of the dimension.

It sounds like the query your users run is an operational query, rather than an analytical one. Wouldn't that be better to run from the operational system?

One way to solve it in the DW is to add the serial number to the Fact table as a degenerate dimension and run the query against the Fact table without the join. Cubes are optimized for aggregation, rather than for grain-level queries.

There are other possible options, but these seem the most obvious to me.

Hope this helps.

Dan

Hi Dan

Thanks for the reply.

The query is operational in nature but data external to the ERP system is being loaded into the DW and related to the ERP data for analysis.

So I've taken your advice and have created a degenerate dimension which I'm currently processing in SSAS.

So thanks again.