Best pratice for resolving Bit operation in Dimension

View previous topic View next topic Go down

Best pratice for resolving Bit operation in Dimension

Post  DilMustafa on Thu Jul 09, 2009 5:43 pm

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,

DilMustafa

Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada

View user profile

Back to top Go down

Re: Best pratice for resolving Bit operation in Dimension

Post  BoxesAndLines on Thu Jul 09, 2009 11:50 pm

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Best pratice for resolving Bit operation in Dimension

Post  DilMustafa on Fri Jul 10, 2009 12:38 am

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.

DilMustafa

Posts : 49
Join date : 2009-02-03
Location : Calgary, Canada

View user profile

Back to top Go down

Re: Best pratice for resolving Bit operation in Dimension

Post  BoxesAndLines on Fri Jul 10, 2009 9:35 am

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.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Best pratice for resolving Bit operation in Dimension

Post  juz_b on Wed Jul 15, 2009 5:47 pm

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.

juz_b

Posts : 17
Join date : 2009-02-07

View user profile

Back to top Go down

Re: Best pratice for resolving Bit operation in Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum