I am trying to find a smart (no redundunt dataflow) way to aggregate data on views/tables to be used from our reporting tools.

One given is that the reporting database is different from the oltp one, so indexed views are not applicable for the first step.

My current plan is:

  1. Replicate clones (maybe with a few filters) from oltp db to reporting one
  2. Use indexed views on the clones in order to perform smart aggregation

Is this a good solution, leading to minimal impact on data update/insertion/deletion on the oltp tables?

An example. Consider a [tran] table containing transactions (and let's think several rows):

| TranID | Store | Value |
|      1 | S1    |  1200 |
|      2 | S2    |   250 |
|      3 | S1    |   800 |

and let's also suppose the reporting tools needs an aggregated version GROUPed BY [Store], so we create an indexed view named v_tran_byStore:

| Store | Value |
| S1    |  2000 |
| S2    |   250 |

We setup the following dataflow:

[oltp database]..tran ==(replication)==> [reporting db]..tran ==(indexed view)==> [reporting db]..v_tran_byStore

If a DML statement modifying only a few rows is run on the tran table, then:

  1. The replication uses delta logic, so there will be minimal changes
  2. The indexed view also has delta mechanisms behind, so again there will be minimal change

Is this a good design? If not, am I wrong in my assumptions? Do you have an alternative recommendation?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.