Enterprise data warehousing

Cloud vs. On-premises Data Warehouse

2021-06-17T22:47:00.003-07:00

Cloud vs. On-premises Data Warehouse

List of Top Data Warehouse Products in 2021

2021-06-15T23:16:00.004-07:00

List of Top Data Warehouse Products in 2021

Oracle Autonomous Data Warehouse
Treasure Data
ThoughtSpotAmazon Redshift
Amazon Redshift
Apache Hive
SAP Data Warehouse Cloud
Google BigQuery
Oracle Exadata
SAP BW
Oracle Data Warehouse
Cloudera Enterprise Data Hub
SAP BW/4HANA
Vertica
Teradata Vantage
Azure Synapse Analytics (Azure SQL Data Warehouse)
ClicData
Actian Matrix
Panoply
IBM Netezza Performance Server
Cloudera Data Platform

Data Warehousing - Terminologies

2015-12-30T07:22:00.000-08:00

Data Warehousing - Terminologies

In this chapter, we will discuss some of the most commonly used terms in data warehousing.

Metadata

Metadata is simply defined as data about data. The data that are used to represent other data is known as metadata. For example, the index of a book serves as a metadata for the contents in the book. In other words, we can say that metadata is the summarized data that leads us to the detailed data.

In terms of data warehouse, we can define metadata as following:

Metadata is a road-map to data warehouse.
Metadata in data warehouse defines the warehouse objects.
Metadata acts as a directory. This directory helps the decision support system to locate the contents of a data warehouse.

Metadata Repository

Metadata repository is an integral part of a data warehouse system. It contains the following metadata:

Business metadata - It contains the data ownership information, business definition, and changing policies.
Operational metadata - It includes currency of data and data lineage. Currency of data refers to the data being active, archived, or purged. Lineage of data means history of data migrated and transformation applied on it.
Data for mapping from operational environment to data warehouse - It metadata includes source databases and their contents, data extraction, data partition, cleaning, transformation rules, data refresh and purging rules.
The algorithms for summarization - It includes dimension algorithms, data on granularity, aggregation, summarizing, etc.

Data Cube

A data cube helps us represent data in multiple dimensions. It is defined by dimensions and facts. The dimensions are the entities with respect to which an enterprise preserves the records.

Illustration of Data Cube

Suppose a company wants to keep track of sales records with the help of sales data warehouse with respect to time, item, branch, and location. These dimensions allow to keep track of monthly sales and at which branch the items were sold. There is a table associated with each dimension. This table is known as dimension table. For example, "item" dimension table may have attributes such as item_name, item_type, and item_brand.

The following table represents the 2-D view of Sales Data for a company with respect to time, item, and location dimensions.

But here in this 2-D table, we have records with respect to time and item only. The sales for New Delhi are shown with respect to time, and item dimensions according to type of items sold. If we want to view the sales data with one more dimension, say, the location dimension, then the 3-D view would be useful. The 3-D view of the sales data with respect to time, item, and location is shown in the table below:

The above 3-D table can be represented as 3-D data cube as shown in the following figure:

Data Mart

Data marts contain a subset of organization-wide data that is valuable to specific groups of people in an organization. In other words, a data mart contains only those data that is specific to a particular group. For example, the marketing data mart may contain only data related to items, customers, and sales. Data marts are confined to subjects.

Points to Remember About Data Marts

Windows-based or Unix/Linux-based servers are used to implement data marts. They are implemented on low-cost servers.
The implementation cycle of a data mart is measured in short periods of time, i.e., in weeks rather than months or years.
The life cycle of data marts may be complex in the long run, if their planning and design are not organization-wide.
Data marts are small in size.
Data marts are customized by department.
The source of a data mart is departmentally structured data warehouse.
Data marts are flexible.

The following figure shows a graphical representation of data marts.

Virtual Warehouse

The view over an operational data warehouse is known as virtual warehouse. It is easy to build a virtual warehouse. Building a virtual warehouse requires excess capacity on operational database servers.

Data Warehousing - Concepts

2015-12-30T07:16:00.003-08:00

Data Warehousing - Concepts

What is Data Warehousing?

Data warehousing is the process of constructing and using a data warehouse. A data warehouse is constructed by integrating data from multiple heterogeneous sources that support analytical reporting, structured and/or ad hoc queries, and decision making. Data warehousing involves data cleaning, data integration, and data consolidations.

Using Data Warehouse Information

There are decision support technologies that help utilize the data available in a data warehouse. These technologies help executives to use the warehouse quickly and effectively. They can gather data, analyze it, and take decisions based on the information present in the warehouse. The information gathered in a warehouse can be used in any of the following domains:

Tuning Production Strategies - The product strategies can be well tuned by repositioning the products and managing the product portfolios by comparing the sales quarterly or yearly.
Customer Analysis - Customer analysis is done by analyzing the customer's buying preferences, buying time, budget cycles, etc.
Operations Analysis - Data warehousing also helps in customer relationship management, and making environmental corrections. The information also allows us to analyze business operations.

Integrating Heterogeneous Databases

To integrate heterogeneous databases, we have two approaches:

Query-driven Approach
Update-driven Approach

Query-Driven Approach

This is the traditional approach to integrate heterogeneous databases. This approach was used to build wrappers and integrators on top of multiple heterogeneous databases. These integrators are also known as mediators.

Process of Query-Driven Approach

When a query is issued to a client side, a metadata dictionary translates the query into an appropriate form for individual heterogeneous sites involved.
Now these queries are mapped and sent to the local query processor.
The results from heterogeneous sites are integrated into a global answer set.

Disadvantages

Query-driven approach needs complex integration and filtering processes.
This approach is very inefficient.
It is very expensive for frequent queries.
This approach is also very expensive for queries that require aggregations.

Update-Driven Approach

This is an alternative to the traditional approach. Today's data warehouse systems follow update-driven approach rather than the traditional approach discussed earlier. In update-driven approach, the information from multiple heterogeneous sources are integrated in advance and are stored in a warehouse. This information is available for direct querying and analysis.

Advantages

This approach has the following advantages:

This approach provide high performance.
The data is copied, processed, integrated, annotated, summarized and restructured in semantic data store in advance.
Query processing does not require an interface to process data at local sources.

Functions of Data Warehouse Tools and Utilities

The following are the functions of data warehouse tools and utilities:

Data Extraction - Involves gathering data from multiple heterogeneous sources.
Data Cleaning - Involves finding and correcting the errors in data.
Data Transformation - Involves converting the data from legacy format to warehouse format.
Data Loading - Involves sorting, summarizing, consolidating, checking integrity, and building indices and partitions.
Refreshing - Involves updating from data sources to warehouse.

Note: Data cleaning and data transformation are important steps in improving the quality of data and data mining results.

Five Benefits of Business Intelligence Software

2014-02-10T08:58:00.001-08:00

Five Benefits of Business Intelligence Software

Business Intelligence Benefits

1. Eliminate guesswork: "Running a business shouldn't be like gambling," said Ken Dixon, executive vice president of marketing at Kogent Corporation. "Far too often, executives must rely on 'best guess' and 'gut feel' decisions as they attempt to steer their companies into the future. They do this because their business data lacks any structure to allow them to make truly informed choices. Business intelligence can provide more accurate historical data, real-time updates, synthesis between departmental data stores, forecasting and trending, and even predictive 'what if?' analysis," eliminating the need to guesstimate.

2. Get faster answers to your business questions: "BI users can quickly get answers to business questions, rather than spending hours reading through volumes of printed reports," said Wende Cover, director of strategic marketing at MicroStrategy.

3. Get key business metrics reports when and where you need them: Today, many business intelligence software vendors are making it possible for users to access key business metrics, reports and dashboards on mobiles devices like their iPhone, iPad, Droid or BlackBerry, giving sales and marketing people access to critical business information on the fly.

4. Get insight into customer behavior: One of the great benefits of business intelligence software is it allows companies to gain visibility into what customers are buying (or not), giving them "the ability to turn this knowledge into additional profit" and retain valuable customers, said Mike Meikle, CEO of the Hawkthorne Group, a boutique management and information technology consulting group that advises companies on business intelligence tools.

5. Identify cross-selling and up-selling opportunities: "Business intelligence software allows firms to leverage customer data to build, refine and modify predictive models [that help] sales representatives to up-sell and cross-sell products at appropriate customer touch points," said Mohit Joshi, vice president and global head of the Sales, Banking and Capital Markets Practice at Infosys Technologies.

Business Intelligence Reporting

2014-01-29T23:13:00.000-08:00

Business Intelligence and Reporting

Business Intelligence Reporting

SQL Server Reporting Services in Microsoft Dynamics GP

Reduce the time it takes to create reports—and take full advantage of your data—with customizable reports for Microsoft® SQl Servertm Reporting Services. Smooth integration supports a range of common data sources from OLE DB and the Open Database Connectivity (ODBC) interface to Microsoft Office System applications.

Because SQL Server Reporting Services offer reports that work with both the Microsoft Dynamicstm GP sample database and your organizational data, your people can quickly build relevant reports, manipulate information, and then share it across your organization.

Benefits

Tailor reports to your organizational needs.
Use the sample reports either as-is or as templates for designing new reports. With customizable report templates at hand, your decision makers and employees can easily and efficiently take full advantage of SQL Server Reporting Services.

Quickly access reports from within Microsoft Dynamics GP.
Maintain one-click access to the reports you use most often by storing them in a personalized My Reports list within Microsoft Dynamics GP.

Leverage existing IT capabilities.
Using Microsoft Visual Studio® .NET and the Microsoft .NET Framework or SQL Server Business Intelligence Development Studio, developers can leverage the capabilities of their existing information systems and connect to custom data sources, produce additional output formats, and deliver to a variety of devices.

Build on your current system capabilities.
Using the familiar, widely- used Microsoft Office Excel® interface, you can get even new employees up to speed quickly, without extensive training.

Take Full Advantage of SQL Server Reporting Services

SQL Server Reporting Services in Microsoft Dynamics GP offer several reports that are designed to work with Fabrikam, the Microsoft Dynamics GP sample database. With some minor changes, you can also work with these reports in an existing Microsoft Dynamics GP environment.

You can download the following Report Pack (which includes eight predefined reports) from the Microsoft Download Center:

• Commissions Report: Expandable views of sales commissions by territory, salesperson, and year, including views of quarterly commissions by territory.
• Customer Profitability Report: Sales, total profit, and gross margin percentage per customer for the selected year. Expandable customer ID offers a view of sales, total profit, and gross margin percentage by document number.
• Inventory Value by Site Report: Bar graph of the value of inventory by site and the total inventory value for the company. Clickable inventory site bars display Inventory value by Site. Use the Item Number Report to view items and their total value for that site.
• Item Quantity Back Order Report: Quantity of items that are back ordered, quantities allocated to purchase orders, and item quantities available for all sites.
• Open Purchase Orders Report: Purchase orders that have not been closed or canceled per vendor or for all vendors, including list views for the number, status, and date of each purchase order, assigned vendor, and line item detail.
• Open Sales Orders Report: Unposted sales orders for a range of customers, including list views for the sales order number and date, customer information, requested ship date, amount remaining, assigned salesperson, and line item detail.
• Work Center by Weeks – Employee Capacity Report: Available work centers and the employee capacity of each work center by weeks.
• Work Center by Weeks – Machine Capacity Report: Available work centers and the machine capacity of each work center by weeks

The SQL Server Reporting Services wizard, available with Microsoft Dynamics GP 10.0, simplifies deployment for the following SQL Server Reporting Services Reports:

Purchase Order Processing (POP)
Receivables Management (RM)
Sales Order Processing (SOP)
General Ledger
Inventory Management
Payables Management (PM)
Payroll – US
Human Resource Management
Bank Reconciliation
Fixed Assets(FA)
Project Accounting (PA)
Manufacturing

FEATURES
Easy Access to Report Formats Access reports easily from within Microsoft Dynamics GP, including one- click access from your personalized My Reports list of frequently used formats.

Drilldown Actions Quickly access essential information with the ability to drill down to details within reports.

Chart Options Take advantage of report layout options such as pie-, line-, or bar-chart capabilities to highlight key information or enhance presentations.

Customized Filtering Filter report data using dynamic parameters.

Flexible Report Views Use a collapsible report view to expand sections, reducing complex reports to manageable proportions.

Subreports Create subreports within another report, or bind a main report to one or more subreports through a set of parameters.

Table View This report layout option quickly presents the data in a table format for easier viewing and distribution across your business.

Microsoft SQL Server 2005 Invest in your company’s future with no worries. SQL Server Reporting Services is compatible with Microsoft SQL Server 2005.

Content taken from below site url
For more information about SQL Server Reporting Services in Microsoft Dynamics GP, visit www.microsoft.com/dynamics/gp.

Data Warehouse Architecture

2014-01-27T07:33:00.005-08:00

Data Warehouse Architecture

Enterprise Data Warehouse Process

2014-01-27T07:31:00.002-08:00

Enterprise Data Warehouse Process

Business Intelligence Trends

2013-04-03T12:14:00.001-07:00

7 Top Business Intelligence Trends For 2013

Short list of BI hot buttons includes dashboards, self-service, mobile, in-memory, cloud, collaboration and, of course, big data.

Many people seem to want to stick a sexier name on business intelligence, whether that's "business analytics" or "big data." To me, it's still business intelligence, a top-priority technology that can help companies boost revenues, improve customer service or control costs by making better, faster decisions.

Whatever you want to call this still-vital category, here are my predictions for the top BI trends of 2013, along with a few looks back at highlights of 2012.

1. Dashboards Evolve, Expand
You would think that there's not much room for dashboard innovation now that they're the bread-and-butter BI interface, already in use among most large and midsized companies. And yet dashboards were rated the top priority for expansion and innovation in the BI Scorecard 2012 Successful BI Survey. The dashboard's rise to prominence is a confluence of next-generation technology along with a recognition that BI must be aligned to business goals to be successful.
[ Want more on trends in the year ahead? Read 5 Cloud App Trends To Expect In 2013. ]
Access to data alone doesn't help a company improve. Next-generation dashboards keep workers focused on the right metrics and inform in a way that lets employees take preemptive action. Key features enabling such dashboards include in-memory processing, the ability for users to mash data together and to assemble their own dashboards, KPIs, faceted (filter-by-category) search, mobile, and the ability to link insight to action.
With big BI platform vendors IBM, Microsoft, and SAP generally lagging the dashboard capabilities provided by specialty vendors, customers will continue to mix and match systems from different providers in 2013. Differentiated leaders include QlikTech, which supports rapid deployment and intuitive "associative" analysis, JackBe, which has strong operational dashboards, and Metric Insight, which offers top-notch KPIs.
Look for all vendors in this space to continue to improve their capabilities in 2013. SAP, for example, recently released its next-generation dashboard tool, Design Studio, though data-source support is initially limited to SAP BW and the Hana in-memory database. Look for SAP to improve related mobile and data-visualization capabilities. SAP will also eventually integrate and merge its once-leading Xcelsius dashboarding product, now rebranded "Dashboards," into Design Studio. QlikTech also is expected to release a next-generation dashboarding product this year.
Looking back, one important dashboard release in 2012 was Oracle Endeca Information Discovery, acquired by Oracle at the end of 2011 and adapted to run on its Exalytics appliance. Oracle classifies this product as a discovery tool, but in my view it's best positioned as a dashboard application uniquely positioned to explore unstructured data using faceted search.

2. Self-Service BI Gets Real
Self-service BI continues to be a vision for many companies in which users are empowered to explore new data sets without much IT support. Visual-data-discovery tools have become synonymous with self-service BI and are growing at three times the pace of the overall BI market. Unfortunately, some vendors are too quick to attach the visual discovery moniker to their products. As I wrote in the latest BI Scorecard Strategic and Product Summary report, there's a continuum of self-service BI capabilities that ranges from interactive reporting to business query to visual data discovery, and yes, even to tools such as spreadsheets.
My hope in 2013 is that practitioners recognize this range of self-service, and that vendors help educate rather than just jumping on whatever bandwagon has the most hype. Leading companies will make the shift to self-service BI, both to empower workers and to ensure the smartest allocation of constrained IT resources. In our Successful BI Survey, 44% of respondents say BI teams do not have adequate time, funding or resources to keep up with BI demand. With the fight for BI talent, simply hiring more people is not the solution. Instead, business users have to embrace responsibility for routine BI tasks. At the same time, IT has to let go of some of the mundane enhancement requests and focus on complex data challenges and leveraging innovations.
In 2013 Tableau will release version 8 of its software, which will include browser and iPad-based authoring, a relative rarity in the visual data discovery category. Also look for improvements in other first-generation visual discovery products:
-- SAS Visual Analytics Explorer, first released in February 2012, is due out with a new version that will support calculated columns, forecasting, decision trees, and maps.
-- Microsoft's Power View via SharePoint (released in Q1 2012) will reemerge as an Excel add-in.
-- AP Visual Intelligence, first released for Hana in March 2012, is now on a six-week release cycle, gaining support for more data sources and capabilities.

3. Mobile BI Boosts BI Adoption
Just when you thought the dust had settled on the question of tablet leadership (the iPad), Microsoft released the Windows Surface and Apple missed Wall Street earnings estimates. Prime-time ads for the Surface abound! And oh, how I would love to more easily synch my Outlook calendar!
Gone are the days when corporate IT can set mobile device standards. Instead, users are increasingly bringing their own devices, forcing IT (and BI vendors) to support a broad swath of smartphones and tablets. The most promising way to support diversity is to support HTML5, but the best user experience continues to be through device-native apps. Just what those apps need to support is a moving target, as user requirements evolve. For example, availability of offline data-interaction capabilities -- rare in 2011, but supported by specialty vendor RoamBI -- increased in 2012 with MicroStrategy, SAP Mobile and Oracle Mobile HD adding such capabilities.
Will we see native support for Microsoft Surface in 2013, or will vendors use the HTML5 approach for this device? It's too early to tell, but I don't anticipate any broad shift. The debate about which capabilities to provide on smartphones versus tablets will continue. Mobile Device Management will remain a separate market segment, but savvy mobile BI providers and customers will integrate with these solutions so that when a device is lost or stolen, there is additional security beyond just a user name and password so that offline data can be wiped.
Mobile will also continue to drive BI adoption in 2013, re-igniting executive interest and making BI more relevant to field and front-line workers. In last year's Successful BI Survey, only 11% of respondents said their firms had successfully deployed mobile BI. BI adoption at those firms stood at 39% of employees, far ahead of the industry average of 24% of employees.

4. In-Memory Goes Mainstream
In-memory technology saw several major releases in 2012, and that makes 2013 an opportune year for companies to implement this technology. In-memory was initially an approach leveraged by a few OLAP systems (like TM1, now part of IBM Cognos) and a few specialty vendors (like QlikTech and Tibco Spotfire).

Now all leading BI platform vendors have in-memory solutions, with Oracle being the last to join the ranks with its Exalytics appliance, which runs the TimesTen in-memory database. Kicking off 2013, SAP announced the ability to run its core transactional (OLTP) applications on the Hana in-memory database. Nonetheless, debate about when to use in-memory or when to use an analytic appliance, columnar database or disk-based data warehouse will continue, driven by constraints including available expertise, analytic demands and cost.

Other noteworthy 2012 in-memory announcements included:
-- Microsoft Hekaton, an in-memory transaction support within SQL Server, expected in 2014 or 2015
-- IBM Cognos 10.2, which includes dynamic, in-memory cubes for relational data sources
-- SAS LASR Server, which combines in-memory processing with Hadoop infrastructure for large-scale analytics and visual discovery.
[ Want more on trends in the year ahead? Read 5 Cloud App Trends To Expect In 2013. ]

5. Big Data Generates Big Interest
Reminiscent of the Gold Rush era, there's money to be had (and to invest) in big data, but only a few, as of yet, are striking it rich.
Big data such as Web clicks, tweets, and genomic data are critical in certain industries, such as ecommerce, gaming, advertising, and healthcare. Some associate big data only with Hadoop and with the demise of the data warehouse. However, I see Hadoop and NoSQL solutions as being only a part of the architecture for handling structured and unstructured data. The traditional data warehouse, analytic appliances and BI vendors all have roles to play here.
So in 2013, companies in certain industries will indeed embrace new solutions from big data startups including Datameer, Karmasphere, Platfora, and SiSense. Others will leverage big-data connectors from their existing BI tools. The majority of companies are still grappling with basic data access, so big data will continue to be just an interesting cover story.

6. Cloud Becomes Just Another Option
Cloud is another area of BI that is grabbing a lot of headlines, but to date, not a lot of deployments. Cloud accounts for only 3% of total BI revenues, according to Gartner. Cloud BI would seem to fall, then, in the domain of "nice to have." But if you talk to CIOs, many would welcome the chance to outsource the problem of infrastructure maintenance and instead focus on higher-value IT investments. BI in the cloud provides that opportunity, along with flexibility to handle elastic (variable) workload demands.
Security concerns were initially the biggest barrier to cloud BI deployments. There's now greater acceptance that cloud BI can be secure. The question becomes whether a cloud provider can do a better job than you can in ensuring reliability and security, though doubts were recently raised by yet another Amazon blip during the make-or-break Christmas season.
BI vendors have had to re-architect their products to be cloud-ready, both with multitenancy and the ability to work with data retained on premises. In 2013 we will see more POCs and trial-use of BI cloud products. Cloud will increasingly become a routine deployment option rather than a product differentiator. Oracle says it will preview a cloud-based reporting and analytics offering in the first half of the year. Also look for Pentaho to release a new, multitenant architecture.

7. Collaboration Goes Beyond Social
Did you think that social networking and collaboration would just be passing fads -- or ways to share photos and look up old flames? In the midst of Hurricane Sandy, many used Facebook and Twitter to find hotel rooms, places to shower and to charge cell phones. It was a study of contrasts of those who knew how to use the technology and those who didn't -- I couldn't find Jersey Central Power & Light's Twitter handle. New Jersey Governor Christie started off with useless tweets, like "watch my live stream" -- uh, not on limited battery power. But he eventually provided more useful tweets, such as when specific communities would get power restored.
I still get the sense that most vendors add collaboration without a clear vision or passion for what sharing can bring. A few BI vendors seem to get it, most notably Panorama and Lyzasoft. Some BI buyers tell me their users don't ask for these types of features, to which I recall that nobody initially asked for mobile phones, either. When I read about how, for example, United Health Group and the Mayo Clinic will be sharing and mining data across boundaries, I think collaboration in BI has the potential to bring the best data to the best analysts.

These are my predictions for the biggest trends in the year ahead. Wherever your priorities lie, enjoy the journey!

Outlook CRM Software

2013-03-29T10:17:00.000-07:00

Outlook CRM and Contact Management – Keeping It Simple

1 Overview
This article looks at how you can use Microsoft Outlook as your Contact Management and CRM
system with out the installation of any additional client software or Outlook Add-Ins, and still interface Outlook with your back-end CRM or ERP system.

It represents a new, yet incredibly simple approach to CRM that is guaranteed to work where other systems may already have failed. Equally important, for those organizations wanting to adopt a CRM system, it is a very good system to implement first, even if one later “graduates” to a more comprehensive system.

2 The Problem
CRM has received a lot of negative publicity over the years because of the failure rate of CRM implementation projects.

A recent Butler Group report found that 70 percent of CRM implementations fail. A Gartner study found that approximately 55 percent of all CRM projects failed to meet software customers' expectations. In a Bain & Company survey of 451 senior executives, CRM ranked in the bottom three categories among 25 popular tools evaluated for customer satisfaction.

The findings of a poll of 100 SME organisations with CRM implementations revealed that while 60% of sales directors insist that CRM is fundamental to their sales processes, a quarter have lost customers directly through their ineffective use of CRM technology.

Essentially sales teams are not using their CRM systems correctly with 44% of sales directors admitting that fewer than 80% of their staff use the technology effectively. The knock-on effect is a loss of potential revenue and increasing levels of customer dissatisfaction.

But sales directors themselves are hardly blameless with 72% confessing that they tolerate inefficient use of the CRM they have invested in, while a mighty 73% do not discipline staff who fail to use CRM systems. Common reasons for this lack of use include:
• resistance to changing the way they work among sales people
• reluctance to use new technologies.

A comment made in response to this report summaries the current situation:
“The main problem with sales CRM systems is that they are simply too cumbersome and
complicated for many salespeople to use. When confronted with something like SalesForce with its multitude of fields and processes, many salespeople just roll their eyes and go back to the Excel spreadsheets that have served them well for years. Until more useable and simple systems are available CRM horror stories will continue to be commonplace.

3 The Solution – Microsoft Outlook
The Fear of/Resistance to Change Syndrome dictates that the less the users have to change the way they work, the more likely they will adopt any new system that is based around what they are already used to. So if your users are already sending mail, managing their own contacts in their own Personal Contacts folder, and scheduling appointments with the Outlook Calendar, they don’t want to change this. ExchangeWise (Pty) Ltd www.exchangewise.com, Outlook CRM and Contact Management – Keeping It Simple

The Resistance to Change factor has another side that’s reflected in a popular saying namely “Up to the age of 18 you make your habits; thereafter they make you”. The reality of these words of wisdom is summarized as follows: If a new system is introduced such that not only is training required to learn the system but one also has to form a new set of habits associated with the procedures necessary to run the system, then it will take the average worker 3 to 6 months to develop these new habits to the point where they are ingrained into their daily work routines. And invariably if the user does not see sufficient benefit in the system soon enough (i.e. before these new habits are fully developed), then they continue to do what they were doing before the new system was introduced and as such the new system falls into disuse. One common excuse we
used to get during post-implementation audits from users who were found not to have been entering activities into the new CRM system was “Oh, I keep forgetting to open the system”, or “it takes too long to open the system when I need it”.

Reluctance to Use New Technology: If everyone is already using Outlook, and has been trained on Outlook, or at least has become familiar with its functionality then there is no impact on their daily routine. They continue to use the same elements of Outlook in the same way they’ve always done. Microsoft has made enormous investments in studying the usability of Outlook and soliciting user feedback. So why reinvent the wheel when the users already know (and usually love) this interface.

The “WIIFM” Concept: Here Outlook definitely comes to the rescue. Users already appreciate the benefits of Outlook as a “Personal Information Manager”, especially as Microsoft touts Outlook as being one. The emphasis on Personal implies the primary benefit is to the user and not necessarily the company. So Microsoft in that sense has solved the issue of “What’s in it for me?”

Having looked at the fact that Outlook certainly addresses some of the “human” factors associated with a CRM implementation, we still need to look at how well Outlook meets the challenge in terms of functionality. We can do this by looking first at the basics of a Contact Management system, which still today is the core of any CRM system.

4 The Basics of any Contact Management System
Any contact management application needs at least the following basic functionality:
a) A mechanism to store and profile Contact information.
b) A means to plan and organize appointments with those contacts, not only for yourself but other team members managing those same contacts.
c) A means to schedule tasks and to-do’s for those contacts.
d) A mechanism to record any kind of interaction with a contact, namely meetings, phone calls, e-mail, documents, etc.
e) Some way of storing documents sent to and received from a contact.
f) A way to send and track e-mail communication. ExchangeWise (Pty) Ltd www.exchangewise.com Outlook CRM and Contact Management – Keeping It Simple

5 Outlook’s inherent Contact Management Functionality
With reference to the requirements list above, Outlook at least satisfies the following
requirements:
a) A mechanism to store and profile Contact information: The “Contacts” folder in
Outlook already allows a comprehensive profile of any personal or business contact to be
maintained.
b) A means to plan and organise appointments for those contacts: Outlook’s
calendaring facilities provide these very effectively and when coupled with Exchange
Server incorporate a huge number of collaborative features that are extremely difficult for
any other stand-alone CRM system to emulate or reproduce.
c) A means to schedule tasks and to-do’s for those contacts: Outlook’s task
management facility is excellent for this.
d) A mechanism to record any kind of interaction with a contact: The “Journal” facility of
Outlook contains the standard fields necessary to record phone calls, meeting, etc. with
clients, and can even time such activities.
e) A way to send and track e-mail communication: The Inbox and Sent Items stores
inward and outward e-mails.
However, while Outlook does have the basic foundation for solid contact management
functionality, there are certain limitations of Outlook that one needs to be aware of.

6 Limitations of Outlook
As a contact management application however, Outlook has the following limitations:
a) Private Mailbox (Contacts, Appointments, etc.): Without setting up and customising Public Folders, most users will just utilise their Private Mailbox Contacts folder for managing their contacts, thus limiting the sharing of that information and potentially creating massive duplication of the same data within the organisation.
b) Contact-centric: By virtue of their being only a Contacts folder (and no Companies folder), Outlook tends to be Contact-centric rather than Account-centric, which can be limiting for those users managing corporate accounts.
Microsoft Outlook is Microsoft’s messaging and personal information management program that helps you manage the following:
• Contacts
• Scheduling (Calendar/Appointments)
• Time/To-Do Management (Tasks)
• Activity Tracking (Journals)
• Messaging (Inbox/E-Mail)
Microsoft product box shot reprinted with permission from Microsoft Corporation ExchangeWise (Pty) Ltd

c) Discrete, independent folders: Most users tend to use their Outlook folders as discrete elements, i.e. because it is fairly cumbersome for users to link one item to another, (e.g. a contact to an appointment) they seldom do this. Thus it is difficult for users in the organisation to get an overall picture of all the activity occurring within the organisation against any particular company or contact. The universal objective of any CRM system however, is to provide a “single-view of all customer-related information to everyone in
the organisation”.

7 Overcoming Outlook’s Limitations with MX-Sync
By installing MX-Sync (http://www.exchangewise.com/Products/MXSync), an application that synchronizes Outlook data between Exchange Server Private/Mailbox folders and a back-end database, the above-mentioned limitations can be overcome.
a) By providing two-way synchronization between a user’s private Contacts folder and the corporate database, everyone is updating the same common customer list, rather than each maintaining separate ‘Personal’ Contacts folders that are not visible to anyone else. One still has the ability to keep certain contacts simply by not marking them with the Category that identifies to MX-Sync that a contact should be added to the corporate database.

b) By providing automatic linking and copying of e-mails, journals, tasks and appointments to the corporate database, the whole company is informed about all important customer interactions.

c) By linking e-mails, appointments etc. to the relevant contact(s), everyone can get a view
of all activity occurring with a specific customer.

8 MX-Sync Features & Benefits
MX-Sync is an Outlook synchronization utility which synchronizes data bi-directionally between standard Microsoft Exchange Server folders (both Private Mailboxes and Public Folders) and any database, be this a SQL Server, Oracle, MySQL or other database. So you can sync Outlook data to SQL or SQL Server data to Outlook/Exchange Server folders. MX-Sync caters for scenarios where data is being updated in either or both databases.

8.1 Contacts
MX-Sync will synchronize selected contacts in the SQL database to each user’s Personal Contacts folder, and vice versa. So MX-Sync will update changes made by anyone on the host database back to each user's respective Contacts folder. If a user has set his or her Personal Contacts folder to sync to their PDA, then these changes will of course be automatically replicated out to their smart-phone, Windows Mobile or Blackberry device. Likewise users can add contacts via their PDA’s or Blackberry devices and flag them in
such a way that when these contacts sync back to their Personal Contacts folder in Outlook, MX-Sync will automatically add them to the SQL database.

In summary:

1. Creating a contact in your Personal Contacts folder and setting a specified Category (e.g. ‘MX-Sync) will create that contact in the back-end SQL database (if it does not exist already). ExchangeWise (Pty) Ltd Outlook CRM and Contact Management – Keeping It Simple

2. Creating or editing a contact in SQL that matches a specified filter condition (per user) will sync that contact from SQL down to the user’s Personal Contacts folder in Exchange/Outlook.

8.2 E-Mail
MX-Sync performs server-side linking of e-mails from designated personal mailboxes to the SQL Server E-Mail Folder. So any mail that drops into the Inbox or Sent Items folder that is from or to a contact that is in the SQL Contacts folder will be automatically linked to that contact and copied to the E-Mail folder even if no Outlook/MX-Contact client is active at the time.

This ‘server-side’ linking of e-mails also means that e-mails sent from a user’s Blackberry or PDA will automatically be copied to the E-Mail table even if that user does not have Outlook open on their machine at the office.

8.3 Tasks and Appointments
Tasks and Appointments will be synchronized between Exchange and SQL in the following scenarios:

1. creating an appointment/task linked to a personal contact in the Personal Contacts folder that has been synchronized to SQL will create the appointment in SQL and then link the appointment to the user, the contact and the contact’s primary company.

2. creating a task/appointment (not linked to contact) but setting a specified category (e.g. ‘MX-Sync’) will create the task/appointment in SQL, linked to the user.

3. creating an appointment where a contact in the SQL database is invited as an ‘attendee’ will create the appointment in SQL and then link the appointment to the user, the contact and the contact’s primary company.

4. creating a task/appointment with the e-mail address or a specified ‘descriptor’ in the Subject line that identifies the contact/company to which the appointment should be linked will create the appointment in SQL and then link the appointment to the user, the contact and any other entities identified by the ‘descriptor’.

8.4 Journals
Journals are probably the least-used folder in Outlook; however they serve a very useful purpose in Contact management with regards to recording details of important phone calls or meetings. Journals will be copied from Exchange to SQL in the following scenarios:
1. creating a journal linked to a personal contact in the Personal Contacts folder that has been synchronized to SQL will create the journal in SQL and then link the journal to the user, the contact and the contact’s primary company. ExchangeWise (Pty) Ltd www.exchangewise.com
Outlook CRM and Contact Management – Keeping It Simple

9 Summary
Microsoft’s earlier promotion of Outlook as a “Personal Information Manager” created the impression amongst users that Outlook was only intended to manage one’s personal contacts and was not suited as the basis for a corporate-wide Customer Management System. However in conjunction with a back-end SQL database that the majority off the office-based organization has access to, Outlook can be a powerful tool that satisfies most of the requirements for basic contact management.

The reality is that many mobile workers do not have time to record more than just the basics of their interactions with customers. And the fact that they can do this recording via the standard Contacts, E-Mail, Tasks, and Appointments functionality on their notebook via Outlook or on their Windows Mobile or Blackberry devices and have this contact information shared automatically with the rest of the company greatly enhances the organization’s ability to manage their customer base efficiently.

Customer Data Intergration Software

2012-12-26T05:34:00.003-08:00

Customer Data Integration: Creating a Single Version of the Truth

Effective customer management happens with online, accurate, integrated and up-to-date customer information. In most organizations, customer information is distributed and duplicated across various applications, and it is difficult to get a single version of the truth. To enhance customer management, organizations are increasingly investing time and money into customer data management, with the customer data integration (CDI) system. CDI involves the integrating and unifying of customer information from disperse and heterogeneous business applications. This integrated customer repository becomes the central repository of customer information being used by various applications, and produces the true view of the customer.

The customer is the heart and soul of an organization. This is the central entity around which business of an organization revolves. The customer is the golden nugget on which the survival and prosperity of an organization depends. The entire universe may be considered as the customer base of an organization in a true sense. The customer can be viewed in various perspectives, three of which are shown in Figure 1.

Figure 1: Customer Classification
From the business interaction angle, customers can be:

Account holders: The existing customers possessing active, inactive, dormant or closed accounts.
Organizations: The internal organization can be analyzed at various levels of granularity and functions, e.g., the employees themselves can be account holders.
Partners: These are channels as well as business supporters. Partner behavior can be studied to understand their needs through the business events. This can be effectively utilized to earn loyalty as well as to grow business.
Competitors: In order to strategically frame the business, competitor activities need to be monitored.

Based on status, the customers can be classified as:

Active customer: One whose account is active and running.
Inactive customer: Customer having valid account but has not used in a period of time.
Dormant customer: Individual or corporation with whom organization did business in the past.
Prospective customer: An individual or corporate that can be targeted as a potential customer.

Based on the organization, the customer can be grouped as:

Individual: A specific person of interest to the organization, i.e., employee, agent or dealer.
Corporate: A group of individuals who have banded together for a commercial purpose.

CDI Overview

Organizations have to build strong customer relationships to stay competitive and grow in today's market. Effective customer management mandates easy and quick access to up-to-date and accurate customer information. To enhance customer management, organizations are investing time and money toward building an integrated central customer repository, which can provide the online, accurate, integrated and up-to-date customer information.
Customer data integration (CDI) is an approach toward integration and unification of the customer information from disperse and heterogeneous business applications. CDI processes consolidate customer information from all available sources, such as operational systems, call centers, customer relationship management (CRM) and data warehousing (DW) applications, and ensures the access of the current and complete view of customer information to the relevant departments/ business groups.

Figure 2: CDI Context Diagram
A successful CDI solution helps organizations in:

Effective customer management by providing a timely and accurate understanding of customer needs and behaviors.
Improved cross-selling and up-selling opportunities by understanding the prospective customers.
Removing duplication and misleading customer information and providing single version of truth across the various business units of an organization.
Providing effective campaign management.
Complying with legislation, regulations and privacy requirements.
Optimizing operational, maintenance and enhancement cost by having a central integrated environment (hardware, software).

Customer Data Integration - Challenges

In most organizations, since customer information is distributed across various applications, the unification and integration of customer information from heterogeneous and dispersed applications is a big challenge. Forester Research has found that though 92 percent companies say that having an integrated customer application is critical or important, only 2 percent have managed to achieve this. There are numerous challenges faced during customer data integration:
Duplicate Customer Data Duplicate customer records hinder the organization's ability to identify the customer uniquely and correctly. Duplicate records also cause problems in relating customer transactions to a single customer record. Also, it becomes difficult for the customer service representative to correctly understand the history of interactions made with a customer. The other significant drawback of duplicate records is that it causes duplicate campaigning. Key factors influencing data duplication issues are:

Local maintenance and storages of customer information in an individual application;
Inorganic growth of the organization (merger and acquisition) resulting in heterogeneous processes and systems to maintain and support customer information;
Different customer details fed through different channels (Web, telephone, etc.);
Data entry error;
Relaxed data entry service level agreements (SLA) and audit;
Lack of briefing, training and education to the customer service/front-end staff about the important and significance of customer data fields.

Individual systems have their own way of maintaining customer details, which lead to ambiguous and duplicate customer information. Data entry error or inconsistent data entry by customer service agents leads to an ambiguous data set. To compound the problem, the customer maintains different contact details when they are interacting through different channels. For example, the name and address can be captured in the various ways, which leads to duplicate or inconsistent customer details within and across the applications.
Inconsistent and Inaccurate Data Inconsistent and inaccurate customer data limits the organization's ability to understand and analyze the customer. This leads to poor decision-making that causes customer dissatisfaction. This inconsistent and inaccurate data set can generate a different version of the customer information and defeat the prime purpose of CDI, which is to produce a single version of the truth. It also leads to data reconciliation issues and affects the functioning of the business applications. Key factors influencing consistency and correctness issues are:

Lack of common metadata control: Distributed and disintegrated customer metadata across application can lead to the inconsistent definition of the customer.
Clerical errors (data entry error): Inaccurate and insufficient data entered by data entry operators or call center agents leads to data sufficiency and accuracy issues.
Lack of data ownership, infrequent audit and relaxed SLA. Based upon business needs, individual business groups (sales, operations, marketing, human resource, etc.) primarily focus on the given subset of customer information. The data fields not being used by given business groups can contain a default or meaningless value in the database. Inconsistent domain range and or default values definition can generate the data consistency issues, i.e., default values for birthdate can be different for different departments. The missing data fields cause data sufficiency issues.

With the increasing volume and velocity of data, managing data growth and maintaining the latest and accurate customer information is a challenging task. Decayed and old data contains no value to the business. The two major areas of data management are growth and latest data management.

Growth management. Business applications generate millions of customer records every year. Inefficient data management and storage can have an adverse affect on the performance and usability of the application. Factors contributing data growth are:

Nature of operational systems (business applications),
Inappropriate historical data management strategy,
Lack of data archival and housekeeping strategy, and
Inappropriate reference data management strategy.

Current data management. Customer information changes over time. The CDI application should track the changes and maintain the most current customer information. Factor contributing information changes are:

Changes in customer credentials;
Changes in customer contact details; and
Changes in customer demographic, psychographic and geographic details.

The CDI Solution Architecture

A customer data integration (CDI) system is the central application to capture, integrate and distribute customer information. The goal of a CDI application is to integrate customer information from different applications with minimum latency. Based on the needs of an organization and the dynamism of the customer data, the CDI architecture can be implemented either using batch processes (ETL - extract, transform and load) or using real-time messaging (EAI - enterprise application integration).
Figure 3: CDI Logical Architecture (Hub-and-Spoke Model)

A CDI system extracts customer information from disperse applications and performs data cleansing, customer matching (deduping) and integration as per the predefined cleansing, matching and integration rules. The central repository contains the integrated customer data with different views of customer information. The data access interface defines the data access mode, restriction and privileges. Business applications and user communities can access only that data set they are authorized to. Business rules (data cleansing, customer matching, data integration and data access rules) can be stored in the central metadata repository or reside in the individual tools repository.

Conceptual Data Model (CDM)
CDI is an application to store and distribute meaningful customer information. The CDI data model contains customer and related entities. The generic CDM is illustrated in the Figure 4.

Figure 4: CDI Conceptual Data Model

Customer and customer classification. A Customer is a Person or Organization of interest. Customers enter in a relationship with other customers. The nature of this involvement is used to determine whether a specific customer in an external customer, employee, supplier, partner or a competitor. The customer can be viewed from various perspectives as discussed in the beginning of this article.

Customer relationship. This entity stores relationship between two customers. Customer relationships can be categorized as personal or professional, e.g., Parent-Child, Employer-Employee, etc.

Customer contact. This entity captures the customer contact details. Customer contact details can be the physical address, telephone contact and electronic information. The postal address can be subgrouped as current address, permanent address, office address, bill-to-address and ship-to-address. Telephone contact consists of home phone number, office phone number, cell number, corporate office number and local office number. Electronic address consists of personal, office and corporate email ID.

Customer details. This entity captures the customer demographic, psychographic and geographic details. This information can be used for customer segmentation and analysis.

The demographic details to be captured are gender, age group, marital status, number of children, profession, income group, other financial details, etc. The psychographic information captured includes channel preference, privacy specifications, market research, etc. The geographic details to be captured are location (country, region), population groups, country development status, primary currency etc.

Customer accounts. A customer account is a contractual relationship between a customer and an organization and is associated with a given product or services. The account entity stores the account details, account type, account status and other related information.

Customer household and household details. Households are the collection of existing or prospect customers. Households and their demographic, psychographic and geographic information will help in understanding the associated patterns and defining the proactive campaign management.

CDI Processes
CDI processes facilitate the consolidation and unification of disparate customer data into integrated and meaningful customer information. The key driver for customer data integration is to provide the true view of customer. The process steps involved in the customer data integration are data acquisition, data cleansing, data integration and data management.

Figure 5: CDI Processes

Data Acquisition
The data acquisition phase helps in understanding the customer data and defining the data extraction strategy. It involves the identification, analysis and extraction of customer data from various business applications (operational systems). A detailed study of source data is performed to understand the data format, characteristics, pattern and usability. A data extraction strategy and approach is defined to extract the relevant customer information from source systems.

Data Cleansing
The data cleansing phase encompasses the processes and procedures for data correction and standardization. Data correction is the process of fixing, spelling and correcting the address, ZIP code, Social Security number and permanent account number. Once the data has been corrected, it needs to be standardized according to the predefined data format and structure through a data standardization process such as storing the Social Security number as 999-99-9999.

The data integration phase includes the processes for matching, merging and linking of customer information. This involves the following processes:

Customer matching and linking - Customer data is deduped to remove the duplicate customer records and generate a single customer record valid across the business applications (source systems). Also, customer records get linked with the other related records, i.e., households and organizations.
Data transformation and integration - Data will be transformed and integrated to produce the true view of customer. On a need basis, in-house customer information will be integrated with external third-party customer data set (e.g., Dun & Bradstreet, Experian) and produce the integrated customer database with various data access views.

Data Management
Data management includes the processes for monitoring and maintenance of customer data, which is dynamic by nature and changes over time. It requires periodic data monitoring and maintenance to keep the up-to-date customer information available.

Data monitoring processes periodically analyze customer data to understand any changes in the customer information. Data maintenance makes the latest information available and archives the old data set. The archived data set is required to reproduce the snapshot of customer information at any given point in time.

Customer Data Integration Architecture

2012-12-26T05:22:00.000-08:00

As above figure depicts, building a customer data hub requires both bulk data movement from ERP, CRM and other operational systems as well as transaction level data validation and customer master management from the customer touch points.

In reality, most data is generated by the operational systems, such as an SAP R/3 system or a Siebel application. Customer name and address data will be maintained by the various operational components that need to communicate with the customer. These systems perform tasks such as invoicing, campaign execution and shipping – each of which provide customer touch points that can aggregate more customer information. One approach for maintaining data integrity would be to attack the problem at the operational system level. This seems to be a practical approach. After all, operational systems are the place where detailed transactions are completed. However, these applications are dedicated to performing one function that represents specific business requirements. The data collected in this environment is a by-product of the transactions that have been executed, and for the most part, the applications found here are not integrated with any other applications. Furthermore, each application is its own standalone environment and is optimized for the particular needs of the application. While this data is optimized for the operational system, to fully understand your customer, you need to consolidate that data, by customer, into a single customer-centric database.

The goal of CDI is to provide the best information from the combination of the customer systems. By combining the systems, you know the customer at each touch point across every line of business. This requires an accurate, coherent customer view. Specifically, the goal is to:

Resolve customer data duplications and ambiguities throughout the entire enterprise.
Supplement gaps in the knowledge of customers from external sources.
Support customer data extraction and creation of an integrated customer database

Customer Data Intergration Techniques

2012-12-26T05:18:00.001-08:00

Techniques for managing complexity

Attributes and their values can become extremely complex and dynamic due to the many changes individuals go through. Multiply all these fields by the millions of records a business or organization may have in its data sources, then factor in how quickly and how often this information changes. The Data Warehousing Institute (TDWI) says: “The problem with data is that its quality quickly degenerates over time. Experts say 2% of records in a customer file become obsolete in one month because customers die, divorce, marry and move.”^[1]
To put this statistic into perspective, assume that a company or charity has 500,000 customers, donors or prospects in its databases. Cumulatively, if 2% of these records become obsolete in one month, 10,000 records go stale per month; or 120,000 records every year. Within two years about half of all the records may become obsolete if left unchecked.
Peppers and Rogers^[who?] call the problem, "an ocean of data"^{[this quote needs a citation]}. Jill Dyche and Evan Levy, gurus in this field^{[citation needed]}, have boiled the challenges down to five primary categories:

completeness – organizations lack all the data required to make sound business or organizational decisions
latency – it takes too long to make the data valuable: by the time of use, too much has become obsolete or outdated (slowed by operational systems or extraction methods)
accuracy
management – data integration, governance, stewardship, operations and distribution all combine to make-or-break data-value
ownership – the more disparate the owners of the data-source owners, the more silos of data exist, and the more difficult it becomes to solve problems

History of customer data integration

2012-12-26T05:16:00.003-08:00

In the late 1990s Acxiom and GartnerGroup coined the term "customer data integration" (CDI).^{[citation needed]} The process of CDI, as Acxiom and Gartner described it, includes:

cleansing, updating, completing contact-data
consolidating the appropriate records, purging duplicates and linking records from disparate sources to enable customer or donor recognition at any touch-point
enriching internal and transactional data with external knowledge and segmentation
ensuring compliance with contact suppression to protect the individual and the organization

As of 2009, service providers deliver CDI as a hosted solution in batch volumes, on demand using a software as a service (SaaS) model, or on-site as licensed software in companies and organizations with the resources to drive their own data integration processing. CDI enables companies to optimize merchandizing (assortment, promotion, pricing and rotation) based on demographics, lifestyle and life-stage, to ensure inventory turn and to reduce waste.^{[citation needed]} CDI also aids companies and organizations in choosing the best location for new branch offices or outlets.^{[citation needed]}
CDI commonly supports both customer relationship management and master data management, and enables access from these enterprise applications to information confidently describing everything known about a customer, donor, or prospect, including all attributes and cross references, along with the critical definition and identification necessary to uniquely differentiate one customer from another and their individual needs.

Customer Data Integration Software

2012-12-26T05:15:00.001-08:00

When companies and organizations wish to compile all of their customer or consumer information into one client, they use customer data integration software. Customer data integration software is used to integrate customer addresses, sales, demographics, customer needs, and features that would appeal to certain customers into one interface system so that the business entity can view all of this information at once in order to speed up production and make more sales. In this article, we will look at customer data integration software and various products available on the market.

In data processing, customer data integration (CDI) combines the technology, processes and services needed to set up and maintain an accurate, timely, complete and comprehensive representation of a customer across multiple channels, business-lines, and enterprises — typically from multiple sources of associated data in multiple application systems and databases. It applies data-integration techniques in this specific area.

What is Customer Data Integration Software
Customer data integration software is used to integrate customer information into one user-friendly console so that companies, organizations, and small businesses may review the information without wasting time by searching through large databases or files. Customer data integration software is generally top of the line but some software is more expensive than others. While many of these programs are similar in nature, they each have slightly different functions and display methods that may make it easier to view customer data. In order to find a customer data integration software that is good for you and your company, you will need to preview several customer data integration programs and select one that your company likes.

