"Joel Leong" <ch_leong@hotmail.com> wrote in message
news:29ad01c5075c$a1ce3a80$a601280a@phx.gbl...
> Thanks for your reply. I appreciate it. BTW, I'm not able
> to fully understand your solution.
>
> Are you mean I should create an indexed view in OLTP and
> replicate this index view to OLAP server as a table.
> Then, I use HOLAP to consume data from the replicated
> table?
>
>
> >-----Original Message-----
> >You would have to be using holap for this to work.
> >
> >Then you should either model the schema you have on your
> holap system using
> >indexed views and replicated it to the olap server using
> the defaul
> >settings - where the indexed views are replicated as
> tables, or use custom
> >sync objects.
> >
> >answers inline
> >
> >"Joel Leong" <ch_leong@hotmail.com> wrote in message
> >news:OaOw2DbBFHA.1404@TK2MSFTNGP11.phx.gbl...
> >> Sync data between OLTP data tables and OLAP fact
> tables.
> >>
> >>
> >>
> >>
> >> I have my data in several data tables, I had built a
> view
> >> to aggregate the data from these data tables.
> >>
> >> This view is similar to a fact table in OLAP Analysis
> >> Services except it has no Fact ID (PKID).
> >>
> >> My OLTP Database in Server A which hold the several
> data
> >> tables and the view is stored in a DB called TransDB
> >> while my OLAP database in Server B will store
> dimensions
> >> and fact tables in a DB called OlapDB.
> >>
> >> E.g.
> >>
> >> TransDB.dbo.FruitsData
> >> ----------------------
> >> PKID: Primary key with identity attriubute.
> >> BranchID: Specifies value from which branch/office.
> >> Month: Month of the data
> >> Year: Year of the data
> >> Value: amount. The meaning of this value is governed by
> >> Measure
> >> FruitName: Name of the fruit
> >> Measure: Specifies what is the value for. Could be
> Sale,
> >> Quantity, AverageWeightPerFruit
> >>
> >>
> >> FruitFactTable View (in TransDB)
> >> --------------------------
> >> BranchID: Specifies value from which branch/office.
> >> Month: Month of the sales
> >> Year: Year of the sales
> >> Value: Sales amount
> >> FruitName: Name of the fruit
> >> Sale: This is a easure
> >> Quantity: This is a measure
> >> AverageWeghtPerKg: This is a measure
> >>
> >>
> >>
> >> OlapDB.dbo.FruitsFactTable
> >> --------------------------
> >> BranchID: Specifies value from which branch/office.
> >> Month: Month of the sales
> >> Year: Year of the sales
> >> Value: Sales amount
> >> FruitName: Name of the fruit
> >> Sale: This is a easure
> >> Quantity: This is a measure
> >> AverageWeghtPerKg: This is a measure
> >>
> >>
> >>
> >>
> >>
> >> My questions are
> >>
> >> (i) Can I build a fact table from this view? How could
> I
> >> assign a fact ID to this fact table?
> >
> >Yes, use an indexed view and replicate this. What
> exactly do you mean by a
> >fact ID? I am familiary with OLAP, but your usage of
> this term confuses me.
> >>
> >> (ii) How could I sync the fact table and view between
> >> TransDB and OlapDB? How can i minimize network traffic
> to
> >> keep both fact table and view in sync?
> >>
> >
> >Use transactional replication
> >
> >> (iii) If I cannot built fact table from this view what
> >> should be the better approach? Shall I add a column in
> >> data tables and then periodically sync to fact table?
> >> After the data is sync, I update this column to
> indicate
> >> that row has been transferred to OlapDB?
> >>
> >
> >> (iv) What if I deleted a row in data tables? How to
> >> reflect the changes to fact table? Shall I keep a
> delete
> >> journal to sync with fact tables?
> >
> >Replication will move the delete to the olap tables.
> >
> >But something is confusing me here. Olap deals with
> aggregate data. It
> >should not be senstive to single delete operations, or a
> single delete
> >operation should not be of much importance to an olap
> cube. Exactly how much
> >data are we talking about?
> >>
> >> (v) are there any solution if i choose replication?
> Does this help?
> >
> >
> >> Please advise! Thanks
> >>
> >
> >
> >.
> >