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


Adding time dimension in the project


Adding time dimension in the project

Author
Message
Smash125
Smash125
SSChasing Mays
SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)SSChasing Mays (617 reputation)

Group: General Forum Members
Points: 617 Visits: 1381
I have two dimensions

Contact and Sales and fact table

fact table contains following attributes

OrderDate,TotalDue,TaxAmt,SubTotal,Freight

My Source table does not contain any Time table.I want to add Time dimension and want to aggregate the data on based on year,semister,quater wise etc

I have done this using some tricks i am not sure whether i am correct or wrong

What i did

1. Right Click on Dimension

2.Add New Dimension

3.Selected 'Generate a time table in the Data Source'

4.Selected First calender day(Jan 1 2001) and Last Calender Day(31 Dec 2004) since Sales Table contains Orderdate starting from 2001 to 2004

5.Selected all the Time Periods

6.Created Time Dimension

7.Added this to existing data source view and made Date as Primary Key and linked to OrderDate attribute in Sales

8.Done the same thing in the attribute relationship tab

For my happiness it worked very well and aggregation i am getting accordingly verified in the data base with SQL statements

So my question is did i made the right relation ship with Sales

table and whatever steps i followed is fine?

Please let me know i am relatively new to SSAS. Any advice or suggestions are appreciated
davoscollective
davoscollective
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1639 Visits: 1008
Sounds OK.

I would make the Calendar table a lot longer, like to 2050 or something. The table won't be very big even with 365/366 rows per year that's only about 13,000 rows. Generally with a datawarehouse you are incrementally adding new data daily (or weekly or whatever) and if you get data after 2004 you won't have any way to link your fact table to your date (calendar) dimension. BTW, a table with dates is not really a time dimension, it's a date or calendar dimension. A separate time dimension would be useful if you are recording time of day, if you require that level of granularity.

Another suggestion is to always use surrogate keys, even for calendar dimensions. I.e. don't use the date value as the key itself. Kimball has a good explanation of this here

The basic reasons are that integer keys are quick and smart keys (with information encoded into the key) are generally a bad idea because it encourages them to be used in client applications and they lose their robustness. It also makes it awkward to have a calendar table that includes holidays for different geographical regions.
You also get stuck if your source data has a bad date, it means you can do error handling so you have a way to link it to an "unknown" member of the date dimension.
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