Popular Customer Data Integration Software
Customer data integration comes in various forms and is sometimes hard to distinguish from other forms of data integration such as application integration. Many companies that provide other forms of data integration, however, also provide customer data integration software. For this reason, we have compiled a small list of customer data integration software for you to review. The following is that list.

IBM WebSphere Customer Center
The IBM WebSphere Customer Center is a very powerful and user-friendly customer data integration software that will get the job done without taxing your patience. The IBM WebSphere Customer Center comes with over 500 individual services and functions to help you manage your customer information. The software is also based on open-source data so its services are constantly being updated and there is much support available on the Internet for this software. Some of the key features of this software is the ability to recognize and process duplicate customer data as well as the ability to integrate IBM WebSphere Customer Center with your other enterprise software.

Adeptia Customer Data Integration Accelerator
The Adeptia Customer Data Integration Accelerator is more than just a customer data integration software. It not only allows you to store and process information from customers but also from distributors, manufacturers, suppliers, and even your partner companies. The Adeptia Customer Data Integration Accelerator is able to process files from multiple sources and even in multiple formats so that you will never have to worry about converting your data to the same type of file. The Adeptia Customer Data Integration Accelerator works with databases, email clients, and a large number of applications which makes it capable of cross-platform functionality.

SAS
SAS is first and foremost a data analysis program but it can also be used as a customer data integration software. SAS is able to process large amounts of customer information and present it in readily-available reports. These reports allow you to not only view the information that you need but also to manage that data in a powerful way. SAS can be used to compile all customer information into one spot and then make predictions about your company's future.

Altova MapForce
Altova MapForce is a data mapping software that is also capable of processing and integrating data into one interface. Altova MapForce can also be used to create customer data integration software from a number of applications and integration methods. Altova MapForce can collect data from databases, spreadsheets, and other documents and sources. MapForce can then build an entirely customized customer data integration software by allowing the user to combine functions and tools. Altova MapForce can be combined with other services such as Visual Studio and Eclipse to build even more advanced software. Altova MapForce also allows the user to build web-based applications which can be used by the entire company from one online location.

Siperian
Siperian, also known as Informatica, is a source for customer data integration software as well as other forms of data integration. Siperian specializes in data integration and can be used by almost anyone, whether they have experience in customer data integration or not. Siperian offers many of the same features that other customer data integration software does but executes their services in a way that has more functionality and support.

SQL Transformation in Informatica

2012-12-19T12:00:00.000-08:00

SQL Transformation is a connected transformation used to process SQL queries in the midstream of a pipeline. We can insert, update, delete and retrieve rows from the database at run time using the SQL transformation.

The SQL transformation processes external SQL scripts or SQL queries created in the SQL editor. You can also pass the database connection information to the SQL transformation as an input data at run time.

The following SQL statements can be used in the SQL transformation.

Data Definition Statements (CREATE, ALTER, DROP, TRUNCATE, RENAME)
DATA MANIPULATION statements (INSERT, UPDATE, DELETE, MERGE)
DATA Retrieval Statement (SELECT)
DATA Control Language Statements (GRANT, REVOKE)
Transaction Control Statements (COMMIT, ROLLBACK)

Configuring SQL Transformation

The following options can be used to configure an SQL transformation

Mode: SQL transformation runs either in script mode or query mode.
Active/Passive: By default, SQL transformation is an active transformation. You can configure it as passive transformation.
Database Type: The type of database that the SQL transformation connects to.
Connection type: You can pass database connection information or you can use a connection object.

We will see how to create an SQL transformation in script mode, query mode and passing the dynamic database connection with examples.

Creating SQL Transformation in Query Mode

Query Mode: The SQL transformation executes a query that defined in the query editor. You can pass parameters to the query to define dynamic queries. The SQL transformation can output multiple rows when the query has a select statement. In query mode, the SQL transformation acts as an active transformation.

You can create the following types of SQL queries

Static SQL query: The SQL query statement does not change, however you can pass parameters to the sql query. The integration service runs the query once and runs the same query for all the input rows.

Dynamic SQL query: The SQL query statement and the data can change. The integration service prepares the query for each input row and then runs the query.

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.

SQL Transformation in Informatica Example Using Static SQL query

2012-12-19T11:24:00.000-08:00

Q1) Let’s say we have the products and Sales table with the below data.

Table Name: Products
PRODUCT 
-------
SAMSUNG
LG
IPhone

Table Name: Sales
PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
LG      3        80
IPhone  5        200
SAMSUNG 5        50

Create a mapping to join the products ant sales table on product column using the SQL Transformation? The output will be

PRODUCT QUANTITY PRICE
----------------------
SAMSUNG 2        100
SAMSUNG 5        500
LG      3        80

Solution: 

Just follow the below steps for creating the SQL transformation to solve the example

Create a new mapping, drag the products source definition to the mapping.
Go to the toolbar -> Transformation -> Create -> Select the SQL transformation. Enter a name and then click create.
Select the execution mode as query mode, DB type as Oracle, connection type as static. This is shown in the below image.Then click OK.

Edit the sql transformation, go to the "SQL Ports" tab and add the input and output ports as shown in the below image

In the same "SQL Ports" Tab, go to the SQL query and enter the below sql in the SQL editor.
```
 
```
```
>>> select product, quantity, price from sales where product = ?product?
```

Here ?product? is the parameter binding variable which takes its values from the input port. Now connect the source qualifier transformation ports to the input ports of SQL transformation and target input ports to the SQL transformation output ports. The complete mapping flow is shown below.

SQL Transformation in Informatica Example Using Full Dynamic query

2012-12-19T11:20:00.000-08:00

Dynamic SQL query: A dynamic SQL query can execute different query statements for each input row. You can pass a full query or a partial query to the sql transformation input ports to execute the dynamic sql queries.

Q2) I have the below source table which contains the below data.

Table Name: Del_Tab
Del_statement
------------------------------------------
Delete FROM Sales WHERE Product = 'LG'
Delete FROM products WHERE Product = 'LG'

Solution:

Just follow the same steps for creating the sql transformation in the example 1.

Now go to the "SQL Ports" tab of SQL transformation and create the input port as "Query_Port". Connect this input port to the Source Qualifier Transformation.
In the "SQL Ports" tab, enter the sql query as ~Query_Port~. The tilt indicates a variable substitution for the queries.
As we don’t need any output, just connect the SQLError port to the target.
Now create workflow and run the workflow.

SQL Transformation in Informatica Example Using Partial Dynamic query

2012-12-19T11:00:00.000-08:00

Q3) In the example 2, you can see the delete statements are similar except Athe table name. Now we will pass only the table name to the sql transformation. The source table contains the below data.

Table Name: Del_Tab
Tab_Names
----------
sales
products

Solution:

Create the input port in the sql transformation as Table_Name and enter the below query in the SQL Query window.

Delete FROM ~Table_Name WHERE Product = 'LG'

The State of Dashboards in 2012: Pathetic

2012-08-27T08:44:00.006-07:00

The State of Dashboards in 2012: Pathetic

Over the last several months, my colleague VP and Research Director Tony Cosentino and I have been assessing vendors and products in the business intelligence market as part of our upcoming Value Index.

Tony recently wrote about the swirling world of business analytics, covering many of the dynamics of this industry. He and I have been reviewing the breadth and depth of over 15 of these vendors using our Value Index methodology, which examines the products closely in terms of usability, adaptability, reliability, capability and manageability. As we have gone through this analysis, we see the dashboard as the most common tool for displaying business intelligence. The early forms of dashboards appeared in the 1980s, but in my honest evaluation, today’s dashboards have not gotten much more intelligent in all those years. The graphics have gotten better, and we can interact with charts in what is commonly called visual discovery so you can drill into and page through data to change its presentation. So some progress has been made, but the basic presentation of a number of charts on the screen has not improved significantly and worse yet neither has the usefulness of the charts. Let’s face it: It’s a big mistake to place several bar and pie charts on a screen side by side and assume that business viewers will know what they mean and what is important in them. We cannot assume that individuals in an audience have the ability to interpret charts and draw the right conclusions from them; just being pretty or interactive will not communicate the desired message.

The lack of adoption of business intelligence that includes dashboards is notorious in this industry, and so are the billions of dollars that companies have spent on BI products in the last decade. It is not helpful to make a big statement that the technology has failed; we should look for reasons that have held it back. Here we might start by questioning whether the tools present the right information in a useful form for business people or if organizations have properly configured what tools they have purchased. If the goal is to inform them through dashboards, then maybe we need to make it explicit what the dashboard or collection of charts actually mean. Typically, this means describing in words the issues or priorities that need to be examined further. A little discipline in populating the dashboard could help, such as presenting only the charts that clearly point out issues that need attention and determining which ones to use by applying analytics. If we ask why Microsoft PowerPoint is so popular as a business intelligence tool, we probably would find that the answer is the descriptive text boxes that accompany charts, providing summary sentences or emphasizing specific bullets in a list on the slide. While many people do not like the static nature of Microsoft Excel based charts in presentations or PDF versions of them, they do through human intervention with annotation and commentary provide better explanation of the charts than dashboards are doing today. If we expect our organizations to move beyond personal productivity tools and work in a collaborative enterprise environment with dashboards, we better understand how business intelligence should adapt to the way people work and operate not the other way around. In this case it may not be true that, as the old saying goes, one picture is worth a thousand words but a hundred or so words explaining the relevance of the chart could really help.

Many technology vendors believe they need to provide better context in their dashboards, so they try to align the charts to the geographic area of focus, or to the product line of responsibility or to management key performance indicators to make them more usable. Providing better role-based dashboards that are generated based on the individual’s level of responsibility and the business context is a good first step, though most business intelligence vendors do not provide this level of support. But just presenting charts tuned to the context of the individual’s role that may or may not require action is not enough. We need to prioritize the information and make it like the news, with headlines and stories that people can read to determine if they need to make decisions or take action. Whether you are reading the physical or the digital version of The Wall Street Journal or USA Today, newspapers have survived over the centuries as the main source of what humans read in formats they can comprehend. When is the last time you saw a dashboard that communicated the story of its charts and explained the analytics?

Well, once upon a time analytics and logic were applied to generate stories, in the early 1990s in a product called IRI CoverStory. Then it was classified as an expert system that programmatically would create English sentences based on the interpretation of the analytics in a memo that the system created. I would even be happy if we had titles and sub-titles to the charts that were dynamically created and represented something to guide an individual to what the purpose of the chart is to represent. Many of the current business intelligence technologies do not even allow for a free form text box that can be placed besides a chart which is really sad as this is one of the most basic methods used in business today. It would be great if dashboards could make these steps forward and make it easier to understand what is presented, but 20 years later, they have not.
Another thing dashboards need to do is help individuals take action based on the information they receive. My colleague Robert Kugel has written about action-oriented information technology frameworks and how they can help increase the productivity and effectiveness of our workers. To date, most developments of the notion of an action-enabled dashboard have focused on data discovery and supporting root-cause analysis; that can’t match the familiar people type actions that happen in our organization – collaboration through dialogue to address issues and opportunities.

Some of my industry colleagues have written books on dashboards to capitalize on the hype surrounding the topic. It’s about time for a set of books about the death of the dashboard or moving beyond dashboards; the current designs are not advancing the ability to take appropriate action on the information presented or provide the right level of guidance using analytics. We are entering the next wave of discussion on visual discovery, but so far much of this focus is just about using visualization on greater volumes and velocity of data, not making it more useful for the general population of business users. If we want to learn from the disappointing decades of business intelligence deployments, then we should find out what our business users really need to take action and make decisions on the information; delivering prettier charts won’t help. Until then, we are just perpetuating the past, and we know it has not had the best track record in advancing usefulness and adoption of business intelligence and dashboards.

I will follow up on this rant of the state of dashboards by writing about the lack of improvement in the types of metrics and indicators as they relate to overall business analytics, which are another source of the problems that underlie our current methods of delivering and providing access to analytics through business intelligence. We all can do a much better job in meeting the needs of business and truly advancing the usefulness of technology that still holds promise for significantly impacting organizations’ effectiveness.
This blog originally appeared at Ventana Research.

Principles of Data Visualization

2012-08-27T08:43:00.001-07:00

Eight Principles of Data Visualization

Imagine you are walking out of the office after a long day and your phone buzzes with a new email. Taking a quick glance, you see that it’s from Joe in operations: "Hey, wondering if you could run me a few numbers and put them in a nice chart to show how well our new store layouts are doing along with the latest sale promo we started last week. Need to put it into a presentation for the executive team next Monday. Thanks."

What does Joe really need? Where do you start? For anyone in a business environment who collects or manages some kind of raw data, tasks that are becoming more pervasive, the need to process that data into a human-usable form is increasingly common.

Visualizations, like the chart Joe asked for, are a great way to accomplish this, but they can be difficult to do properly, as anyone who has sat through a slide show presentation with an unreadable pie chart or vague growth projection graph can attest. As available data becomes more complex and extensive, weaving it into a visualization that invites engagement, understanding and decision-making is a bigger challenge, with a bigger opportunity for payoff.

Some of the traditional business standbys, like a one-off pie chart or simple line graph, even if done well, may not offer enough data to answer multi-faceted questions like Joe's. (See Figures 1 and 2, at left.) How can we take visualizations to the next level, so they can take on the challenge of today's business complexity?

Get the Fundamentals Right

The first step is to back up and focus on the basics. If you have ever played a team sport with a good coach, you may recall that he or she spent a lot of time working on fundamentals. Trick plays or advanced moves don’t win a game without solid fundamentals supporting them, and data visualization is no different. The most complex, data-rich graphic is useless unless it follows basic principles of good visualization:

1. Understand the problem domain. If you are producing visualization for your own use or that of your department, chances are good you already understand the area you will be working in. But if, as in our scenario with Joe, the visualization is for another department, or even an external stakeholder such as a customer or partner, you may need to ask questions and do more research to understand what is involved. In this case, you should investigate when these initiatives started, whether any others are in progress at the same time and what metrics the executive team will use to determine success.

2. Get sound data. This may seem obvious, but good data is at the heart of any effective visualization. Make sure the data you select is as accurate as possible, and that you have a sense of how it was gathered and what errors or inadequacies may exist. For example, maybe our store sales data for Joe is only current as of the last close of business, thanks to an older cash register system. Make sure you get relevant data and enough of it. We probably want not only sales data after these changes, but also the month or quarter before and even the same period in past years for comparison purposes. Above all, to create an effective visualization, you need to understand the meaning of the data you are working with. This can be a challenge if it has been stored as raw numbers. In this case, we may need to determine the store visitor counting method being used to know what those numeric tallies mean.

3. Show the data and show comparisons. Picking the best type of visualization is an art and science; however, the basic rule of thumb is to choose a spatial metaphor that will show your data and the relationships within it, with minimum distractions or effort on the part of the viewer. As Eddie Breidenbach explains, most graphic arrangements fall into one of four categories or metaphors (see Figure 3, at left):

Network - to show connections, sometimes in a radial layout.
Linear - to show how something varies over time or in relation to another factor, often on an X/Y space.
Hierarchical - to show groupings and importance; these can come in many different layouts.
Parallel - to show reach, frequency or shares of a whole; these can come in many different layouts.

For Joe's chart, we can start with a well-labeled, linear line graph since we want to see how sales have been affected since introducing these new initiatives. (See Figure 4, at left.)

4. Incorporate visual design principles. Using sound visual design elements, like line, form, shape, value and color, with principles like balance and variety, make a visualization both more inviting and easier to read for trends and comparisons. (See Figure 5, at left.) This will become particularly important as we take our linear metaphor visualization to the next level.

Bring in More Dimensions

Once we have good data and a sound underlying spatial metaphor (in this case, a linear metaphor), it is time to take account of the complexity at play. Though it might seem like we have satisfied the initial question at face value (“Sales are up since changing the store layout and starting the new promo”), this answer is likely to spur more questions

Based on our knowledge and research into the problem domain, we can come up with initial follow-up questions after looking at the simple linear metaphor visualization:

We started both of these initiatives right before a holiday weekend. How do we know that this uptick in sales is not just a seasonal trend?
Total sales are up, but has the new store layout succeeded in improving the performance of some departments that were struggling before?
Are we succeeding in getting more customers into the store and not just selling more to existing ones?
Are customers shopping more departments and buying a more diverse mix of items?

Asking these kinds of questions is a great exercise to begin taking a visualization to the next level because they prompt us to add more dimensions that allow viewers to explore and understand the subject from additional angles and in more detail. There are a variety of solid techniques that can help achieve this additional dimensionality. Below are the answers to these questions:

5. Add small multiples. As described by author Edward Tufte, small repeated variations of a graphic side-by-side allow for quick visual comparison. Whenever possible, scales should be kept the same and the axis of comparison, aligned. Adding some small, stacked thumbnails of our chart next to the main one allows a comparison of sales trends for the same period last year, and the one before that. (See Figure 6, at left.) This answers our first question: sales do normally go up this time of year, but the increase seems to be quite a bit bigger this time, so it is probably not just the normal seasonal cycle.

6. Add layers. Adding extra levels of information, while preserving the high-level summary data, can make a graphic more flexible and useful. Next, we are going to break down the "top line" of total sales into departments. (See Figure 7, at left.)The resulting stacked area chart answers our second question, showing that sales from the appliances department have increased as a proportion of the whole, but media department sales have not improved much.

7. Add axes or coding patterns. Another way to get more dimensions in a graphic is to add additional patterns for coding information, such as varying the shape or color of points on a plot based on a variable. In some cases, an extra axis in space, alongside an existing one or in a new direction (for a 3D chart), can also be useful for showing new variables. It's important to be careful with this approach, as it can add clutter, but when used sparingly and with good design principles it can increase a graphic's usefulness. In Figure 8 (at left) we added an additional vertical axis on the right to show daily foot traffic into the store, with its scale overlaid carefully to be comparable but distinct. To answer question number three, “Yes; we have increased foot traffic, but only after the sales promotion.”

8. Combine metaphors. So far, we have used a linear metaphor for our visualization. However, to answer our last question, we want to add a network metaphor to show connections between product categories in purchases. A pair of circular relationship (chord) diagrams showing snapshots at the beginning and end of the time period under consideration can help compare these connections. Like a pie chart, each product category is assigned a section of the circle, by percentage of total sales, but the center of the circle is hollow. If a majority of purchases containing items in one category also included items in a second category, a line is drawn to that second category; line width is based on the average proportion of both categories in the mixed purchases. As shown in Figure 9 (at left), the increase in these chord lines from the first to second diagram suggests there are indeed more purchases that cross departments since our initiatives went into place.
This relationship data would be even better if we could see it at any chosen point in time (for example, to see what effect, if any, the layout change alone had, before the promotion started). A zoomed-in view of the chord diagrams for detailed study might be useful, too. Clearly, some presentation media lend themselves to these opportunities more than others. As our graphics increase in complexity and sophistication, we need to think more carefully about how to deliver them.

Consider New (and Old) Delivery Methods

The point of any visualization is to be viewed by the right people, in the right context. Unfortunately, many business visualizations have a fleeting life on a slide, up one minute on a low-resolution projector to be scanned from across the room, and nothing but a vague memory the next.
What if, instead of a “flash on a slide” with all of these limitations, Joe's final visualization was printed in high-resolution color on a handout? Everyone could refer back to it as a touchstone during the whole presentation, seeing how the data backs up Joe's conclusions. Afterward, they could tack it up on a whiteboard for further study and follow-up.
On the other hand, maybe Joe needs people at a remote site to see this graphic or he would just prefer not to kill so many trees. He might consider putting a high-resolution version on the Web (or corporate intranet) for viewing on a PC or tablet. This could be as simple as a static graphic like the paper copy, but it also opens all kinds of possibilities for interactivity. To give just a few examples, we could enable scrubbing through time (great for seeing more network metaphors), drilling down and zooming out for a bird's eye view, seeing new data live as it becomes available or even manipulating future variables to watch different scenarios play out.

For more ideas of what's possible, and a great tool for building these using HTML standards that will work on the boss’s iPad, the Data Driven Documents JavaScript library is a great place to start.

Toward the Future

As visualization moves toward delivery via electronic medium, complex data visualization is increasingly blending into the discipline of user experience design and programming. Business analysts, IT staff and knowledge workers will need more skills designing, building and using fluid, interactive, dynamic visualizations. Fortunately, there are great tools and great groups of people focused on user experience, The potential payoff for the investment is huge: visualizations invite us to explore, understand and decide, not as one-off disposable products, but rather as robust, enduring touchstones that customers and leaders return to for insight, conversation and connection.
Note: For more on visualization fundamentals, a good place to start is Edward Tufte's excellent series beginning with “The Visual Display of Quantitative Information.” Also see “Visual Design Fundamentals: A Digital Approach” by Alan Hashimoto.

Ryan Bell is a user interface developer for EffectiveUI, where he gets to employ his passion for building great user experiences and indulge his inner information-design enthusiast.

Principles for Enterprise Data Warehouse Design

2012-08-27T08:39:00.003-07:00

Seven Principles for Enterprise Data Warehouse Design

This month, I'd like to narrow the focus to one particular aspect of the enterprise information management spectrum: data warehouse (DW) design.

Contrary to popular sentiment, data warehousing is not a moribund technology; it's alive and kicking. Indeed, most companies deploy data warehousing technology to some extent, and many have an enterprise-wide DW.

However, as with any technology, a DW can quickly become a quagmire if it's not designed, implemented and maintained properly. With this in mind, I'd like to discuss seven principles that I believe will help you start - and keep - your DW design and implementation on the road to achieving your desired results (see Figure 1). I'm including both business and IT principles because most IT issues really involve business and IT equally.

Business Principles

Organizational Consensus

From the outset of the data warehousing effort, there should be a consensus-building process that helps guide the planning, design and implementation process. If your knowledge workers and managers see the DW as an unnecessary intrusion - or worse, a threatening intrusion - into their jobs, they won't like it and won't use it.

Make every effort to gain acceptance for, and minimize resistance to, the DW. If you involve the stakeholders early in the process, they're much more likely to embrace the DW, use it and, hopefully, champion it to the rest of the company.

Data Integrity

The brass ring of data warehousing - of any business intelligence (BI) project - is a single version of the truth about organizational data. The path to this brass ring begins with achieving data integrity in your DW.

Therefore, any design for your DW should begin by minimizing the chances for data replication and inconsistency. It should also promote data integration and standardization. Any reasonable methodology you choose to achieve data integrity should work, as long as you implement the methodology effectively with the end result in mind.

Implementation Efficiency

To help meet the needs of your company as early as possible and minimize project costs, the DW design should be straightforward and efficient to implement. This is truly a fundamental design issue. You can design a technically elegant DW, but if that design is difficult to understand or implement or doesn't meet user needs, your DW project will be mired in difficulty and cost overruns almost from the start.

Opt for simplicity in your design plans and choose (to the most practical extent) function over beautiful form. This choice will help you stay within budgetary constraints, and it will go a long way toward providing user needs that are effective.

User Friendliness

User friendliness and ease of use issues, though they are addressed by the technical people, are really business issues. Why? Because, again, if the end business users don't like the DW or if they find it difficult to use, they won't use it, and all your work will be for naught.

To help achieve a user-friendly design, the DW should leverage a common front-end across the company - based on user roles and security levels, of course. It should also be intuitive enough to have a minimal learning curve for most users. Of course, there will be exceptions, but your rule of thumb should be that even the least technical users will find the interface reasonably intuitive.

Operational Efficiency

This principle is really a corollary to the principle of implementation efficiency. Once implemented, the data warehouse should be easy to support and facilitate rapid responses to business change requests. Errors and exceptions should also be easy to remedy, and support costs should be moderate over the life of the DW.

The reason I say that this principle is a corollary to the implementation efficiency principle is that operational efficiency can be achieved only with a DW design that is easy to implement and maintain. Again, a technically elegant solution might be beautiful, but a practical, easy-to-maintain solution can yield better results in the long run.

IT Principles

Scalability

Scalability is often a big problem with DW design. The solution is to build in scalability from the start. Choose toolsets and platforms that support future expansions of data volumes and types as well as changing business requirements. It's also a good idea to look at toolsets and platforms that support integration of, and reporting on, unstructured content and document repositories.

Compliance with IT Standards

Perhaps the most important IT principle to keep in mind is to not reinvent the wheel when you build your DW. That is, the toolsets and platforms you choose to implement your DW should conform to and leverage existing IT standards.

You also want, as much as possible, to leverage existing skill sets of IT and business users. In a way, this is a corollary of the user friendliness principle. The more your users know going in, the easier they'll find the DW to use once they see it.

Following these principles won't guarantee you will always achieve your desired results in designing and implementing your DW. Beware of any vendors that tell you it's a slam-dunk if you follow their methodology. There will almost always be problems that seem intractable at first - and may eventually prove to be so. Nevertheless, if you build your DW following these seven principles, you should be in a better position to recognize and address potential problems before they turn into project killers.

Rich Cohen is a principal in Deloitte Consulting LLP's Information Dynamics practice where he is responsible for the strategy, development and implementation of data governance, data warehousing, decision support and data mining engagements to support the emergence of world-class business intelligence applications. Cohen has more than 27 years of experience in the design, development, implementation and support of information technology in a variety of industries. Over the last 18 years, he has had extensive experience in the creation of technology strategies, implementations and deployment of CRM and business intelligence solutions to drive improved business performance.

Informatica - Power Center - Transformations

2012-08-20T06:56:00.000-07:00

Transformations

Power Center Transformations (partial list)

Source Qualifier: reads data from flat file and relational sources
Expression: performs row-level calculations
Filter: drops rows conditionally
Sorter: sorts data
Aggregator: performs aggregate calculations
Joiner: joins heterogeneous sources
Lookup: looks up values and passes them to other objects
Update Strategy: tags rows for insert, update, delete, reject
Router: routes rows conditionally
Transaction Control: allows data-driven commits and rollbacks

Advanced Power Center Transformations

Union: Performs a union-all join between two data streams
Java: allows Java syntax to be used within Power Center
Midstream XML Parser: reads XML from anywhere in mapping
Midstream XML Generator: writes XML to anywhere

More Source Qualifiers: read from XML, message queues
and applications

Mapplet - Set of Transformation that can be reusable

Example : Data Sources Defined Outside Mapplet

Recap

1. ETL - a. Extract, transform and load data
2. Designer - b. Create mapping objects
3. Mapping - c. Logically defines the ETL process
4. Transformation - d. Generates or manipulates data
5. Mapplet - Set of transformations that can be reused in multiple mappings

Informatica - Power Center Basic Concepts

2012-08-20T06:44:00.003-07:00

Power Center Introduction

Is a single, unified enterprise data integration platform that allows companies and government
organizations of all sizes to access, discover,and integrate data from virtually any business
system, in any format, and deliver that data throughout the enterprise at any speed
An ETL Tool (Extract, Transform and Load)

Power Center Client Applications

Designer Tools – Create mappings

Mapping

A mapping is a set of source and target definitions linked by transformation
objects that define the rules for data transformation. Mappings represent the
data flow between sources and targets. When the Integration Service runs a
session, it uses the instructions configured in the mapping to read,
transform, and write data.
• Every mapping must contain the following components:
Source definition. Describes the characteristics of a source table or file.
Transformation. Modifies data before writing it to targets. Use different transformation objects to
perform different functions.
Target definition. Defines the target table or file.
Links. Connect sources, targets, and transformations so the Integration Service can move the
data as it transforms it.
• A mapping can also contain one or more mapplets. A mapplet is a set of transformations that you

Example

Give me an Excel file with Total Order Amount per Customer. I also need to know when this data was
extracted (date) and the customer type initial ( first letter of the customer type)
• Define the sources
• Orders
• Customers
• Define any required transformation
• Sum of order amount
• Get extracted date
• Get first letter of customer type
• Create the file

Migrating to an InfoSphere Warehouse instance that is installed on a different computer

2012-07-21T01:38:00.002-07:00

Migrating to an InfoSphere Warehouse instance that is installed on a different computer

You can migrate from Data Warehouse Edition V9.1.x to InfoSphere Warehouse when these products are installed on two separate computers. Assume that you want to migrate a Data Warehouse Edition V9.1.x instance that is installed on computer A to an InfoSphere Warehouse instance that is installed on computer B.

Before you begin

Back up the metadata and scheduler databases on computer A.
Copy all of the data warehouse projects that you want to migrate to computer B.
Copy all of the deployed data warehousing applications from computer A to the same location on computer B.
To deploy data warehousing applications, three directories are used:
- Application home directory
- Log directory
- Working directory
All the three directories must be created at exactly the same location on computer B. For example, if you have an application deployed on computer A at C:\application_dir\applicaton_1, then this directory becomes the application home directory. Assume that C:\log\ is the log directory, and C:\temp\working\ is the working directory. You must create these three directories on computer B, and copy all the files in these directories from computer A to computer B. Otherwise, the migrated application will not work. Note that each data warehouse application can have their own three directories. So, if you have 100 applications, there can be 300 different directories that you must copy to computer B. If some directories are not copied, you might see an error message in the migration log that indicates that certain files are missing during migration.

ProcedureTo complete the migration from computer A to computer B:

Restore the metadata and scheduler databases on computer B.
Migrate the WebSphere^® application profile. For detailed help on migrating the WebSphere application profile that is located on a separate computer, see the WebSphere Information Center.
Run the InfoSphere Warehouse Configuration Tool on computer B.
Specify the migration settings in the migration.properties file.
Run the migration script on computer B.