Close to real time sync for data in relational and flat schemas

Hi all,
Can someone point me to the right direction?
I need to synchronize data updates in relational schema with data in it's
flat (denormalized) equivalent in near real time mode.
What is the best way to achieve this if using triggers is not an option ?
Are there any proven approaches or tools (incl. 3rd party) which do that ?
Is there more appropriate microsoft.public.sqlserver.server sub-group for
this topic ?
Any good idea or suggestion is greatly appreciated,
Thank you,
VT"VT" <VT@.discussions.microsoft.com> wrote in message
news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> Hi all,
> Can someone point me to the right direction?
> I need to synchronize data updates in relational schema with data in it's
> flat (denormalized) equivalent in near real time mode.
> What is the best way to achieve this if using triggers is not an option ?
> Are there any proven approaches or tools (incl. 3rd party) which do that ?
> Is there more appropriate microsoft.public.sqlserver.server sub-group for
> this topic ?
> Any good idea or suggestion is greatly appreciated,
> Thank you,
> VT
>
A denomalized schema is simply the result of joins in a normalized schema.
That's easily achieved just by using views.
I suspect you want something more than a denormalized schema though. Are you
talking about data warehousing? If so there is a whole industry of products
to support you but I hesitate to suggest anything without knowing more about
what you are trying to achieve.
Have you seen the new Change Data Capture feature in SQL Server 2008?
--
David Portas|||"VT" <VT@.discussions.microsoft.com> wrote in message
news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> Hi all,
> Can someone point me to the right direction?
> I need to synchronize data updates in relational schema with data in it's
> flat (denormalized) equivalent in near real time mode.
> What is the best way to achieve this if using triggers is not an option ?
> Are there any proven approaches or tools (incl. 3rd party) which do that ?
> Is there more appropriate microsoft.public.sqlserver.server sub-group for
> this topic ?
> Any good idea or suggestion is greatly appreciated,
> Thank you,
> VT
Like David pointed out, you can use views, indexed views, and several other
possible alternatives including third-party tools. Can you be more specific
about your situation and ultimate goal?|||> A denomalized schema is simply the result of joins in a normalized schema. That's easily achieved
> just by using views.
Which can be created in the source database. You can then use transaction replication to replicate
the source database and query off of the destination database (if you want to off-load reporting
from the production db, that is).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1ISdnQ1HG_gX_EnanZ2dneKdnZydnZ2d@.giganews.com...
> "VT" <VT@.discussions.microsoft.com> wrote in message
> news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
>> Hi all,
>> Can someone point me to the right direction?
>> I need to synchronize data updates in relational schema with data in it's
>> flat (denormalized) equivalent in near real time mode.
>> What is the best way to achieve this if using triggers is not an option ?
>> Are there any proven approaches or tools (incl. 3rd party) which do that ?
>> Is there more appropriate microsoft.public.sqlserver.server sub-group for
>> this topic ?
>> Any good idea or suggestion is greatly appreciated,
>> Thank you,
>> VT
>
> A denomalized schema is simply the result of joins in a normalized schema. That's easily achieved
> just by using views.
> I suspect you want something more than a denormalized schema though. Are you talking about data
> warehousing? If so there is a whole industry of products to support you but I hesitate to suggest
> anything without knowing more about what you are trying to achieve.
> Have you seen the new Change Data Capture feature in SQL Server 2008?
> --
> David Portas
>|||Hi all,
Thank you for your responces.
What I am trying to achieve is to search against flat schema which as it was
rightfully pointed out - same as a join.
Profiling of the search against relational schema shows bulk of the time
with each query goes into joining of the tables.
I think of flat table as a substitute for that join.
Datawarehousing solutions may not be applicable for this as they are not
designed to work in realtime. They are more like batch type processes which
happen periodically.
Using view is great, but problem is that join has outer joins and such view
has nulls and therefore cannot be indexed.
Thanks,
VT
"Mike C#" wrote:
> "VT" <VT@.discussions.microsoft.com> wrote in message
> news:E782A78E-692C-4945-B7B5-6BA764AE3A0A@.microsoft.com...
> > Hi all,
> >
> > Can someone point me to the right direction?
> >
> > I need to synchronize data updates in relational schema with data in it's
> > flat (denormalized) equivalent in near real time mode.
> >
> > What is the best way to achieve this if using triggers is not an option ?
> >
> > Are there any proven approaches or tools (incl. 3rd party) which do that ?
> >
> > Is there more appropriate microsoft.public.sqlserver.server sub-group for
> > this topic ?
> >
> > Any good idea or suggestion is greatly appreciated,
> >
> > Thank you,
> > VT
> Like David pointed out, you can use views, indexed views, and several other
> possible alternatives including third-party tools. Can you be more specific
> about your situation and ultimate goal?
>
>