Best pratice for resolving Bit operation in Dimension

Hi,

We have a scenerio where work order could be in many states. Completed, Ordered, Cancelled, Posted etc....... This all come as a bitmap kind of thing from the source system (1011000). What are the best practices around modeling this scenerio in the Data Warehouse.

Thanks,

From a logical modeling perspective or even a dimensional modeling perspective, this is bad design. You want to store data in its atomic form. In your example, you have 7 attributes crammed into one column. I would break the information out into their respective columns.

Are you recommending creating columns
Completed
Cancelled
Entered
Posted
....
....
....
In the table. I do not think this is good design either, whenever there is a new state added I have to change the Database.

Yes, that it what I am recommending. You do not have to accept my recommendations though. However, simple analysis will show that the states of an order are static. The appropriate fact design is an accumulating snapshot.

It appears what you have here is a potential for implementing the Junk Dimension design.

Kimball Design Tip #48: De-Clutter With Junk (Dimensions)
http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT48DeClutter.pdf

Design Tip #113 Creating, Using, and Maintaining Junk Dimensions
http://www.kimballgroup.com/html/09dt/DT113CreatingUsingMaintainingJunkDimensions.pdf

A possible solution is to incorporate both designs into a single Junk Dimension table.

1. Breaking up the individual STATE into seperate fields to facilitate the filtering by the queries.
2. Include a single field with the concatenated string (of all STATEs) to facilitate the dimensional lookups (not exposed to end user).

As far as adding a new field to the dimension table as new states arise, this is not uncommon and is part of the evolution of a datawarehouse anyway.

Hope this helps.