June 5, 2003 at 1:04 am
Hi All,
I am a novice to Analysis Services and OLAP, I want to ask that i have a table from where i'll build the time dimension and it contains time with date as well. i need advice that what should i do mean how should i handle this, since entries contains time and definitely its important as transactions can be done with seconds difference. if i build a separate dimension then i would create a huge dimension is it OK?. What is the best practice for creating a time dimension. if i eleminate the time part then there would be a problem that primary key violation will occur due to duplicate entries. I cannot aggregate the entries as i want to keep the unit price measure in Fact Table.
Please reply urgently, thanks in advance
Regards,
Affan
June 5, 2003 at 9:01 pm
Hi Affan,
Are you saying that you want the time (HH:MM:SS) in your time dimension in your cube(s)?
If yes,then the member counts on this dimension are likely to be large, especially if you include all times within a day at say the minute level.
Alternatively, you could put time of day into a separate dimension, and depending on the level of detail, the number of members won't be too bad.
If you you don't want to have time in your Time dimension, then there is no reason to aggregate the days together in the fact. Why not create a view over your fact table that casts the datetime to a date only, and join your dimension table to the fact on this newly created field.
HTH,
Steve.
Steve.
June 6, 2003 at 1:48 am
Hi Steve,
The point is that i want the HH:MM:SS as there could be any transaction of the same id with time difference. e.g. in my fact fact table if i remove the time part from time dimension then the rows will be get duplicated and this will violate the PK constraint of the fact table. So what should i do then should i keep the time in dimension.
I will always have time and i wnat time in my transaction.
Please explain this further
"Alternatively, you could put time of day into a separate dimension, and depending on the level of detail, the number of members won't be too bad."
Kind Regards,
Affan
June 9, 2003 at 11:35 am
maffan76,
You can remove the 'time part' from the date without causing any PK contraint violations.
You have to re-consider the primary key of the fact table.
What is the current primary-key of the fact table?
Why do you need primary-key in the fact-table? In case you need a primary key, consider using a surrogate key(generated during the load process)
(If you want to discuss Surrogate keys: better start a new thread.)
Ram
June 9, 2003 at 4:46 pm
Couldn't you use the transaction ID as the primary key?
To remove the time part, use a view which makes use of the cast or convert function.
RE: Using time in a separate dimension, although in AS you can have a dimension that has levels of HH, MM & SS below the (say) Years/Quarter/Month/Day levels, you could also have 2 dimensions, one that has the traditional Year/Month/Day levels, and a second that has Hours/Minutes or Hours/Minutes/Seconds.
Usage of either method really requires you to find out from the business users whether they need "time" in their Time dimension, or if they are just happy with date related information.
Steve.
Steve.
June 9, 2003 at 11:57 pm
Actually i was reading the articles and looking at the examples and thats why i was using the composite keys. If there are other ways of solving thsi problem, i'll implement that as long as i know what i am doing
Similarly, i have CompanyID, AcocuntID, TimeID as PK. Similarly if i use surrogate key then i will have different versions of truth at the same time whihc is incorrect as i have removed the time part from row.
Regards,
Affan
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply