Trusted, Enterprise QlikViewreporting. data Integration and data Quality (It s all about data)

(1)

Trusted, Enterprise QlikView-

reporting with Informatica

data Integration and

data Quality

(

It’s all about data)

Arjan Hijstek

senior sales consultant Informatica Nederland bv [email protected] 06-22.454.327

(2)

2

Agenda

• Introduction Informatica

• Qlikview and reporting : data warehouse?

- What data do I get? Relationships? data profile! - (bad) data quality? data quality! - Extraction, Transform and Load of any data ETL!

• Qlikview and Informatica • Data Virtualization

- faster access to new data • Quick demo

(3)

Do you know Informatica?

(PowerCenter, DataQuality, other)

Do you use Informatica?

(Data Warehouse, Migration)

(4)

4

Informatica

The #1 Independent Leader in Data Integration

• Founded: 1993

• 2011 Revenue: $784 million • 6-year Average Growth Rate:

20% per year

• Employees: 2,554 • Partners: 400+

• Major SI, ISV, OEM and

On-Demand Leaders

• Customers: 4,630

• > 70% of the Global 500

• Customers in 82 Countries

• Direct Presence in 26 Countries

• # 1 in Customer Loyalty Rankings

(6 Years in a Row) $0 $100 $200 $300 $400 $500 $600 $700 $800 2005 2006 2007 2008 2009 2010 2011

(5)

Improve Decisions Business & Operational Intelligence Data Warehouse

Beyond Data Warehousing

Empowering the Data-Centric Enterprise

Improve Business Processes Improve Efficiency & Reduce Costs Mergers Acquisitions & Divestitures Acquire & Retain Customers Outsource Non-core Functions Governance Risk Compliance Increase Partner Network Efficiency Increase Business Agility Business Imperatives Application Portfolio Optimization Application Retirement Application Consolidation Customer, Supplier, Product Hubs BPO SaaS Risk Mitigation & Regulatory Reporting B2B Integration Zero Latency Operations IT Initiatives Data Services Data Migration & Archiving Master Data Management Data Synchronization B2B Data Exchange Data Consolidation Complex Event Processing Ultra Messaging

(6)

6

The Tradition Approach

87% of Enterprises Use Hand-Coding for Data Integration

75% of enterprises reported

increased maintenance costs

1 _{Forrester Research, The State Of Enterprise IT Budgets: 2008, March 27, 2008} 2 _{Forrester Research, “Addressing Data Integration Challenges with SOA”, 2007}

Data Warehousing Data Services Data Migration & Archiving Master Data Management Data Synchronization B2B Data Exchange Data Consolidation Complex Event Processing Ultra Messaging

(7)

The Informatica Approach

Comprehensive, Unified, Open and Economical platform

Data Warehousing Data Services Data Migration & Archiving Master Data Management Data Synchronization B2B Data Exchange Data Consolidation Complex Event Processing Ultra Messaging

(8)

(9)

Agenda

(10)

10

Reporting: do you see unexpected results/values?

(11)

Data Analysis

& Discovery

Using Informatica Analyst Tools

to Profile your Data

Increase productivity and efficiency by enabling the business to

proactively take responsibility for data quality and reduce their

reliance on IT.

Data

Steward

(12)

12

Agenda

(13)

Data Quality

Problem:

- many systems and data-sources having information about customers/products/suppliers etc.

- need to profile, standardize, cleanse, de-dup: automatically!

- otherwise wrong/strange results in Data Warehouse or in new systems after migration/conversion

- where ‘to do Data Quality’: at source? In Data Warehouse? - when is ‘data quality’ finished?

(14)

14

Reporting: do you have/see Data Quality issues?

How is it solved?

(15)

What data is missing?

How do you measure data quality?

What data gives conflicting information?

What data does not reflect reality or is out of date?

What data is not referenced or missing? What data or attributes

are repeated?

Completeness

What data is stored in non-standard formats?

Conformity

Consistency

Accuracy

Duplications

Integrity

(16)

16

Data Quality issues, examples

(17)

Frequent Requirements

Data Analysis

& Discovery

Parsing

and

Standardization

Address

Validation

Matching &

De-duplication

Monitoring

&

Reporting

And do this for all domains & data

types…

(18)

18

Using Informatica Analyst Tools

to Profile your Data

Data Analysis

& Discovery

Increase productivity and efficiency by enabling the business to

proactively take responsibility for data quality and reduce their

reliance on IT.

Data

Steward

(19)

Agenda

(20)

20

Data Integration / ETL

- Extraction, Transformation and Load

- For Data Warehousing, conversions, migrations, testdata-management, MDM, … - For Informatica this means:

- integration of Profiling, Data Quality and ETL

- independent vendor, so connectivity to almost all databases, incl. Teradata, Netezza, Greenplum etc.

- low or very high data volumes, batch and/or realtime - simple or complex environments

- no coding! Infrastructure-independent (database , OS) - multi-user, multi-project

(21)

Data Integration / ETL: data mapping

• Source, target & transformation blocks

• connectors

(22)

22

Transformation types

“Lego blocks”

Joiner

Expression

Sorter

Lookup

Aggregator

Normalizer

Router

Sequence

generator

Filter

Union

Update strategy

Transaction

control

Stored procedure

External

transformation

Java

transformation

Source qualifier

Association

transformation

Consolidation

transformation

Ranker

SQL

transformation

Web services

… and more

(23)

(24)

24

(25)

(26)

26

Agenda

(27)

(28)

28

Qlikview connector for

Informatica Powercenter

(29)

Agenda

(30)

30

Data Virtualization

Problem:

- Enterprise Data Warehouse takes 3-9 months (modeling, extract, reports/dashboards)

- what if new data from a new source needs to be added to a report?

- another 3+ months? Or just 1 day?

- (complex) operational reporting?

(31)

Virtual View

DATA CONSUMERS

(operational) DATA SOURCES

Portals

Messages

Cloud Unstructured Data Semi-structured Data

Application Database

Mainframe Flat Files Database

Solution with

Data Services

(Federation)

Daily extract (PowerCenter)

DWH / DM

SA/ODS Daily update to DWH / DM ‘operational extracts’ from DWH and/or even directly from Data Sources, accessible

via standard SQL (Reporting Tool)

(32)

32