Re: Left Join like link between Dimension and Fact table

Great Stuff !!!

Seems like we are going to roll out 2005 soon.

Thanks


"Deepak Puri" <deepak_puri@xxxxxxxxxxxxxxx> wrote in message
news:OlweCisMGHA.500@xxxxxxxxxxxxxxxxxxxxxxx
In AS 2005, the Unknown Member feature may help you - as described in
Christian Wade's blog:

http://blogs.conchango.com/christianwade/archive/2005/04/01/1228.aspx

Unknown Member and Analysis Services 2005
..
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 ***


.

Relevant Pages

  • Re: 2005 MDX
    ... Territory].[Sales Territory Country].currentmember}, ... MEMBER Measures.B AS [Sales Territory].[Sales Territory ... Territory Country] from Sales Territory dimension but when i put any ...
    (microsoft.public.sqlserver.olap)
  • Re: 2005 MDX
    ... Territory].[Sales Territory Country].currentmember}, ... MEMBER Measures.B AS [Sales Territory].[Sales Territory ... Territory Country] from Sales Territory dimension but when i put any ...
    (microsoft.public.sqlserver.olap)
  • Re: 2005 MDX
    ... Territory].[Sales Territory Country].currentmember}, ... MEMBER Measures.B AS [Sales Territory].[Sales Territory ... Territory Country] from Sales Territory dimension but when i put any ...
    (microsoft.public.sqlserver.olap)
  • Re: 2005 MDX
    ... MEMBER Measures.B AS [Sales Territory].[Sales Territory ... Territory Country] from Sales Territory dimension but when i put any other ...
    (microsoft.public.sqlserver.olap)
  • Re: Unknown Members
    ... It's becuase the dimension key in the fact table do not exist in the ... showing Unknown as an additional member in almost every dimension. ... sometimes it is showing the whole amount against Unknown ...
    (microsoft.public.sqlserver.datawarehouse)