Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Data Dictionary Table in Staging Database Expand / Collapse
Author
Message
Posted Monday, June 30, 2014 11:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
We are trying to extract flat files into a staging database. The extracted data from the flat files should be "raw" in the Kimball sense. That means there shouldn't be any changes to the data as they are being extracted. The staging tables should have the exact format as the source flat files and there should be a table for each type of flat file.

The boss wants an additional table in the staging database: a data dictionary. There should be data flow task that populates the data dictionary table the same time as the staging tables are populated. I have copied and pasted the script that creates the data dictionary table below.

CREATE TABLE [dbo].[DataDictionary](
[ExtractDate] [datetime] NULL,
[TblName] [varchar](65) NOT NULL,
[ColName] [varchar](50) NOT NULL,
[MacroName] [varchar](30) NULL,
[LongName] [varchar](65) NULL,
[Description] [varchar](500) NULL,
[PKPos] [smallint] NOT NULL,
[Datatype] [varchar](50) NULL,
[Length] [int] NULL,
[Prec] [smallint] NULL,
[Scale] [smallint] NULL,
[IsNullable] [bit] NOT NULL,
[IsIdentity] [bit] NOT NULL,
[DefaultValue] [varchar](100) NULL,
[Active] [bit] NOT NULL,
[CreatedByObj] [varchar](50) NULL,
[IgnoreOnCompare] [char](1) NOT NULL,
[ChangeHashBytes] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


Here are my questions: 1) do we need this data dictionary table in our staging database that is basically the flat files in tables? 2) Why or why not?
Post #1587735
Posted Monday, June 30, 2014 11:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
imani_technology (6/30/2014)
We are trying to extract flat files into a staging database. The extracted data from the flat files should be "raw" in the Kimball sense. That means there shouldn't be any changes to the data as they are being extracted. The staging tables should have the exact format as the source flat files and there should be a table for each type of flat file.

The boss wants an additional table in the staging database: a data dictionary. There should be data flow task that populates the data dictionary table the same time as the staging tables are populated. I have copied and pasted the script that creates the data dictionary table below.

CREATE TABLE [dbo].[DataDictionary](
[ExtractDate] [datetime] NULL,
[TblName] [varchar](65) NOT NULL,
[ColName] [varchar](50) NOT NULL,
[MacroName] [varchar](30) NULL,
[LongName] [varchar](65) NULL,
[Description] [varchar](500) NULL,
[PKPos] [smallint] NOT NULL,
[Datatype] [varchar](50) NULL,
[Length] [int] NULL,
[Prec] [smallint] NULL,
[Scale] [smallint] NULL,
[IsNullable] [bit] NOT NULL,
[IsIdentity] [bit] NOT NULL,
[DefaultValue] [varchar](100) NULL,
[Active] [bit] NOT NULL,
[CreatedByObj] [varchar](50) NULL,
[IgnoreOnCompare] [char](1) NOT NULL,
[ChangeHashBytes] [varbinary](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO


Here are my questions: 1) do we need this data dictionary table in our staging database that is basically the flat files in tables? 2) Why or why not?


It looks like a log table to me and I think it's a way to keep track of what files where loaded and how they were handled. Something like this is super important during audits and investigations.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1587858
Posted Monday, June 30, 2014 11:40 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Today @ 4:06 AM
Points: 781, Visits: 1,389
+1 Jeff,

By the way, did your boss shared any information when he asked for this table. He might have shared something didn't he?
Post #1587863
Posted Tuesday, July 1, 2014 2:30 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:50 AM
Points: 561, Visits: 1,209
Yes there are uses for these files. It logs what has been delivered and lets others see what fields are available.
It also gives you a way of checking if the file exists before you try to import it.
Post #1587896
Posted Tuesday, July 1, 2014 10:53 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 35,366, Visits: 31,905
P Jones (7/1/2014)
Yes there are uses for these files. It logs what has been delivered and lets others see what fields are available.
It also gives you a way of checking if the file exists before you try to import it.


Just to be clear, it's not a file. It's a table. And, sorry but I have to ask, how does the simple existence of a table help you with determining if the file exists before you try to import it?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1588280
Posted Wednesday, July 2, 2014 2:34 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 2:35 AM
Points: 2,126, Visits: 1,473
Jeff Moden (7/1/2014)

Just to be clear, it's not a file. It's a table. And, sorry but I have to ask, how does the simple existence of a table help you with determining if the file exists before you try to import it?

Presumably one could check the table to see whether a file of the same name had already been imported, but I agree, it won't do anything to determine the actual existence of the file. I'm not so sure that the purpose of the table is to track imported files though; it looks more like a table that holds data about other tables to me - a metadata table if you will.

Regards
Lempster
Post #1588350
Posted Wednesday, July 2, 2014 2:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 3:59 PM
Points: 213, Visits: 572
Sorry, it is a metadata table, not a logging table. It contains information about the structure of the source system. The question is whether a metadata table of the source system belong in a staging database. My boss (who has never done ETL) seems to think so. I'm not so sure.
Post #1588352
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse