Datatype on IsXXXX columns
5 posters
Page 1 of 1
Datatype on IsXXXX columns
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
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
Re: Datatype on IsXXXX columns
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Datatype on IsXXXX columns
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
Re: Datatype on IsXXXX columns
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
Re: Datatype on IsXXXX columns
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.
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
Re: Datatype on IsXXXX columns
I leave the conversion of 0 and 1 to something more meaningful for the business to the reporting layer.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Datatype on IsXXXX columns
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
![-](https://2img.net/i/empty.gif)
» How many columns is too many OR how to reduce the columns
» Time Dimension, NULLs and Time datatype
» SCD 2 Audit / Metadata Columns
» Kalido: Need to add two columns
» Fact with large columns, 320.
» Time Dimension, NULLs and Time datatype
» SCD 2 Audit / Metadata Columns
» Kalido: Need to add two columns
» Fact with large columns, 320.
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum