Dylan's BI Study Notes

My notes about Business Intelligence, Data Warehousing, OLAP, and Master Data Management

Use the Industry Standard Code in Data Warehouse

Posted by Dylan Wan on January 11, 2008

Several well-developed industry standard code schemes can help you to capture the information. Here are advantages for you to adopt the industry standard codes in your information system:

  • Save the time to develop the coding scheme by yourselves
  • Apply the best practice from the industry
  • Ease the exchange data between trading partners (B2B)
  • Facilitate the data and application integration (A2A)
  • Consolidate your information into a data warehouse cross multiple internal/legacy systems

Here are some of the well-known industrial standard codes I am aware of or have been involved in the past:

  • Country Code: There are ISO standards both two and three digit format. It is used as a component of addresses. It also can be used to drive localization.
  • Language Code: There are ISO standards. Some database systems, such as Oracle also have its own coding scheme. It is useful for supporting NLS and MLS. Java language also have its “Locale” standard. Sometime you simply need to capture this for the person data in your system. For example, they can be used as the skill captured for employees or as a contact preference for contacting your customers.
  • SIC Code: This is the United States standard. Actually the name SIC is outdated. The latest version is called NAICS. It is useful for classifying your customer and your suppliers
  • UNSPSC: This the UN standard product code.
  • DUNS Number: The number is granted by Dun & Breadstreet (the old name of the company). DUNS Number helps the trading partners to identify each others. Getting the number from your trading partner can at least certify that the company is known to D&B.
  • Timezone: It is useful to show the local time based on the user context.
  • Address Components like STATE, CITY, COUNTY, PROVINCE, can also be stanadized. It is very important for de-dup your customer data.

In addition, some coding scheme are very industry specific. For example, in Construction, the work can be classified by the standard coding scheme for construction cost estimation. Some Project/ Job Cost system use the code as the based for budgeting and cost control.

It would be very useful for you if a pre-package enterprise application can provide the built-in support for these standard coding scheme. I feel that the support can be done in several levels:

1. Package the standard codes into the product: This is what I did for E-Business Suite several year ago. We package the various versions of SIC codes into the products and the customers can thus use them to classify their customers.

2. Provide a loader or a adapter: Sometime there are data licensing consideration. The application deploying company need to pay for the data. This is how Oracle E-Business Suite handles D&B data. A standard application integration is provided and the deploying company can use the service to purchase the data.

3. Provide the data model and placeholder for the data: At least the application vendor should aware the existence of the industry standards and put the concept into their application schema. For example, in Oracle BI Apps, you can capture the UNSPSC commodity codes for your supplier product dimension.

If you are choosing a pre-packaged apps, this should be one of the selection criteria.


2 Responses to “Use the Industry Standard Code in Data Warehouse”

  1. Signage Manufacturer in Malaysia…

    […]Use the Industry Standard Code in Data Warehouse « Dylan’s BI Study Notes[…]…

  2. Hi Dylan, Do you have any sample data warehouse coding standards document that you would have used in one of the projects you worked. I am referring to a document that will mention about how to model the new dimensions, new facts, naming conventions, etc

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s