Datatype on IsXXXX columns

[hirths]

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

[BoxesAndLines]

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.

[bruce.szalwinski]

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.

[Jeff Smith]

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.

[hirths]

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.

[BoxesAndLines]

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

[ian chapman]

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.