Posts

InfoCepts Successfully Upgrades Leading Energy Utility’s Informatica PowerCenter with Zero Downtime

We helped a leading energy company upgrade from Informatica PowerCenter 8.6 to 9.5.1, providing a better system for integrating, virtualizing, validating, and monitoring data, as well as managing metadata. Using the InfoCepts Informatica upgrade framework, the client experienced a smooth upgrade with zero downtime and full access to a robust features suite.

Download

Thinking Outside the Box: A Dynamic Framework Solution

Honey LahotiMarch 29, 2016

Simplify Source Data Analysis with a Dynamic Framework Solution for Better Performance with Less Expertise Required
Data management is challenging on many levels—from cleaning and formatting the data to handling large volumes of data in a limited time. Data quality issues often inhibit successful data analysis, making it difficult to maximize the value of the source data to derive solutions or actionable insights.

Recently, a retail client of ours was struggling with this very issue—how to generate high quality data for analysis. They were having trouble with issues such as bucketization of rules—a common part of the data quality analysis process. Upon in-depth analysis, we found the following causes:

  • Lack of proper ETL implementation
  • Data from different sources with prevalent data issues
  • Data quality tool performance issues

While a common approach to these types of data issues is to use a data quality tool, the tools available for this client’s data environment had performance issues due to the voluminous amount of data they needed to analyze. In addition, our client was reluctant to use a data quality tool due to their own lack of expertise. Finally, the data quality tools available on the market did not provide the flexibility the client required to accommodate different business rules.

Thinking Outside the Box: A Dynamic Framework Solution
In order to overcome the above limitations while optimizing the data quality analysis process, InfoCepts’ team carefully studied the client’s data environment, including the entire structure of the data. To resolve the issue, we developed a generalized, dynamic, process-oriented framework.

This solution included the ability to bucketize rules so they can be generalized and grouped together based on different quality attributes such as:

  • Referential Integrity
  • Nullability
  • ST Mapping
  • Duplicate records
  • Uniqueness
  • Aggregation
  • Reconciliation
  • Pattern analysis
  • Range

How It Works
To develop a dynamic framework solution, we started by creating a metadata in the database for the above bucketized set of rules. This structure allows all the dynamic information pertaining to queries—such as table name, column, or other information for validation—to be captured and fed into the metadata table for each rule. Additionally, whenever a new rule is introduced, the metadata becomes a repository and new rules are added as per the defined standards.

Based on these data quality attributes and the different parameters required to process the query on a daily basis, we then created a framework where the parameters could be approved by the client through a simple Excel file. Next, a set of stored procedures were created for each data quality attribute and a generic query was written. The query fetches the parameters from the metadata to create the query and user input file for parameters. This process helps modify the WHERE clause of the queries for dynamic execution.

The overall framework provides a completely dynamic solution. Allowing the client to validate over 155 rules for approximately 20 different combinations—meaning that around 3,100 queries (155×20) can be executed within an hour or less. Also, the results of any count mismatch are added to a table on daily basis and emailed to the concerned audience automatically through script—making it simple to find and fix errors.

Dynamic Framework Solition_Example

Benefits of a Dynamic Framework
A dynamic framework offers many benefits. Not only is its creation a one-time effort, but implementation is faster than using a data quality tool. Additionally, a dynamic framework provides considerable flexibility in terms of modularity of the framework.

Other benefits of a dynamic framework solution include:

  • Requires less skilled resources to run the solution, saving costs
  • Eliminates dependency on a data quality tool
  • Simple to understand and maintain by ETL teams
  • Resolves performance issues associated with data quality tools
  • Customizable and accommodating of new rules
  • Maintains rule repository for any future reference

Due to all of the above benefits and our own experience, we believe that a dynamic framework can not only achieve the results necessary, but that it is a better solution than investing in data quality tools to analyze incoming data.

If you’d like more information on how a dynamic framework might help you achieve better results with your source data analysis, please send an email to sales@infocepts.com.

Convert Date and Time Data with the Informatica Mapplet

Rohit PatrikarDecember 24, 2015

The BI industry deals with enormous volumes of data shared across many disparate locations from cloud-based to physical locations. For the purposes of this article, we will use the retail industry as an example where organizations have distribution centers and stores around the world.

 

Data from distribution centers often originates from diverse time zones and ends up at one central warehouse for further analysis. As the data loads, local time from the original source to the warehouse frequently does not account for time zone differences. This presents a challenge when the data must be analyzed based on local time of origination because BI reports can display the wrong results. These reports, stamped with the time at the warehouse’s location, can reflect a time lag and influence decisions without using accurate data and potentially cause financial losses.

 

With many clients facing these challenges, we discovered a need for the source data time to be converted into the local time zones of the target system. None of the existing ETL tools were smart enough to handle the time zone difference and daylight savings adjustments in certain geographical areas.

 

Correctly converting source time to destination time with special handling for daylight savings adjustments is complex and tedious, so careful consideration was needed when developing a solution that could do both. Generally, many codes ignored time zone adjustments or standardized the source time data as it was presented.

 

The process of gathering this data and standardizing it is time-consuming and frequently inaccurate. This is also a non-scalable solution because each time a new geographic location is added, the code has to be manually altered to provide results based on the additional time zone.

 

InfoCepts Solution:
Our solution to this time zone conversion issue was to create a script and logic that quickly and automatically converts source time zone into destination geographical time zone during data loading in order to effectively populate proper BI reporting metrics. InfoCepts’ solution in Informatica converts date and time data of a given time zone to a target time zone and includes accommodations for daylight savings.

 

The Informatica mapplet is one of the components in the PowerCenter Designer, which is generally used for designing reusable code. It can be embedded into any mapping code easily and will run during respective sessions or task-runs via the Informatica Workflow to provide accurate results. It can also be used in Informatica Developer by importing the XML.

 

Usage:
This mapplet enables location-based analysis, where the results need to be in the local time zone rather than the originating source data’s time zone. This is especially useful for supply chain projects generating data in different time zones for given dates.

 

Benefits of Informatica’s Time Zone Conversion Mapplet

  • Easy to embed in any PowerCenter mapping to fulfill business requirements
  • Eliminates the requirements of creating complicated Excel spreadsheets with formulas and transformations to convert to the desired time from different time zones for complete analysis and BI metrics reporting
  • Allows for minimal manual intervention by effectively using automated code, saving time and money
  • Can handle dates from YEAR – 1901 to YEAR – 2038
  • Highly reusable and can be used with any source or target databases supported by Informatica or flat files
  • Adds more meaning to data thus helping the user to make more accurate decisions
  • Perfect for supply chain projects, where distribution centers and stores are in different time zones and all the time lines (delivery time, order time, and more) need to be converted into local time zones

With the time zone mapplet, it becomes easier to generate and manage data with real-time results across different time zones with greater accuracy. Want to learn more about how the time zone mapplet can ensure accurate and timely reporting of all your data? Find specs and details in the Informatica Marketplace.

Automation: Key to a Faster Clean Up of Orphan and Unused Objects in Informatica

Yogesh AgrawalNovember 23, 2015

Orphan and unused objects are a major headache for most developers. Despite best efforts to keep the data environment clean, no company is immune to orphan and unused objects.

What puts companies at risk? During the development phase, it’s not uncommon for a developer to forget to delete child objects that aren’t used in parent files, or to delete a mapping and be left with orphan sources and targets unassociated with any other mapping. Other times, a developer might intentionally choose not to delete objects, thinking they may be needed in the future. But over time, as the objects remain unused, no one remembers why they exist.

Getting rid of your repository’s orphan objects is a tedious, time-consuming process — yet integral to the success of your operation. Unfortunately, many developers overlook or ignore the issue for as long as possible, despite the best practice of a quarterly clean-up of orphan objects in an active development environment. When this happens, the metadata eventually becomes too large and cluttered, slowing down the repository’s performance and adding to your overall development time. The accumulation of unused and orphan objects also makes it difficult to understand how the database objects work together, leading some developers to spend time fixing and maintaining objects that are not even used.

The Standard Approach to Orphan Clean Up
Even when it’s clear that unused and orphan objects are interfering with your repository’s performance, orphan clean up isn’t a task many of us want to undertake. While Informatica offers some tools to help, clean-up is still a manual, lengthy process with multiple steps. These include:

  • Identifying orphans and unused objects – Developers must go to the repository and identify all orphan and unused objects. Depending on how many exist and the size of the metadata, this process can be quite cumbersome.
  • Creating a backup of all orphans and unused objects – Once the orphans and unused objects are identified, a backup of each object must be created.
  • Deleting orphans and unused objects – Deleting the orphans is a step-by-step, manual process. A script can be created for the deletion process to automate this step somewhat, but writing a script requires additional time.
  • Verifying that unused objects don’t have dependencies – Once the clean-up process is complete, the database should be checked to ensure that all objects deleted were not associated and that the database is functioning correctly.

A Better Way: Automation
To help make the cleanup of orphans and unused objects in Informatica less painful and time-consuming, our team at InfoCepts developed a way to automate the entire clean-up process in any Windows-based Informatica environment. To start the process, you first need to set up the parameters for the clean-up. Typically, this includes:

  • Name of the domain
  • Metadata repository details
  • Credentials to connect
  • Control file
  • Specifying the location to store backup files

Once these parameters are set, our solution is applied to the environment, enabling the remaining steps to be automated, including:

  • Identifying orphans
  • Generating a list of orphan objects
  • Creating a backup XML file
  • Deleting orphans
  • Cross verification of unused objects to ensure the objects don’t have dependencies

With our automated solution in place, developers can complete the clean-up process at an 80 percent faster rate than they can with the standard manual process. In addition, the simplicity of the solution increases the chance of adopting and sticking with the best practice of a quarterly clean up.

Eliminating unused and orphan objects from the repository can have a significant impact on your repository’s overall performance. It can also reduce maintenance hassles, make your database easier to navigate, reduce the need to fix unused objects, and allow your development team to work more efficiently.

Want to learn more about how InfoCepts can automate the clean-up of orphan and unused objects in your metadata? Find specs and details in the Informatica Marketplace.

End to End BI Application Management, Support and Optimization

Our customer, a leading specialty retailer had built their BI applications on the MicroStrategy, Informatica and Netezza platforms. Leveraging our global delivery model and technology specific tools and methodology, we helped our customer increase efficiency for numerous processes that included automating upgrades and testing routines for MicroStrategy, and reducing the load time and downtime by 70% and 30% respectively for Informatica. We also reduced MicroStrategy licensing costs by automating the license management process. We provided 24×7 proactive monitoring and support that has reduced the overall incident tickets for the customer’s BI system by 20%.

Download