Loading data from one fact to another summary fact

Hello Folks,

I am looking to take the information from one Account fact table and then load it into another account summary fact table and then end of every month. Is this right architecture.. This is absolutely required for periodic snapshot

So on the whole can I load data from one fact table and another fact table.

Thanks in advance for the reply.

Of course you can, and you should. The periodic snapshot in your case are the aggregates based on transaction facts. Loading aggregates from base level facts can guarantee the reconcilable rolling up.

Yes it is the logical thing to do. It will make you reporting more efficient and happier customers. Only caveat is ensure that your summaries are not designed for a small set of reports..., and that your periodic snapshop always reconcile back to your atomic fact table.

You can even load the aggregate from many fact tables as long as the grains are the same.

Would one model this summary table as a stand-alone table or as a fact table with the same dimensions as the original fact table?

Thanks in advance for your reply.

Rick

rschulz01 wrote:Would one model this summary table as a stand-alone table or as a fact table with the same dimensions as the original fact table?

Thanks in advance for your reply.

Rick

It would be a fact table with the same or fewer dimensions.

Hi Jamie,

Personally I would take the snapshot into a separate fact table, but avoid aggregating it. While this may seem redundant, it actually speeds the design / build / test effort (e.g. snapshot table columns = source table columns + As_At_Date, with simple ETL), and also builds in more flexibility for future (unknown) requirements.

Good luck!
Mike