3

Is there a term for a dimensions that solely transforms data?

For instance, I came across a dimension that transforms date data to different representations (e.g. an abbreviation for a fiscal year such an FY1 and other representations)?

stevebot
  • 23,275
  • 29
  • 119
  • 181

3 Answers3

3

All dimensions are the result of "transformations" of your data, in some sense or other, even if it's just a de-normalization of multiple relational tables into a wide dimension table with repeated attributes.

Having multiple representations of a date in your date dimensions is a good practice. It allows you to store things like a Fiscal Calendar (5-4-4 fiscal weeks, etc) which may vary by organization, and are not easily created with a formula. Using this dimension, you can then build aggregates based on specific attributes (reporting by Fiscal Month vs by Calendar Month), etc.

Yes, all the attributes of that date might be "implict" in the DATETIME type, but it makes for much more maintainable queries and ease of business users using the data to provide multiple attributes based on that date.

N West
  • 6,768
  • 25
  • 40
2

I'd say that all the represntations of the date should be stored permanently in the calendar dimension. The warehouse often has an enterprise layer where data is brought together from multiple systems, conformed (so that keys and dates etc are all in the same format) and enriched - i.e. we create all the representation of dates to be used in the calendar dimension. Then you have the presentation layer (Kimball), which is de-normalised in deliberate ways to make the queries run faster. The table which allows us to enrich the dimensions are part of the enterprise layer, not the presentation layer, so are not, by definition, dimensions. Dimensions sit solely in the presentation layer. Im my opinion, of course!

Steve
  • 245
  • 1
  • 6
1

All dimensions in a datawarehouse solution basically exist because the end user want to be able to make decisions based on that dimension.
(In practice the end user would probably base his decisions on a combination of several dimensions).

And the dimensions that ends up in the final datawarehouse solution are themselves a result of a large data transformation proces.

that is:

  • transformation of the raw data where data is alligned from several sources
  • transformation in form of enrichment of the raw data (fx applying hierarchies)
  • and so on...

So speaking of a dimension that solely tranforms data is for a start a vague way of describing the properties of a dimension, since it is a little unclear what is meant.

However with that in mind I acknowledge your question in that way that it makes us ask ourselves: could a "tranformation dimension" be a new type of dimension that exists in the datawarehouse?

Well if you think of your "tranformation dimension" as a dimension that is totally derived from another dimension, without the influence of the raw data, then the notion of "tranformation dimension" becomes more precise.

So in your case a dimension that transforms date data to different representations could correctly be referred to as a "tranformation dimension".

For a more comprehensive list of dimension table types in datawarehousing check out this question:
What are the types of dimension tables in star schema design?

Community
  • 1
  • 1
MOLAP
  • 784
  • 4
  • 13
  • 28
  • A dimension is a collection of related attributes about a specific measure. Why would I build a new dimension whose attributes are wholly derived from another dimension, since all of those attributes are, *by definition* related to the values that they are being derived from! If you are pulling them out to "normalize" them, you're just snowflaking, not creating a new dimension. We don't derive a "PRODUCT TYPE dimension" from my "PRODUCT" dimension, we just store the product type attributes on the PRODUCT dim. – N West Aug 30 '12 at 17:07