Dimensional Modeling definition......

Dimensional modeling is the design concept used by many data warehouse designers to build their data warehouse. Dimensional model is the underlying data model used by many of the commercial OLAP products available today in the market. In this model, all data is contained in two types of tables called Fact Table and Dimension Table.


Dimensional Modeling - Fact TableIn a Dimensional Model, Fact table contains the measurements or metrics or facts of business processes. If your business process is Sales, then a measurement of this business process such as "monthly sales number" is captured in the fact table. In addition to the measurements, the only other things a fact table contains are foreign keys for the dimension tables.

Dimensional Modeling - Dimension TableIn a Dimensional Model, context of the measurements are represented in dimension tables. You can also think of the context of a measurement as the characteristics such as who, what, where, when, how of a measurement (subject ). In your business process Sales, the characteristics of the 'monthly sales number' measurement can be a Location (Where), Time (When), Product Sold (What).

The Dimension Attributes are the various columns in a dimension table. In the Location dimension, the attributes can be Location Code, State, Country, Zip code. Generally the Dimension Attributes are used in report labels, and query constraints such as where Country='USA'. The dimension attributes also contain one or more hierarchical relationships.Before designing your data warehouse, you need to decide what this data warehouse contains. Say if you want to build a data warehouse containing monthly sales numbers across multiple store locations, across time and across products then your dimensions are: Location, Time, ProductEach dimension table contains data for one dimension. In the above example you get all your store location information and put that into one single table called Location. Your store location data may be spanned across multiple tables in your OLTP system (unlike OLAP), but you need to de-normalize all that data into one single table.

Types of Dimensions in datawarehouse

Confirmed Dimensions, Junk Dimensions, and Degenerated Dimensions

Conformed Dimensions (CD): these dimensions are something that is built once in your model and can be reused multiple times with different fact tables. For example, consider a model containing multiple fact tables, representing different data marts. Now look for a dimension that is common to these facts tables. In this example let’s consider that the product dimension is common and hence can be reused by creating short cuts and joining the different fact tables.Some of the examples are time dimension, customer dimensions, product dimension.

Junked Dimensions (JD): When you consolidate lots of small dimensions and instead of having 100s of small dimensions, that will have few records in them, cluttering your database with these mini ‘identifier’ tables, all records from all these small dimension tables are loaded into ONE dimension table and we call this dimension table Junk dimension table. (Since we are storing all the junk in this one table) For example: a company might have handful of manufacture plants, handful of order types, and so on, so forth, and we can consolidate them in one dimension table called junked dimension table.

Degenerated Dimension (DD): An item that is in the fact table but is stripped off of its description, because the description belongs in dimension table, is referred to as Degenerated Dimension. Since it looks like dimension, but is really in fact table and has been degenerated of its description, hence is called degenerated dimension. Now coming to the slowly changing dimensions (SCD) and Slowly Growing Dimensions (SGD): I would like to classify them to be more of an attributes of dimensions its self.

Although other might disagree to this view but Slowly Changing Dimensions are basically those dimensions whose key value will remain static but description might change over the period of time. For example, the product id in a companies, product line might remain the same, but the description might change from time to time, hence, product dimension is called slowly changing dimension.

Lets consider a customer dimension, which will have a unique customer id but the customer name (company name) might change periodically due to buy out / acquisitions, Hence, slowly changing dimension, as customer number is static but customer name is changing, However, on the other hand the company will add more customers to its existing list of customers and it is highly unlikely that the company will acquire astronomical number of customer over night (wouldn’t the company CEO love that) hence, the customer dimension is both a Slowly changing as well as slowly growing dimension.

$Define
Defines identifiers that control program compilation or supplies replacement text for an identifier.
$IfDef
Tests an identifier to see if it is defined or not defined.
$IfNDef
Tests an identifier to see if it is defined or not defined.
$Include
Inserts source code contained in a separate file and compiles it along with the main program.
$Undefine
Removes an identifier that was set using the $Define statement. If no identifier is set, $Undefine has no effect.
Abs
Returns the absolute (unsigned) value of a number.
ACos
returns the arc-cosine of number in degrees. ACos is the inverse of Cos.
Alpha
Checks if a string is alphabetic. If NLS is enabled, the result of this function is dependent on the current locale setting of the Ctype convention.
Ascii
Converts the values of characters in a string from EBCDIC to ASCII.
ASin
returns the arc-sine of number in degrees. ASin is the inverse of Sin.
ATan
returns the arc-tangent of number in degrees. ATan is the inverse of Tan.
BitAnd
compares two integers bit by bit. For each bit, it returns bit 1 if both bits are 1; otherwise it returns bit 0.
BitNot
inverts the bits in an integer, that is, changes bit 1 to bit 0, and vice versa. If bit.number is specified, that bit is inverted; otherwise all bits are inverted.
BitOr
compares two integers bit by bit. For each bit, it returns bit 1, if either or both bits is 1; otherwise it returns bit 0.
BitReset
resets the specified bit to 0. If it is already 0, it is not changed.
BitSet
sets the specified bit to 1. If it is already 1, it is not changed.
BitTest
tests if the specified bit is set. It returns 1 if the bit is set; 0 if it is not.
BitXOr
compares two integers bit by bit. For each bit, it returns bit 1 if only one of the two bits is 1; otherwise it returns bit 0.
Byte
Lets you build a string byte by byte.
ByteLen
Returns the length of a string in bytes
ByteType
Determines the internal function of a particular byte.
ByteVal
Determines the value of a particular byte in a string.
Call
Calls a subroutine.
Case
Alters the sequence of execution in the program according to the value of an expression.
Cats
Concatenates two strings.
Change
Replaces one or more instances of a substring.
Char
Generates an ASCII character from its numeric code value.
Checksum
Returns a checksum value for a string.
CloseSeq
Closes a file after sequential processing.
Col1
Returns the character position preceding the substring specified in the most recently executed Field function.
Col2
Returns the character position following the substring specified in the most recently executed Field function.
Common
Defines a common storage area for variables.
Compare
Compares two strings. If NLS is enabled, the result of this function depends on the current locale setting of the Collate convention.
Convert
Replaces every instance of specified characters in a string with substitute characters.
Cos
returns the cosine of an angle. number is the number of degrees in the angle. Cos is the inverse of ACos.
CosH
returns the hyperbolic cosine of an angle. number is the number of degrees in the angle.
Count
Counts the number of times a substring occurs in a string.
CRC
Returns a 32-bit cyclic redundancy check value for a string.
CRC32
Returns a 32-bit cyclic redundancy check value for a string.
Date
Returns a date in its internal system format.
DCount
Counts delimited fields in a string.
Deffun
Defines a user-written function.
Dimension
Defines the dimensions of one or more arrays.
Div
Divides one number by another.
DownCase
Converts uppercase letters in a string to lowercase. If NLS is enabled, the result of this function depends on the current locale setting of the Ctype convention.
DQuote
Encloses a string in double quotation marks.
DSAttachJob
Attaches to a job in order to run it in job control sequence. A handle is returned which is used for addressing the job. There can only be one handle open for a particular job at any one time.
DSCheckRoutine
Checks if a BASIC routine is cataloged, either in the VOC as a callable item, or in the global catalog space.
DSDetachJob
This routine is used to give back a JobHandle acqu
DSExecute
Executes a DOS or DataStage Engine command from a before/after subroutine.
DSGetJobInfo
Provides a method of obtaining information about a job, which can be used generally as well as for job control. It can refer to the current job or a controlled job, depending on the value of JobHandle.
DSGetLinkInfo
Provides a method of obtaining information about a link on an active stage, which can be used generally as well as for job control. This routine may reference either a controlled job or the current job, depending on the value of JobHandle.
DSGetLogEntry
This function is used to read the full event details given in EventId.
DSGetLogSummary
Returns a list of short log event details. The details returned are determined by the setting of some filters. (Care should be taken with the setting of the filters, otherwise a large amount of information can be returned.)
DSGetNewestLogId
This function is used to get the ID of the most recent log event in a particular category, or in any category.




DSGetParamInfo
This function provides a method of obtaining information about a parameter, which can be used generally as well as for job control. This routine may reference either a controlled job or the current job, depending on the value of JobHandle.
DSGetProjectInfo
Provides a method of obtaining information about the current project.
DSGetStageInfo
Provides a method of obtaining information about a stage, which can be used generally as well as for job control. It can refer to the current job, or a controlled job, depending on the value of JobHandle.
DSGetStageLinks
Returns a field mark delimited list containing the names of all of the input/output links of the specified stage.
DSLogEvent
This function is used to log an event message to a job other than the current one. (Use DSLogInfo, DSLogFatal, or DSLogWarn to log an event to the current job.)
DSLogFatal
Logs a fatal error message in a job's log file and aborts the job.
DSLogInfo
Logs an information message in a job's log file.
DSLogToController
This routine may be used to put an info message in the log file of the job controlling this job, if any. If there isn't one, the call is just ignored.
DSLogWarn
Logs a warning message in a job's log file.
DSMakeJobReport
Generates a string describing the complete status of a valid attached job.
DSMakeMsg
Insert arguments into a message template. Optionally, it will look up a template ID in the standard DataStage messages file, and use any returned message template instead of that given to the routine.
DSPrepareJob
Used to ensure that a compiled job is in the correct state to be run or validated.
DSRunJob
Used to start a job running. Note that this call is asynchronous; the request is passed to the run-time engine, but you are not informed of its progress.
DSSendMail
This
DSSetGenerateOpMetaData
Use this to specify whether the job generates operational meta data or not. This overrides the default setting for the project. In order to generate operational meta data the Process MetaBroker must be installed on your DataStage machine.
DSSetJobLimit
By default a controlled job inherits any row or warning limits from the controlling job. These can, however, be overridden using the DSSetJobLimit function.
DSSetParam
Used to specify job parameter values prior to running a job. Any parameter not set will be defaulted.
DSSetUserStatus
This routine applies only to the current job, and does not take a JobHandle parameter. It can be used by any job in either a JobControl or After routine to set a termination code for interrogation by another job. In fact, the code may be set at any poin
DSStopJob
This routine should only be used after a DSRunJob has been issued. It immediately sends a Stop request to the run-time engine.
DSTransformError
Logs a warning message to a job log file. Called from transforms only.
DSTranslateCode
Converts a job control status or error code into an explanatory text message.
DSWaitForFile
Suspend a job until a named file either exists or does not exist.
DSWaitForJob
This function is only valid if the current job has issued a DSRunJob on the given JobHandle. It returns if that job has started since the last DSRunJobwas issued on it and si
Dtx
Converts a decimal integer to hexadecimal.
Ebcdic
Converts the values of characters in a string from ASCII to EBCDIC format.
Else
Define several blocks of statements and the conditions that determine which block is executed. You can use a single line syntax or multiple lines in a block.
End
Indicates the end of a program, a subroutine, or a block of statements.
Equate
Equates a value to a symbol or a literal string during compilation.
Ereplace
Replaces one or more instances of a substring.
Exchange
Replaces a character in a string.
Exit
Define a program loop.
Exp
Returns the value of "e" raised to the specified power.
Field
Returns delimited substrings in a string.
FieldStore
Modifies character strings by inserting, deleting, or replacing fields separated by specified delimiters.




Fix
Use the FIX function to convert a numeric value to a floatin-point number with a specified precision. FIX lets you control the accuracy of computation by eliminating excess or unreliable data from numeric results.
Fmt
Formats data for output.
FmtDP
In NLS mode, formats data in display positions rather than by character length.
Fold
Folds strings to create substrings.
FoldDP
In NLS mode, folds strings to create substrings using character display positions.
For
Create a For…Next program loop.
GetLocale
In NLS mode, retrieves the current locale setting for a specified category.
GoSub
Transfers program control to an internal subroutine.
GoTo
Transfers program control to the specified statement.
Iconv
Converts a string to an internal storage format.
If
Execute one or more statements conditionally. You can use a single line syntax or multiple lines in a block.
Index
Returns the starting position of a substring.
InMat
Retrieves the dimensions of an array, or determines if a Dim statement failed due to insufficient memory.
Int
Returns the integer portion of a numeric expression.
IsNull
Tests if a variable contains a null value.
Left
Extracts a substring from the start of a string.
Len
Returns the number of characters in a string.
LenDP
In NLS mode, returns the length of a string in display positions.
Ln
Calculates the natural logarithm of the value of an expression, using base "e".
Locate
Use a LOCATE statement to search dynamic.array for expression and to return a value


Loop
Define a program loop.
Mat
Assigns values to the elements of an array.
Match
Compares a string with a format pattern. If NLS is enabled, the result of a match operation depends on the current locale setting of the Ctype and Numeric conventions.
MatchField
Searches a string and returns the part of it that matches a pattern element.
Mod
Returns the remainder after a division operation.
Nap
Pauses a program for the specified number of milliseconds.
Neg
Returns the inverse of a number.
Next
Create a For…Next program loop.
Not
Inverts the logical result of an expression.
Null
Performs no action and generates no object code.
Num
Determines whether a string is numeric. If NLS is enabled, the result of this function depends on the current locale setting of the Numeric convention.
Oconv
Converts an expression to an output format.
On
Transfer program control to an internal subroutine.
OpenSeq
Opens a file for sequential processing.
Pwr
Raises the value of a number to the specified power.
Quote
Encloses a string in double quotation marks.
Randomize
Generates a repeatable sequence of random numbers in a specified range.
ReadSeq
Reads a line of data from a file opened for sequential processing.
Real
Use the REAL function to convert number into a floating-point number without loss of accuracy. If number evaluates to the null value, null is returned.
Repeat
Define a program loop.
Return
Ends a subroutine and returns control to the calling program or statement.
Right
Extracts a substring from the end of a string.
Rnd
Generates a random number.
Seq
Converts an ASCII character to its numeric code value.
SetLocale
In NLS mode, sets a locale for a specified category.
Sin
returns the sine of an angle. number is the number of degrees in the angle. Sin is the inverse of ASin.
SinH
returns the hyperbolic sine of an angle. number is the number of degrees in the angle.
Sleep
Pauses a program for the specified number of seconds.
Soundex
Generates codes that can be used to compare character strings based on how they sound.
Space
Returns a string containing the specified number of blank spaces.
Sqrt
Returns the square root of a number.
SQuote
Encloses a string in single quotation marks.
Status
Returns a code that provides information about how a preceding function was executed.
Str
Composes a string by repeating the input string the specified number of times.
Substring
Returns a substring of a string.
Substrings
Returns a substring of a string.
Tan
returns the hyperbolic tangent of an angle. number is the number of degrees in the angle.
TanH
returns the hyperbolic tangent of an angle. number is the number of degrees in the angle.
Then
Execute one or more statements conditionally. You can use a single line syntax or multiple lines in a block.
Time
Returns the internal system time.
TimeDate
Returns the system time and date. If NLS is enabled, the result of this function depends on the current locale setting of the Time convention.
Trim
Trims unwanted characters from a string.

TrimB
Trims trailing spaces from a string.
TrimF
Trims leading spaces and tabs from a string.
UniChar
In NLS mode, generates a single character in Unicode format.
UniSeq
In NLS mode, converts a Unicode character to its equivalent decimal value.
Until
Define a program loop.
UpCase
Changes lowercase letters in a string to uppercase. If NLS is enabled, the result of this function depends on the current locale setting of the Ctype convention.
WEOFSeq
Writes an end-of-file mark in an open sequential file.
While
Define a program loop.
WriteSeq
Writes a new line to a file that is open for sequential processing and advances a pointer to the next position in the file.
WriteSeqF
Writes a new line to a file that is open for sequential processing, advances a pointer to the next position in the file, and saves the file to disk.
Xtd
Converts a hexadecimal string to decimal.

-------------------------------------------------------------------------------------