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
DBA2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.