• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 466
  • Last Modified:

Data Mart

I am trying to create a data mart for my company invoices.
My Quastion is how I can deal with the DateDim?
the information I have is the Invoice date how I can create a table for the date and what should the DateID be and how I can deal with that on the SSIS?

Thanks
0
DBA2010
Asked:
DBA2010
  • 5
1 Solution
 
ValentinoVBI ConsultantCommented:
Check out the following script: http://sql-bi-dev.blogspot.com/2010/05/date-dimension.html

As ID it uses the date itself in the YYYYMMDD format.  If you then put your clustered index in every Fact table on the DateID, your records will be ordered chronologically, which is another best practice.
0
 
ValentinoVBI ConsultantCommented:
To create the value for your FactInvoice (or whatever your table may be called), you can simply use the invoice date and create the ID from that.  Watch out for day and month numbers below 10, they need a leading zero.  You'll probably find the Script component the most handy one to construct the ID.
0
 
PedroCGDCommented:
Take a look at the article I wrote to Microsoft Knowledge Base
http://support.microsoft.com/kb/2293691/pt
Is in portuguese but you can download the scripts.
Regards,.
Pedro
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
ValentinoVBI ConsultantCommented:
As I mentioned in my previous comment: "You'll probably find the Script component the most handy one to construct the ID."

Here's a code snippet how you could achieve that (in C#).
If Row.EndTime_IsNull Then
            Row.DateID = -1
        Else
            Row.DateID = Convert.ToInt32(Row.InvoiceDate.ToString("yyyyMMdd"))
End If

Open in new window

0
 
ValentinoVBI ConsultantCommented:
Darn, clicked Submit too fast.
(when are they going to implement functionality here that let's you edit your comment to correct small mistakes?)

Here's the corrected snippet (assuming that your incoming field is called InvoiceDate and the output field is DateID.
If Row.InvoiceDate_IsNull Then
            Row.DateID = -1
        Else
            Row.DateID = Convert.ToInt32(Row.InvoiceDate.ToString("yyyyMMdd"))
End If

Open in new window

0
 
ValentinoVBI ConsultantCommented:
Ow, and the code is VB.NET, not C# (I really should have gotten that coffee first)...
(one more wish for an Edit button here)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now