new problem

I got the following error message when I do an insert trigger, can anyone
please help? Thanks.
Server: Msg 8115, Level 16, State 8, Procedure ADM_APRN_ins_upd_trg, Line
108
Arithmetic overflow error converting numeric to data type numeric.
The statement has been terminated.Pls post "ADM_APRN_ins_upd_trg" code, or continue using
imagination. Thanks.
Regards,
Alfred.
>--Original Message--
>I got the following error message when I do an insert
trigger, can anyone
>please help? Thanks.
>Server: Msg 8115, Level 16, State 8, Procedure
ADM_APRN_ins_upd_trg, Line
>108
>Arithmetic overflow error converting numeric to data type
numeric.
>The statement has been terminated.
>
>.
>|||Hello Alfred,
There is very little we can do here.
The problem as the error states on in line 108. I am going
to assume that it is some sort of maths function being
carried out. Whatever it is been converted into does not
like it.
Could you post that line and the 5 lines before and after
it.
Peter
"A man is never more truthful than when he acknowledges
himself a liar."
Mark Twain
>--Original Message--
>Pls post "ADM_APRN_ins_upd_trg" code, or continue using
>imagination. Thanks.
>Regards,
>Alfred.
>>--Original Message--
>>I got the following error message when I do an insert
>trigger, can anyone
>>please help? Thanks.
>>Server: Msg 8115, Level 16, State 8, Procedure
>ADM_APRN_ins_upd_trg, Line
>>108
>>Arithmetic overflow error converting numeric to data
type
>numeric.
>>The statement has been terminated.
>>
>>.
>.
>

new patch for 32 bit and 64 bit SQL server

How do you determine if a server is running the 32 bit or
64 bit version. I have seen the query to determine service
pack level but I don't see an indicator to determine if a
server is 32 bit or 64 bit. There is a different patch
dependant on this value.>--Original Message--
>Pat,
>Is it the season? lol :-)
>I just answered a similar query a while back...
>--
>Dinesh.
>SQL Server FAQ at
>http://www.tkdinesh.com
>"Pat" <mcilweep@.ctbsonline.com> wrote in message
>news:01ee01c352d6$7d4ca5c0$a301280a@.phx.gbl...
>> How do you determine if a server is running the 32 bit
or
>> 64 bit version. I have seen the query to determine
service
>> pack level but I don't see an indicator to determine if
a
>> server is 32 bit or 64 bit. There is a different patch
>> dependant on this value.
>
>.
>I appreciate your reply but still don't see the info I
need. I found the info about determining the SP of SQL on
your website FAQ's, but I don't see anything that
indicates that it is a 32 bit or 64 bit system. What am I
missing?|||Pat,
May be I got carried away because I answered couple of similar posts
one-by-one in .server :-) The latest one was by Aparna Rege.Anyways, this is
the info:
You should be able to tell by running a SELECT @.@.version against your SQL
Server.
On 32-bit, you will see the following:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86)
May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 1)
On 64-bit, the architecture should be Intel IA-64
Microsoft SQL Server 2000 - 8.00.818 (Intel IA-64)
Thanks to : Arvind Krishnan
--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com
"Pat" <mcilweep@.ctbsonline.com> wrote in message
news:03cb01c352e6$10f67990$a101280a@.phx.gbl...
> >--Original Message--
> >Pat,
> >
> >Is it the season? lol :-)
> >
> >I just answered a similar query a while back...
> >
> >--
> >Dinesh.
> >SQL Server FAQ at
> >http://www.tkdinesh.com
> >
> >"Pat" <mcilweep@.ctbsonline.com> wrote in message
> >news:01ee01c352d6$7d4ca5c0$a301280a@.phx.gbl...
> >> How do you determine if a server is running the 32 bit
> or
> >> 64 bit version. I have seen the query to determine
> service
> >> pack level but I don't see an indicator to determine if
> a
> >> server is 32 bit or 64 bit. There is a different patch
> >> dependant on this value.
> >
> >
> >.
> >I appreciate your reply but still don't see the info I
> need. I found the info about determining the SP of SQL on
> your website FAQ's, but I don't see anything that
> indicates that it is a 32 bit or 64 bit system. What am I
> missing?

