How to use "SUBSTR" functiion functii on in mapping. Explanation : Returns a portion of a string. SUBSTR counts all characters, including blanks, starting at the beginning of the string. Syntax
SUBSTR( string , start [, length ] )
Example
Substr (IN_PHONE, 1 ,3)
Design a mapping , which generates sequence of numbers nu mbers using setvariable function in exp transformation( without using sequence generator) Mapping Solution
Download
Design a mapping generates sequence of numbers without using sequence generator? : Source : Flatfile Flatfile Target : Relational Database : Oracle Note : usage of setmaxvariable() function and mapping variables ! :
:
XML FILE m_sequence_variablefunction
DWH Design a mapping to move first half of the data to one target and second half of the data dat a to other target? eg., if you 20 records in source - first 10 to one target and other 10 to second target or if your source records have odd number first n/2 +1 in one target and other in second target? Mapping Solution
seco nd half to other target. : first half to one target and second : Source : Flatfile Flatfile Target : Relational Database : Oracle Tip : use stored procedure to count the records
Download
:
XML FILE m_firsthalf_secondhalf
REPOSITORY ADMIN CONSOLE Actions y y y y y y y
y y y y
Create
Local or Global Repository Start Repositories. Back up repository Move the copy of the Repository to a different Server Disable the Repository. Export connection information. Notificy Users :: Notification message can be send to all the users connected to the Repository Propagate Register Repositories Rstore Repository Upgrade Repository
Actions y y y y y y y
y y y y
Create
Local or Global Repository Start Repositories. Back up repository Move the copy of the Repository to a different Server Disable the Repository. Export connection information. Notificy Users :: Notification message can be send to all the users connected to the Repository Propagate Register Repositories Rstore Repository Upgrade Repository
Actions y y y
Create
Reusable tasks , Worklets , Workflows. Schedule Workflows. Configure tasks.
Workflow A workflow is a set of instructions that describes how and when to run tasks related to extracting, transforming, and loading data.
Worklets
A worklet is an object that represents a set of tasks.
When
to create Worklets? Create a worklet when you want to reuse a set of workflow logic in several workflows. Use the Worklet Designer to create and edit worklets. Where
to use Worklets? You can run worklets inside a workflow. The workflow that contains the worklet is called t he parent workflow. You can also nest a worklet in another worklet. WORKFLOW MONITOR You can monitor workflows and tasks in the Workflow Monitor. View details about a workflow or task in Gantt Chart view or Task view.
Actions You can run, stop, abort, and resume workflows from the Workflow Monitor. You can view the log file and Performance Data Slowly Changed Dimension y
It is a Dimension which slowly changes over a time.
Slowly Changed Dimension Mapping SCD Type 1
Type Slowly Changing Dimension
SCD Type 2 /Version Slowly Changing Dimension Data
SCD Type 2 /Flag Current
Slowly Changing Dimension
SCD Type 2 /Date Range
Slowly Changing Dimension
SCD Type 3
Slowly Changing Dimension
Description Inserts new dimensions. Overwrites existing dimensions with changed dimensions. (Shows Current Data) Inserts new and changed dimensions. Creates a version number and increments the primary key to track changes. Inserts new and changed dimensions. Flags the current version and increments the primary key to track changes. Inserts new and changed dimensions. Creates an effective date range to track changes. Inserts new dimensions. Updates changed values in existing dimensions. Optionally uses the load date to track changes.
OLTP On Line Transaction processing Continuously updates data Tables are in normalized form Single record access Holds current data Records are maintained using Primary key feild Delete the table or record Complex data model
OLAP On Line Analytical processing Read Only Data Partially Normalized / Denormalized Tables Multiple records for analysis purpose Holds current and historical data Records are baased on surogate keyfield Cannot
delete the records Simplified data model
DATAMART A scaled - down version of the Data Warehouse that addresses only one subject like Sales Department, HR Department etc.,
DATA WAREHOUSE It is a database management system that facilitates on-line analytical processing by allowing the data to be viewed in different dimensions or perspectives to provide business intelligence. One fact table with multiple dimension More than one fact table and multiple tables. dimension tables. [Sales Department] [HR Department] [Sales Department , HR Department , [Manufacturing Department] Manufacturing Department] Bigger Organization prefer DATA Small Organizations prefer DATAMART WAREHOUSE Ans
DIMENSION TABLE
FACT TABLE
It provides the context /descriptive It provides measurement of an enterprise. information for a fact table measurements. Structure of Dimension - Surrogate key , one or more other fields that compose the natural key (nk) and set of Attributes. Size of Dimension Table is smaller than Fact Table.
Measurement is the amount determined by observation.
. In a schema more number of dimensions are presented than Fact Table. Surrogate Key is used to prevent the primary key (pk) violation(store historical data). Provides entry points to data.
Size of Fact Table is larger than Dimension Table. In a schema less number of Fact Tables observed compared to Dimension Tables.
Values of fields are in numeric and text representation.
Structure of Fact Table - foreign key (fk), Degenerated Dimension and Measurements.
Compose
of Degenerate Dimension fields act as Primary Key. Values of the fields always in numeric or integer form.
DATA MINING VS WEB MINING DATA MINING Data mining involves using techniques to find underlying structure and relationships in large amounts of data. Data mining products tend to fall into five categories: neural networks, knowledge discovery, data visualization, fuzzy query analysis and case-based reasoning.
WEB MINING Web mining involves the analysis of Web server logs of a Web site. The Web server logs contain the entire collection of requests made by a potential or current customer through their browser and responses by the Web server
FACT TABLE VS DIMENSION TABLE FACT TABLE A table in a data warehouse whose entries describe data in a fact table. Dimension tables contain the data from which dimensions are created. A fact table in data ware house is it describes the transaction data. It contains characteristics and key figures. In a Data Model schema less number of fact tables are observed. RDBMS SCHEMA VS DWH SCHEMA RDBMS SCHEMA * Used for OLTP systems * Traditional and old schema * Normalized * Difficult to understand and navigate * Cannot solve extract and complex problems * Poorly modelled
DIMENSION TABLE A dimensional table is a collection of hierarchies and categories along which the user can drill down and drill up. it contains only the textual attributes.
In a Data Model schema more number of dimensional tables are observed.
DWH SCHEMA * Used for OLAP systems * New generation schema * Denormalized * Easy to understand and navigate * Extract and complex problems can be easily solved * Very good model
How to find the number of success , rejected and bad records in the same mapping.
First we seperate this data using Expression transformation.Which is used to flag the ro w for 1 or 0 .The condition as follows .. IIF(NOT IS_DATE(HIREDATE,'DD-MON-YY') OR ISNULL(EMPNO) OR ISNULL(NAME) OR ISNULL(HIREDATE) OR ISNULL(SEX) ,1,0) FLAG =1 is considered as invalid data and FLAG =0 is considered as valid data .This data will be routed into next transformation using router transformation .Here we added two user groups one as FLAG=1 for invalid data and the other as FLAG=0 for valid data. y
y
y
FLAG=1 data is forwarded to the expression transformation .Here we t ake one variable port and trwo ouput ports .One for increament purpose and the other for flag the row ... y
