Explain what is a Dimension (Techinically)? what are dimension attributes?
Explain what is a Dimension (Techinically)? what are dimension attributes?
Dimension: one of the perspectives that can be used to analyze the data.Originally Posted by raghuladdu
Sanghala
If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.
Dimension is the object which we used to answer the user queries.There in Data warehousing generally dimension are of discuss in terms of Slowly changing dimension and slowly growing dimensions.
Slowly changing Dimension(SCD) are of three types:-
type1:-in which only the current data is maintained
type2:-in which both current and historical data is maintained.It has three types:-a)flag type:-where we assign the flag to newly inerted data for recognising it.
b) version type:-we assign a version number with each insert or update so taht we can judge which is new record and which one is old
c) date type:-date is added with each record
type 3:- here we maintained only current data and one time history.
example of dimension are Time, region, product,quantity,customer.
Dimension attributes depends upon the granularity level.e.g.time has following attribute:-Year,quater,month,week,day,hour,min,sec
REgion:-world,Asia,India,south,bangalore,BTM Layout
slowly groing dimensiona re those which always agrow but slowly.
What is dimension table dimension table is one that describe the business entities of an enterprise, represented as hierarchical, categorical information such as time, departments,locations,and products.dimension tables are sometimes called lookup or reference tables. What is time dimension? in a relational data model, for normalization purposes, year lookup, quarter lookup, month lookup, and week lookups are not merged as a single table. In a dimensional data modeling (star schema), these tables would be merged as a single table called time dimension for performance and slicing data. What is organization dimension in a relational data model, for normalization purposes, corporate office lookup, region lookup, branch lookup, and employee lookups are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called organization dimension for performance and slicing data. What is product dimension in a relational data model, for normalization purposes, product category lookup, product sub-category lookup, product lookup, and and product feature lookups are are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called product dimension for performance and slicing data requirements. What are the different methods of loading dimension tables? depends on dimension type for static dimensions one time or periodical loading can be done for slolwy changing dimensions slowly changing dimensions are often categorized into three types type1,type2 & type3 ______ type 1: overwriting the old values. In the year 2005, if the price of the product changes to $250, then the old values of the columns "year" and "product price" have to be updated and replaced with the new values. In this type 1, there is no way to find out the old value of the product "product1" in year 2004 since the table now contains only the new price and year information. Product product id(pk) year product name product price 1 2005 product1 $250 ______ type 2: creating an another additional record. In this type 2, the old values will not be replaced but a new row containing the new values will be added to the product table. So at any point of time, the difference between the old values and new values can be retrieved and easily be compared. This would be very useful for reporting purposes. Product product id(pk) year product name product price 1 2004 product1 $150 1 2005 product1 $250 the problem with the above mentioned data structure is "product id" cannot store duplicate values of "product1" since "product id" is the primary key. Also, the current data structure doesn't clearly specify the effective date and expiry date of product1 like when the change to its price happened. So, it would be better to change the current data structure to overcome the above primary key violation. Product product id(pk) effective datetime(pk) year product product expiry name price datetime 1 01-01-2004 12.00am 2004 product1 $150 12-31-2004 11.59pm 1 01-01-2005 12.00am 2005 product1 $250 in the changed product table's data structure, "product id" and "effective datetime" are composite primary keys. So there would be no violation of primary key constraint. Addition of new columns, "effective datetime" and "expiry datetime" provides the information about the product's effective date and expiry date which adds more clarity and enhances the scope of this table. Type2 approach may need additional space in the data base, since for every changed record, an additional row has to be stored. Since dimensions are not that big in the real world, additional space is negligible. ______ type 3: creating new fields. In this type 3, the latest update to the changed values can be seen. Example mentioned below illustrates how to add new columns and keep track of the changes. From that, we are able to see the current price and the previous price of the product, product1. Product product id(pk) current product current old product old year year name product price price 1 2005 product1 $250 $150 2004 the problem with the type 3 approach, is over years, if the product price continuously changes, then the complete history may not be stored, only the latest change will be stored. For example, in year 2006, if the product1's price changes to $350, then we would not be able to see the complete history of 2004 prices, since the old values would have been updated with 2005 product information. Product product id(pk) year product product old product old year name price price 1 2006 product1 $350 $250 2005 what are confirmed dimensions? conformed dimensions are the dimensions which can be used across multiple data marts in combination with multiple facts tables accordingly e.g : date dimension cd means the exact same thing with every possible fact table to which they are joined.ex. Date dimenstion is connected all fact tables like sales facts,inventory fact etc. Well this's what i can tell abt - dimension's - "technically" from my point of view, it might not be the apt but shall give an edge/idea. Should you need any further details pls ask me . Regards venkat
Re: Dimensions
What's the hell is this ?
Are you able to understand/read... Venkatkalpana?
Please edit your post ?
Sanghala
If you are satisfied with my reply. Please click “Thanks” button which increases the reputation and can serve you better.
HI guys! I will make my answer short: Dimension is what describes the measures in fact tables.
Dimension is the catogiry of information by which summeriged data can be viewed. for example a profit summerized fact table can be viewed by time dimension.
Dear Reader,
I'm sorry for that big explanation, I was giving u all that bcos i was wondering u might have the other related topics on Fact and Dimensions....anyways Now that i know what kind of person u are ...I'm sorry to bother u ...and waste ur time...I regret for any ....
NOTE:- I've pasted what i've and what i know...
Regards
Venkat
Re: Dimensions
Ha ha ha hey cheerup buddy, your explanation is correct (you just copied the whole article). Anyways, don't stop posting for those who love to read surely they get additional info from it.
In DWH there are two types of Tables used in Star and Snow flake schemas
They are
1. Fact
2. Dimension
1. FACT: In data warehousing, a fact table consists of the measurements, metrics or facts of a business process. It is often located at the centre of a star schema, surrounded by dimension tables.
Fact tables provide the (usually) additive values which act as independent variables by which dimensional attributes are analyzed. Fact tables are often defined by their grain. The grain of a fact table represents the most atomic level by which the facts may be defined. The grain of a SALES fact table might be stated as "Sales volume by Day by Product by Store". Each record in this fact table is therefore uniquely defined by a day, product and store. Other dimensions might be members of this fact table (such as location/region) but these add nothing to the uniqueness of the fact records. These "affiliate dimensions" allow for additional slices of the independent facts but generally provide insights at a higher level of aggregation (region is made up of many stores)
2.DIMENSION TABLE: In data warehousing, a dimension table is one of the set of companion tables to a fact table.
The fact table contains business facts or measures and foreign keys which refer to candidate keys (normally primary keys) in the dimension tables.
The dimension tables contain attributes (or fields) used to constrain and group data when performing data warehousing queries.
For example, "Customer", "Date", and "Product" are all dimensions that could be applied meaningfully to a sales receipt
Over time, the attributes of a given row in a dimension table may change. For example, the shipping address for a company may change. Kimball refers to this phenomenon as Slowly Changing Dimensions. Strategies for dealing with this kind of change are divided into three categories:
• Type One - Simply overwrite the old value(s).
• Type Two - Add a new row containing the new value(s), and distinguish between the rows using Tuple-versioning techniques (Tuple-versioning (also called point-in-time) is a mechanism used in a relational database management system to store past states of a relation.).
• Type Three - Add a new attribute to the existing row.
Dimension: A textual data which is avialable in a table
Fact : Numeric Data which is avialalable in a table
The relation between Dim->Fact is 1:N
Each Dim has a primary key which is referred by a foreign key that is avilable in Fact Table.Collection of Foreign key in fact table is what we called composite natural key here what we call this as surrogate key.
Thanks,
Sivakumar.R
Posting Permissions
Reply With Quote