Teradata RDBMS for IBM Infosphere Datastage

Teradata RDBMS
---------------------------------------------------
Runs on SMP and MPP systems
High Volume database
Parallel database procesessing
Utilities for reading and writing to the RDBMS
-Load utilities
# FastLoad
# MultiLoad
# TPump
-Read utility
# FastExport
---------------------------------------------------
Teradata Architecture:

# Disks contain database tables
- Rows of tables are distributed across disks
# AMPs( Access Module Processors) are VPROCs ( Virtual Processors )that manage the
database.
- Locking
- Joining
- Read and Write functions
# PEs (Parsing Engines)are VPROCs that parse SQL sent to the Teradata Server by
clients (e.g.,DataStage)
- Check syntax
- Create optimized Parse Tree
- Generate task Steps
- Dispatch Steps to AMPs
# VPROCs run under PDE ( Parallel Database Extensions)



The communication layer consists of BNET communications software supporting TCP/IP communication between the AMP processors. It supports broadcast, multicast, and point-to-poin communcation.

An AMP, or unit of parallelism, "owns" a portion of the database. Multiple AMPs reside on a single 2-CPU SMP. Therefore, the Teradata Database doesn't rely on the hardware platform for parallelism, scalability, reliability, or availabl.
These capabilities are inherent in the database architecture and are independent of the operating system and hardware configuration.

AMPs are one of two types of virtual processors ( VPROCs). The second type of VPROC is Parsing Engines (PE), which break up a request or query into manageable pieces and distribute the work to the AMP VPROCs for processing. Multiple PEs can also exist on a single node. It's important to note that each PE has access to each AMP, which allows for complete parallel processing of each request.

PEs are similar in concept to a database optimizer.
----------------------------------------------------------
Client Access to Teradata (ex: Datastage )

# Utilities access
- Teradata has a set number of slots (15 by default) in which the
Teradata utilites can run concurrently.
* A Datastage job that invokes a utility when no slot is available will abort.
- the MultiLoad stage supports tenacity, which queues the process for retry when slots aren't available.
- Teradata utilites for loading tables
Vary in whether they take up a utility slot
Vary in the number of target tables they can load in a single run
Vary in their update and load capabilities
Vary in whether the table in locked for use by other users
Vary in their performance
- Teradata utility for reading from tables
Only one, so no choice here
# Non-utilities access
- ODBC: Access through ODBC driver
- CLI ( Call Level Interface )
Programmed access.
-------------------------------------------------------
Teradata Utilities
----------------------------------------------------
# Utilities for loading tables
- FastLoad ( Takes a utility slot )
High performance loading to a single, empty table
No updates or upserts
Locks table
Supports checkpoint restart
- MultiLoad ( Takes a utility slot )
High performance inserts, updates and deletes
Can update multiple tables per run
Table need not be empty
Locks table
Supports checkpoint restart
- TPump ( Does not table a utility slot; runs in the background )
Supports inserts, updates, upserts, and deletes
Can update multiple tables per run
"Trickle feed"; Like a pump the data is fed to the target in a slow, steady stream that it can handle
- Flow of data can be throttled to a specifed number of updates per minute
Supports checkpoint restart
# Utilities for reading from tables
- FastExport ( Takes a utility slot )
Extracts large amounts of distributed data.
-----------------------------------------------------------------
Teradata Stage:
-----------------------------
# Four Teradata stages
- Teradata Enterprise
- Teradata MultiLoad
- Teradata API
- Teradata Load

# All support one input link and/or one output link
- Output link reads from database tables or load files
- Input writes to database tables.

# How they differ
- Teradata utilities used / not used
- Performance
- Whether they take up a utility slot
- Update (upsert) capabilities
- Target one table or more than one per run
- Whether table is locked.

Performance Tuning Datastage Jobs

Performance Tuning - Basics
BasicsParallelism Parallelism in DataStage Jobs should be optimized rather than maximized. The degree of parallelism of a DataStage Job is determined by the number of nodes that is defined in the Configuration File, for example, four-node, eight –node etc. A configuration file with a larger number of nodes will generate a larger number of processes and will in turn add to the processing overheads as compared to a configuration file with a smaller number of nodes. Therefore, while choosing the configuration file one must weigh the benefits of increased parallelism against the losses in processing efficiency (increased processing overheads and slow start up time).Ideally , if the amount of data to be processed is small , configuration files with less number of nodes should be used while if data volume is more , configuration files with larger number of nodes should be used.

Partioning :
Proper partitioning of data is another aspect of DataStage Job design, which significantly improves overall job performance. Partitioning should be set in such a way so as to have balanced data flow i.e. nearly equal partitioning of data should occur and data skew should be minimized.

Memory :
In DataStage Jobs where high volume of data is processed, virtual memory settings for the job should be optimised. Jobs often abort in cases where a single lookup has multiple reference links. This happens due to low temp memory space. In such jobs $APT_BUFFER_MAXIMUM_MEMORY, $APT_MONITOR_SIZE and $APT_MONITOR_TIME should be set to sufficiently large values.

Performance Analysis of Various stages in DataStag

Sequential File Stage -
The sequential file Stage is a file Stage. It is the most common I/O Stage used in a DataStage Job. It is used to read data from or write data to one or more flat Files. It can have only one input link or one Output link .It can also have one reject link. While handling huge volumes of data, this Stage can itself become one of the major bottlenecks as reading and writing from this Stage is slow.Sequential files should be used in following conditionsWhen we are reading a flat file (fixed width or delimited) from UNIX environment which is FTPed from some external systemsWhen some UNIX operations has to be done on the file Don’t use sequential file for intermediate storage between jobs. It causes performance overhead, as it needs to do data conversion before writing and reading from a UNIX file.In order to have faster reading from the Stage the number of readers per node can be increased (default value is one).

Data Set Stage :
The Data Set is a file Stage, which allows reading data from or writing data to a dataset. This Stage can have a single input link or single Output link. It does not support a reject link. It can be configured to operate in sequential mode or parallel mode. DataStage parallel extender jobs use Dataset to store data being operated on in a persistent form.Datasets are operating system files which by convention has the suffix .dsDatasets are much faster compared to sequential files.Data is spread across multiple nodes and is referred by a control file.Datasets are not UNIX files and no UNIX operation can be performed on them.Usage of Dataset results in a good performance in a set of linked jobs.They help in achieving end-to-end parallelism by writing data in partitioned form and maintaining the sort order.

Lookup Stage –
A Look up Stage is an Active Stage. It is used to perform a lookup on any parallel job Stage that can output data. The lookup Stage can have a reference link, single input link, single output link and single reject link.Look up Stage is faster when the data volume is less.It can have multiple reference links (if it is a sparse lookup it can have only one reference link)The optional reject link carries source records that do not have a corresponding input lookup tables.Lookup Stage and type of lookup should be chosen depending on the functionality and volume of data.Sparse lookup type should be chosen only if primary input data volume is small.If the reference data volume is more, usage of Lookup Stage should be avoided as all reference data is pulled in to local memory

Join Stage :
Join Stage performs a join operation on two or more datasets input to the join Stage and produces one output dataset. It can have multiple input links and one Output link.There can be 3 types of join operations Inner Join, Left/Right outer Join, Full outer join. Join should be used when the data volume is high. It is a good alternative to the lookup stage and should be used when handling huge volumes of data.Join uses the paging method for the data matching.

Merge Stage :
The Merge Stage is an active Stage. It can have multiple input links, a single output link, and it supports as many reject links as input links. The Merge Stage takes sorted input. It combines a sorted master data set with one or more sorted update data sets. The columns from the records in the master and update data sets are merged so that the output record contains all the columns from the master record plus any additional columns from each update record. A master record and an update record are merged only if both of them have the same values for the merge key column(s) that you specify. Merge key columns are one or more columns that exist in both the master and update records. Merge keys can be more than one column. For a Merge Stage to work properly master dataset and update dataset should contain unique records. Merge Stage is generally used to combine datasets or files.

Sort Stage :
The Sort Stage is an active Stage. The Sort Stage is used to sort input dataset either in Ascending or Descending order. The Sort Stage offers a variety of options of retaining first or last records when removing duplicate records, Stable sorting, can specify the algorithm used for sorting to improve performance, etc. Even though data can be sorted on a link, Sort Stage is used when the data to be sorted is huge.When we sort data on link ( sort / unique option) once the data size is beyond the fixed memory limit , I/O to disk takes place, which incurs an overhead. Therefore, if the volume of data is large explicit sort stage should be used instead of sort on link.Sort Stage gives an option on increasing the buffer memory used for sorting this would mean lower I/O and better performance.

Transformer Stage :
The Transformer Stage is an active Stage, which can have a single input link and multiple output links. It is a very robust Stage with lot of inbuilt functionality. Transformer Stage always generates C-code, which is then compiled to a parallel component. So the overheads for using a transformer Stage are high. Therefore, in any job, it is imperative that the use of a transformer is kept to a minimum and instead other Stages are used, such as:Copy Stage can be used for mapping input links with multiple output links without any transformations. Filter Stage can be used for filtering out data based on certain criteria. Switch Stage can be used to map single input link with multiple output links based on the value of a selector field. It is also advisable to reduce the number of transformers in a Job by combining the logic into a single transformer rather than having multiple transformers .

Funnel Stage –
Funnel Stage is used to combine multiple inputs into a single output stream. But presence of a Funnel Stage reduces the performance of a job. It would increase the time taken by job by 30% (observations). When a Funnel Stage is to be used in a large job it is better to isolate itself to one job. Write the output to Datasets and funnel them in new job. Funnel Stage should be run in “continuous” mode, without hindrance.

Overall Job Design :
While designing DataStage Jobs care should be taken that a single job is not overloaded with Stages. Each extra Stage put in a Job corresponds to lesser number of resources available for every Stage, which directly affects the Jobs Performance. If possible, big jobs having large number of Stages should be logically split into smaller units. Also if a particular Stage has been identified to be taking lot of time in a job, like a transformer Stage having complex functionality with a lot of Stage variables and transformations, then the design of jobs could be done in such a way that this Stage is put in a separate job all together (more resources for the transformer Stage!!!). Also while designing jobs, care must be taken that unnecessary column propagation is not done. Columns, which are not needed in the job flow, should not be propagated from one Stage to another and from one job to the next. As far as possible, RCP (Runtime Column Propagation) should be disabled in the jobs. Sorting in a job should be taken care try to minimise number sorts in a job. Design a job in such a way as to combine operations around same sort keys, if possible maintain same hash keys. Most often neglected option is “don’t sort if previously sorted” in sort Stage, set this option to “true”. This improves the Sort Stage performance a great deal. In Transformer Stage “Preserve Sort Order” can be used to maintain sort order of the data and reduce sorting in the job.In a transformer minimum of Stage variables should be used. More the no of Stage variable lower is the performance. An overloaded transformer can choke the data flow and lead to bad performance or even failure of job at some point. In order to minimise the load on transformer we can Avoid some unnecessary function calls. For example to convert a varchar field with date value can be type cast into Date type by simple formatting the input value. We need not use StringToDate function, which is used to convert a String to Date type.Implicit conversion of data types.
Reduce the number of Stage variables used. It was observed in our previous project by removing 5 Stage variables and 6 function calls, runtime for the job was reduced from 2 hours to 1 hour 10 min (approximately) with 100 million records input.Try to balance load on transformers by sharing the transformations across existing transformers. This would ensure smooth flow of data.If you require type casting, renaming of columns or addition of new columns, use Copy or Modify Stages to achieve thisWhenever you have to use Lookups on large tables, look at the options such as unloading the lookup tables to datasets and using, user defined join SQL to reduce the look up volume with the help of temp tables, etc.The Copy stage should be used instead of a Transformer for simple operations including:o Job Design placeholder between stages o Renaming Columnso Dropping Columnso Implicit (default) Type Conversions The “upsert” works well if the data is sorted on the primary key column of the table which is being loaded. Or Determine , if the record already exists or not to have “Insert” and “Update” separately.It is sometimes possible to re-arrange the order of business logic within a job flow to leverage the same sort order, partitioning, and groupings. Don’t read from a Sequential File using SAME partitioning. Unless more than one source file is specified, this scenario will read the entire file into a single partition, making the entire downstream flow run sequentially

The Dangers of Denormalizing in Dimensional Modeling

When designing data warehouse models, the designer is freed from many of the constraints faced in the past when using the normalization rules of entity relationship modeling. In particular, we are no longer forced to avoid data replication. This article discusses some of the unexpected side effects of denormalization, along with alternative design solutions.

We begin with a brief recent history of modeling from the entity relationship perspective, through the early data warehouse “models” and to the more structured solutions devised by Bill Inmon and Ralph Kimball.

Since denormalizing for dimensional models has side effects, we review those side effects and present multiple alternatives for intelligently creating denormalized designs. These alternatives include continuing to replicate, designing normalized dimension tables, or some combination of separating the attributes into separate dimension tables and combining these tables.

Since our designs are typically very flexible, no one solution is the “right” one, but we discuss the advantages and disadvantages of each.

Among the benefits of data warehouse dimensional models are their inherent simplicity. Using the same tools as traditional entity relationship modeling, the business user can now intuitively understand the structure of the model and find with relative ease database attributes required for reporting and analysis. The dimensional model or star schema also provides the designer with a simple mechanism for training and validation with even the most non-technical staff, since the model is based on business needs and subject areas. Because the model is based on a series of two-table joins, the business can understand both the model and provide feedback early in the design process if there are gaps or inconsistencies. Technically, the dimensional model is still based largely on relational database technology, so the advantages of using the major RDBMS packages and the skill sets required to maintain the physical models are transferred into the new realm. With all the advantages of dimensional modeling, it would appear that there is no downside. However, one side effect as dimensional models become more commonplace is the run-away replication of some attributes. While this appears benign during the design phase, in fact it could cause significant maintenance and reporting concerns by complicating the model. As we move into a more commonly accepted version of denormalizing data models we must be careful to add structure to the model or face a new round of challenges in maintaining them.

Traditional entity relationship modeling followed the tenets of normalization introduced by Dr. E.F. Codd in the 1970s and widely adopted since then. E-R modeling breaks entities (or tables in the physical database) into small sets of distinct attributes (or columns), joining them on unique identifiers (or keys). The final model includes a number of entities, which theoretically have no repeating or redundant values. The biggest value in such a model is that it made the most efficient use of a scarce and expensive resource (disks) and the best technique for inserting, updating, or retrieving data in large online applications such as banking, retail or Web-based transactions. Normalization also brought structure to the earlier data models, which were largely based on a particular application. Figure 1 shows a purchase order model designed using the standard normalization techniques.

Figure 1.

The disadvantage of the ER modeling, however, is that it did not lend itself to querying and reporting. Having multiple table joins meant that reports or ad hoc queries for a particular subject area most likely required joins across several tables. In general, adding successive table joins translates into additional disk reads. Since disk reads are 100 times slower than in-memory reads, a normalized model for millions or even hundreds of thousands of records will slow the response and report run time down by several minutes or hours. Also contributing to the appearance of non-normalized models were the drop in disk price. It was no longer imperative that the model and underlying data be fully normalized since the cost of hardware, particularly among high-end PCs, dropped significantly beginning with the advent of more powerful PCs in the early 1990s. This opened the door to new modeling techniques, particularly those geared directly to reporting. Early versions of reporting, or executive information systems applications tended to throw out any techniques. Since the goal was to speed reporting, early versions of data warehouse models were simply a few large tables with all of the reporting attributes replicated in a single table. If the same attribute was required in another report it was simply replicated in a second table. An example of the purchase order subject area is shown in Figure 2. It soon became apparent, however, that not normalizing was cumbersome, disk intensive, and inflexible from a maintenance and data loading perspective. It was also apparent that it was impossible to add all attributes required for all reporting and ad hoc querying into a single table.


Figure 2.

The next generation of scholars took two distinct routes. Dr. Bill Inmon took the approach that the data warehouse consisted of a repository of historical, clean data from multiple source systems. This repository, or information store, could partially be used directly for detailed reporting but could also be a store from which future data models particularly geared to reporting could be identified and modeled. This approach took the application system or normalized model as the source, identified the subject areas important for reporting, designed the subject area report tables along with their important measures and left the standard descriptive attributes modeled as they appeared in the initial normalized model. The results of this technique appear in Figure 3.


Figure 3.

This technique allowed the warehouse design to remain true to its initial source while placing the design emphasis on getting the correct measures for the business. However, it left the final reporting and analysis database design open ended. The result was that many of the early dimensional models retained the initial normalized flavor. This actually allowed for little or no reporting performance improvements since much of the design emphasis remained on the store of data in the normalized model. As a result, many businesses continued to maintain a highly normalized clean data model separate from, but similar to, the initial source application model. This continued to result in performance issues, since reporting continued to be done against a series of table joins.

Dr. Ralph Kimball’s design method allowed for intelligently and selectively denormalizing the data model while still retaining the measurements inherent in Dr. Inmon’s approach. Central to Dr. Kimball’s design was that since the biggest performance bottlenecks of the normalized model are through the additional table joins and associated disk reads, eliminating the additional joins will speed up reporting. Dr. Kimball’s method also brought structure to the large denormalized tables. Since it is impossible to predict all necessary reporting attributes and replicate these in all tables, the approach followed in this method separates the reporting or descriptive attributes into a separate table and joins this table or these tables through no more than one or several two-table joins back to the measurement table. This keeps the disk-read performance bottlenecks to a minimum, while structuring the large tables set attributes into specific entities. In Dr. Kimball’s star schema, there are no longer multiple normalized versions of the descriptive table but simply a single intuitive denormalized version of the tables (Figure 4).


Figure 4.

Dr. Kimball’s approach has been widely adopted and has proven again and again to be a solid design methodology. Since this article is not intended to be a Kimball versus Inmon discussion, the remainder of the article is related specifically to denormalizing using Dr. Kimball’s star schema design.

The danger when using the star schema design technique, however, has been in the widely adopted approach of designers to ignore the rules of normalization. Although the star schema simplifies the initial model and allows for N two-way joins, it is not intended to create free-for-all unstructured models. Too often, designers have created models that simply replicate attributes across multiple dimension tables. The danger in doing so is that it reduces the simplicity of the model and increases the cost and complexity of maintaining these models. For example, as we can see in Figure 5, the item dimension table includes attributes from the people dimension (planner name) as well as from the organization dimension (organization name). While this may be necessary from a reporting perspective, particularly if the item is related specifically to one or more organizations, it complicates the model by adding redundancy back to the model. If the source application system changes and the extract, transform, and load (ETL) process is not fully documented, the complexity of modifying the load for multiple organization codes or multiple employee names is increased. In a large-scale data warehouse implementation we may face this or similar issues across 20 to 40 dimension tables with hundreds of attributes. These large dimension tables are especially critical in the warehouse since they are what is known as the “conformed dimensions.” Conformed dimensions are those dimension tables that contain the biggest and most critical elements and also appear in several different stars or joins within the data warehouse or across several departmental data marts. Not only are we concerned with replicating data across several dimension tables, we should also be aware that the dimension tables affected by this replication are usually the conformed dimensions which are the most critical to the business.

The typical characteristics of a conformed dimension are as follows:

The table is wide with many attributes

One of the benefits in designing large dimension tables is in the ability of the user to query across several attributes which may not have been apparent during the initial design of the report or reports using the dimension table. For this reason, while the design challenge is harnessing repeating attributes, we should not shy away from including a significant number of attributes in the dimension table in order to avoid this.
The table appears across several or all subject areas or stars

Typically, businesses place a great deal of time and commitment into ensuring that items, customers, and organizational hierarchies are reported on in the same way corporatewide. The resulting conformed dimension tables should be the same for the marketing departmental data mart as it will be for the finance departmental data mart and the manufacturing departmental data mart.
The table contains attributes which are critical for reporting and which usually must be reported on in a standard way

The conformed dimensions are usually those with attributes that are stored in a corporatewide agreed-upon definition and include standard corporate hierarchies. Reporting is usually done on one or several attributes in the conformed dimensions, usually rolling up or down on the hierarchies contained in the table. For example, in the item dimension, the company usually reports on the individual orders by item number, while summarizing on total sales by item groups or families.

All of these contribute to the complexity of the issue. This also adds complexity to the front-end reporting tool, whether by increasing the number of common elements into a front-end universe design, or by requiring the report developer to understand all the common attributes in the model and where best to derive them from for reporting purposes. In conjunction with this, if the ETL process correctly changes an attribute in one dimension table while leaving another common attribute unchanged in a second dimension table, it may be possible for two report developers to write conflicting reports.

Although the flexibility of dimensional modeling frees us from the restrictions of traditional ER modeling, in order to gain the full benefit of the simpler model we still need to follow techniques to apply denormalization in a consistent manner. Essentially, we can do one of the following:

Selectively ignore the data replication issue and continue to design large dimensions with repetitive attributes.
Maintain a unique normalized staging area/operational data store for use as a single source.
Maintain normalized or snowflaked dimension tables and join these tables where applicable.
Design models which denormalize attributes into a single denormalized table.
Continue to design with repetitive attributes. Despite the disadvantages stated above, this technique has the benefit of allowing the business user a wealth of reporting options without having to select from multiple dimension tables. In our example, one specific and potentially critical ad hoc query which is answerable without the need for multiple table joins is “What are the item groupings/families by item planner.” Another query may be planner and product counts in order to gauge the business impact to a planner transfer. Also critical in the particular example above is that the item dimension truly exists as a unique item under various organizations. By separating the organization into a separate dimension table, the ad hoc query user may understate the number of distinct item numbers since the combined item number/organization ID are not considered. This is a good argument in favor of using this method. If both the ETL process and the front-end meta data layer are well documented, the inconsistencies involved in replicating attributes should also be minimal. However, this design method should be used selectively.

Figure 5.

Maintain a unique normalized staging area/operational data store as single source. This is actually similar to Dr. Inmon’s approach. This schema would continue to be normalized but may be slightly different than the initial source system model(s), both because the staging area would typically contain multiple source systems and because the “fact” table measures would still be rolled into specific source staging tables.

The biggest benefit of this approach is that in the event that a change is made to the underlying source system, the ETL process would simply modify a single attribute from the source to staging area, rather than managing multiple changes across multiple dimension tables in the data warehouse ETL process. As the source for the dimensional data warehouse, the staging area would still retain the new correct value. The dimensional model could conceivably remain as in Figure 5 with the attributes replicated across one or more dimension tables.

The biggest disadvantage of this method is that businesses rarely view the cost of the additional storage and hardware required for a staging area or operational data store to be of enough importance to allow for this to be incorporated into the data warehouse design. Too often, the design of a clean operational data store is left out of the overall design because of this restriction and data continues to be loaded into the dimensional model directly from the source system or systems. A second disadvantage is that this approach does not resolve the business reporting complexity that replicating dimensional attributes brings to the business community.

Figure 6.

Maintain normalized or snowflaked dimension tables. This was a widely used approach in earlier data warehouse models and is still in use today. Theoretically, since an item dimension in the source system is based on several related tables, many early dimensional models retained this association. By retaining the normalized approach for dimension tables in the dimensional model, we solve the repetitive attributes issue. One wrinkle that we add to the model is that we still need to join the item dimension to the personnel dimension and the organization dimension. This appears to be benign until we realize that, like the model in Figure 5, the person dimension and organization dimension are also joined directly to the fact table using different roles. The resulting star schema appears in Figure 6.

While this approach solves our ETL maintenance issue, it actually complicates the reporting and ad hoc view of the data significantly, depending on the reporting tool being used. An additional side effect is that performance could continue to be an issue due to the addition of multiple table joins from the fact table through to the outer most dimension table. Due to the additional reporting complexity and to the performance issues, this approach is not recommended.

Denormalize attributes into a single denormalized dimension table. The design flaw in Figure 5 is due to the fact that the dimension tables have not been denormalized but are simply non-normalized tables. The distinction between the two is subtle but important. When denormalizing, we are actually taking the normalized model and reversing the normalization into a single table. However, time and again, we replicate or denormalize attributes indiscriminately, effectively leading to a non-normalized model. A denormalized version of Figure 5 appears in Figure 7.

This does, however, create an interesting training issue or reporting requirement. Since, in our example, items are related to item planners and are specific to organizations, when we do not replicate these attributes in the item dimension, we risk misreporting the item information. We are left with the following choices:

Creating a relationship between the product dimension and other dimensions through a factless fact table.

Or
Replicating the planner and organization information by joining the organization and employee dimensions to each applicable fact table.
Creating a relationship between the product dimension and other dimensions through a factless fact table. This option appears in Figure 8. To satisfy the reporting questions such as who is the current planner for the item or which organizations do item xyz belong to, we create the association through a second factless fact table. Doing so allows us to maintain the denormalized model while allowing the business to report on dimensional relationships. Conceptually, this also allows us theoretically to capture these relationship changes over time, effectively allowing the business to track costly trends in planners or organization changes.

Figure 7.

The biggest drawback of this design is that it does require the business to understand a separate relationship and to know how to use it. It also does not solve our intrinsic problem which was to report on the combined item, organization, and planner relative to a subject area. We discuss this below.

Replicating the planner and organization information by joining dimensions to each applicable fact table. The design in Figure 9 allows us to report on all of the significant attributes in the three dimensions relative to this subject area. In this design, we include the organization and planner as additional foreign key joins to the existing fact table. This has the advantage of ensuring that these relationships are clearly enough defined for the business community to use them appropriately. The biggest disadvantage to this method is the additional foreign key joins but due to the fact that the foreign keys are numeric and are typically indexed, the performance impact should be negligible.

Figure 8.

Therefore, there are many options available when designing dimensional models. Most designs have benefits and hidden inconsistencies and it is rare that a single solution will work for every design. Possibly the most important component in the final design is to understand the drawbacks and advantages of each option described above and the long-term results of making a particular design decision over the long-term life of the warehouse.

Figure 9.

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.

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