New Member | Need Tutorial

Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAKHere's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK|||Here is a link to Books Online
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
--
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
? Administering Microsoft SQL Server 2000
? Programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
--
Br,
Mark Broadbent
mcdba , mcse+i
============="GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||If your new to SQL Server as an Administrator then I would
recommend the book SQL Server 2000 Step By Step. Its a
really good book for the beginner, I use it in mentoring
new dba's.
J
>--Original Message--
>Hi All,
> I am a new member of this group. Can any one guide me to
get more basic and
>intermediate level of learning MS-SQL-Server. I need
tutorials, downloads
>thank you
>GAK
>
>.
>

New Member | Need Tutorial

Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK
Here's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK
|||Here is a link to Books Online
http://www.microsoft.com/sql/techinf...2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
Administering Microsoft SQL Server 2000
Programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
|||w
"GAK" wrote:

> Hi All,
> I am a new member of this group. Can any one guide me to get more basic and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
>

New Member | Need Tutorial

Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAKHere's a list of various resources:
http://vyaskn.tripod.com/sqlserverres.htm
But don't forget to read Books Online, that gets installed with SQL Server.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
Hi All,
I am a new member of this group. Can any one guide me to get more basic and
intermediate level of learning MS-SQL-Server. I need tutorials, downloads
thank you
GAK|||Here is a link to Books Online
http://www.microsoft.com/sql/techin.../2000/books.asp
and a good beginner book was SQL Server for Dummies. And that is not a
joke. It was all around a good learning tool for begining.
Jeff Duncan
MCDBA, MCSE+I
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||Start with Books On-Line (shortened to BOL by many posters). I also
recommend Inside SQL Server 2000 by Kalen Delaney. This is a real book that
you will need to purchase, but it should take you a long way towards your
goals. After you work with these for a while, you will likely need in-depth
information on specific areas of SQL server. We (the community) will be
happy to share our opinions on other books and learning resources.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||GAK, there are so many different websites offering information on SQL Server
you will be amazed.
First you need a starting point of learning and there are two distinct paths
(although inter-related)
Administering Microsoft SQL Server 2000
programming Microsoft SQL Server 2000
If you are not a developer then you will almost certainly be looking to
learn the first route. And even if you are then the first route is probably
still a good idea.
I could give you loads of suggestions for books, but quite frankly the best
idea for you would be to pop into a book shop and look through several until
you find one that is going to be the right fit for you.
--
Br,
Mark Broadbent
mcdba , mcse+i
=============
"GAK" <galchemy01@.hotmail.com> wrote in message
news:%239feQTeMEHA.624@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I am a new member of this group. Can any one guide me to get more basic
and
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>|||w
"GAK" wrote:

> Hi All,
> I am a new member of this group. Can any one guide me to get more basic a
nd
> intermediate level of learning MS-SQL-Server. I need tutorials, downloads
> thank you
> GAK
>
>

New Java driver for SqlServer

Theres been rumors on a future effort in building a new JDBC driver to support the SqlServer 2k5. What level of JDBC will it strive to accomplish? Can it be a more open project? Also, the old driver perhaps lacks some things that higher JDBC specs feature so it would be nice if it could provide some sort of backward compatibility.
It would be nice to have the new RowSet thingies and some generics support.Hi schrepfler,

where can I get that old jdbc driver from? and did you use it with 2005 ?|||The old driver (latest version is SP3) can be found here. It's not a bad driver but it's old in architecture and should be updated. I haven't tried it with 2k5 as it is said it's not compatible plus I haven't installed sqlserver 2k5 yet (having problems in installation procedure).|||Thank you!!|||The best place to ask this is the Data Access forum, but we are working on the new driver so now is the time to give the feedback.

-Euan|||I thought first we have a alpha or beta product then we give feedback?|||You can wait but the earlier we get the feedback the better|||Ok, you convinced me, you want me to go to the DataAccess forum and ask what I want in the driver?|||Can I get a beta of the new Driver? I'm developing a CMS using a MySQL Database and would like to see if SQL Server 2005 is more powerful.|||Yep please do, we want all the input we can get.

-Euan

New Database System Overview

