Hi All,. This document is for the reference of Implementing SCD Type 2 using Dynamic Lookup Cache. Thanks. SCD Type-2 using Dynamic. Creating+a+SCD+Type+2+mapping+using+the+Informatica+PowerCenter+ Mapping+Wizard. Uploaded by SCD Type 2 in Informatica - Download as PDF File .pdf), Text File .txt) or read Hyperion Planning Implementation Boot Camp - Exercise.

Scd Type 2 Implementation In Informatica Ebook Download

Language:English, Portuguese, Japanese
Genre:Fiction & Literature
Published (Last):07.08.2016
ePub File Size:19.80 MB
PDF File Size:12.41 MB
Distribution:Free* [*Registration needed]
Uploaded by: ONIE

It is easy to implement but does not maintain any history of prior the oracle emp table source data implemented on SCD type-1, how to Figure 2: Target Table an Target Designer. IV. EXPRESSION TRANSFORMATION IN INFORMATICA . U.K. Available at: how to implement the SCD. Type 2 Effective Date in informatica. As in case of any SCD Type 2 implementation[1], here we need to first find out the set of . Available at: [3]. T. Jun, C. Kai, Feng . SCD Type-1 Implementation in Informatica using dynamic Lookup The intent of Create two groups in router transformation one for INSERT and another one for.

A fact table contains numerical data and foreign keys from related dimensional tables. An example of the fact table can be seen from Figure 2 shown above. Question 9 What are the different types of dimensions you have come across? Explain each of them in detail with an example?

Ans There are typically five types of dimensions. For example, if subscriber dimension is connected to two fact tables — billing and claim then the subscriber dimension would be treated as conformed dimension. Generally, these are the properties like flags or indicators. So, we combine all such attributes and put in a single dimension table called as junk dimension having unique junk IDs with a possible combination of all the indicator values.

So, such a dimension will be called as Role playing dimension. The primary key of Date dimension will be associated with multiple foreign keys in the fact table.

These are the dimensions where attribute values vary with time.

Below are the varies types of SCDs Type These are the dimensions where attribute value remains steady with time. Type These are the dimensions where previous value of the attribute is replaced by the current value. No history is maintained in Type-1 dimension. Type These are the dimensions where unlimited history is preserved. There will be some column s that will identify the current address.

INFORMATICA TUTORIAL: Complete Online Training

Type These are the type of dimensions where limited history is preserved. And we use an additional column to maintain the history. So, instead of having multiple rows, we will be having just one row showing current as well as the previous address of the subscriber.

Type In this type of dimension, the historical data is preserved in a separate table. The main dimension table holds only the current data. For example, the main dimension table will have only one row per subscriber holding its current address. All other previous addresses of the subscriber will be kept in the separate history table.

This type of dimension is hardly ever used. It does not have its own dimension table. We can also call it as a single attribute dimension table. But, instead of keeping it separately in a dimension table and putting an additional join, we put this attribute in the fact table directly as a key.

Since it does not have its own dimension table, it can never act a foreign key in fact table. Question 10 Give your idea regarding factless fact?

And why do we use it? Ans Factless fact table is a fact table that contains no fact measure in it. It has only the dimension keys in it. At times, certain situations may arise in the business where you need to have factless fact table. For example, suppose you are maintaining an employee attendance record system, you can have a factless fact table having three keys.

Question 12 What do you understand by data mart? Ans Data marts are for the most part intended for a solitary branch of business. They are designed for the individual departments. For example, I used to work for a health insurance provider company which had different departments in it like Finance, Reporting, Sales and so forth. We had a data warehouse that was holding the information pertaining to all these departments and then we have few data marts built on top of this data warehouse.

These DataMart were specific to each department.

Data Warehousing Concept Using ETL Process for SCD Type-2

In simple words, you can say that a DataMart is a subset of a data warehouse. Question 13 What are the different types of measures?

Ans We have three types of measures Non- additive measures Semi-additive measures Additive measures Non-additive measures are the ones on top of which no aggregation function can be applied. Semi-additive measures are the ones on top of which some but not all aggregation functions can be applied.

Example — fee rate or account balance. Additive measures are the ones on top of which all aggregation functions cab be applied. Example- units downloadd. Question 14 What is a Surrogate key? How is it different from a primary key?

Related titles

Ans Surrogate key is a unique identifier or a system generated sequence number key that can act as a primary key. It can be a column or a combination of columns. Unlike a primary key, it is not picked up from the existing application data fields. Question 15 Is this true that all databases should be in 3NF?

Ans It is not mandatory for a database to be in 3NF. However, if your purpose is an easy maintenance of data, less redundancy, and efficient access then you should go with a de-normalized database. Question 16 Have you ever came across the scenario of recursive relationships?

If yes, how did you handle it?

Ans A recursive relationship occurs in the case where an entity is related to itself. Yes, I have come across such scenario. Talking about health care domain, it is a possibility that a health care provider say, a doctor is a patient to any other health care provider.

Because, if the doctor himself falls ill and needs a surgery, he will have to visit some other doctor for getting the surgical treatment. So, in this case, the entity — health care provider is related to itself. Question 17 List out few common mistakes encountered during Data Modelling? Ans Below are the few common mistakes encountered during Data Modelling Building massive data models: Large data models are like to have more design faults.

Try to restrict your data model to not more than tables.

Lack of purpose: If you do not know that what is your business solution is intended for, you might come up with an incorrect data model. So having clarity on the business purpose is very important to come up with a right data model. Inappropriate use of surrogate keys: Surrogate key should not be used unnecessarily. Use surrogate key only when the natural key cannot serve the purpose of a primary key. Question 18 What is the number of child tables that can be created out from a single parent table?

Question 19 Employee health details are hidden from his employer by the health care provider.

Find a copy online

Which level of data hiding is this? Conceptual, physical or external? Ans This is the scenario of an external level of data hiding. The steps involved are: Create the source and dimension tables in the database. Open the mapping designer tool, source analyzer and either create or import the source definition.

Go to the Warehouse designer or Target designer and import the target definition. Go to the mapping designer tab and create new mapping. Drag the source into the mapping. Go to the toolbar, Transformation and then Create.

Select the lookup Transformation, enter a name and click on create. You will get a window as shown in the below image. Select the customer dimension table and click on OK.

Edit the lookup transformation, go to the ports tab and remove unnecessary ports. Enter the below expressions for output ports. The steps involved are: Now create a filter transformation to identify and insert new record in to the dimension table.Ans Metadata is data about data. However, Insofar is considered as the best educational background for this job post. Try to restrict your data model to not more than tables. Question 10 Give your idea regarding factless fact? Coming to the snowflake schema, since it is in normalized form, it will require a number of joins as compared to a star schema, the query will be complex and execution will be slower than star schema.

It has only the dimension keys in it. To Summarize: Practical understanding of Data Modelling concept and how it fits into the assignments done by you is much needed to crack a data modeling interview. Note: Facets is an end to end solution to manage all the information for health care industry. Ans Below are the few common mistakes encountered during Data Modelling Building massive data models: Large data models are like to have more design faults.

DEANE from Laredo
Look over my other posts. I am highly influenced by pentathlon. I do love reading comics immediately .