Hi

Is it possible to have a left join like link between a fact table and a
dimension?
Reason being, If no value is selected in the dimension I want to see all
data in the fact table, not just what is relevant to the fact table.

Is this possible, and how can I do it?

Thanks

In AS 2005, the Unknown Member feature may help you - as described in
Christian Wade's blog:

http://blogs.conchango.com/christian...4/01/1228.aspx
...
The idea with the unknown member is that, for each level of a hierarchy
(or for each attribute in Analysis Services 2005), there is an unknown
member. Members at a lower level that cannot be resolved (no matching
parent), or fact table rows that cannot be resolved, are mapped to the
unknown bucket.

Why would we want to do this?

As we often gather data from various sources, it will sometimes not be
fully synchronised. Let’s say we have a fact table that is dimensioned
by 20 dimension tables. Some fact data happens to come in that is
resolved to 19 of the dimensions, but not to one less important
dimension. This might be because the dimension gets data from a
different source to the fact table and it has not yet been refreshed.
When browsing the cube by any of the 19 dims, we cannot tell that
another dimension is unresolved. When browsing by the unresolved dim,
the data appears under the unknown bucket. Then, 1 or 2 ETL cycles
later, the dimension is refreshed; the facts are fully resolved and
relate to the correct member.

Let’s say the fact table has a Sales measure. If we do not allow for
the unknown member, we will get incorrect sales amounts across all 20
dimensions because not all the sales data is accounted for.
...

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Great Stuff !!!

Seems like we are going to roll out 2005 soon.

Thanks


"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote