Handling Data Integrity Issues in SQL2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

appdevtech - 16 Jun 2005 21:10 GMT

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

Reply to this Message

Myles.Matheson@gmail.com - 16 Jun 2005 22:51 GMT

Hello AppDev,

I am assuming you are using a Star schema in a SQL Server database
somewhere. The Star Schema is used as a basis for the OLAP cube you
are creating.

Remember Analysis Services requires all dimension records to be unique
and fact records to have a relationship with all dimensions. This means
Fact records that do not have a correlating dimension record will not
appear in your cube. (This only happens when you included dimensions
that do not have a relationship with all fact records)

There is a quick solution to this. Create an 'Unknown' record in
your dimension table. When populating the fact table you can return the
unknown surrogate key value to join to the unknown dimension record.

This will make unknown records appear in your cube.

Hope this helps

Myles Matheson
Data Warehouse Architect

Reply to this Message

Dave Wickert [MSFT] - 16 Jun 2005 22:56 GMT

(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.

Signature

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.

> Hello,
> I'm looking for some help with analysis services.
[quoted text clipped - 39 lines]
> SQL2000
> Thanks

Reply to this Message

appdevtech - 17 Jun 2005 18:47 GMT

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

> (microsoft.public.sqlserver.olap is a better newsgroup for a posting like
> this, but here goes)
[quoted text clipped - 61 lines]
> > SQL2000
> > Thanks

Reply to this Message

Peter Yang [MSFT] - 20 Jun 2005 10:33 GMT

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.

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

Signature

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
[quoted text clipped - 21 lines]
|
| When you say "create an UNKNOWN member in the base dimensionâ€ do you

mean

| adding an additional record to my dimension table?
|
[quoted text clipped - 70 lines]
| > > SQL2000
| > > Thanks

Reply to this Message