Docstoc

The big data warehouse debates

Document Sample
The big data warehouse debates Powered By Docstoc
					               To Find the Balance at a Point-in-time SQL

Account fact
dateKey (FK)
                              Date                    Account
                              dateKey(PK)             accountKey(PK)
branchKey (FK)
                              fullDate                accountNum
productKey (FK)
                              …                       …
accountKey(FK)
accountStatusKey (FK)
householdKey (FK)
balance
                                To find the balance as of a certain
…                               date requires finding the last
                                transaction prior to the date
                                The following assumes the date
                                surrogate keys are “increasing” as the
                                date “increases”



                                                                       1
               To Find the Balance at a Point-in-time SQL

Account fact
dateKey (FK)
                              Date                         Account
                              dateKey(PK)                  accountKey(PK)
branchKey (FK)
                              fullDate                     accountNum
productKey (FK)
                              …                            …
accountKey(FK)
accountStatusKey (FK)
householdKey (FK)
                         SELECT   accountNum, balance
                         FROM     fact f, account a
balance
                         WHERE    f.accountKey = a.accountKey
…                        AND      f.dateKey =
                                  (select max(g.dateKey)
                                  FROM fact g
                                  WHERE g.accountKey = f.accountKey
                                  AND        g.dateKey IN
                                             (SELECT d.dateKey
                                             FROM date d
                                             WHERE d.fullDate <= 'January 5, 2002')
                                  )

                                                                            2

				
DOCUMENT INFO
Shared By:
Categories:
Stats:
views:3
posted:4/9/2010
language:English
pages:2