Check Foreign characters support on Unix machine and Informatica.

If you want to test all the foreign characters supported on your machine then checking the encodings supported on your machine helps. This kind of test might be required when you copy a file into your unix machine and see that some of the characters are not getting displayed. BEncodings such as ISO8859-1 and UTF-8, UTF-16 can support french characters. ISO-8859-15 supports some of the characters such as the pound sign that is not supported by ISO8859-1. Below is the test results on the solaris machine.

Try the command locale -a to check all the character encodings.

mymachine>locale -a
en_CA
en_CA.ISO8859-1
en_CA.UTF-8
en_US
en_US.ISO8859-1
en_US.ISO8859-15
en_US.ISO8859-15@euro
es
es_MX
es_MX.ISO8859-1
es_MX.UTF-8
fr
fr_CA
fr_CA.ISO8859-1
fr_CA.UTF-8
iso_8859_1

To change the encoding try the following commands:

mymachine>export LC_CTYPE=fr_CA.ISO8859-1

mymachine>dumpcs
:::::::::::::::::::::::::::::
LC_CTYPE:fr_CA.ISO8859-1 CS:0
::::::::::::::::::::::::::::
    ! " # $ % & ' ( ) * + , - . / 0 1 2 3 4 5 6 7 8 9 : ; < = > ?
@ A B C D E F G H I J K L M N O P Q R S T U V W X Y Z [ \ ] ^ _
` a b c d e f g h i j k l m n o p q r s t u v w x y z { | } ~*

dumpcs command above is used to display all the characters supported by an encoding.


In Informatica, code page setting determines the characters supported by informatica. If any character is not supported by Informatica try changing the code page setting on the source or target setting in the informatica setting. For flat files we should be able to edit the code page in the advance file properties in the session.
In general, the code page settings are done at the server level using the Informatica admin or while creating the informatica repository. To test if Informatica supports french character, process a simple text file using a simple informatica mapping and write the flat file contents to a target flat file. If you can see all the characters in the target file then informatica supports those characters.


See Also:
http://dwbitechguru.blogspot.ca/2014/07/issue-xml-reader-error.html

A Typical ETL (Extract, Transform, Load) Architecture for Data Integration

A Typical ETL (Extract, Transform, Load) Architecture for Data Integration

It is pretty much a common question in interviews to ask about ETL architecture. Let this not be confused with the various data warehouse architectures which is a bigger universe and we will discuss about data warehouse architecture in our next post. The diagram below is simple illustration of the ETL architecture. The ETL tool used here can be Informatica or any other tool such as SSIS or Data Stage.

The diagram below shows that ETL as a data integration tool can pull data from multiple sources. The source can be flat files, VSAM, web services, XML, or relational databases such as Oracle, DB2,SQL server etc.

 

Landing Area: The data from these sources in a normal ETL architecture is loaded first into a landing area. This landing area will be table(s) in some database that holds the source data as is. Not many transformations is applied to data while being loaded into landing area. If this involves daily loads, the batch keys can be used to identify the load for a particular day. Once the data is in the landing area, various transformations and business logic can be applied and data moved to staging area.

Staging Area: The staging area contains data to which the application logic is applied. Data from a single table or multiple tables from landing area can be combined to create table(s) in the staging area. The data is transformed, aggregated, filtered, and all the business logic applied before loading the data into staging area. For example: If there are daily loads and if the data from the latest batch updates the historical data, then the staging area is supposed to hold the most latest version. If data from multiple columns in landing table are used to derive a column for a business, then this transformation is done in the mappings that loads data to the staging area.

There can be instances where landing and staging are merged into one table if separating them is not required. Having a separate landing area helps to recreate the staging area any time from all the data in the landing area. So that means that landing area has all the historical data which is data from the first load upto to the latest batch of data.

Datawarehouse Area: Call it the data warehouse or datamart area is the place that business users and the reporting layer have access to. The data in the data mart is the final version of the data. Usually data from staging to datamart is passed with minimal transformation. It is a good practice to make data in datamart recoverable any time from the data in staging.Landing and staging are intermediate areas before the data lands up in datamarts.

This is quiet simple isn’t it? If any questions, drop a comment below.


See also: http://dwbitechguru.blogspot.ca/2014/07/business-intelligence-simple-definition.html

SAP Business Object (BO) Reports Testing

The testing of business object reports or in general any report is discussed below:

The following testing is required for business object reports

1) Data validation: this is to test if the report displays the data that is required of it. The data is formatted properly with cross tabs, blocks, sections, filters, etc.

2) The format of the report is correct i.e. the orientation, font size, page size, page orientation.

3) Check the print settings. Print the report to check if it prints property. Check if the report can be saved as excel/html/pdf format.

4) Regression test by comparing to older version of the report.

How to test the reports:
1) Scope definition: Testing scope varies depending on the changes involves i.e. if it is a new report or updates to existing report.

2) Regression testing: If is updates to existing report then refresh the older version of the report to compare that the changes have not impacted the existing report.Microsoft excel or beyondcompare are some of the tools that are used to compare two reports and check for any discrepencies.

4) Data validation: SQLs and eye ball testing can be used to validate the report data. Take sample of data and test it against operational/data warehouse data to check the data is accurate. 

5) Universe testing: If testing scope involves testing the universe, then all the objects and classes that are required as per BRD can be checked to check they exist. Also, existence of any loops and any universe validation issues can be checked and resolved.

6) Implementation testing: After implementation, the report can be refreshed in UAT/prod environment to check if it is still working and displaying data. Check if the report is getting refreshed as per schedule and the report is getting published/emailed as per requirements.

See Also: http://dwbitechguru.blogspot.ca/2014/07/etl-testing-challenges.html

Informatica - ETL testing -Challenges

ETL testing is a bit complicated since the complexity of testing cannot be hidden behind button or GUIs. The tester will have write SQLs on variety of databases, some times write own mappings to test the ETL code since ETL involves integration of data from heterogeneous data sources and transformation that cannot be easily coded using SQLs.

Things that might have to be tested during ETL testing:

1) The data from multiple sources is properly integrated, transformed, aggregated, sorted etc and loaded into the target database.
2) The data is properly landed, staged and loaded into mart tables.
3) Proper batch keys are inserted for delta loads and delta loads are working properly.
4) The data is inserted/updated in the target table.
5) Historical loads and daily loads are working as expected.
6) Test of events, commands tasks, schedule and notification.
7) Validation of error cases and rejected data.
8) Performance testing
9) Impact testing: Testing interfaces to upstream and downstream processes.
10) Regression testing: test if the existing data/tables/etc are not broken.

Complication in ETL testing:

a) Availability of all test data and creation of test data for all the test cases.
b) Understanding the complexities of the ETL tool and data warehousing.
c) Working with data spread across multiple databases.

Just out of interest, If you want to check out different roles in data warehousing, please read:
http://dwbitechguru.blogspot.ca/2015/09/career-and-hot-jobs-in-data-warehousing.html
How ETL testing can be done:

a) If you are testing informatica code and if testing involves only a few impacted sessions, a separate workflow can be created just for the purpose of testing. This avoids searching for the right session to run to test the code.

b) Perform a run of the ETL jobs for historical data and for daily loads separately and test both loads.

c) Test the load of data into landing area. If this is working properly, then it is easy to write SQLs to validate data against landing area since data from multiple sources are brought into one landing area.

d) If jobs involve files, make sure the files are deleted/archived and folders ready for next load.

e) Look at the ETL design document to understand how ETL is designed.

f) Make sure the ETL code can handle the data volume and meet all the performance parameters.

g) There might be some ETL testing tools that could make testing easier. Informatica has a set of etl testing tools of their own. 

See also:
http://dwbitechguru.blogspot.ca/2014/07/business-object-reports-testing.html

The power of IBM Netezza appliance

Netezza is a powerful data warehouse appliance that can handle gigabyte volume of data and used widely is many medium and large scale organizations. Some very interesting facts about Netezza is below:

Good stuff:
1) Netezza is indeed very powerful for OLAP and ETL purpose. The configuration I'm used to can handle queries on tables that have billions of records.  Netezza uses asymetric massive parallel processing and have multiple processing units to make query processing faster and run in parallel. Ofcourse, proper joins and distribution keys have to be set  when dealing with billions of records.


2) No tedious performance tuning required like in oracle or sql server. i.e. stuff like adding indexes, creating partitioning etc is not required. It allows adding primary keys but does not enforce that constrain and leaves it to the tools that loads data into netezza. Only distribution keys have to be set properly on the tables.

3) The above reduces the administration effort. Apparently, Netezza is promoted more like a no DBA tool. No software needs to be installed and comes installed with the netezza appliance.

4) Migration of data from one environment to another is very easy using tools such as nz_migrate, nz_sql, nzload.

5) NZ_Migrate is a handy tool to migrate bunch of tables  in one shot to another environment along with creation of the tables in the new environment. NZ_Migrate and NZ_UNLOAD are multi-threaded and can load /unload data using many threads.

6) Bulk loads are very fast. Netezza has its own Powerconnect driver to power these operations.

7) NA admin tool provides a good interface to administer the tool and as well see the resource availability and active/inactive queries.

Check also: Extract, Load, Migrate in Netezza:
http://dwbitechguru.blogspot.ca/2014/11/extract-load-migrate-filesdata-from.html

Some not so great stuff:

1) In earlier version, if we have to add a column then we have to drop a table and recreate it.

2) Same thing with views. We had to drop a view if the column data type changes in the base table.

3) It does not support sub queries in the select statement though this is not a big problem in ETL tools. Not sure if this is supported is latest versions.

4) How scalable is netezza? What to do when the volume of data or number of queries that need to run increase to a point where existing netezza appliance cannot support? This might mean buying more hardware to upgrade netezza. Compared to solutions like Hadoop or Amazon redshift, netezza might be not be very scalable.

Informatica ETL tool capabilities

If you are designing a solution and need to know when you can use Informatica or for that matter any ETL tool, the below list can be useful:

a) Data Integration: Informatica is a data integration tool .i.e. it can pull data from multiple heterogenous sources and extract, tranform, and load the data to again another set of data targets.
The source/target database can be mysql, oracle, sql server, Netezza, Db2, Flat files, etc.

The source can also be web service that provides data in XML format. Informatica can also generate files in XML format.

The source can be the main frame VSAM files generated by main frames.

There can be data also read from cloud such as Amazon red shift. Big data integration are also available.

b) Informatica is pretty good in reading from flat files and loading data into database or converting it to flat files or xml feeds.

c) Informatica is a tool that can be used to apply lot of transformation logic. This tranformation logic could be something simple like changing a data type or doing some arithmetic using data, aggregating, sorting, ranking, normalizing, etc or complex such as xml parsing.

d) Informatica workflows can also be called as web service i.e. you can create web services from Informatica.

e) Informatica can be used to execute commands on the host machine for any purpose such a sftpign a file, moving a file, etc. It can wait on events such a touch files or completion of jobs.

f) Informatica comes with a basic scheduler that can be used to schedule the jobs. Also, provides various notification options for failures, success, job completion etc using emails etc.

g) Informatica can be used to sequence jobs, apply conditions between each job i.e. it can be used to create workflows where each job gets executed one after another in a defined sequence.

h) Informatica can be used to create operation data warehouses, datamarts, star schemas, slowly changing dimensions, type1/type2/type3 dimensions etc/.

Other tools from Informatica such as IDQ and MDM can be used for address validation and master data management respectively. Informatica cloud allows pushing jobs to the cloud.

Informatica might not do when when do..while or for ..loop kind of logic is required to handle data. However using java transformations or other workaround this kind of logic can be implemented in Informatica.

See also: http://dwbitechguru.blogspot.ca/2014/07/performance-tuning-in-informatica.html

Open Source Data Warehousing and BI world.

If we have to design a data warehousing architecture with all open source systems then checking the below alternatives could be a good start:

Open source databases:

MySQL: probably the most heard of database and preferred for OLTP application and backends for many web servers. Can run on multiple operating systems.

PostgreSQL: Apparently more advanced than MySQL and can run provide better performance for huge queries.

Hadoop/Hive: Recently Hadoop/big data is the buzzword in data warehousing world. Hadoop from Apache can be a good alternative to store huge amount of data and run batch processing on huge volumes of data. Hadoop is very scalable and you can scale it with commodity hardware. Hive can be used for data warehousing, PIG for storing data along with some ETL processing, an similarly HBASE for storing and manipulating data.

Open source ETL tools:

Pentaho Kettle: has a bigger user community than other open source tools and a good GUI for ETL and data quality.
Talend and CloverETL are apparently both eclipse based tools and generate Java code for ETL. Both have GUI support.
Pentaho and talend can be integrated with big data.

Open Source Reporting tools:

Jaspersoft, BIRT, Pentaho BI suits are some of open source reporting tools.