0

I have concerning question as to why when preparing Cubes in sql server they have have a time DIMENSION. From my studies on time I have always learnt that time in math is a function. t squared is speed t cubed is velocity. ok a time dimension sounds like something out of sci fi film. so why did they call it this?

Any ideas.

Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
Chris113
  • 9
  • 3
  • It's not about physics, functions or even SQL Server. Or even SQL. A star schema contains measurements for *many* dimensions - products, customers etc. A dimension that's common to almost all star schemas is time (or rather, date) – Panagiotis Kanavos Aug 27 '20 at 08:11
  • It's a dimension with times in it, ergo a time dimension. A Dimension in a Cube isn't the same as a dimension is physics. A time, in data, is a specific value of time, it's not a concept. Data, maths, and theoretical physics are vastly different. – Thom A Aug 27 '20 at 08:11
  • Check the tags I added, especially `dimensions` and `multidimensional-array`. An *array* can have many dimensions. This has nothing to do with physics – Panagiotis Kanavos Aug 27 '20 at 08:13

2 Answers2

0

Dimensional modeling (DM) is part of the Business Dimensional Lifecycle methodology developed by Ralph Kimball.

In DM you have fact tables and dimentsion tables.

A fact table consists of the measurements, metrics or facts of a business process.

A dimension table is one of the set of companion tables to a fact table. They contain descriptive attributes that generally provide the context for (how,where,when) the fact occured.

So you if you have a table dimension that contains time, you would call it a time dimension. Most of the time however, it is called a date dimension.

Gabriel Durac
  • 2,610
  • 1
  • 12
  • 13
  • Seems really badly named. A Dimension is a measurable extent of a particular kind. But usually you use time to measure things facts your not measuring time itself. Like we don’t say number dimension. You know what mean. – Chris113 Aug 27 '20 at 09:19
  • You are entitled to your own opionon, but DM was introuduced in 1996 and has become a standard. I would suggest you just get on board :) – Gabriel Durac Aug 27 '20 at 10:01
0

Date and Time dimensions serve very different purposes. You must remember that the purpose of a dimension is to describe the event(Fact) that occurred. Whereas a date dimension can be used to answer questions like events on a holiday or events per quarter, the Time dimension usually breaks the grain down to the second (but can go lower) to assign additional meaning beyond just the ticks of the clock.

For example your business may designate business hours as 9am-5pm. You can associate a flag attribute "Business Hours" to each second to allow you to easily identify facts occurring after business hours. If you have multiple overlapping work shifts, you can create a column for each shift to indicate if that time corresponds to that shift.

A time dimension relationship should tell you more than just when the event occurred by adding additional analytical layers to the When.

For example, relating events to multiple time dimensions (e.g. local vs UTC) can also allow interesting analysis across time zones (How much arson occurs between 1 - 2am) or (How much arson occurred after the flying spaghetti monster attacked at 1:23am UTC).

FYI: I would expressly recommend against combining time and date into a single DateTime dimension. Clock seconds are finite (86400 per day); days are infinite. DateTime dimension at the second grain can become unmanageably large very quickly. However, some analysis needs this grain, specifically when looking at events dependent on sunrise/sunset or other attributes that vary based on the Date AND Time.

Wes H
  • 4,186
  • 2
  • 13
  • 24