Re: Validating Surrogate Keys

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: TP (thom_at_pantazi.net)
Date: 11/09/04 

Date: 8 Nov 2004 19:58:46 -0800

Thanks, Dave. We aren't using Analysis Services. We are using Cognos
to build our cubes, it doesn't such a feature. What do you think of
the script idea?

"Dave Wickert [MSFT]" <dwickert@online.microsoft.com> wrote in message news:<ekhnpwexEHA.3376@TK2MSFTNGP12.phx.gbl>...
> Using Analysis Services, we fully expect that RI will not necessarily be
> in-place with your DW. Thus when we are building cubes from this data,
> Analysis Services has an optional exception reporting facility where it will
> log off to a separate file any records which are processed into cubes with
> missing dimension entries. This allows you to collect (and report on) which
> facts have missing surrogate keys.
> --
> 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.
>
> "TP" <thom@pantazi.net> wrote in message
> news:7d353aba.0411081318.645fafc5@posting.google.com...
> > We have a small data warehouse (less than 50 million rows in roughly
> > 75 gigabytes) however, it is growing steadily. We have noticed that
> > we get much better performance on queries when there are no foreign
> > key constraints. We have demonstrated a 14% improvement. I am aware
> > that many data warehouses are designed without foreign key constraints
> > to improve the load performance of the ETL process. We have yet to
> > need that, as a matter of fact until now, we have used a script to
> > build the constraints before the ETL process and drop them after it.
> >
> > The process of rebuilding the constraints is time consuming. As more
> > data is put into the warehouse the time to build the constraints will
> > continue to become longer. At some point we anticipate it impacting
> > our batch window. It has been my recommendation that we completely
> > remove the constraints and leave them off.
> >
> > Since our use for the constraints is to check the integrity of the ETL
> > process, I was wondering if anyone has a methodology for validating
> > the surrogate keys besides building foreign key constraints. We have
> > considered building a series of test scripts that will perform selects
> > against the data looking for invalid key values.
> >
> > Thanks,
> > Thom

Relevant Pages

  • Re: Add columns to table, but in an order
    ... in the order that SQL Server mentions them, with no way for the end user ... existing constraints and recreate them when the new table is in place. ... be very critical of the script ... Make sure to test the conversion on a test server first. ...
    (microsoft.public.sqlserver.server)
  • Re: Turning Off Foreign Keys during DB coping
    ... will disable and then reenable the constraints. ... > Do you suggest me to write the script for each table to disable keys? ... >> CREATE TABLE Test2 ...
    (microsoft.public.sqlserver.dts)
  • Re: cannot ad hoc on system tables on SQL server 2005
    ... identical so the constraints are not identical,this is the reason i'm ... You can lighten your load a bit by using Management Studio to script the ... Changing schemas will block data operations, ... Creating a new database and importing the data (using Integration Services ...
    (microsoft.public.sqlserver.programming)
  • Re: cannot ad hoc on system tables on SQL server 2005
    ... identical so the constraints are not identical,this is the reason i'm ... If you choose to script all tables and set the option to script constraints as well, Management Studio will put all DEFAULT, CHECK and FOREIGN KEY constraints at the end of the script. ... Changing schemas will block data operations, which can be a problem for large tables. ... Creating a new database and importing the data is a more attractive option if you're changing many fields. ...
    (microsoft.public.sqlserver.programming)
  • Re: Validating Surrogate Keys
    ... Analysis Services has an optional exception reporting facility where it will ... > We have a small data warehouse (less than 50 million rows in roughly ... > that many data warehouses are designed without foreign key constraints ...
    (microsoft.public.sqlserver.datawarehouse)