Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Best pratice for resolving Bit operation in Dimension

3 posters

Go down

Best pratice for resolving Bit operation in Dimension Empty Best pratice for resolving Bit operation in Dimension

Post  DilMustafa 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

Back to top Go down

Best pratice for resolving Bit operation in Dimension Empty Re: Best pratice for resolving Bit operation in Dimension

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

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

Back to top Go down

Best pratice for resolving Bit operation in Dimension Empty Re: Best pratice for resolving Bit operation in Dimension

Post  DilMustafa 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

Back to top Go down

Best pratice for resolving Bit operation in Dimension Empty Re: Best pratice for resolving Bit operation in Dimension

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

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

Back to top Go down

Best pratice for resolving Bit operation in Dimension Empty Re: Best pratice for resolving Bit operation in Dimension

Post  juz_b 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

Back to top Go down

Best pratice for resolving Bit operation in Dimension Empty Re: Best pratice for resolving Bit operation in Dimension

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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