Incremental Loading for Dimension Table

hould we do incremental loading for dimensions?

In a dimensional model, we may perform incremental loading for dimension tables also. One may argue that this wont be necessary as data volume in dimension tables are not as high as the data volumes in the fact tables, hence we can simply do a full load every time.

I personally do not agree to this argument. This is because during the last few years I have seen tremendous growth in the data in dimension tables and things can get quite heavy especially if we are trying to load SCD type 2 dimensions. Anyway, without much ado, let's delve deep.

Read more »

Try U R Hand's on Admin-Console

• Repository Manager Tasks: 

• Add domain connection information
• Add and connect to a repository
• Work with Power Center domain and repository connections
• Search for repository objects or keywords
• View object dependencies
• Compare repository objects
• Truncate session and workflow log entries
• View user connections
• Release locks
• Exchange metadata with other business intelligence tools

Add a repository to the Navigator, and then configure the domain connection information when we connect to the repository.

1.Adding a Repository to the Navigator :

1. In any of the Power Center Client tools, click Repository > Add.

2. Enter the name of the repository and a valid repository user name.

3. Click OK.

Before we can connect to the repository for the first time, we must configure the Connection information for the domain that the repository belongs to.

2.Configuring a Domain Connection

1. In a Power Center Client tool, select the Repositories node in the Navigator.
2. Click Repository > Configure Domains to open the Configure Domains dialog box.
3. Click the Add button. The Add Domain dialog box appears.
4. Enter the domain name, gateway host name, and gateway port number.
5. Click OK to add the domain connection.

3.Connecting to a Repository

1. Launch a Power Center Client tool.
2. Select the repository in the Navigator and click Repository > Connect, or double-click the repository.
3. Enter a valid repository user name and password.
4. Click Connect.

         Click on more button to add, change or view domain information.

4.Viewing Object Dependencies

Before we change or delete repository objects, we can view dependencies to see the impact on other objects. For example, before you remove a session, we can find out which workflows use the session. We can view dependencies for repository objects in the Repository Manager, Workflow Manager, and Designer tools.

Steps:

1. Connect to the repository.
2. Select the object of use in navigator.
3. Click Analyze and Select the dependency we want to view.

5.Validating Multiple Objects

We can validate multiple objects in the repository without fetching them into the workspace. We can save and optionally check in objects that change from invalid to valid status as a result of the validation. We can validate sessions, mappings, mapplets, workflows, and worklets.

Steps:

1. Select the objects you want to validate.
2. Click Analyze and Select Validate
3. Select validation options from the Validate Objects dialog box
4. Click Validate.
5. Click a link to view the objects in the results group.

6.Comparing Repository Objects

We can compare two repository objects of the same type to identify differences between the objects. For example, we can compare two sessions to check for differences. When we compare two objects, the Repository Manager displays their attributes.

Steps:

1. In the Repository Manager, connect to the repository.
2. In the Navigator, select the object you want to compare.
3. Click Edit > Compare Objects.
4. Click Compare in the dialog box displayed.

7.Truncating Workflow and Session Log Entries

When we configure a session or workflow to archive session logs or workflow logs, the Integration Service saves those logs in local directories. The repository also creates an entry for each saved workflow log and session log. If we move or delete a session log or workflow log from the workflow log directory or session log directory, we can remove the entries from the repository.

Steps:

1. In the Repository Manager, select the workflow in the Navigator window or in the Main window.
2. Choose Edit > Truncate Log. The Truncate Workflow Log dialog box appears.
3. Choose to delete all workflow and session log entries or to delete all workflow and session log entries with an end time before a particular date.
4. If you want to delete all entries older than a certain date, enter the date and time.
5. Click OK.

8.Managing User Connections and Locks

In the Repository Manager, we can view and manage the following items:

Repository object locks: The repository locks repository objects and folders by user. The repository creates different types of locks depending on the task. The Repository Service locks and unlocks all objects in the repository.

