Datatype on IsXXXX columns

View previous topic View next topic Go down

Datatype on IsXXXX columns

Post  hirths on Tue Dec 11, 2012 8:33 pm

Does anyone have any input on what data type I should make my IsXXXX columns in a dimension

eg. in a date dimension I would have IsLastDayOfMonth

I am using SQL Server, my natural instinct would be to make this a BIT, but it could just as easily be a CHAR(1) with a Y/N value

Any advise on pros/cons of either way would be appreciate

Thanks
Steve

hirths

Posts : 4
Join date : 2011-02-07

View user profile

Back to top Go down

Re: Datatype on IsXXXX columns

Post  BoxesAndLines on Tue Dec 11, 2012 9:56 pm

For SQL Server my standard is tinyint and I use values 0,1. This way, I can easily sum these in a fact table. FWIW, I call these indicators, e.g. MONTH_LAST_DAY_IND. I also don't use prepositions such as of, from, to, etc.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Datatype on IsXXXX columns

Post  bruce.szalwinski on Wed Dec 12, 2012 12:06 pm

I like ints for the same reason, easy to sum. Since Steve said these were dimension attributes though, I'm tending towards char(1). Sum(isLastDayOfMonth) doesn't make any sense.

bruce.szalwinski

Posts : 8
Join date : 2012-12-11

View user profile

Back to top Go down

Re: Datatype on IsXXXX columns

Post  Jeff Smith on Wed Dec 12, 2012 5:53 pm

I tend to use 3 columns in my dimension table to handle these types of fields. Usually, the source system sends me a 0/1 field. A 1/0 can't really be used in a report and many users won't know how to filter with the such a field. To make it easier to filter, I will add a field with "Yes/No" values. But Yes/No still isn't enough because it doesn't have much use in a report. In the example for a Work_Day_Indicator, I would have a field with the values "Work Day" and "Non Work Day". It takes some work but once it's done, it's done.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Datatype on IsXXXX columns

Post  hirths on Thu Dec 13, 2012 7:14 pm

Thanks guys for the feedback

I can see the merits of using a TINYINT in a fact table to allow aggregation, my main concern was in a dimension, and I can see from an end-user perspective a BIT isn't very descriptive

I think I'll go with adding an extra column along the lines of Jeff's WorkDay/NonWorkDay example.

My example was a bit contrived as well, just trying to think of an example and I was knee deep in my Date dimension at the time.

hirths

Posts : 4
Join date : 2011-02-07

View user profile

Back to top Go down

Re: Datatype on IsXXXX columns

Post  BoxesAndLines on Thu Dec 13, 2012 8:08 pm

I leave the conversion of 0 and 1 to something more meaningful for the business to the reporting layer.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Datatype on IsXXXX columns

Post  ian chapman on Thu Jan 03, 2013 11:41 am

I have used small integre (0 and 1) in the past, with computed columns to represent the Yes/No values. This makes it easier to ensure the values are consistent across attributes.

ian chapman

Posts : 2
Join date : 2013-01-03

View user profile

Back to top Go down

Re: Datatype on IsXXXX columns

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


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