Fitfab: Data Warehouse Table Prefix

Data Warehouse Table Prefix

The typical dimension tables up a tiny fraction of the total warehouse space. That said it is a good practice to document a guideline for naming data warehouse objects.

Warehouse Catalog Michael Sandberg S Microstrategy Tips Tricks

The generated technical name for a table must be 27 characters or less to reserve space for index names built from the table technical name.

Data warehouse table prefix. Do not prefix table name with their data type with the exeption of bit use hungarian notation example. This requirement exists in the edss data warehouse since the original complete technical table name must be present in the names of indexes for the table. Lastly if a prefix is used for this purpose the shared schemadatabase prefix is a higher grouping level and comes first in the name for example hcpayclients not payhcclients.

Pre or postfix views. Once data is in the integration table you can use the power of sql data warehouse to perform transformation operations. Once the data is prepared you can insert it into production tables.

However space use of dimension tables really is a non issue in data warehouse systems. It may seem frugal to save space on unnecessary columns especially in dimension tables that only have type 1 history. Name views the same way tables are named.

The maximum oracle table name length is 30. If data is coming from multiple data stores you can bring the data into the data warehouse and store it in an integration table. The goal of both this prefix and the aforementioned shared schemadatabase prefix is to allow you to group specific tables together alphabetically in lists and distinguish them from unrelated tables.

It is one aspect to simply define naming conventions utilized within the development of your data warehouse but it is completely another to establish consistency as to create defined naming conventions that are to become standards. Prefix with tbl by prefixing tables with their type it becomes hard for the dba to replace tables with viewsynonyms to maintain schema compatibility.

Data Warehouses Should Stage Source Data Dataversity