Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

Go down

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

Post  parbie 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
parbie
parbie

Posts : 11
Join date : 2010-04-06

Back to top Go down

Back to top

- Similar topics

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