Trudiogmor: Data Warehouse Table Prefix

Data Warehouse Table Prefix

If data is coming from multiple data stores you can bring the data into the data warehouse and store it in an integration table. Once the data is prepared you can insert it into production tables.

Kb41809 How To Get The Schema Name Of An Oracle Warehouse Table

You can take some ideas from the oracle bi applications data model.

Data warehouse table prefix. Once data is in the integration table you can use the power of sql data warehouse to perform transformation operations. Not so much with oltp. I use the following prefixes.

A copy of transaction data specifically structured for query and analysis ralph kimball a subject oriented nonvolatile integrated time variant collection of data in support of managements decisions bill inmon data mart. Some of his ideas may not translate to a data warehouse environment but it sounds like youre in an oltp situation. 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. Do prefix with just a meaningful part of it this helps tremendously when writing or reading queries. These are some of the naming conventions included.

For example a sales analyst would be interested in sales numbers but it turns out that this sales number is really salescommissionableamount which is different from salesgrossamount and salesnetamount. It may seem frugal to save space on unnecessary columns especially in dimension tables that only have type 1 history. 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.

The typical dimension tables up a tiny fraction of the total warehouse space. Warehouse table column names often end up in metadata on reports being read by both developers and users. 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.

The two are very different. That said it is a good practice to document a guideline for naming data warehouse objects. However space use of dimension tables really is a non issue in data warehouse systems.

Dont prefix with the entire table name thats way too long. 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. This article presents a naming scheme for database tables and fields.

Warehouse vs oltp naming. A data mart that contains only the fact table perhaps several gigabytes of data is not a data mart. Scheme i like to prefix each table name with one of three prefixes to indicate the type of table.

Because in a data warehouse the number of aggregate tables tend to be high the naming convention provides an help to immediately identify all data structures associated with a specific business process. Data tables i use the. The maximum oracle table name length is 30.

It is a problem.

Missing Ruleskey Values When Loading The Data Warehouse Database