hash warning, hash recursion errors

sql2000 sp3.
strange problem here. i've got an extremely complex query written by a
developer. the query works and it comes back in a reasonable amount of
time. however, the query generates a "hash warning/hash recursion"
error when it runs. after reading bol, these errors don't really seem
to be very bad. i decided to investigate it further by restoring the
production db to a test server and try it there. guess what. no hash
warning errors on the test server.
prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
allocated for sql server).
test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
allocated for sql server).
the query runs slower on the test server (as expected) even with little
to no traffic and no hash warnings.
my next guess was that on the prod server, all of sql server's ram was
being used by other objects. i did a dropcleanbuffers and a
freeproccache. query still generates hash warnings.
any ideas as to what would cause the hash warnings on prod server but
not test server?
i can't reboot the prod server and i can't stop sqlserver on prod
server. are there any things i can try in sqlserver to free up
resources other than dropcleanbuffers and freeproccache?Does SQL choose the same execution plan on the production and test server?
It's possible that the plans aren't the same...
also... hashing algorithtms take amount of memory into consideration. So
it's entirely possible that the prod server is making a mistake when it
guesses how much memory will ultimately be available for it.
You might also want to make sure statistics are up to date on prod. Out of
date stats might make the optimizer come up with bad hashing (and other)
decisions...
--
Brian Moran
"chxxx" <chxxx@.dontemailme.com> wrote in message
news:3FBB6FB8.C5FF3195@.dontemailme.com...
> sql2000 sp3.
> strange problem here. i've got an extremely complex query written by a
> developer. the query works and it comes back in a reasonable amount of
> time. however, the query generates a "hash warning/hash recursion"
> error when it runs. after reading bol, these errors don't really seem
> to be very bad. i decided to investigate it further by restoring the
> production db to a test server and try it there. guess what. no hash
> warning errors on the test server.
> prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> allocated for sql server).
> test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> allocated for sql server).
> the query runs slower on the test server (as expected) even with little
> to no traffic and no hash warnings.
> my next guess was that on the prod server, all of sql server's ram was
> being used by other objects. i did a dropcleanbuffers and a
> freeproccache. query still generates hash warnings.
> any ideas as to what would cause the hash warnings on prod server but
> not test server?
> i can't reboot the prod server and i can't stop sqlserver on prod
> server. are there any things i can try in sqlserver to free up
> resources other than dropcleanbuffers and freeproccache?
>
>|||I have a bizarre "Hash Warning" performance issue I am trying to
resolve - any help, explanations or thoughts appreciated:
Configuration:
sql2000 sp3 & as2000 sp3
1Gb RAM, Single 1GHz CPU
Note: All queries/testing described below is performed on the same
database on the same server. Statistics are up to date for this
database.
When drilling through on a cube, Analysis Server generates a T-SQL
SELECT and executes it via a call to sp_prepexec.
For a specific drill-through I am testing (see below for actual
T-SQL), this code takes between 28 and 40 seconds to execute
(regardless of caching).
If I copy the exact query (captured via SQL Profiler) into Query
Analyzer, the same query executes in less than 5 seconds - sub-second
on subsequent executions (i.e. with cached data).
The result is the same slow execution using either Analysis Server's
cube browser or a web-based OLAP client application.
I ran SQL Profiler to capture the execution of this code from both
Analysis Server on Drill-Through and Query Analyzer to determine any
differences, and the only differences I can find are:
1) Query Analyzer event is captured as "SQL:BatchCompleted", whereas
the Analysis Server event is captured as "RPC:Completed"
2) No Warning or Error events are generated by Query Analyzer
executing the code, however the Analysis Server-based execution
generated 3 "HASH WARNING" events. The ObjectIDs captured by SQL
Profiler for these 3 Hash Warnings are 1, 12 and 12 again for the
third event, which map to the "sysobjects" and "sysdepends" tables!!
The specific code being executed for this test case is as follows:
================================================================declare @.P1 int
set @.P1=1
exec sp_prepexec @.P1 output, N'@.P1 tinyint,@.P2 char(3),@.P3 char(3),@.P4
char(3),@.P5 char(3),@.P6 char(3),@.P7 char(3),@.P8 char(3),@.P9
char(3),@.P10 char(3),@.P11 char(3),@.P12 char(3),@.P13 char(3),@.P14
char(3),@.P15 char(3),@.P16 char(3),@.P17 char(3),@.P18 char(3),@.P19
char(3),@.P20 char(3),@.P21 char(3),@.P22 char(3),@.P23 char(3),@.P24
char(3),@.P25 char(3),@.P26 char(3),@.P27 char(3),@.P28 char(3),@.P29
char(3),@.P30 char(3),@.P31 char(3),@.P32 char(3),@.P33 char(3),@.P34
char(3),@.P35 char(3),@.P36 char(3),@.P37 char(3),@.P38 char(3),@.P39
char(3),@.P40 char(3),@.P41 char(3),@.P42 char(3),@.P43 char(3),@.P44
char(3),@.P45 char(3),@.P46 char(3),@.P47 char(3),@.P48 char(3),@.P49
char(3),@.P50 char(3),@.P51 char(3),@.P52 char(3),@.P53 char(3),@.P54
char(3),@.P55 char(3),@.P56 char(3),@.P57 char(3),@.P58 char(3),@.P59
char(3),@.P60 varchar(13),@.P61 int,@.P62 int,@.P63 varchar(7)', N'SELECT
"dbo"."mr_Activity"."DetectionDate",
"dbo"."mr_Activity"."ReversalFlag", "dbo"."mr_Activity"."VenueID",
"dbo"."mr_Activity"."ProductCode",
"dbo"."mr_Activity"."ActivityAmount", "dbo"."mr_Venue"."VenueName",
"dbo"."mr_ProductStructure"."ProductHierarchyName",
"dbo"."mr_AccountRef"."XrefNumber", "dbo"."mr_CaseRef"."WIN" FROM
"dbo"."mr_Activity", "dbo"."mr_ActivityReason", "dbo"."mr_Calendar",
"dbo"."mr_ProductStructure", "dbo"."mr_AccountRef",
"dbo"."mr_CaseRef", "dbo"."mr_Venue" WHERE
(mr_ProductStructure.ProductHierarchyID=mr_Activity.ProductCode AND
mr_AccountRef.DataWarehouseAccountID=mr_Activity.DataWarehouseAccountID
AND mr_CaseRef.DataWarehouseCaseID=mr_Activity.DataWarehouseCaseID)
AND (("dbo"."mr_Venue"."VenueID"="dbo"."mr_Activity"."VenueID")) AND
("dbo"."mr_Activity"."ActivityTypeID"=@.P1) AND
("dbo"."mr_Activity"."ProductCode" IN
(@.P2,@.P3,@.P4,@.P5,@.P6,@.P7,@.P8,@.P9,@.P10,@.P11,@.P12,@.P13,@.P14,@.P15,@.P16,@.P17,@.P18,@.P19,@.P20,@.P21,@.P22,@.P23,@.P24,@.P25,@.P26,@.P27,@.P28,@.P29,@.P30,@.P31,@.P32,@.P33,@.P34,@.P35,@.P36,@.P37,@.P38,@.P39,@.P40,@.P41,@.P42,@.P43,@.P44,@.P45,@.P46,@.P47,@.P48,@.P49,@.P50,@.P51,@.P52,@.P53,@.P54,@.P55,@.P56,@.P57,@.P58,@.P59))
AND ("dbo"."mr_ActivityReason"."BusinessType"=@.P60) AND
("dbo"."mr_Activity"."ReasonID"="dbo"."mr_ActivityReason"."ReasonID")
AND ("dbo"."mr_Activity"."ActivityTypeID"="dbo"."mr_ActivityReason"."ActivityTypeID")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 100) +
DatePart(month,"dbo"."mr_Calendar"."CalendarDate")=@.P61) AND
("dbo"."mr_Calendar"."CalendarDate"="dbo"."mr_Activity"."DetectionDate")
AND (( DatePart(year,"dbo"."mr_Calendar"."CalendarDate") * 10) +
"dbo"."mr_Calendar"."FiscalQtr"=@.P62) AND (''Measure''=@.P63)', 1,
'-36', '-39', '261', '222', '-40', '042', '-42', '259', '221', '-43',
'141', '-45', '030', '025', '026', '027', '028', '029', '-46', '035',
'032', '024', '-47', '282', '283', '284', '285', '286', '287', '-11',
'288', '289', '290', '291', '292', '293', '-11', '294', '295', '296',
'297', '298', '299', '-11', '-11', '-48', '-51', '220', '215', '216',
'217', '218', '219', '-53', '257', '214', '139', '-54', 'New
Business', 200307, 20031, 'Measure'
select @.P1
================================================================
The fact that Hash Warnings are appearing against sysobjects and
sysdepends in the database I find quite bizarre. I suspect these hash
warnings are related to the performance discrepancy betweeen the two
"modes" of execution (1 second vs. 30 seconds).
Any help on resolving (or even explaining) this performance
discrepancy issue is greatly appreciated.
Piquet.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message news:<epmCOPqrDHA.536@.tk2msftngp13.phx.gbl>...
> Does SQL choose the same execution plan on the production and test server?
> It's possible that the plans aren't the same...
> also... hashing algorithtms take amount of memory into consideration. So
> it's entirely possible that the prod server is making a mistake when it
> guesses how much memory will ultimately be available for it.
> You might also want to make sure statistics are up to date on prod. Out of
> date stats might make the optimizer come up with bad hashing (and other)
> decisions...
> --
> Brian Moran
>
> "chxxx" <chxxx@.dontemailme.com> wrote in message
> news:3FBB6FB8.C5FF3195@.dontemailme.com...
> > sql2000 sp3.
> >
> > strange problem here. i've got an extremely complex query written by a
> > developer. the query works and it comes back in a reasonable amount of
> > time. however, the query generates a "hash warning/hash recursion"
> > error when it runs. after reading bol, these errors don't really seem
> > to be very bad. i decided to investigate it further by restoring the
> > production db to a test server and try it there. guess what. no hash
> > warning errors on the test server.
> >
> > prod server has dual pentium 3 at 1ghz with 2 gigs of ram (1.7gb
> > allocated for sql server).
> > test server has dual pentium 3 at 500megahertz with 1 gig of ram (850mb
> > allocated for sql server).
> > the query runs slower on the test server (as expected) even with little
> > to no traffic and no hash warnings.
> >
> > my next guess was that on the prod server, all of sql server's ram was
> > being used by other objects. i did a dropcleanbuffers and a
> > freeproccache. query still generates hash warnings.
> >
> > any ideas as to what would cause the hash warnings on prod server but
> > not test server?
> >
> > i can't reboot the prod server and i can't stop sqlserver on prod
> > server. are there any things i can try in sqlserver to free up
> > resources other than dropcleanbuffers and freeproccache?
> >
> >
> >sql

Hardware sizing documentation?

I am running an SAP system with approximately 250 users (150-170
concurrent). The database is currently around 200Gb in size, running on SQL
2000 standard edition and Windows 2000 standard server. The server is a quad
500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having whats
left. What I am looking for is a rough guide to how much memory the server
should have in relation to the database size. Anybody know of any good
guides to sizing the server hardware? (I realise that to use over 2Gb I need
different versions of SQL and OS)
thanks
GavGav
http://www.sql-server-performance.com/awe_memory.asp
[url]http://www.sql-server-performance.com/performance_monitor_counters_memory.asp[/url
]
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl80ka$m3s$1@.sparta.btinternet.com...
> I am running an SAP system with approximately 250 users (150-170
> concurrent). The database is currently around 200Gb in size, running on
SQL
> 2000 standard edition and Windows 2000 standard server. The server is a
quad
> 500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having
whats
> left. What I am looking for is a rough guide to how much memory the server
> should have in relation to the database size. Anybody know of any good
> guides to sizing the server hardware? (I realise that to use over 2Gb I
need
> different versions of SQL and OS)
> thanks
> Gav
>|||You can also go to HP and IBM websites. They both have sizing tools. You
type in SQL Server, transaction load, write %, size, etc. It recommends a
machine. It's generally pretty accurate, if you're not running anything
wacky.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uJ#ccP1tEHA.904@.TK2MSFTNGP11.phx.gbl...
> Gav
> http://www.sql-server-performance.com/awe_memory.asp
>
http://www.sql-server-performance.c...ters_memory.asp">
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:cl80ka$m3s$1@.sparta.btinternet.com...
> SQL
> quad
> whats
server[vbcol=seagreen]
> need
>

Hardware sizing documentation?

I am running an SAP system with approximately 250 users (150-170
concurrent). The database is currently around 200Gb in size, running on SQL
2000 standard edition and Windows 2000 standard server. The server is a quad
500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having whats
left. What I am looking for is a rough guide to how much memory the server
should have in relation to the database size. Anybody know of any good
guides to sizing the server hardware? (I realise that to use over 2Gb I need
different versions of SQL and OS)
thanks
Gav
Gav
http://www.sql-server-performance.com/awe_memory.asp
http://www.sql-server-performance.co...ers_memory.asp
"Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
news:cl80ka$m3s$1@.sparta.btinternet.com...
> I am running an SAP system with approximately 250 users (150-170
> concurrent). The database is currently around 200Gb in size, running on
SQL
> 2000 standard edition and Windows 2000 standard server. The server is a
quad
> 500Mhz Xeon with 4Gb of ram, 1.7Gb assigned to SQL, SAP and OS having
whats
> left. What I am looking for is a rough guide to how much memory the server
> should have in relation to the database size. Anybody know of any good
> guides to sizing the server hardware? (I realise that to use over 2Gb I
need
> different versions of SQL and OS)
> thanks
> Gav
>
|||You can also go to HP and IBM websites. They both have sizing tools. You
type in SQL Server, transaction load, write %, size, etc. It recommends a
machine. It's generally pretty accurate, if you're not running anything
wacky.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uJ#ccP1tEHA.904@.TK2MSFTNGP11.phx.gbl...
> Gav
> http://www.sql-server-performance.com/awe_memory.asp
>
http://www.sql-server-performance.co...ers_memory.asp[vbcol=seagreen]
> "Gav" <gavin.metcalfe@.nospam.portakabin.com> wrote in message
> news:cl80ka$m3s$1@.sparta.btinternet.com...
> SQL
> quad
> whats
server
> need
>

Hardware scalability

Dear all,
My company has got a Win 2000 SP3 (PIII 1.2Ghz, 1.5Go RAM) server with SQL
2000 SP3a installed (around 90 databases in which around 10 are used daily
not intensively - total data size : 5.5Go - in which 10% is for daily used
databases). SQL Server is setup to use at most 800Mo of RAM. Transactional
replication is implemented. The server is setup as editor only (distributor
and subscribers are on other powerful servers). All is working fine. Network
load is low (10Mo pikes for I/O). We have got around 20 frequent users on
this server.
We are planning to implement two new databases that should represents a
significant increase in workload (frequent heavy batches processes - 1Go of
data). Moreover, we need to integrate them in the transactional replication
process.
We are wondering if our hardware will be sufficient enough to support this
added workload. Yet, I've not found any rule to deduce hardware requirements
from databases size and use.
Could you give me clues for scaling my server, knowing that I have no
similar test server to make benchmarking? Maybe have you similar systems?
Thanks a lot,
Eric.
Eric,
You're correct there's not much to go on here. However, I would point out
one thing. Sounds like the server infrequently services reasonably short
requests. That indicates that the single processor is probably keeping up
with the requests because its generally only getting one request at a time.
So the responsiveness to the users is acceptable. By integrating heavy
batch processes into the mix, there's a strong likelihood that SQL won't
have an internal scheduler free when a user request is initiated. A mix of
heavy batch or large query, with OLTP on too few processors usually results
in end users waiting on screens.
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:DF71D8B2-6184-4631-82F3-6FE96FA81514@.microsoft.com...
> Dear all,
> My company has got a Win 2000 SP3 (PIII 1.2Ghz, 1.5Go RAM) server with SQL
> 2000 SP3a installed (around 90 databases in which around 10 are used daily
> not intensively - total data size : 5.5Go - in which 10% is for daily used
> databases). SQL Server is setup to use at most 800Mo of RAM. Transactional
> replication is implemented. The server is setup as editor only
> (distributor
> and subscribers are on other powerful servers). All is working fine.
> Network
> load is low (10Mo pikes for I/O). We have got around 20 frequent users on
> this server.
> We are planning to implement two new databases that should represents a
> significant increase in workload (frequent heavy batches processes - 1Go
> of
> data). Moreover, we need to integrate them in the transactional
> replication
> process.
> We are wondering if our hardware will be sufficient enough to support this
> added workload. Yet, I've not found any rule to deduce hardware
> requirements
> from databases size and use.
> Could you give me clues for scaling my server, knowing that I have no
> similar test server to make benchmarking? Maybe have you similar systems?
> Thanks a lot,
> Eric.
|||"Danny" <someone@.nowhere.com> wrote in message
news:KpdWe.6396$XO6.2458@.trnddc03...
> Eric,
> You're correct there's not much to go on here. However, I would point out
> one thing. Sounds like the server infrequently services reasonably short
> requests. That indicates that the single processor is probably keeping up
> with the requests because its generally only getting one request at a
> time. So the responsiveness to the users is acceptable. By integrating
> heavy batch processes into the mix, there's a strong likelihood that SQL
> won't have an internal scheduler free when a user request is initiated. A
> mix of heavy batch or large query, with OLTP on too few processors usually
> results in end users waiting on screens.
>
I have to agree with Danny on this one. The right answer (as always with
database) is, It depends.
Do you want to optimize your server for general usage, or do you want to
optimize your server to handle the spikes in performance.
For general usage, I would suggest that you add more RAM to the box. 4GB
total and give 2GB to SQL Server. You will still have spikes, most likely
due to the processor running heavy batches, but should otherwise be in
decent shape. On the replication side of the house, depending on the size
of your transactions which are being replicated and how often replication
occurs (immediate, every 15 minutes etc.). You may want to upgrade your NIC
if possible to 100MB or even 1GB.
If you want to optimize to handle the spikes, then more RAM, 2 procs with
higher speeds and larger L2 caches should help out.
You can read up on a lot of the perf counters to watch for at
www.sql-server-performance.com Take a look at McGeHee's article... It's a
great first step...
http://www.sql-server-performance.co...ance_audit.asp
Rick Sawtell
MCT, MCSD, MCDBA
|||The "standard" config for a dedicated and heavy-duty SQLServer
hardware is 2-processors, all the RAM you can get, at least separate
physical drive for log files, generally RAID-5 for the main DBs.
These days with 200gb drives going for a hundred bux you don't need
RAID just to get your storage size up, but it still helps isolate
physical storage concerns. Network-attached storage is even better,
if you have gigahertz networks. And oh yes, Windows2003, makes
hyperthreading work and has better general threading and COM 1.5+.
Click up Dell and configure such a server, betcha can get a couple of
3ghz processors starting around, um, ... $10k? $15k? Depends. Once
you reach blade-scale, adding another processor is cheap.
Let's say a proper current box like this would be around 5x faster
than a single PIII with a single physical disk drive.
J.
On Thu, 15 Sep 2005 02:00:07 -0700, "itparis"
<itparis@.discussions.microsoft.com> wrote:

>Dear all,
>My company has got a Win 2000 SP3 (PIII 1.2Ghz, 1.5Go RAM) server with SQL
>2000 SP3a installed (around 90 databases in which around 10 are used daily
>not intensively - total data size : 5.5Go - in which 10% is for daily used
>databases). SQL Server is setup to use at most 800Mo of RAM. Transactional
>replication is implemented. The server is setup as editor only (distributor
>and subscribers are on other powerful servers). All is working fine. Network
>load is low (10Mo pikes for I/O). We have got around 20 frequent users on
>this server.
>We are planning to implement two new databases that should represents a
>significant increase in workload (frequent heavy batches processes - 1Go of
>data). Moreover, we need to integrate them in the transactional replication
>process.
>We are wondering if our hardware will be sufficient enough to support this
>added workload. Yet, I've not found any rule to deduce hardware requirements
>from databases size and use.
>Could you give me clues for scaling my server, knowing that I have no
>similar test server to make benchmarking? Maybe have you similar systems?
>Thanks a lot,
>Eric.

Hardware scalability

Dear all,
My company has got a Win 2000 SP3 (PIII 1.2Ghz, 1.5Go RAM) server with SQL
2000 SP3a installed (around 90 databases in which around 10 are used daily
not intensively - total data size : 5.5Go - in which 10% is for daily used
databases). SQL Server is setup to use at most 800Mo of RAM. Transactional
replication is implemented. The server is setup as editor only (distributor
and subscribers are on other powerful servers). All is working fine. Network
load is low (10Mo pikes for I/O). We have got around 20 frequent users on
this server.
We are planning to implement two new databases that should represents a
significant increase in workload (frequent heavy batches processes - 1Go of
data). Moreover, we need to integrate them in the transactional replication
process.
We are wondering if our hardware will be sufficient enough to support this
added workload. Yet, I've not found any rule to deduce hardware requirements
from databases size and use.
Could you give me clues for scaling my server, knowing that I have no
similar test server to make benchmarking? Maybe have you similar systems?
Thanks a lot,
Eric.Eric,
You're correct there's not much to go on here. However, I would point out
one thing. Sounds like the server infrequently services reasonably short
requests. That indicates that the single processor is probably keeping up
with the requests because its generally only getting one request at a time.
So the responsiveness to the users is acceptable. By integrating heavy
batch processes into the mix, there's a strong likelihood that SQL won't
have an internal scheduler free when a user request is initiated. A mix of
heavy batch or large query, with OLTP on too few processors usually results
in end users waiting on screens.
"itparis" <itparis@.discussions.microsoft.com> wrote in message
news:DF71D8B2-6184-4631-82F3-6FE96FA81514@.microsoft.com...
> Dear all,
> My company has got a Win 2000 SP3 (PIII 1.2Ghz, 1.5Go RAM) server with SQL
> 2000 SP3a installed (around 90 databases in which around 10 are used daily
> not intensively - total data size : 5.5Go - in which 10% is for daily used
> databases). SQL Server is setup to use at most 800Mo of RAM. Transactional
> replication is implemented. The server is setup as editor only
> (distributor
> and subscribers are on other powerful servers). All is working fine.
> Network
> load is low (10Mo pikes for I/O). We have got around 20 frequent users on
> this server.
> We are planning to implement two new databases that should represents a
> significant increase in workload (frequent heavy batches processes - 1Go
> of
> data). Moreover, we need to integrate them in the transactional
> replication
> process.
> We are wondering if our hardware will be sufficient enough to support this
> added workload. Yet, I've not found any rule to deduce hardware
> requirements
> from databases size and use.
> Could you give me clues for scaling my server, knowing that I have no
> similar test server to make benchmarking? Maybe have you similar systems?
> Thanks a lot,
> Eric.|||"Danny" <someone@.nowhere.com> wrote in message
news:KpdWe.6396$XO6.2458@.trnddc03...
> Eric,
> You're correct there's not much to go on here. However, I would point out
> one thing. Sounds like the server infrequently services reasonably short
> requests. That indicates that the single processor is probably keeping up
> with the requests because its generally only getting one request at a
> time. So the responsiveness to the users is acceptable. By integrating
> heavy batch processes into the mix, there's a strong likelihood that SQL
> won't have an internal scheduler free when a user request is initiated. A
> mix of heavy batch or large query, with OLTP on too few processors usually
> results in end users waiting on screens.
>
I have to agree with Danny on this one. The right answer (as always with
database) is, It depends.
Do you want to optimize your server for general usage, or do you want to
optimize your server to handle the spikes in performance.
For general usage, I would suggest that you add more RAM to the box. 4GB
total and give 2GB to SQL Server. You will still have spikes, most likely
due to the processor running heavy batches, but should otherwise be in
decent shape. On the replication side of the house, depending on the size
of your transactions which are being replicated and how often replication
occurs (immediate, every 15 minutes etc.). You may want to upgrade your NIC
if possible to 100MB or even 1GB.
If you want to optimize to handle the spikes, then more RAM, 2 procs with
higher speeds and larger L2 caches should help out.
You can read up on a lot of the perf counters to watch for at
www.sql-server-performance.com Take a look at McGeHee's article... It's a
great first step...
http://www.sql-server-performance.c...mance_audit.asp
Rick Sawtell
MCT, MCSD, MCDBA|||The "standard" config for a dedicated and heavy-duty SQLServer
hardware is 2-processors, all the RAM you can get, at least separate
physical drive for log files, generally RAID-5 for the main DBs.
These days with 200gb drives going for a hundred bux you don't need
RAID just to get your storage size up, but it still helps isolate
physical storage concerns. Network-attached storage is even better,
if you have gigahertz networks. And oh yes, Windows2003, makes
hyperthreading work and has better general threading and COM 1.5+.
Click up Dell and configure such a server, betcha can get a couple of
3ghz processors starting around, um, ... $10k? $15k? Depends. Once
you reach blade-scale, adding another processor is cheap.
Let's say a proper current box like this would be around 5x faster
than a single PIII with a single physical disk drive.
J.
On Thu, 15 Sep 2005 02:00:07 -0700, "itparis"
<itparis@.discussions.microsoft.com> wrote:

>Dear all,
>My company has got a Win 2000 SP3 (PIII 1.2Ghz, 1.5Go RAM) server with SQL
>2000 SP3a installed (around 90 databases in which around 10 are used daily
>not intensively - total data size : 5.5Go - in which 10% is for daily used
>databases). SQL Server is setup to use at most 800Mo of RAM. Transactional
>replication is implemented. The server is setup as editor only (distributor
>and subscribers are on other powerful servers). All is working fine. Networ
k
>load is low (10Mo pikes for I/O). We have got around 20 frequent users on
>this server.
>We are planning to implement two new databases that should represents a
>significant increase in workload (frequent heavy batches processes - 1Go of
>data). Moreover, we need to integrate them in the transactional replication
>process.
>We are wondering if our hardware will be sufficient enough to support this
>added workload. Yet, I've not found any rule to deduce hardware requirement
s
>from databases size and use.
>Could you give me clues for scaling my server, knowing that I have no
>similar test server to make benchmarking? Maybe have you similar systems?
>Thanks a lot,
>Eric.

Hardware requirement

Hello!
Does anyone have any idea what the hardware requirements for a SQL2000 box
would be if I would like to process about 20000 - 30000 inserts per second?
We need to insert large amount of data in SQL database and that would be
peek number of inserts that we need. Any rough numbers? (number of
processors, RAM, disk subsytem configuration...) Database size and number of
user conections are not an important factor at the time since they are quite
small (100GB, 50 connections).
Does anyone have similar processing power on SQL?
Thanks
Dan20-30k inserts/sec is possible on a 2 CPU Xeon, but it
depends on exactly what you are doing and how.
if the only meaningful load is the inserts, then a dual
processor system should be able to handle your load,
otherwise, you might go to a 4 CPU box
RAM and disks will depend on the specifics of what your
are doing
i will be talking on this subject at the next SQL Server
Magazine Connections conference (www.sqlconnections.com)
-joe chang
>--Original Message--
>Hello!
>Does anyone have any idea what the hardware requirements
for a SQL2000 box
>would be if I would like to process about 20000 - 30000
inserts per second?
>We need to insert large amount of data in SQL database
and that would be
>peek number of inserts that we need. Any rough numbers?
(number of
>processors, RAM, disk subsytem configuration...) Database
size and number of
>user conections are not an important factor at the time
since they are quite
>small (100GB, 50 connections).
>Does anyone have similar processing power on SQL?
>Thanks
>Dan
>
>.
>

Handling Data Integrity Issues in SQL2000

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
ThanksHello 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|||(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.
"appdevtech" <appdevtech@.online.nospam> wrote in message
news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
> 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
>|||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)?
"Dave Wickert [MSFT]" wrote:

> (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 i
s
> basic RI between a fact table and the dimension. You should be processing
> your dimension to pickup additions prior to processing the fact table. Thi
s
> 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 UNKNO
WN
> 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 t
he
> 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
.
>
> "appdevtech" <appdevtech@.online.nospam> wrote in message
> news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
>
>|||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: "examnotes" <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)?
|
| "Dave Wickert [MSFT]" wrote:
|
| > (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.
| >
| >
| > "appdevtech" <appdevtech@.online.nospam> wrote in message
| > news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
| > > 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
| > >
| >
| >
| >
|

Handling Data Integrity Issues in SQL2000

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 NumberInvestment Objective
12345678A
22222222A
33333333B
44444444X
A dimension is needed for the investment objective
So I have a lookup table which is
ValueDescription
AGrowth
BNo-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
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
|||(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.
"appdevtech" <appdevtech@.online.nospam> wrote in message
news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
> 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
>
|||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)?
"Dave Wickert [MSFT]" wrote:

> (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.
>
> "appdevtech" <appdevtech@.online.nospam> wrote in message
> news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
>
>
|||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)?
|
| "Dave Wickert [MSFT]" wrote:
|
| > (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.
| >
| >
| > "appdevtech" <appdevtech@.online.nospam> wrote in message
| > news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
| > > 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
| > >
| >
| >
| >
|