Hi guys,
I am currently finishing off my high level structure for the new
business intelligence database system for my company and without going
into too much detail I would like to get your comments on it. Maybe
there is something you have tried that did/didn't work. Any comments
at all would be appreciated.
I have a pretty standard system plan in place. We have about 10 OLTP
systems/databases currently and all vary in the amount of traffic from
low (5 transactions a minute) to heavy (5-30 rows a second). We have
thousands of financial terminals all over the world and these
terminals feed our OLTP servers in near real time. Our customer
service team must have near real time access to this data so this is
where I want to make use of an ODS. My thought is to develop a
messaging system from all of the OLTP systems and stage this data to
this ODS. This will be where the tech support/ops/customer care teams
can have instant access to the data and be able to support the
customers with a real time view to the session events. Much of our
daily reporting and information gathering is done by the
afforementioned teams. Customer payments, machine health, peripheral
activity and software events are all gathered and sent back. I am
designnig the ODS so our employees can make quick decisions based on
the incoming events. Separate from the ODS, I am also going to
implement a data warehouse. This area will contain more long term
trending of transactions and other data used for executive decisions.
I have already designed and successfully built data warehouses before
so I am very confident in this area. The area that I do have a few
questions is the bridge between the ODS and the data warehouse. I have
read many articles dealing with real time data warehousing and ODS ETL
and many people have different opinions on what they think is the way
to go. My initial thought is to stage OLTP data to the ODS in near
real time. From there I will stage the data into the data warehouse
every night as we have the hardware to do it this frequently. So my
question is:
Should I run two systems (ODS/DW) side by side for long periods of
time and use the systems as separate entities for different groups of
people or should I use the ODS for say just the current day and the
run any earlier reporting off the data warehouse?
I know having redundant data costs money and greater effort to manage
but we have the resources to back this if need be. Our OPS team needs
data in real time, our financial team needs it daily and the executive
team needs it monthly so the ODS would serve the OPS team pretty much
exclusively.
Does anyone have any general comments to describe their experiences in
designing and building similiar systems? Our data flow is not overly
large right now but it is growing exponentially every month and I want
to be ready for it years down the road. Thanks for reading my long
winded article and I would love to hear all of your thoughts. It is
late on a Sunday night and I am very tired so I apologize if my
article is not gramatically ready for tomorrow's Wall Street
Journal . Thanks in advance.
CoreyHi Corey,
this is an interesting scenario.
I can only do some general consideration.
It would be important to know if you are going to use Analysis
Services or not. One of the new features of Analysis Services is the
ability to directly push data into dimensions and measure group
partitions. This means that you could implement an almost continuous
update of the cube with data that are pushed into analysis services
directly from a queue that is fed by the middle tier. This can be
useful when you cannot afford a query on OLTP during daily operations.
Anyway, there are a wide range of opportunities here, ranging from
these extremely optimized one to the classical relational data
warehouse with intra-day operations that is updated frequently and
consolidated into a larger data warehouse nightly.
I you want to design a "classical" relational data warehouse, the best
practice is to design a simpler star schema, with fewer dimensions
than the big one, just to make sure that you are able to insert data
into fact table in a fast way. It is important to use some decoupling
technique between the OLTP transaction and the ODS update: Message
Queue, Service Broker or something like this.
I would not consider using a single data warehouse with even the intra-
day updates, unless both data warehouse and daily values can share the
same dimensions, both in number and in contents. In that case, I would
create a partition (or a separate fact table) with only the intra-day
operation, and then each night I would consolidate data making all
necessary cleansing operations.
Good work!
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Jan 29, 9:10 am, kild...@.yahoo.ca wrote:
> Hi guys,
> I am currently finishing off my high level structure for the new
> business intelligence database system for my company and without going
> into too much detail I would like to get your comments on it. Maybe
> there is something you have tried that did/didn't work. Any comments
> at all would be appreciated.
> I have a pretty standard system plan in place. We have about 10 OLTP
> systems/databases currently and all vary in the amount of traffic from
> low (5 transactions a minute) to heavy (5-30 rows a second). We have
> thousands of financial terminals all over the world and these
> terminals feed our OLTP servers in near real time. Our customer
> service team must have near real time access to this data so this is
> where I want to make use of an ODS. My thought is to develop a
> messaging system from all of the OLTP systems and stage this data to
> this ODS. This will be where the tech support/ops/customer care teams
> can have instant access to the data and be able to support the
> customers with a real time view to the session events. Much of our
> daily reporting and information gathering is done by the
> afforementioned teams. Customer payments, machine health, peripheral
> activity and software events are all gathered and sent back. I am
> designnig the ODS so our employees can make quick decisions based on
> the incoming events. Separate from the ODS, I am also going to
> implement a data warehouse. This area will contain more long term
> trending of transactions and other data used for executive decisions.
> I have already designed and successfully built data warehouses before
> so I am very confident in this area. The area that I do have a few
> questions is the bridge between the ODS and the data warehouse. I have
> read many articles dealing with real time data warehousing and ODS ETL
> and many people have different opinions on what they think is the way
> to go. My initial thought is to stage OLTP data to the ODS in near
> real time. From there I will stage the data into the data warehouse
> every night as we have the hardware to do it this frequently. So my
> question is:
> Should I run two systems (ODS/DW) side by side for long periods of
> time and use the systems as separate entities for different groups of
> people or should I use the ODS for say just the current day and the
> run any earlier reporting off the data warehouse?
> I know having redundant data costs money and greater effort to manage
> but we have the resources to back this if need be. Our OPS team needs
> data in real time, our financial team needs it daily and the executive
> team needs it monthly so the ODS would serve the OPS team pretty much
> exclusively.
> Does anyone have any general comments to describe their experiences in
> designing and building similiar systems? Our data flow is not overly
> large right now but it is growing exponentially every month and I want
> to be ready for it years down the road. Thanks for reading my long
> winded article and I would love to hear all of your thoughts. It is
> late on a Sunday night and I am very tired so I apologize if my
> article is not gramatically ready for tomorrow's Wall Street
> Journal . Thanks in advance.
> Corey|||Thanks for the reply Marco. I think we are along the same lines here.
I guess my biggest concern is the difference in the requirements of
our operations department and the rest of the company. The biggest
challenge will be to "combine" their needs within the ODS with the
warehouse. Also, transactions can be voided days later and since the
datawarehouse is a non volitile area I am going have to figure out how
to make changes to the fact tables or wait a few days before the
warehouse gets staged. So, in your opinion,would you run the ODS side
by side with the warehouse to serve the functional needs of the
different corporate departments or should I try my best to get all of
the info into the warehouse and use the ODS for only 1 day or so worth
of data. I am just concerned that the two systems will not mesh well
enough to support everyone involved. Thanks for your input. It is very
much appreciated.
BTW... we haven't made up our minds on Analysis server yet but I am
going to definitely do some research on its feasibility here.
Corey|||Corey,
Your approach sounds reasonable. If I remember correctly, Kimball addresses
the ODS in his book: The Data Warehouse Lifecycle Toolkit.
-- Bill
<kildenc@.yahoo.ca> wrote in message
news:1170058228.206470.269570@.s48g2000cws.googlegroups.com...
> Hi guys,
> I am currently finishing off my high level structure for the new
> business intelligence database system for my company and without going
> into too much detail I would like to get your comments on it. Maybe
> there is something you have tried that did/didn't work. Any comments
> at all would be appreciated.
> I have a pretty standard system plan in place. We have about 10 OLTP
> systems/databases currently and all vary in the amount of traffic from
> low (5 transactions a minute) to heavy (5-30 rows a second). We have
> thousands of financial terminals all over the world and these
> terminals feed our OLTP servers in near real time. Our customer
> service team must have near real time access to this data so this is
> where I want to make use of an ODS. My thought is to develop a
> messaging system from all of the OLTP systems and stage this data to
> this ODS. This will be where the tech support/ops/customer care teams
> can have instant access to the data and be able to support the
> customers with a real time view to the session events. Much of our
> daily reporting and information gathering is done by the
> afforementioned teams. Customer payments, machine health, peripheral
> activity and software events are all gathered and sent back. I am
> designnig the ODS so our employees can make quick decisions based on
> the incoming events. Separate from the ODS, I am also going to
> implement a data warehouse. This area will contain more long term
> trending of transactions and other data used for executive decisions.
> I have already designed and successfully built data warehouses before
> so I am very confident in this area. The area that I do have a few
> questions is the bridge between the ODS and the data warehouse. I have
> read many articles dealing with real time data warehousing and ODS ETL
> and many people have different opinions on what they think is the way
> to go. My initial thought is to stage OLTP data to the ODS in near
> real time. From there I will stage the data into the data warehouse
> every night as we have the hardware to do it this frequently. So my
> question is:
> Should I run two systems (ODS/DW) side by side for long periods of
> time and use the systems as separate entities for different groups of
> people or should I use the ODS for say just the current day and the
> run any earlier reporting off the data warehouse?
> I know having redundant data costs money and greater effort to manage
> but we have the resources to back this if need be. Our OPS team needs
> data in real time, our financial team needs it daily and the executive
> team needs it monthly so the ODS would serve the OPS team pretty much
> exclusively.
> Does anyone have any general comments to describe their experiences in
> designing and building similiar systems? Our data flow is not overly
> large right now but it is growing exponentially every month and I want
> to be ready for it years down the road. Thanks for reading my long
> winded article and I would love to hear all of your thoughts. It is
> late on a Sunday night and I am very tired so I apologize if my
> article is not gramatically ready for tomorrow's Wall Street
> Journal . Thanks in advance.
> Corey
>|||Corey,
On further thought, I would address each one as an independent requirement.
If you constrain the ODS based upon the requirements of the DW, then the ODS
may not be as agile as it could be. More than likely, the requirements of
the ODS will be far more dynamic than that of the DW. Just a few thoughts.
-- Bill
<kildenc@.yahoo.ca> wrote in message
news:1170179507.956288.267830@.q2g2000cwa.googlegroups.com...
> Thanks for the reply Marco. I think we are along the same lines here.
> I guess my biggest concern is the difference in the requirements of
> our operations department and the rest of the company. The biggest
> challenge will be to "combine" their needs within the ODS with the
> warehouse. Also, transactions can be voided days later and since the
> datawarehouse is a non volitile area I am going have to figure out how
> to make changes to the fact tables or wait a few days before the
> warehouse gets staged. So, in your opinion,would you run the ODS side
> by side with the warehouse to serve the functional needs of the
> different corporate departments or should I try my best to get all of
> the info into the warehouse and use the ODS for only 1 day or so worth
> of data. I am just concerned that the two systems will not mesh well
> enough to support everyone involved. Thanks for your input. It is very
> much appreciated.
> BTW... we haven't made up our minds on Analysis server yet but I am
> going to definitely do some research on its feasibility here.
> Corey
>|||I agree with AlterEgo: if requirements are going to change, the
maintenance of your Data Warehouse could become a nightmare.
That said, if one of your requirements is the real-time update of
information compared with historical data, you should maintain some
relationship between the two worlds.
Talking about void transaction, you can incrementally handle them by
inserting opposite transactions in the Data Warehouse. Not so easy,
but possible.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Jan 31, 12:10 am, "AlterEgo" <altereg...@.dslextreme.com> wrote:
> Corey,
> On further thought, I would address each one as an independent requirement
.
> If you constrain the ODS based upon the requirements of the DW, then the O
DS
> may not be as agile as it could be. More than likely, the requirements of
> the ODS will be far more dynamic than that of the DW. Just a few thoughts.
> -- Bill
> <kild...@.yahoo.ca> wrote in message
> news:1170179507.956288.267830@.q2g2000cwa.googlegroups.com...
>
>
>
>
> - Show quoted text -|||Thanks guys,
As the project proceeds more of these questions will be answered. I
have read some very interesting articles from Kimball dealing with the
ODS. I can understand why he would want to bring the ODS under the
roof of the data warehouse and it is a definite possibility but as you
guys mentioned that it may become a very tedious task to keep the
requirements of both systems the same so they mesh well together. I
just see the operations requirements being too granular. For instance,
a simple question from our operations team would be, "What are the
transactions details for the last payment for the customer with the
phone number 5555555?". Then show me all of the session details for
this transaction. I just don't see this data fitting well into the
datawarehouse. Thanks for the input Bill and Marco. It is very much
appreciated.
Corey

