Part 2 Data Mart Informatica Introduction of Data

Part 2: Data Mart & Informatica Introduction of Data mart and Informatica concepts Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

What is Data Mart? Data Mart is a subset of Data warehouse that is designed for a particular line of business, such as sales, marketing or finance. In a independent data mart, data can derived from an enterprise-wide data warehouse. In a independent data mart, data can be collected directly from sources. Intellipaat Software Solutions Pvt. Ltd.

Dimensions A dimension table consists of the attributes about the facts. Dimensions store the textual descriptions of the business. With out the dimensions, we cannot measure the facts. The different types of dimension tables are explained in detail below. Types of Dimensions 1. Conformed dimension 2. Junk dimension 3. Degenerate dimension 4. Role-playing dimension 5. Rapidly changing dimension 6. Inferred dimension 7. Shrunken dimension 8. Static dimension. Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

Conformed Dimension: A Dimension that is used in multiple locations is called a conformed dimension. A conformed dimension may be used with multiple fact tables in a single database, or across multiple data marts or data warehouses. Ex: Name, Item, Date (these will use in Sales fact also in Purchase fact) Junk Dimension: A junk dimension is a collection of random transactional codes flags and/or text attributes that are unrelated to any particular dimension. The junk dimension is simply a structure that provides a convenient place to store the junk attributes. Ex: Time and Date dimension (we can join both the dimensions in a single table, instead of providing 2 keys into the fact) Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

Degenerated Dimension: A degenerate dimension is when the dimension attribute is stored as part of fact table, and not in a separate dimension table. Ex: Active, Auto generate no. Role-playing Dimension: Dimensions which are often used for multiple purposes within the same database are called role-playing dimensions. Ex: A date dimension can be used for “date of sale", as well as "date of delivery", or "date of hire". Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

Slowly Changing Dimension: Attributes of a dimension that would undergo changes over time. Types of SCD 1. SCD Type 1 – Maintain the updated records (No history will maintain). 2. SCD Type 2 – Maintain the history of records. 3. SCD Type 3 – Maintain the history of records for the particular column. Rapidly Changing Dimension: Dimension which has attributes where values will be getting changed often. Ex: Customer table has a field for rating attribute where the value changes very often. Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

Inferred Dimension: While loading fact records, a dimension record may not yet be ready. One solution is to generate an surrogate key with Null for all the other attributes. Ex: Passing null values into Id column to generate the fact values. Shrunken Dimension: A shrunken dimension is a subset of another dimension. Ex: A source fact table may include a foreign key for Product, but the Target fact table may include a foreign key of Product Category, in this case we can create a small dimension for Product category as primary key. Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

Static Dimension: Static dimensions are not extracted from the original data source, but are created within the context of the data warehouse. Ex: Date, Time, Codes. Facts: A fact table is the one which consists of the measurements, metrics or facts of business process. Ex: Summing up the total amount for the particular product. Types of facts: 1. Additive 2. Semi Additive 3. Non Additive 4. Factless fact Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

Additive: Summed up through all of the dimensions in the fact table. Ex: Sales fact is a good example. Semi Additive: Summed up for some of the dimensions in the fact table, but not the others. Ex: Daily balances fact can be summed up through the date/customer dimension but not through the time dimension. Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

Non Additive: Summed up for any of the dimensions present in the fact table. Ex: Percentages, ratios calculated. Factless fact: A fact table that contains no measures or facts Ex: A fact table which has only product key and date key is a factless fact. There are no measures in this table. But still you can get the number products sold over a period of time. Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved

Informatica Architecture: Designer Workflow Manger Workflow Monitor Repository Manager Create Source Definition Create target Definition Create session for each Mapping Create Workflow View workflow & session status Get Session log Create Edit & Delete folders Create Users, groups, assign permission. Define T/R Rule Execute Workflow Schedule Workflow Design Mapping Integration Services Repository Mapping Source Definition Target Definition T/R Rule Session Workflow Session log Schedule info Intellipaat Software Solutions Pvt. Ltd. E T Web Services Hub External Client L Staging Area Source DB Target DB © Copyright Intellipaat. com All rights reserved

Thank You Email us – support@intellipaat. com Visit us - https: //intellipaat. com Intellipaat Software Solutions Pvt. Ltd. © Copyright Intellipaat. com All rights reserved