SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Dictionary Table in Staging Database


Data Dictionary Table in Staging Database

Author
Message
imani_technology
imani_technology
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1754 Visits: 787
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?
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216382 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
twin.devil
twin.devil
SSCertifiable
SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)SSCertifiable (5.4K reputation)

Group: General Forum Members
Points: 5364 Visits: 2692
+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?
P Jones
P Jones
SSCrazy
SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)SSCrazy (2.9K reputation)

Group: General Forum Members
Points: 2860 Visits: 1524
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.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)SSC Guru (216K reputation)

Group: General Forum Members
Points: 216382 Visits: 41986
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lempster
Lempster
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5111 Visits: 1657
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
imani_technology
imani_technology
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1754 Visits: 787
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search