New Database System Overview

Hi guys,
I am currently finishing off my high level structure for the new
business intelligence database system for my company and without going
into too much detail I would like to get your comments on it. Maybe
there is something you have tried that did/didn't work. Any comments
at all would be appreciated.
I have a pretty standard system plan in place. We have about 10 OLTP
systems/databases currently and all vary in the amount of traffic from
low (5 transactions a minute) to heavy (5-30 rows a second). We have
thousands of financial terminals all over the world and these
terminals feed our OLTP servers in near real time. Our customer
service team must have near real time access to this data so this is
where I want to make use of an ODS. My thought is to develop a
messaging system from all of the OLTP systems and stage this data to
this ODS. This will be where the tech support/ops/customer care teams
can have instant access to the data and be able to support the
customers with a real time view to the session events. Much of our
daily reporting and information gathering is done by the
afforementioned teams. Customer payments, machine health, peripheral
activity and software events are all gathered and sent back. I am
designnig the ODS so our employees can make quick decisions based on
the incoming events. Separate from the ODS, I am also going to
implement a data warehouse. This area will contain more long term
trending of transactions and other data used for executive decisions.
I have already designed and successfully built data warehouses before
so I am very confident in this area. The area that I do have a few
questions is the bridge between the ODS and the data warehouse. I have
read many articles dealing with real time data warehousing and ODS ETL
and many people have different opinions on what they think is the way
to go. My initial thought is to stage OLTP data to the ODS in near
real time. From there I will stage the data into the data warehouse
every night as we have the hardware to do it this frequently. So my
question is:
Should I run two systems (ODS/DW) side by side for long periods of
time and use the systems as separate entities for different groups of
people or should I use the ODS for say just the current day and the
run any earlier reporting off the data warehouse?
I know having redundant data costs money and greater effort to manage
but we have the resources to back this if need be. Our OPS team needs
data in real time, our financial team needs it daily and the executive
team needs it monthly so the ODS would serve the OPS team pretty much
exclusively.
Does anyone have any general comments to describe their experiences in
designing and building similiar systems? Our data flow is not overly
large right now but it is growing exponentially every month and I want
to be ready for it years down the road. Thanks for reading my long
winded article and I would love to hear all of your thoughts. It is
late on a Sunday night and I am very tired so I apologize if my
article is not gramatically ready for tomorrow's Wall Street
Journal . Thanks in advance.
Corey
Hi Corey,
this is an interesting scenario.
I can only do some general consideration.
It would be important to know if you are going to use Analysis
Services or not. One of the new features of Analysis Services is the
ability to directly push data into dimensions and measure group
partitions. This means that you could implement an almost continuous
update of the cube with data that are pushed into analysis services
directly from a queue that is fed by the middle tier. This can be
useful when you cannot afford a query on OLTP during daily operations.
Anyway, there are a wide range of opportunities here, ranging from
these extremely optimized one to the classical relational data
warehouse with intra-day operations that is updated frequently and
consolidated into a larger data warehouse nightly.
I you want to design a "classical" relational data warehouse, the best
practice is to design a simpler star schema, with fewer dimensions
than the big one, just to make sure that you are able to insert data
into fact table in a fast way. It is important to use some decoupling
technique between the OLTP transaction and the ODS update: Message
Queue, Service Broker or something like this.
I would not consider using a single data warehouse with even the intra-
day updates, unless both data warehouse and daily values can share the
same dimensions, both in number and in contents. In that case, I would
create a partition (or a separate fact table) with only the intra-day
operation, and then each night I would consolidate data making all
necessary cleansing operations.
Good work!
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Jan 29, 9:10 am, kild...@.yahoo.ca wrote:
> Hi guys,
> I am currently finishing off my high level structure for the new
> business intelligence database system for my company and without going
> into too much detail I would like to get your comments on it. Maybe
> there is something you have tried that did/didn't work. Any comments
> at all would be appreciated.
> I have a pretty standard system plan in place. We have about 10 OLTP
> systems/databases currently and all vary in the amount of traffic from
> low (5 transactions a minute) to heavy (5-30 rows a second). We have
> thousands of financial terminals all over the world and these
> terminals feed our OLTP servers in near real time. Our customer
> service team must have near real time access to this data so this is
> where I want to make use of an ODS. My thought is to develop a
> messaging system from all of the OLTP systems and stage this data to
> this ODS. This will be where the tech support/ops/customer care teams
> can have instant access to the data and be able to support the
> customers with a real time view to the session events. Much of our
> daily reporting and information gathering is done by the
> afforementioned teams. Customer payments, machine health, peripheral
> activity and software events are all gathered and sent back. I am
> designnig the ODS so our employees can make quick decisions based on
> the incoming events. Separate from the ODS, I am also going to
> implement a data warehouse. This area will contain more long term
> trending of transactions and other data used for executive decisions.
> I have already designed and successfully built data warehouses before
> so I am very confident in this area. The area that I do have a few
> questions is the bridge between the ODS and the data warehouse. I have
> read many articles dealing with real time data warehousing and ODS ETL
> and many people have different opinions on what they think is the way
> to go. My initial thought is to stage OLTP data to the ODS in near
> real time. From there I will stage the data into the data warehouse
> every night as we have the hardware to do it this frequently. So my
> question is:
> Should I run two systems (ODS/DW) side by side for long periods of
> time and use the systems as separate entities for different groups of
> people or should I use the ODS for say just the current day and the
> run any earlier reporting off the data warehouse?
> I know having redundant data costs money and greater effort to manage
> but we have the resources to back this if need be. Our OPS team needs
> data in real time, our financial team needs it daily and the executive
> team needs it monthly so the ODS would serve the OPS team pretty much
> exclusively.
> Does anyone have any general comments to describe their experiences in
> designing and building similiar systems? Our data flow is not overly
> large right now but it is growing exponentially every month and I want
> to be ready for it years down the road. Thanks for reading my long
> winded article and I would love to hear all of your thoughts. It is
> late on a Sunday night and I am very tired so I apologize if my
> article is not gramatically ready for tomorrow's Wall Street
> Journal . Thanks in advance.
> Corey
|||Thanks for the reply Marco. I think we are along the same lines here.
I guess my biggest concern is the difference in the requirements of
our operations department and the rest of the company. The biggest
challenge will be to "combine" their needs within the ODS with the
warehouse. Also, transactions can be voided days later and since the
datawarehouse is a non volitile area I am going have to figure out how
to make changes to the fact tables or wait a few days before the
warehouse gets staged. So, in your opinion,would you run the ODS side
by side with the warehouse to serve the functional needs of the
different corporate departments or should I try my best to get all of
the info into the warehouse and use the ODS for only 1 day or so worth
of data. I am just concerned that the two systems will not mesh well
enough to support everyone involved. Thanks for your input. It is very
much appreciated.
BTW... we haven't made up our minds on Analysis server yet but I am
going to definitely do some research on its feasibility here.
Corey
|||Corey,
Your approach sounds reasonable. If I remember correctly, Kimball addresses
the ODS in his book: The Data Warehouse Lifecycle Toolkit.
-- Bill
<kildenc@.yahoo.ca> wrote in message
news:1170058228.206470.269570@.s48g2000cws.googlegr oups.com...
> Hi guys,
> I am currently finishing off my high level structure for the new
> business intelligence database system for my company and without going
> into too much detail I would like to get your comments on it. Maybe
> there is something you have tried that did/didn't work. Any comments
> at all would be appreciated.
> I have a pretty standard system plan in place. We have about 10 OLTP
> systems/databases currently and all vary in the amount of traffic from
> low (5 transactions a minute) to heavy (5-30 rows a second). We have
> thousands of financial terminals all over the world and these
> terminals feed our OLTP servers in near real time. Our customer
> service team must have near real time access to this data so this is
> where I want to make use of an ODS. My thought is to develop a
> messaging system from all of the OLTP systems and stage this data to
> this ODS. This will be where the tech support/ops/customer care teams
> can have instant access to the data and be able to support the
> customers with a real time view to the session events. Much of our
> daily reporting and information gathering is done by the
> afforementioned teams. Customer payments, machine health, peripheral
> activity and software events are all gathered and sent back. I am
> designnig the ODS so our employees can make quick decisions based on
> the incoming events. Separate from the ODS, I am also going to
> implement a data warehouse. This area will contain more long term
> trending of transactions and other data used for executive decisions.
> I have already designed and successfully built data warehouses before
> so I am very confident in this area. The area that I do have a few
> questions is the bridge between the ODS and the data warehouse. I have
> read many articles dealing with real time data warehousing and ODS ETL
> and many people have different opinions on what they think is the way
> to go. My initial thought is to stage OLTP data to the ODS in near
> real time. From there I will stage the data into the data warehouse
> every night as we have the hardware to do it this frequently. So my
> question is:
> Should I run two systems (ODS/DW) side by side for long periods of
> time and use the systems as separate entities for different groups of
> people or should I use the ODS for say just the current day and the
> run any earlier reporting off the data warehouse?
> I know having redundant data costs money and greater effort to manage
> but we have the resources to back this if need be. Our OPS team needs
> data in real time, our financial team needs it daily and the executive
> team needs it monthly so the ODS would serve the OPS team pretty much
> exclusively.
> Does anyone have any general comments to describe their experiences in
> designing and building similiar systems? Our data flow is not overly
> large right now but it is growing exponentially every month and I want
> to be ready for it years down the road. Thanks for reading my long
> winded article and I would love to hear all of your thoughts. It is
> late on a Sunday night and I am very tired so I apologize if my
> article is not gramatically ready for tomorrow's Wall Street
> Journal . Thanks in advance.
> Corey
>
|||Corey,
On further thought, I would address each one as an independent requirement.
If you constrain the ODS based upon the requirements of the DW, then the ODS
may not be as agile as it could be. More than likely, the requirements of
the ODS will be far more dynamic than that of the DW. Just a few thoughts.
-- Bill
<kildenc@.yahoo.ca> wrote in message
news:1170179507.956288.267830@.q2g2000cwa.googlegro ups.com...
> Thanks for the reply Marco. I think we are along the same lines here.
> I guess my biggest concern is the difference in the requirements of
> our operations department and the rest of the company. The biggest
> challenge will be to "combine" their needs within the ODS with the
> warehouse. Also, transactions can be voided days later and since the
> datawarehouse is a non volitile area I am going have to figure out how
> to make changes to the fact tables or wait a few days before the
> warehouse gets staged. So, in your opinion,would you run the ODS side
> by side with the warehouse to serve the functional needs of the
> different corporate departments or should I try my best to get all of
> the info into the warehouse and use the ODS for only 1 day or so worth
> of data. I am just concerned that the two systems will not mesh well
> enough to support everyone involved. Thanks for your input. It is very
> much appreciated.
> BTW... we haven't made up our minds on Analysis server yet but I am
> going to definitely do some research on its feasibility here.
> Corey
>
|||I agree with AlterEgo: if requirements are going to change, the
maintenance of your Data Warehouse could become a nightmare.
That said, if one of your requirements is the real-time update of
information compared with historical data, you should maintain some
relationship between the two worlds.
Talking about void transaction, you can incrementally handle them by
inserting opposite transactions in the Data Warehouse. Not so easy,
but possible.
Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi
On Jan 31, 12:10 am, "AlterEgo" <altereg...@.dslextreme.com> wrote:
> Corey,
> On further thought, I would address each one as an independent requirement.
> If you constrain the ODS based upon the requirements of the DW, then the ODS
> may not be as agile as it could be. More than likely, the requirements of
> the ODS will be far more dynamic than that of the DW. Just a few thoughts.
> -- Bill
> <kild...@.yahoo.ca> wrote in message
> news:1170179507.956288.267830@.q2g2000cwa.googlegro ups.com...
>
>
>
> - Show quoted text -
|||Thanks guys,
As the project proceeds more of these questions will be answered. I
have read some very interesting articles from Kimball dealing with the
ODS. I can understand why he would want to bring the ODS under the
roof of the data warehouse and it is a definite possibility but as you
guys mentioned that it may become a very tedious task to keep the
requirements of both systems the same so they mesh well together. I
just see the operations requirements being too granular. For instance,
a simple question from our operations team would be, "What are the
transactions details for the last payment for the customer with the
phone number 5555555?". Then show me all of the session details for
this transaction. I just don't see this data fitting well into the
datawarehouse. Thanks for the input Bill and Marco. It is very much
appreciated.
Corey