October 11, 2012 at 8:50 am
Hello all! I'm new to the forum (and SQL Server to an extent) and am hoping someone can give some advice on how best to handle cryptic column names in a provided data set.
Here is my scenario. I periodically receive a large .csv data set that I need to load into SQL Server 2008. The column names are like 'ABC1', 'ABC2', 'ABC3', etc. I need to preserve those names so that I can still tie back to the original data source, but I also need to make these columns more useful for the analysts that will be writing queries over the tables. I would like to have short meaningful column names, but then also have the longer definition somewhere because some of these columns represent complex financial formulas that the analysts will need to reference.
Any ideas? Thanks in advance!
October 11, 2012 at 8:54 am
I had to do that a lot previously.
keep the table you import into, but create a view with column names that better describe the purpose of the columns;
even better, the view is updatable by default, so your biz people can treat it as if it is the actual table and insert/update/delete if needed.
so for example:
CREATE TABLE 7Z94FB (
ABC1 int,
ABC2 varchar(30)
ABC3 varchar(50) ,
ABC4 DECIMAL(19,4))
GO
CREATE VIEW ImportedCustomers
AS
SELECT
ABC1 AS ID,
ABC2 AS FirstName,
ABC3 As LastName,
ABC4 As Balance
FROM 7Z94FB;
Lowell
October 11, 2012 at 9:11 am
Thank you, Lowell! That is a great idea and it solves most of my problem. The analysts will be able to see a column name that makes sense, and that is the most important thing. I was also hoping to provide the longer description somewhere. Some of these columns represent calculations that get pretty complicated, and I'd like the formulas provided to be visible somehow.
I would be fine with storing these definitions in a separate table or view, but I don't know how I would be able to join to that table since I'm trying to define the column names themselves and not the values in those columns.
October 11, 2012 at 9:17 am
yeah the second half of the issue is tough; you can stick the descriptive data in a relationsship witht eh columns/tables, etc with extended properties(sys.sp_addextendedproperty and fn_listextendedproperty), but that also means the end users need the ability to extract that out again...no easy way end user to do that.
same thing with having a seperate document, or sticking the descriptions/formulas in a table...they need to be shown where to look and make it a part of their routines.
Lowell
October 11, 2012 at 9:25 am
You can add more detailed descriptions as extended properties via SSMS or with a stored procedure call:
EXEC sys.sp_addextendedproperty
@name=N'ColumnDescription',
@value=N'Credit Card Expiration Date' ,
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'CustomerOrder',
@level2type=N'COLUMN',
@level2name=N'Order_CCExp'
October 11, 2012 at 9:28 am
They're some pretty savvy SQL users, so I may just give them a table with definitions and let them look up the values whenever they need to. That might be good enough since they won't be including the long definitions in their reporting. It is really just for their reference.
Thanks again for your help!
October 11, 2012 at 9:35 am
nmaddox (10/11/2012)
They're some pretty savvy SQL users, so I may just give them a table with definitions and let them look up the values whenever they need to. That might be good enough since they won't be including the long definitions in their reporting. It is really just for their reference.Thanks again for your help!
You can do lookups of extended properties via the sys.extended_properties catalog view, and users can also see them in SSMS.
There are also third party tools, such as Redgate SQL Doc that use extended properties to document the database. There are also free documentation tools that use extended properties.
October 11, 2012 at 10:20 am
Thank you so much! I've never worked with extended properties before, but this looks interesting and I will read up on it. Thanks for the suggestion!
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply