Hello Myles,

For example, you could add a new record in the dimension table with
"value"="unknown". You could create a view fact1 and use this as the fact
table when you create a cube

create view fact1 as
select f.AccountNumber,
case when exists (select d.value from dim1 d where d.value=f.objectvalue )
then f.[Investment Objective]
else 'unknown'
end
as InvestmentObjective,

f.number

from fact f

Regards,

Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support

When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.

=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.




--------------------
| Thread-Topic: Handling Data Integrity Issues in SQL2000
| thread-index: AcVzZJNkPr6fSXC8RMqPvlgseXtMiA==
| X-WBNR-Posting-Host: 12.155.246.10
| From: "=?Utf-8?B?YXBwZGV2dGVjaA==?=" <appdevtech@online.nospam>
| References: <44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@microsoft.com>
<#BtbY5rcFHA.1324@tk2msftngp13.phx.gbl>
| Subject: Re: Handling Data Integrity Issues in SQL2000
| Date: Fri, 17 Jun 2005 10:47:05 -0700
| Lines: 98
| Message-ID: <6CC7E76E-3E1C-49BE-B976-71D0D694B5AB@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:1828
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| Thanks Dave,
| My gut feeling is to fix the RI issues first also, but in this case I can
| use the cube to identify the issues, versus catching the data during
import
| then writing a report to notify the users of the inconsistent data.
|
| When you say "create an UNKNOWN member in the base dimension” do you
mean
| adding an additional record to my dimension table?
|
| Value Description
| A Growth
| B No-Growth
| X Unknown
|
| Then in the view if it does not exist, return X(Unknown)?
|
[quoted text, click to view]
|
| > (microsoft.public.sqlserver.olap is a better newsgroup for a posting
like
| > this, but here goes)
| >
| > My first gut feel is that you should not be allowing this to occur.
This is
| > basic RI between a fact table and the dimension. You should be
processing
| > your dimension to pickup additions prior to processing the fact table.
This
| > will ensure that this doesn't occur if RI is in-place on the RDBMS.
| >
| > The row is disappearing from the fact table because the default SQL
| > statement is an inner join between the fact table and the dimension
table.
| > Thus the row will not be returned to Analysis Services at all . . . we
| > simply don't see it. The RDBMS eliminates it before we get it.
| >
| > In SQL2K, you best option is to create an UNKNOWN member in the base
| > dimension and then load your fact data through a view. In the view use
a
| > CASE clause with an EXISTS and replace the FK being returned based on
| > whether or not that key exists. If it doesn't exist, then return the
UNKNOWN
| > member.
| >
| > In SQL2K5, the system supports an unknown member directly and you can
load
| > data w/ an error configuration which tells it to assign invalid FKs
with the
| > system generated unknown member directly.
| > --
| > Dave Wickert [MSFT]
| > dwickert@online.microsoft.com
| > Program Manager
| > BI SystemsTeam
| > SQL BI Product Unit (Analysis Services)
| > --
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
[quoted text, click to view]
| > > Hello,
| > > I'm looking for some help with analysis services.
| > >
| > > I ahve a very simple fact table which has 2 columns
| > > Account Number and Investment Objective.
| > >
| > > Fact table
| > > Account Number Investment Objective
| > > 12345678 A
| > > 22222222 A
| > > 33333333 B
| > > 44444444 X
| > >
| > > A dimension is needed for the investment objective
| > > So I have a lookup table which is
| > >
| > > Value Description
| > > A Growth
| > > B No-Growth
| > >
| > > There is not an X value in the lookup table so when I look at the
count
| > > for
| > > accounts I only get 3. The account 44444444 never shows up.
| > > Is there a way to have 44444444 or any other account that might get a
| > > value
| > > not in the lookup table to fall into an 'Unknown' type description?
| > >
| > > I understand the best way to solve is to make sure I have a value in
the
| > > lookup table for every value that is in the investment objective
fact, the
| > > problem is I cant control what might get added to it, and we want to
be
| > > able
| > > to have an unknown description and have everything that falls out of
the
| > > range of the lookup go into that. This will allow the users of the
cube
| > > to
| > > find the bad entries and fix them.
| > >
| > > Of course this is sample data and the real tables have millions of
records
| > > and 100's of columns, but I think the basic concept applies.
| > >
| > > Any help or a direction to go in would be greatly appreciated. BTW
this
| > > is
| > > SQL2000
| > > Thanks
| > >
| >
| >
| >
|