Last month, we issued an insert transaction against an asserted version table on 1/01/04. For discussion purposes, it is now 5/01/04, and in the intervening four months, nothing has happened to the policy table. Today we are going to update the co-pay amount on the policy from $15 to $20. The state of the database prior to this update is shown in Figure 1.
Fact tables are the foundation of the data warehouse. They contain the fundamental measurements of the enterprise, and they are the ultimate target of most data warehouse queries. Perhaps you are wondering why it took me so long to get to fact tables in DM Review? Well, there is no point in hoisting fact tables up the flagpole unless they have been chosen to reflect urgent business priorities, have been carefully quality assured and are surrounded by dimensions that provide a wealth of entry points for constraining and grouping. Now that we have paved the way for fact tables, let’s see how to build them and use them.
Note: Beginning with this column, it will be increasingly important for readers to refer to our glossary, which can be found on the author’s Web sites (MindfulData.com and InbaseInc.com) with a listing of previous columns and articles. Our glossary-defined terms form a closely-knit conceptual network, and none of these terms can be fully understood apart from their semantic connections with other terms in the Glossary. And a note on the use of italics: we use italics for emphasis, but we also italicize the initial occurrence, from this column forward, of glossary-defined terms.
An original transaction is an insert, update or delete, whose target is an asserted version bi-temporal table. It is the transaction as written by its author and as submitted by the database administrator (DBA) to the database management system (DBMS). A temporal transaction is a physical transaction against an asserted version table. These physical transactions are either physical updates (updates in place) or physical inserts; there are no temporal transactions that are physical deletes.
V O T E !
Dimensions implement the user interface UI in a business intelligence BI tool. In a dimensional data warehouse DW/BI system, the textual descriptors of all the data warehouse entities like customer, product, location and time reside in dimension tables. My two previous columns carefully described three major types of dimensions according to how the DW/BI system responds to their slowly changing characteristics. But why all this fuss about dimensions? They are the smallest tables in the data warehouse, and the real “meat” is actually the set of numeric measurements in the fact tables. But that argument misses the point that the DW/BI system is always accessed through the dimensions. The dimensions are the gatekeepers, the entry points, the labels, the groupings, the drill-down paths and, ultimately, the texture of the DW/BI system. The actual content of the dimensions determines what is shown on the screen of a BI tool and what UI gestures are possible. That is why we say that the dimensions implement the UI.
Dimensions are key to navigating the data warehouse / business intelligence system, and hierarchies are the key to navigating dimensions. Any time a business user talks about wanting to drill up, down or into the data, they are implicitly referring to a dimension hierarchy. In order for those drill paths to work properly, and for a large DW/BI system to perform well, those hierarchies must be correctly designed, cleaned, and maintained.
IntelligentEnterprise : Kimball University: Maintaining Dimension Hierarchies.
PURGE MASTER LOGS TO ‘mydb-bin.0023′;
Then the binlog before ‘mydb-bin.0023′ will be deleted.
You may also do this:
PURGE MASTER LOGS BEFORE ‘2008-06-13 08:00:00′;