Transactional detail fact table w/complimentary snapshot table. How do the two play together?

View previous topic View next topic Go down

Transactional detail fact table w/complimentary snapshot table. How do the two play together?

Post  parbie on Tue Feb 01, 2011 7:53 pm

Take a detail daily banking transaction table with standard credit and debit level transactions. I want to build the accompanying snapshot that would present the higher level rollups of these lower transactions:

There are about 250 daily transaction types in the Amount column with a Transaction description dimension in the FactDaily table.

There would be about 30 measure amount columns in the FactDailySnapshot table, including: #ofCredits, #ofDebits, TotalCredits, TotalDebits, #ofAccountsOpenedToday, #ofAccountsClosed, EndingLedgerBalance, EndingAvailableBalance, FeesCharged, FeesWaived, WeightedAvgRate, WeightedAvgYield etc...


My only option for aggregate awareness is using DB2 MQTs. Access is through a very basic version of Crystal Reports, and through an in-house coded, interface using Silverlight and .net to present ad-hoc and predefined views. (No real "BI tool" or BI engine other than the code behind the UI and a proprietary general purpose database access layer designed mainly for transactional purposes)

The UI and reports as you might expect are very slow involiving queries of 15 or more days. How does the snapshot table work in conjunctions with the detail table in terms of drilling down? In terms of a performance aggregate? Do I need to construct it as an MQT? If I build the snapshot in the ETL, how does aggregate navigation work then?

No other options on the database, data access or presentation tools are availble at this time.
Thanks
avatar
parbie

Posts : 11
Join date : 2010-04-06

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum