Saturday, December 5, 2015

Principles for effective risk data aggregation and risk Reporting - Page 2


Principles for effective risk data aggregation and risk reporting - Basel III - Page1


Monday, November 30, 2015

Data Quality




CPPS -- Cleansing , Profiling , Parsing and Standarization
MEM --  Matching , Enrichment ,  Monitoring.


Data Profiling is a methodical approach to identify the data issues like inconsistency , Missing Data , duplicates etc.  A data Profiling will be carried out to analyze the health of the data ..

Data Quality Metrics --

Completeness , Conformity , Consistency ,  Duplicates, Accuracy , and  Validity .

IDQ has two types of transformations -- a. general (power center transformations) b. data quality transformations



Profiling Data Overview

Use profiling to find the content, quality, and structure of data sources of an application, schema, or enterprise. The data source content includes value frequencies and data types. The data source structure includes keys and functional dependencies.

 Analysts and developers can use these tools to collaborate, identify data quality issues, and analyze data relationships


Data profiling is often the first step in a project. You can run a profile to evaluate the structure of data and verify that data columns are populated with the types of information you expect. If a profile reveals problems in data, you can define steps in your project to fix those problems. For example, if a profile reveals that a column contains values of greater than expected length, you can design data quality processes to remove or fix the problem values.


A profile that analyzes the data quality of selected columns is called a column profile.

Note: You can also use the Developer tool to discover primary key, foreign key, and functional dependency relationships, and to analyze join conditions on data columns.

A column profile provides the following facts about data:

• The number of unique and null values in each column, expressed as a number and a percentage.
 • The patterns of data in each column, and the frequencies with which these values occur.
• Statistics about the column values, such as the maximum and minimum lengths of values and the first and last values in each column.
• For join analysis profiles, the degree of overlap between two data columns, displayed as a Venn diagram and as a percentage value. Use join analysis profiles to identify possible problems with column join conditions.

You can run a column profile at any stage in a project to measure data quality and to verify that changes to the data meet your project objectives. You can run a column profile on a transformation in a mapping to indicate the effect that the transformation will have on data

You can perform the following tasks in both the Developer tool and Analyst tool:

• Perform column profiling. The process includes discovering the number of unique values, null values, and data patterns in a column.
• Perform data domain discovery. You can discover critical data characteristics within an enterprise.
• Curate profile results including data types, data domains, primary keys, and foreign keys.
 • Create scorecards to monitor data quality.
 • View scorecard lineage for each scorecard metric and metric group.
• Create and assign tags to data objects.
• Look up the meaning of an object name as a business term in the Business Glossary Desktop. For example, you can look up the meaning of a column name or profile name to understand its business requirement and current implementation.


You can perform the following tasks in the Developer tool:

• Discover the degree of potential joins between two data columns in a data source.
• Determine the percentage of overlapping data in pairs of columns within a data source or multiple data sources.
• Compare the results of column profiling.
• Generate a mapping object from a profile.
• Discover primary keys in a data source.
• Discover foreign keys in a set of one or more data sources.
• Discover functional dependency between columns in a data source.
• Run data discovery tasks on a large number of data sources across multiple connections. The data discovery tasks include column profile, inference of primary key and foreign key relationships, data domain discovery, and generating a consolidated graphical summary of the data relationships.

You can perform the following tasks in the Analyst tool:

• Perform enterprise discovery on a large number of data sources across multiple connections. You can view a consolidated discovery results summary of column metadata and data domains.
• Perform discovery search to find where the data and metadata exists in the enterprise. You can search for specific assets, such as data objects, rules, and profiles. Discovery search finds assets and identifies relationships to other assets in the databases and schemas of the enterprise




Informatica Analyst Frequently Asked Questions


Can I use one user account to access the Administrator tool, the Developer tool, and the Analyst tool? Yes. You can give a user permission to access all three tools. You do not need to create separate user accounts for each client application.


Where is my reference data stored?

You can use the Developer tool and the Analyst tool to create and share reference data objects.
The Model repository stores the reference data object metadata. The reference data database stores reference table data values. Configure the reference data database on the Content Management Service.


Informatica Developer Frequently Asked Questions

What is the difference between a source and target in PowerCenter and a physical data object in the Developer tool?

In PowerCenter, you create a source definition to include as a mapping source. You create a target definition to include as a mapping target. In the Developer tool, you create a physical data object that you can use as a mapping source or target.

What is the difference between a mapping in the Developer tool and a mapping in PowerCenter?

A PowerCenter mapping specifies how to move data between sources and targets.
A Developer tool mapping specifies how to move data between the mapping input and output.
A PowerCenter mapping must include one or more source definitions, source qualifiers, and target definitions. A PowerCenter mapping can also include shortcuts, transformations, and mapplets.
A Developer tool mapping must include mapping input and output. A Developer tool mapping can also include transformations and mapplets.


The Developer tool has the following types of mappings:

• Mapping that moves data between sources and targets. This type of mapping differs from a PowerCenter mapping only in that it cannot use shortcuts and does not use a source qualifier.

• Logical data object mapping. A mapping in a logical data object model. A logical data object mapping can contain a logical data object as the mapping input and a data object as the mapping output. Or, it can contain one or more physical data objects as the mapping input and logical data object as the mapping output.

• Virtual table mapping. A mapping in an SQL data service. It contains a data object as the mapping input and a virtual table as the mapping output.

• Virtual stored procedure mapping. Defines a set of business logic in an SQL data service. It contains an Input Parameter transformation or physical data object as the mapping input and an Output Parameter transformation or physical data object as the mapping output.



What is the difference between a mapplet in PowerCenter and a mapplet in the Developer tool?

A mapplet in PowerCenter and in the Developer tool is a reusable object that contains a set of transformations. You can reuse the transformation logic in multiple mappings.

A PowerCenter mapplet can contain source definitions or Input transformations as the mapplet input. It must contain Output transformations as the mapplet output.

A Developer tool mapplet can contain data objects or Input transformations as the mapplet input. It can contain data objects or Output transformations as the mapplet output.

A mapping in the Developer tool also includes the following features:

• You can validate a mapplet as a rule. You use a rule in a profile.

• A mapplet can contain other mapplets.


What is the difference between a mapplet and a rule?

You can validate a mapplet as a rule. A rule is business logic that defines conditions applied to source data when you run a profile. You can validate a mapplet as a rule when the mapplet meets the following requirements:

• It contains an Input and Output transformation.

• The mapplet does not contain active transformations.

• It does not specify cardinality between input groups.

Sunday, November 22, 2015

Database Session Topics - Newbies

Database Session Topics
What is RDBMS – Abstraction Layers
Concepts
§  SQL
§  Relationships
§  Transactions
§  ACID – Atomicity Consistency Isolation Durability
§  CRUD
§  DATABASE – data file & log file
§  ODBC / JDBC
§  Database Objects
§  Table -- column row
§  View
§  Transaction Log
§  Trigger
§  Index
§  Stored procedure
§  Functions
§  Cursor
§  Indexes:  Primary key, foreign key 
Components
§  Concurrency control
§  Data dictionary
§  Query language
§  Query optimizer
§  Query plan
Conclusion
§  Data Models : ER Models / Dimensional Models

§  OLTP/OLAP/ETL

Friday, April 26, 2013

Interesting Things to check about Data and Political Campign (Jeremy Bird)

http://neworganizing.com/toolbox/?module=the-choice




http://www.hks.harvard.edu/syllabus/MLD-377.pdf



http://www.270strategies.com/


Wednesday, December 12, 2012

Scale Up (Vertical Scaling) - Scale Out (Horizontal Scaling)

Scale Up (Vertical Scaling) : Buy a New System and Increase the Hardware.
Scale Out (Horizontal Scaling) is done on two vectors
               a. Functional Scaling( Divide the tables depending on functionality into different databases)
               b. Sharding   (Same Functionality is divided into different databases i.e., adding one more dimension to the above Functional Group).

Both Horizontal Scaling approaches can be applied atonce as follows.

Tuesday, November 6, 2012

Changing Data Compression for a List of Partitions ; when Switchout is failed

Background :  I need to move the Partitioned data from my Production Server to Archive Server.
Following steps I follow
a. Switch out data from Main Table (Partioned) to a Temp table (Partitioned).
b. Move the Temp Table to a Temporary Database( ex: DBArchive)
c. Backup the Temporary Database( ex: DBArchive)
d. Restore the Temporary Database( ex: DBArchive) on Archive Server.
e. Merge the Partition Data to the Main Tables

when I am Switching out  to a Temp Table sometimes Switch out is failed because source Table (Main Table) partition compression is different from Target Table (Temp Table).

-- Changing Data Compression for a List of Partitions ; when Switchout is failed.
-- Find out the Compression in between Main table and Dump Table
-- If the Table Compression is different then How to Change the Compression in the Target Table.

Check Compression in between Main table and Dump Table

select distinct data_compression_desc From sys.partitions (nolock)
where OBJECT_NAME(object_id) = 'MainTable'
and partition_number >= 609
and partition_number <= 670
go

select distinct data_compression_desc From sys.partitions (nolock)
where OBJECT_NAME(object_id) = 'dumpTable'
and partition_number >= 609
and partition_number <= 670
go

-------------------


ALTER TABLE dumpTable REBUILD PARTITION =   WITH (DATA_COMPRESSION = NONE); 
In the above should be generated dynamically and will follow as follows.



SELECT TOP 500 ColumnName
INTO #1
FROM dbo.SampleTable (NOLOCK) --
go


WITH RowNumber as
(
SELECT ROW_NUMBER() Over (ORDER BY ColumnName) as rn
FROM #1
)

select 'ALTER TABLE dumpTable REBUILD PARTITION = ' + CONVERT(VARCHAR,rn) + ' WITH (DATA_COMPRESSION = NONE);'
From RowNumber
where rn between 62 and 426
go

Wednesday, October 31, 2012

Different Databases , Where it exactly Fits.



Databases One Size Does not Fit all
Credit goes to Tim Gasper Product Manager at Infochimps
From TechCrunch.com

Friday, September 28, 2012

Tuesday, August 21, 2012

Database Landscape - SQL & NOSQL Databases


http://www.alberton.info/nosql_databases_what_when_why_phpuk2011.html#.UNniIOTO0VA


Cassandra - A Decentrailzed Structured Storage System, Lakshman,Ladis

http://www.cs.cornell.edu/projects/ladis2009/papers/lakshman-ladis2009.pdf





Towards Robust Distributed Systems,Eric brew

http://www.cs.berkeley.edu/~brewer/cs262b-2004/PODC-keynote.pdf





Cassandra Expalined, Eric Evans

http://www.slideshare.net/jericevans/cassandra-explained





Introduction to Cassandra, Gary Dusbabek

http://www.slideshare.net/gdusbabek/introduction-to-cassandra-june-2010





Hbase,Ryan Rawson

http://www.slideshare.net/adorepump/hbase-nosql





CouchDB vs. MongoDB,Gabriele Lana

http://www.slideshare.net/gabriele.lana/couchdb-vs-mongodb-2982288





NoSQL Dabases,Main Dimitrov

http://www.slideshare.net/marin_dimitrov/nosql-databases-3584443



NoSQL for Dummies,Tobias Ivarsson

http://www.slideshare.net/thobe/nosql-for-dummies



NoSQL Database - Part 1 - Landscape , Vineet Gupta

http://www.vineetgupta.com/2010/01/nosql-databases-part-1-landscape/





Introduction to NoSQL Databases by Derek Stainer

http://www.allthingsdistributed.com/





---------------------------------------------------

BASE: An ACID Alternative, Dan Prichett

http://delivery.acm.org/10.1145/1400000/1394128/p48-pritchett.pdf?ip=171.159.64.10&acc=OPEN&CFID=155770146&CFTOKEN=16853736&__acm__=1355249710_c2c1438f6a022a378edebaaad8253a4b



HBase Architecture 101

http://www.larsgeorge.com/2009/10/hbase-architecture-101-storage.html



Dynamo: Amazon’s Highly Available Key-value Store

http://s3.amazonaws.com/AllThingsDistributed/sosp/amazon-dynamo-sosp2007.pdf



Bigtable: A Distributed Storage System for Structured Data

http://static.googleusercontent.com/external_content/untrusted_dlcp/research.google.com/en/us/archive/bigtable-osdi06.pdf




In-Memory Database

Book
In-Memory Data Management: An Inflection Point for Enterprise Applications
by Hasso Plattner ,  Alexander Zeier

---------------------------------------------------------------------

To Learn MongoDB

https://education.10gen.com/

-----------------------------------------------------------------------

Good Blog about HADOOP

http://hadoopblog.blogspot.com/

--------------------------------------------------------------------

Microsoft related
https://www.microsoftvirtualacademy.com/Studies/SearchResult.aspx
http://www.microsoft.com/bigdata
http://msbiacademy.com/
http://bradmcgehee.com



-----------------------------------------------------------------------

http://www.ibm.com/developerworks/data/library/techarticle/dm-1209hadoopbigdata/



Following is a collection from Linked In

Google's MapReduce Paper:
http://research.google.com/archive/mapreduce.html 
* File System (GFS) paper:

http://static.googleusercontent.com/external_content/untrusted_dlcp/research.google.com/en//archive/gfs-sosp2003.pdf 
* Google Bogtable:

http://research.google.com/archive/bigtable.html


------------
Jiri Kaplan • Hi,

Some useful links:
http://www.cloudera.com/resources/training/ (basics of hadoop - video presentations)
http://hadoop.apache.org/common/docs/r1.0.3/

If you prefer video and just use cases, hints, news and general talk about hadoop some presentations could be useful:
http://www.hadoopworld.com/agenda/http://hadoopsummit.org/

Best way to learn it? Download it and run it in local mode or pseudo-cluster mode imo. See http://hadoop.apache.org/common/docs/r0.20.2/quickstart.html or https://ccp.cloudera.com/display/DOC/Documentation (look for quick start guide)

Learn hadoop by exampes:
hadoop-examples and hadoop-test jars are good for first touch with Hadoop jobs
$HADOOP_HOME/src/test* there are sources of examples (Package org.apache.hadoop.examples in API)

Good luck.

-------------

Lou Dasaro •

Here are some videos I collected.
The last one does a pretty good job of explaining
the hadoop ecosystem.

See http://www.youtube.com/playlist?list=PLF82F6499E89E1BAE&feature=mh_lolz

------------
Tudor Lapusan • Hi,
I think the best way to start learning hadoop is to :
1. - make a single/multiple cluster, run wordcount example.

http://www.michael-noll.com/tutorials/running-hadoop-on-ubuntu-linux-single-node-cluster/
1. - read more about hadoop core functionalities.

http://developer.yahoo.com/hadoop/tutorial/

bye!

------------


for a complete round up of study materials for Hadoop MapReduce and HDFS, please go to http://lnkd.in/jycmQd

Copying the content from the above link -
Studying Hadoop or MapReduce can be a daunting task if you get your hand dirty at the start.
Some of the prerequisites for learning Hadoop are having a good experience in Java. Good Analytical skills help a lot as well and final secret sauce for being successful is – you need to be motivated to self learn lot of things in the bigdata arena.

For Learning Hadoop ,I followed the schedule as follows :

Start with very basics of MR with http://code.google.com/edu/parallel/dsd-tutorial.htmlhttp://code.google.com/edu/parallel/mapreduce-tutorial.html
Then go for the first two lectures in http://www.cs.washington.edu/education/courses/cse490h/08au/lectures.htm A very good course intro to MapReduce and Hadoop.
Read the seminal paper http://labs.google.com/papers/mapreduce.html and its improvements in the updated version http://www.cs.washington.edu/education/courses/cse490h/08au/readings/communications200801-dl.pdf
Then go for all the other videos in the U.Washington link given above.
Try youtubing the terms Map reduce and hadoop to find videos by ORielly and Google RoundTable for good overview of the future of Hadoop and MapReduce
Then off to the most important videos -
Cloudera Videos
http://www.cloudera.com/resources/?media=Video
and
Google MiniLecture Series
http://code.google.com/edu/submissions/mapreduce-minilecture/listing.html

Along with all the Multimedia above we need good written material
Documents:

Architecture diagrams at http://hadooper.blogspot.com are good to have on your wall
Hadoop: The definitive guide goes more into the nuts and bolts of the whole system where as Hadoop in Action is a good read with lots of teaching examples to learn the concepts of hadoop. Pro Hadoop is not for beginners
pdfs of the documentation from Apache Foundation
http://hadoop.apache.org/common/docs/current/
and http://hadoop.apache.org/common/docs/stable/
will help you learn as to how model your problem into a MR solution in order to gain the advantages of Hadoop in total.
HDFS paper by Yahoo! Research is also a good read in order to gain in depth knowledge of hadoop
Subscribe to the User Mailing List of Commons, MapReduce and HDFS in order to know problems, solutions and future solutions.
Try the http://developer.yahoo.com/hadoop/tutorial/module1.html link for beginners to expert path to Hadoop

In Addition following 3 books are good resources:

Hadoop – Definitive Guide : Good info on Internals
Hadoop in Action : Good Programming Guide
Pro Hadoop : Overall good book with very good explanation
to the advanced concepts.

For Any Queries …
Contact Apache, Google, Bing, Yahoo!

And for setting up a single node hadoop setup and setting up eclipse for Hadoop Programming, please go to http://orzota.com/blog/single-node-hadoop-setup-2/ and http://orzota.com/blog/eclipse-setup-for-hadoop-development/

--------------------


Joey Calca • I gave a speech at DefCon 17 that was 40 minutes
long about how Hadoop works as well as a look at some sample code
and what it does.

You can find the video of the speech in this playlist (you can skip the first video, its just a teaser from the end of the speech)

https://www.youtube.com/watch?v=JYACdhxsUNs&list=PL64E697915C5C24FB&feature=plcp

The Source Code from the presentation and a simple breakdown of what it does can be found on the project page here:

http://hackedexistence.com/project-netflix.html

Hope this helps



Thursday, August 9, 2012

Synch up production SSAS cube to TFS.

Steps Synch up production SSAS cube to TFS.


1. In Visual Studio , File -> New -> Project -> Import Analysis Services 2008 Project -> select the temporary folder path and generate Project file

2. Go to TFS get the Latest version and check out all the files within the cube folder.

3. Now copy temporary folder path (step 1 above) to the TFS folder path

4. In Visual Studio, File -> Source Control -> Change Source Control -> Bind the TFS Path.

5. Check in Solution file.

6. If you still see few files which have not bind to TFS , then go to TFS folder Path, click add file , select the files which are not bind and then check in.

7. If you still see couple of files got deleted then deleted the files in the TFS folder Path and then check in.

Wednesday, May 23, 2012

Partitioned Table / Non Partitioned Table with Logical and Physical File Name

select distinct PartitionedTable = OBJECT_NAME(SI.object_id), sysindexes_data_spaceId = SI.data_space_id , destination_data_spaces_data_spaceId = DDS.data_space_id, PhysicalFileName = DF.physical_name , LogicalFileName = DF.name from sys.indexes AS SI (nolock) -- Partitioned Table contains Partition schema data_space_id -- Non-Partitioned Table contains File Group data_space_id join sys.data_spaces AS DS (nolock) -- Data_space_id info for Partition schema and File Group -- Determines PS or FG on DS.data_space_id = SI.data_space_id join sys.destination_data_spaces AS DDS -- Partition schema (Data_space_id) inturn linked to File Group (Data_space_id) on DS.data_space_id = DDS.partition_scheme_id join sys.database_files AS DF -- File Group data_space_id with Physical file name on DF.data_space_id = DDS.data_space_id AND DF.type = 0 where DS.type = 'PS' and OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' and SI.index_id IN(0,1) order by DDS.data_space_id go select distinct PartitionedTable = OBJECT_NAME(SI.object_id) ,DS.data_space_id ,DF.physical_name from sys.indexes AS SI (nolock) join sys.data_spaces AS DS (nolock) on DS.data_space_id = SI.data_space_id join sys.database_files AS DF on DF.data_space_id = DS.data_space_id AND DF.type = 0 where DS.type = 'FG' and OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' and SI.index_id IN(0,1) --and DS.data_space_id IN ( 65606,65607) order by DS.data_space_id go

Tuesday, March 20, 2012

In SharePoint 2010 Integrated Mode - Reporting Services does not display Hyperlink for Text Box Properties with Go to URL Action.

When using Reporting Services in Sharepoint Integrated mode a Text box label with Go to URL action will not render as a hyperlink for VALID internal links.

Notice that the links work in Visual Studio Preview mode for the report. It's only after you deploy the report that all valid internal links are stripped.

Here is a solution (someone else's idea)

While designing the report:

Open up a text box properties window
Go to Action
Enable Jump to URL
In the URL enter ="javascript:void window.location.href='http://xxx/xxx/xxxxx.docx')"

Friday, March 9, 2012

Using an HTTP Module to assist in adjusting the value of aspnet:MaxHttpCollectionKeys imposed by MS11-100

After Sharepoint Upgarded to 2010. one of SSRS report stopted working because Parameter value is limited to 1000. To resolve please follow the following steps.






http://blogs.msdn.com/b/paulking/archive/2012/01/16/using-an-http-module-to-assist-in-adjusting-the-value-of-aspnet-maxhttpcollectionkeys-imposed-by-ms11-100.aspx

Main Points.

1. Build MS11-1000Helper.DLL (download from the above link) and place it in your web application’s Bin directory

2. Add the following to your web.config file to enable the HTTP Module

system.web
httpModules
add type="MS11_100_Helper.CounterModule, MS11-100-Helper" name="CounterModule"
/httpModules
/system.web

3. Add the following to your appSettings in web.config file


appSettings
add key="aspnet:MaxHttpCollectionKeys" value="9999"
appSettings