Re: Handling Data Integrity Issues in SQL2000

(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@xxxxxxxxxxxxxxxxxxxx
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"appdevtech" <appdevtech@xxxxxxxxxxxxx> wrote in message
news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@xxxxxxxxxxxxxxxx
> 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
>


.

Relevant Pages

  • Re: Handling Data Integrity Issues in SQL2000
    ... When you say "create an UNKNOWN member in the base dimension” ... > basic RI between a fact table and the dimension. ... >> There is not an X value in the lookup table so when I look at the count ... This will allow the users of the cube ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Handling Data Integrity Issues in SQL2000
    ... you could add a new record in the dimension table with ... table when you create a cube ... you best option is to create an UNKNOWN member in the base ... |>> There is not an X value in the lookup table so when I look at the ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Unknown member question
    ... It's true that dimension key errors should not occur because the dimension ... all dimension have a Unknown Member by default. ...
    (microsoft.public.sqlserver.datawarehouse)
  • Re: Left Join like link between Dimension and Fact table
    ... The idea with the unknown member is that, for each level of a hierarchy ... by 20 dimension tables. ... When browsing by the unresolved dim, ... Let?s say the fact table has a Sales measure. ...
    (microsoft.public.sqlserver.olap)
Loading