Clever Geek Handbook

When are bitmap tables actually used?

I am trying to collect information about temporary databases. I know this is not modern technology, but I have seen that many people who work with databases never know how the temporary approach works (I asked some senior programmers and systems analysts about temporary databases and they answered that something like "Yes?").

I know there are state tables and transaction state tables as well as bit domain tables. I think bitporal tables are too complicated for most usages because space is no longer a problem nowadays and it is more efficient to write the same information on 2 different tables even if the data is redundant. However, I did a lot of searches on the internet trying to see where the bit domain tables are actually used, but I didn't find anything useful.

Are there cases where using a bitmap table is actually more convenient than the validity and transaction time state tables separately? Are there real examples?

+3


source to share


1 answer


Sure! Take balance sheet data, for example. You will see this information change from WD1 (business day) to WD x due to late arriving data, settings, manual errors, etc.

To include duplicate reports, audit trails and time comparisons, the record must be kept in the "old" (invalid?) Results. Bitemporal is a great way to manage such updates, especially on an intraday basis. I don't think this is difficult from a user perspective - just another filter in the where clause.

I admit that the download process is complicated, but it's not that bad. I literally just finished writing a generic transformation (in SAS, handling all the scenarios for a unique business key) and it took one day.



Coming back to the use cases. Having both the valid (working) time and the transaction (version) time in the same table, you can:

  • Repeatable results (with separate tables and related updates can mean getting different results for the same query over two different days)
  • Comparable results (may answer questions such as "what was the value of X, how did we know it at the time of Y?")
  • Fast results (only for one table updated in a transparent and easy way).

In this sense, it is a suitable structure for use in many, if not all, tables in DWh.

+2


source






More articles:


All Articles