User connections: Use the Repository Manager to monitor user connections to the repository. We can end connections when necessary.

Types of locks created:

1. In-use lock: Placed on objects we want to view

2. Write-intent lock: Placed on objects we want to modify.

3. Execute lock: Locks objects we want to run, such as workflows and sessions

Steps:

1. Launch the Repository Manager and connect to the repository.
2. Click Edit > Show User Connections or Show locks
3. The locks or user connections will be displayed in a window.
4. We can do the rest as per our need.

9.Managing Users and Groups

1. In the Repository Manager, connect to a repository.
2. Click Security > Manage Users and Privileges.
3. Click the Groups tab to create Groups. or
4. Click the Users tab to create Users
5. Click the Privileges tab to give permissions to groups and users.
6. Select the options available to add, edit, and remove users and groups.

There are two default repository user groups:

Administrators: This group initially contains two users that are created by default.

           The default users are Administrator and the database user that created the repository. We cannot delete these users from the repository or remove them from the Administrators group.

Public: The Repository Manager does not create any default users in the Public group.

10 Working with Folders

We can create, Edit or delete folder as per our need.

1. In the Repository Manager, connect to a repository.
2. Click Folder > Create.

Enter the following information:

3. Click ok.

Read more »

What is Informatica?

Informatica Power Center is a powerful ETL tool from Informatica Corporation.

Informatica Corporation products are:

• Informatica Power Center
• Informatica on demand
• Informatica B2B Data Exchange
• Informatica Data Quality
• Informatica Data Explorer

Informatica Power Center is a single, unified enterprise data integration platform for accessing, discovering, and integrating data from virtually any business system, in any format, and delivering that data throughout the enterprise at any speed.

Informatica Power Center Editions :

Because every data integration project is different and includes many variables such as data volumes, latency requirements, IT infrastructure, and methodologies—Informatica offers three Power Center Editions and a suite of Power Center Options to meet your project’s and organization’s specific needs.

• Standard Edition
• Real Time Edition
• Advanced Edition

Informatica Power Center Standard Edition:

Power Center Standard Edition is a single, unified enterprise data integration platform for discovering, accessing, and integrating data from virtually any business system, in any format, and delivering that data throughout the enterprise to improve operational efficiency.

Key features include:

• A high-performance data integration server
• A global metadata infrastructure
• Visual tools for development and centralized administration
• Productivity tools to facilitate collaboration among architects, analysts, and developers .

Informatica Power Center Real Time Edition :

Packaged for simplicity and flexibility, Power Center Real Time Edition extends Power Center Standard Edition with additional capabilities for integrating and provisioning transactional or operational data in real-time. Power Center Real Time Edition provides the ideal platform for developing sophisticated data services and delivering timely information as a service, to support all business needs. It provides the perfect real-time data integration complement to service-oriented architectures, application integration approaches, such as enterprise application integration (EAI), enterprise service buses (ESB), and business process management (BPM).

Key features include:

• Change data capture for relational data sources
• Integration with messaging systems
• Built-in support for Web services
• Dynamic partitioning with data smart parallelism
• Process orchestration and human workflow capabilities

Informatica Power Center Real Time Edition :

Power Center Advanced Edition addresses requirements for organizations that are Standardizing data integration at an enterprise level, across a number of projects and  departments. It combines all the capabilities of Power Center Standard Edition and features additional capabilities that are ideal for data governance and Integration Competency Centers.

Key features include:

• Dynamic partitioning with data smart parallelism
• Powerful metadata analysis capabilities
• Web-based data profiling and reporting capabilities

Power Center includes the following components:

• Power Center domain
• Administration Console
• Power Center repository
• Power Center Client
• Repository Service
• Integration Service
• Web Services Hub
• SAP BW Service
• Data Analyzer
• Metadata Manager
• Power Center Repository Reports

Read more »