real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
--
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> > How does one implement real time reporting off of data from the OLTP
> > systems. Right now we run nightly extracts and load into OLAP DBs to
> > minimise locking,etc while the extracts are being run. However our
> > customers
> > now want to see real time data as soon as possible and we cannot give
them
> > access to the live OLTP systems. Are there any ways to implement this
> > efficiently ?
> >
> >
>

real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?
Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>
|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them
>

real time reporting + OLTP dbs

How does one implement real time reporting off of data from the OLTP
systems. Right now we run nightly extracts and load into OLAP DBs to
minimise locking,etc while the extracts are being run. However our customers
now want to see real time data as soon as possible and we cannot give them
access to the live OLTP systems. Are there any ways to implement this
efficiently ?Have you tried replication? It should give you more frequent updates.
You might want to consider using read uncommitted query because replication
also needs to make changes to your OLAP database to keep data up to date.
Wei Xiao [MSFT]
SQL Server Storage Engine Development
http://weblogs.asp.net/weix
This posting is provided "AS IS" with no warranties, and confers no rights.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
> How does one implement real time reporting off of data from the OLTP
> systems. Right now we run nightly extracts and load into OLAP DBs to
> minimise locking,etc while the extracts are being run. However our
> customers
> now want to see real time data as soon as possible and we cannot give them
> access to the live OLTP systems. Are there any ways to implement this
> efficiently ?
>|||we thought about replication but cant really justify replicating each of our
OLTP dbs to serve the reporting needs besides the administration needed to
support it ,etc..
"wei xiao [MSFT]" <weix@.online.microsoft.com> wrote in message
news:Oc0vFSf0EHA.2568@.TK2MSFTNGP11.phx.gbl...
> Have you tried replication? It should give you more frequent updates.
> You might want to consider using read uncommitted query because
replication
> also needs to make changes to your OLAP database to keep data up to date.
> --
> Wei Xiao [MSFT]
> SQL Server Storage Engine Development
> http://weblogs.asp.net/weix
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:upTfU1d0EHA.3584@.TK2MSFTNGP11.phx.gbl...
them[vbcol=seagreen]
>

Real Time DSS

Right now we tend to provide extracts to our data warehousing system once a
day. Users seem to be presseing for near real time warehousing. It seems to
be a lot of processing to happen on the production side to get this data
during regular hours and hence the nightly extracts..
I am critical to know how we can provide such a real time solution and not
affect the OLTP performance..
Thanks
There are ways they are usually products by third parties like Informatica
or Ascential. However, this sounds far more like an ODS or reporting
database than a warehouse. The solutions range in complexity and cost from
simple replication of require tables for reporting to providing a service
oriented architecture where transactions can be posted in multiple
directions.
Post more details like number of transactions, size of warehouse, DW
architecture, ETL tools in use and perhaps the group can provider further
advice.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Right now we tend to provide extracts to our data warehousing system once
> a
> day. Users seem to be presseing for near real time warehousing. It seems
> to
> be a lot of processing to happen on the production side to get this data
> during regular hours and hence the nightly extracts..
> I am critical to know how we can provide such a real time solution and not
> affect the OLTP performance..
> Thanks
>
|||What is SOA ? I keep hearing about it. Can you send me a good reading link
to that
"Ray" <someone@.nowhere.com> wrote in message
news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.c om...
> There are ways they are usually products by third parties like Informatica
> or Ascential. However, this sounds far more like an ODS or reporting
> database than a warehouse. The solutions range in complexity and cost
from[vbcol=seagreen]
> simple replication of require tables for reporting to providing a service
> oriented architecture where transactions can be posted in multiple
> directions.
> Post more details like number of transactions, size of warehouse, DW
> architecture, ETL tools in use and perhaps the group can provider further
> advice.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
once[vbcol=seagreen]
not
>
|||why don't you try having 2 AS servers pointing to the same Warehouse
(cleaned) databases for their source dimensions and facts. Then have the
application switch between them (via control table indicating which AS is
currently processing data and which one is not so the app would call a stored
procedure to look in the control table to tell it which AS it should connect
to get data). This way you can process the cubes at appropriate time
(whether at a regular time interval or when data volume coming into warehouse
tables met certain row count threshold) then switch over to the other server
to process it also when you are done with the first one.
The user experience is that they will see data is always available as close
to real time as possible...depending on your processing time. By the way,
AS2005 can 'sniff' your warehouse source tables for changes in data to update
the cube automatically (you can do the same now in AS2000 but via ROLAP
storage model which looks at your fact tables so indexing is critical here).
"Hassan" wrote:

> What is SOA ? I keep hearing about it. Can you send me a good reading link
> to that
> "Ray" <someone@.nowhere.com> wrote in message
> news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.c om...
> from
> once
> not
>
>

Real Time DSS

Right now we tend to provide extracts to our data warehousing system once a
day. Users seem to be presseing for near real time warehousing. It seems to
be a lot of processing to happen on the production side to get this data
during regular hours and hence the nightly extracts..
I am critical to know how we can provide such a real time solution and not
affect the OLTP performance..
ThanksThere are ways they are usually products by third parties like Informatica
or Ascential. However, this sounds far more like an ODS or reporting
database than a warehouse. The solutions range in complexity and cost from
simple replication of require tables for reporting to providing a service
oriented architecture where transactions can be posted in multiple
directions.
Post more details like number of transactions, size of warehouse, DW
architecture, ETL tools in use and perhaps the group can provider further
advice.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
> Right now we tend to provide extracts to our data warehousing system once
> a
> day. Users seem to be presseing for near real time warehousing. It seems
> to
> be a lot of processing to happen on the production side to get this data
> during regular hours and hence the nightly extracts..
> I am critical to know how we can provide such a real time solution and not
> affect the OLTP performance..
> Thanks
>|||What is SOA ? I keep hearing about it. Can you send me a good reading link
to that
"Ray" <someone@.nowhere.com> wrote in message
news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.com...
> There are ways they are usually products by third parties like Informatica
> or Ascential. However, this sounds far more like an ODS or reporting
> database than a warehouse. The solutions range in complexity and cost
from
> simple replication of require tables for reporting to providing a service
> oriented architecture where transactions can be posted in multiple
> directions.
> Post more details like number of transactions, size of warehouse, DW
> architecture, ETL tools in use and perhaps the group can provider further
> advice.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:%23QbRFX5JFHA.3196@.TK2MSFTNGP15.phx.gbl...
once[vbcol=seagreen]
not[vbcol=seagreen]
>|||why don't you try having 2 AS servers pointing to the same Warehouse
(cleaned) databases for their source dimensions and facts. Then have the
application switch between them (via control table indicating which AS is
currently processing data and which one is not so the app would call a store
d
procedure to look in the control table to tell it which AS it should connect
to get data). This way you can process the cubes at appropriate time
(whether at a regular time interval or when data volume coming into warehous
e
tables met certain row count threshold) then switch over to the other server
to process it also when you are done with the first one.
The user experience is that they will see data is always available as close
to real time as possible...depending on your processing time. By the way,
AS2005 can 'sniff' your warehouse source tables for changes in data to updat
e
the cube automatically (you can do the same now in AS2000 but via ROLAP
storage model which looks at your fact tables so indexing is critical here).
"Hassan" wrote:

> What is SOA ? I keep hearing about it. Can you send me a good reading link
> to that
> "Ray" <someone@.nowhere.com> wrote in message
> news:iYYYd.12119$WH2.9250@.newssvr31.news.prodigy.com...
> from
> once
> not
>
>