Category Archives: Big Data

Data Governance Navigating the Information Value Chain Demystifying the path forward

Leave a reply

The challenge for businesses is to seek answers to questions, they do this with Metrics (KPI’s) and know the relationships of the data, organized by logical categories(dimensions) that make up the result or answer to the question. This is what constitutes the Information Value Chain

Navigation

Let’s assume that you have a business problem, a business question that needs answers and you need to know the details of the data related to the business question.

Information Value Chain

Information Value Chain

 

  • Business is based on Concepts.

 

  • People thinks in terms of Concepts.

 

  • Concepts come from Knowledge.

 

  • Knowledge comes from Information.

 

  • Information comes from Formulas.

 

  • Formulas determine Information relationships based on quantities.

 

  • Quantities come from Data.

 

  • Data physically exist.

In today’s fast-paced high-tech business world this basic navigation (drill thru) business concept is fundamental and seems to be overlooked, in the zeal to embrace modern technology

 

In our quest to embrace fresh technological capabilities, a business must realize you can only truly discover new insights when you can validate them against your business model or your businesses Information Value Chain, that is currently creating your information or results.

 

Today data needs to be deciphered into information in order to apply formulas to determine relationships and validate concepts, in real time.

 

We are inundated with technical innovations and concepts it’s important to note that business is driving these changes not necessarily technology

 

Business is constantly striving for a better insights, better  information and increased automation as well as the lower cost while doing these things several of these were examined and John Thuma’s‘  latest article

 

Historically though these changes were few and far between however innovation in hardware storage(technology) as well as software and compute innovations have led to a rapid unveiling of newer concepts as well as new technologies     

 

 

Demystifying the path forward.

In this article we’re going to review the basic principles of information governance required for a business measure their performance. As well as explore some of the connections to some of these new technological concepts for lowering cost       

To a large degree I think we’re going to find that why we do things has not changed significantly it’s just how, we know have different ways to do them.

 

It’s important while embracing new technology to keep in mind that some of the basic concepts, ideas, goals on how to properly structure and run a business have not changed even though many more insights and much more information and data is now available.

 

My point is in the implementing these technological advances could be worthless to the business and maybe even destructive, unless they are associated with a actual set of Business Information Goals(Measurements KPI’s) and they are linked directly with understandable Business deliverables.

 

And moreover prior to even considering or engaging a data science or attempt data mining you should organize your datasets capturing the relationships and apply a “scoring” or “ranking” process and be able to relate them to your business information model or Information Value Chain, with the concept of quality applied real time.  

 

The foundation for a business to navigate their Information Value Chain is an underlying Information Architecture. An Information Architecture typically, involves a model or concept of information that is used and applied to activities which require explicit details of complex information systems.

 

Subsequently a data management and databases are required, they form the foundation of your Information Value Chain, to bring this back to the Business Goal. Let’s take a quick look at the difference between relational database technology and graph technology as a part of emerging big data capabilities.

 

However, considering the timeframe for database technology evolution, has is introduced a cultural aspect of implementing new technology changes, basically resistance to change. Business that are running there current operations with technology and people form the 80s and 90s have a different perception of a solution then folks from the 2000s. 

 

Therefore, in this case regarding a technical solution “perception is not reality”, awarement is. Business need to find ways to bridge the knowledge gap and increase awarement that simply embracing new technology will not fundamentally change the why a business is operates , however it will affect how.  

 

Relational databases were introduced in 1970, and graph database technology was introduced in the mid to 2000

 

There are many topics included in the current Big Data concept to analyze, however the foundation is the Information Architecture, and the databases utilized to implement it.

 

There were some other advancements in database technology in between also however let’s focus on these two

History

 

1970

In a 1970s relational database, Based on mathematical Set theory, you could pre-define the relationship of tabular (tables) , implement them in a hardened structure, then query them by manually joining the tables thru physically naming attributes and gain much better insight than previous database technology however if you needed a new relationship it would require manual effort and then migration of old to new , In addition your answer it was only good as the hard coding query created

2020

In  mid-2000’s the graph database was introduced , based on graph theory, that defines the relationships as tuples  containing nodes  and edges.  Graphs represent things and relationships events describes connections between things, which makes it an ideal fit for a navigating relationship. Unlike conventional table-oriented databases, graph databases (for example Neo4J, Neptune) represent entities and relationships between them. New relationships can be discovered and added easily and without migration, basically much less manual effort. 

 

Nodes and Edges

Graphs are made up of ‘nodes’ and ‘edges’. A node represents a ‘thing’ and an edge represents a connection between two ‘things’. The ‘thing’ in question might be a tangible object, such as an instance of an article, or a concept such as a subject area. A node can have properties (e.g. title, publication date). An edge can have a type, for example to indicate what kind of relationship the edge represents.

Takeaway.

 

The takeaway there are many spokes on the cultural wheel, in a business today, encompassing business acumen, technology acumen and information relationships and raw data knowledge and while they are all equally critical to success, the absolute critical step is that the logical business model defined as the Information Value Chain is maintained and enhanced.

 

It is a given that all business desire to lower cost and gain insight into information, it is imperative that a business maintain and improve their ability to provide accurate information that can be audited and traceable and navigate the Information Value Chain Data Science can only be achieved after a business fully understand their existing Information Architecture and strive to maintain it.

 

Note as I stated above an Information Architecture is not your Enterprise Architecture. Information architecture is the structural design of shared information environments; the art and science of organizing and labelling websites, intranets, online communities and software to support usability and findability; and an emerging community of practice focused on bringing principles of design, architecture and information science to the digital landscape. Typically, it involves a model or concept of information that is used and applied to activities which require explicit details of complex information systems.

In essence, a business needs a Rosetta stone in order translate past, current and future results.

 

In future articles we’re going to explore and dive into how these new technologies can be utilized and more importantly how they relate to all the technologies.

Merry Christmas Data Classification, Feature Engineering , Data Governance. ‘How to’ do it and some code take a look

Leave a reply


I was heavily involved in business intelligence, data warehousing and data governance as of several years ago and recently have had many chaotic personal challenges, upon returning to professional practice I have discovered things have not changed that much in 10 yearsagovernance The methodologies and approaches are still relatively consistent however the tools and techniques have changed and In my opinion not for the better, without focusing on specific tools I’ve observed that the core to data or MDM is enabling and providing a capability for classifying data into business categories or nomenclature.. and it has really not improved.

  • This basic traditional approach has not changed, in essence man AI model predicst a Metric and is wholly based on the integrity of its features or Dimensions.



Therefore I decided, to update some of the techniques and code patterns, I’ve used in the past regarding the information value chain and or record linkage , and we are going to make the results available with associated business and code examples initially with SQL Server and data bricks plus python

My good friend, Jordan Martz of DataMartz fame has greatly contrinuted to this old mans BigData enlightenment as well as Craig Campbell in updating some of the basic classification capabilities required and critical for data governance. If you would like a more detailed version of the source as well as the test data, please send me an email at iwhiteside@msn.com. Stay tuned for more update and soon we will add Neural Network capability for additional automation of “Governance Type” automated classification and confidence monitoring.

Before we focus on functionality let’s focus on methodology


Initially understand key metrics to be measured/KPI‘s their formulas and of course teh businesse’s expectation of their calculations

Immediately gather file sources and complete profiling as specified in my original article found here

Implementing the processes in my meta-data mart article would provide numerous statistics regarding integers or float field however there are some special considerations for text fields or smart codes

Before beginning classification you would employ similarity matching or fuzzy matching as described here



As I said I posted the code for this process on SQL Server Central 10 years ago here is s Python Version.

databricks-logo Roll You Own – Python Jaro_Winkler(Python)

databricks-logoroll You Own – Python Jaro_Winkler(Python)
Import Notebook
Step 1a – import pandas
import pandas

Step 2 – Import Libraries

libraries from pyspark.sql.functions import input_file_name

from pyspark.sql.types import *
import datetime, time, re, os, pandas

ML Libraires

from pyspark.ml.feature import RegexTokenizer, StopWordsRemover, NGram, HashingTF, IDF, Word2Vec, Normalizer, Imputer, VectorAssembler
from pyspark.ml import Pipeline

import mlflow

from mlflow.tracking import MLFlowClient

from sklearn.cluster import KMeans
import numpy as np
Step 3 – Test JaroWinkler

JaroWinkler(‘TRAC’,’TRACE’)
Out[5]: 0.933333
Step 4a =Implement JaroWinkler(Fuzzy Matching)

%python

def JaroWinkler(str1_in, str2_in):

if(str1_in is None or str2_in is None):
return 0

tr=0
common=0
jaro_value=0
len_str1=len(str1_in)
len_str2=len(str2_in)
column_names=[‘FID’,’FStatus’]

df_temp_table1 = pandas.DataFrame(columns=column_names)
df_temp_table2 = pandas.DataFrame(columns=column_names)

#clean_string(str1_in)
#clean_string(str2_in)

if len_str1 > len_str2:
swap_len=len_str2
len_str2=len_str1
len_str1=swap_len
swap_str=str1_in
str1_in=str2_in
str2_in=swap_str

max_len=len_str2

iCounter=1

while(iCounter <= len_str1):
df=pandas.DataFrame([[iCounter,0]], columns=column_names)
df_temp_table1=pandas.concat([df_temp_table1,df], ignore_index=True)
iCounter=iCounter+1

iCounter=1

while (iCounter <= len_str2):
df=pandas.DataFrame([[iCounter,0]], columns=column_names)
df_temp_table2=pandas.concat([df_temp_table2,df], ignore_index=True)
iCounter=iCounter+1

iCounter=1
m=round((max_len/2)-1,0)
i=1

while(i <= len_str1):
a1=str1_in[i-1]

if m >= i:
  f=1
  z=i+m 
else:
  f=i-m
  z=i+m 
if z > max_len:
  z=max_len
while (f <= z):
  a2=str2_in[int(f-1)]
  if(a2==a1 and df_temp_table2.loc[f-1].at['FStatus']==0):
    common = common + 1
    df_temp_table1.at[i-1,'FStatus']=1
    df_temp_table2.at[f-1,'FStatus']=1 
    break
  f=f+1
i=i+1

i=1
z=1
while(i <= len_str1): v1Status=df_temp_table1.loc[i-1].at[‘FStatus’] if(v1Status==1): while(z <= len_str2): v2Status=df_temp_table2.loc[z-1].at[‘FStatus’] if(v2Status==1): a1=str1_in[i-1] a2=str2_in[z-1] z=z+1 if(a1 != a2 ): tr = tr+0.5 break break i=i+1 wcd = 1.0/3.0 wrd = 1.0/3.0 wtr = 1.0/3.0 if (common != 0): jaro_value = (wcd * common)/ len_str1 + (wrd * common) / len_str2 + (wtr * (common – tr)) / common return round(jaro_value,6) Step 4b – Register JaroWinkler spark.udf.register(“JaroWinkler”, JaroWinkler) Out[6]:
Step8a – Bridge vs Master vs AssociativeALL

%sql
DROP TABLE IF EXISTS NameAssociative;
CREATE TABLE NameAssociative;

SELECT
Name
,NameInput

,sha2(replace ( NameLookput,’%[^a-Z0-9, ]%’,’ ‘) , 256) as NameLookupCeaned ,a.NameLookupKey
,sha2(replace( NameInput,’%[^a-Z0-9, ]%’,’ ‘) , 256) as NameInput,b.NameInputKey
,JaroWinkler(a.NameLookup, b.NameInput) MatchScor
,RANK() OVER (Partition by a.DetailedBUMaster ORDER BY JaroWinkler(a.NameLookupCleande, b.NameInputCleaned) DESC) NameLookup,b.NameLookupKey
FROM NameInput as a
CROSS JOIN NameLookup as b

Self Service Semantic BI (Business Intelligence) Concept

Leave a reply

metadatamarttripleWe want to build an Enterprise Analytical capability by integrating the concepts for building a Metadata Mart with the facilities for the Semantic Web

 

    • Metadata Mart Source
      • (Metadata Mart as is) Source Profiling(Column, Domain & Relationship)
    • +
      • (Metadata Mart Plus Vocabulary(Metadata Vocabulary)) Stored as Triples(subject-predicate-object) (SSIS Text Mining)
    • +
      • (Metadata Mart Plus)Create Metadata Vocabulary following RDFa applied to Metadata Mart Triple(SSIS Text Mining+ Fuzzy (SPARGL maybe))
    • +
    • Bridge to RDFa – JSON-LD via Schema.org
      • Master data Vocabulary with lineage (Metadata Vocabulary + Master Vocabulary) mapped to  MetaContent Statements)) based on person.schema.org
      • Creates link to legacy data in data warehouse
      • +RDFa applied to web pages
      • +JSON-LD applied to
      • + any Triples from any source
  • Semantic Self Service BI
    • Metadata Mart Source + Bridge to RDFa

 

 

 

 

I have spent some time in this for quite a while now and I believe there is a quite a bit of merit in approaching the collection of domain data and column profile data, in regards to the meta-data mart, and organize them in a triple’s fashion

 

The basis for JSON-LD and  RDFa is the collection of data as a triple. Delving into said deeper

 

I believe with the proper mapping for the object reference and deriving of the appropriate predicates in the collection of the value we could gain some of the same benefits as well as bringing the web data being “collected, there by linking to source data.

Consider the following excerpt regarding Vocabularies derived from MetaContent via Metadata Structure

“Metadata structures[edit]

Metadata (metacontent), or more correctly, the vocabularies used to assemble metadata (metacontent) statements, are typically structured according to a standardized concept using a well-defined metadata scheme, including: metadata standards and metadata models. Tools such as controlled vocabularies, taxonomies, thesauri, data dictionaries, and metadata registries can be used to apply further standardization to the metadata. Structural metadata commonality is also of paramount importance in data model development and in database design.

Metadata syntax[edit]

Metadata (metacontent) syntax refers to the rules created to structure the fields or elements of metadata (metacontent).[11] A single metadata scheme may be expressed in a number of different markup or programming languages, each of which requires a different syntax. For example, Dublin Core may be expressed in plain text, HTML, XML, and RDF.[12]

A common example of (guide) metacontent is the bibliographic classification, the subject, the Dewey Decimal class number. There is always an implied statement in any “classification” of some object. To classify an object as, for example, Dewey class number 514 (Topology) (i.e. books having the number 514 on their spine) the implied statement is: “<book><subject heading><514>. This is a subject-predicate-object triple, or more importantly, a class-attribute-value triple. The first two elements of the triple (class, attribute) are pieces of some structural metadata having a defined semantic. The third element is a value, preferably from some controlled vocabulary, some reference (master) data. The combination of the metadata and master data elements results in a statement which is a metacontent statement i.e. “metacontent = metadata + master data”. All these elements can be thought of as “vocabulary”. Both metadata and master data are vocabularies which can be assembled into metacontent statements. “

 

The MetadataMart serve as the source for both metadata vocabulary and MDM for the Master Data Vocabulary.

For the Master Data Vocabulary consider schema.org which defines most of the schemas we need. Consider the following schema.org Persons Properties of Objects and Predicates:

 

Thing > Person

A person (alive, dead, undead, or fictional).

Property Expected Type Description
Properties from Person
additionalName Text An additional name for a Person, can be used for a middle name.
address PostalAddress Physical address of the item.

The key is to link source data in the Enterprise via a Business Vocabulary from MDM to the Source Data Metadata Vocabulary from a Metadata Mart to conform the triples collected internally and externally.

 

In essence information from the web applications can be integrated with the dimensional metadata mart, MDM Model and existing Data Warehouses providing lineage for selected raw data from web to Enterprise conformed Dimensions that have gone thru Data Quality processes.

 

Please let me know your thoughts.

Creating a Metadata Mart via TSQL – Complete Data Profiling Kit – Download

14 Replies

With Data Profiling can apply the age old management adage “You get what you inspect, not what you expect” Readers Digest.

This article will describe how to implement a data profiling dashboard in Excel and a metadata repository as well as the TSQL required to load and update the repository.

Future articles will explore the data model as well as column and table relationship analysis using the Domain profiling results.

Data Profiling is essential  to properly determine inconsistencies  as well data transformation requirements  for integration efforts.

It is also important to be able to communicate the general data quality for the datasets or tables you will be processing.

With the assistance over the years of a few friends(Joe Novella, Scott Morgan and Michael Capes) as well as the work of Stephan DeBlois,  I have created a set of TSQL  Scripts that will create a set of tables that will provide the statistics to present your clients with a Data Quality Scorecard comparable to existing vendor tools such as Informatica , Data Flux , Data Stage and the SSIS Data Profiling Task.

This article contains a  Complete Data Profiling Kit – Download (Code, Excel Dashboards and Samples) providing capabilities similar to leading vendor tools, such as Informatica, Data Stage, Dataflux etc…

The primary difference is the repository is open and the code is also open and available and customizable. The profiling process has 4 steps as follows:

  1. Create Table Statistics – Total count of records.
  2. Create Column Statistics – Specific set of statistics for each column(i.e… minimum value, maximum value , distinct count, mode pattern, blank count, null count etc…)
  3. Create Column Domain Statistics – domain count(count of unique vales),domain pattern(SSN=999-99-9999,ZIP =  99999-9999)

Here is a sample of the Column Statistics Dashboard: There are three panels show one worksheet for a sample “Customers” file.

Complete Column Dashboard:

  • DatabaseName – Source Data Base Name
  • SchemaName – Source Schema Name
  • TableName – Source Table Name
  • ColumnName – Source Column Name within this Table
  • RecordCount – Table record count
  • DistinctDomainCount – The number of distinct values within entire table.
  • UniqueDomainRatio – The ration of unique records to total records.
  • MostPopularDomain – The most frequently occurring value for this column.
  • MostPopularDomainCount – The total for the most popular domain.
  • MostPopularDomainRatio – The ration for the most popular value to total number of values.
  • MinDomain – The lowest value with in this column.
  • First indicator for valid values violations.
  • MaxDomain – The highest value with in this column.
  • First indicator for valid values violations.
  • MinDomainLength – Length in characters for the minimum domain.
  • MaxDomainLength – Length in characters for the maximum domain.
  • NullDomainCount – The number of nulls within this column.
  • NullDomainRatio – The ration of nulls to total records.
  • BlankDomainCount – The number of blanks within this column.
  • BlankDomainRatio – The ration of blanks to total records.
  • DistinctPatternCount – The number of Distant values within entire table.
  • MostPopularPattern – The most frequently occurring pattern for this column.
  • MostPopularPatternCount – The total for the most popular domain.
  • MostPopularPatternRatio – The ration for the most popular pattern to total number of patterns.
  • InferredDataType – The data inferred from the values.

Complete Dashboard:

ProfilngDashboard1Panoramic

Column Profiling Dashboard 1-3:

ProfilngDashboard

 Column Profiling Dashboard 2-3:

ProfilngDashboard1

Column Profiling Dashboard 3-3:

ProfilngDashboard2

Domain Analysis:

In the example below you see an Excel  Worksheet that contains a pivot table allowing you to examine a columns patterns , in this cse Zip code, and subsequently drill into the actual values related to one of the patterns. Notice the Zip code example, we will review the pattern “9999”, or Zip code with only 4 numeric digits. When you click on the pattern og “9999” the actual value is revealed is

 Domain Analysis for ZipCode

ProfilingDomainAnalysis

Domain Analysis for Phone1

ProfilingDomainAnalysis2

Running the Profiling Scripts Manually

Perquisites:

The scripts support two databases. One is the MetadataMart for storing the profiling results, the other is the source for your profiling.

There are four scripts , simple run them in the following order:

  1. 0_Create Profilng Objects – Create all the Data Profiling Tables and Views

  2. 1_Load_TableStat – This script will load records into the TableStat profiling table

  3. 2_Load ColumnStat – This script will load records into the ColumnStat profiling table.  Specify Database, Schema and Table name filters as needed. Example, to profile every table names starting with “Dim” then change the table filter to SET @TABLE_FILTER = ‘Dim%. Specify the Database where the Data Profiling tables reside DECLARE @MetadataDB VARCHAR(256) SET @MetadataDB = ‘ODS_METADATA’ Specify Database, Schema and Table name filters as needed. Example, to profile every table names starting with “Dim” then change the table filter to SET @TABLE_FILTER = ‘Dim%

  4. SET     @DATABASE_FILTER = ‘CustomerDB’

  5. SET  @SCHEMA_FILTER = ‘dbo’

  6. SET  @TABLE_FILTER = ‘%Customer%’

  7. SET  @COLUMN_FILTER = ‘%’

  8. 3_load DomainStat – This script will load records into the DomainStat profiling table. Specify the Database where the Data Profiling tables reside DECLARE @MetadataDB VARCHAR(256) SET @MetadataDB = ‘ODS_METADATA’ Specify Database, Schema and Table name filters as needed. Example, to profile every table names starting with “Dim” then change the table filter to SET @TABLE_FILTER = ‘Dim%

  9. SET     @DATABASE_FILTER = ‘CustomerDB’

  10. SET  @SCHEMA_FILTER = ‘dbo’

  11. SET  @TABLE_FILTER = ‘%Customer%’

  12. SET  @COLUMN_FILTER = ‘%’

  13. -1_DataProfiling – Restart – Deletes and recreates all profiling tables

 Please feel free to contact me with any questions?

Agile MDM Data Modeling and Requirements incorporating a Metadata Mart.(Guerilla MDM)

Leave a reply

Recently an acquaintance of mine asked about my thoughts on the approach for creating a MDM data model and the requirements artifacts for loading MDM.

I believe with this client and with most clients that the first thing that we have to help them understand is that they have to take an organic and evolving a.k.a. an “agile” approach utilizing Extreme Scoping in developing and fleshing out their MDM data model as well as the requirements.

High-level logical data models for customer, location, product etc, such as Open Data Model are readily available and generally not extremely customized or different.

If we look at the MDM aspect of data modeling and requirements, I look at it as three layers:

1.the logical data model.

2.the sub models(reference models) which detail the localization in the  sources for each domain, which in essence is our source-based reference models.

3.an associative model that allows for the nexus/mapping between the sources their domain values in the master domain model.

It doesn’t really get interesting until you’re able to dive into the submodel and associative models and  relationships creation and understand the issues that you’re dealing with and anomalies in the source data, which inherently involves quite a bit of data profiling and metadata analysis.

In my experience the creation of the MDM model and sub models involves three simultaneous and parallel tracks.

1. Meet with users of and preform metric decomposition(defined in my slides) and define the (information value chain) and create logical definitions of metrics , groupings of dimensions and hire attributes  and subsequently hierarchies that are required from an analysis perspective. A data dictionary.

1a. The business deliverable here is a business matrix which will show the relationship between the metrics and the dimensions in relation to the business processes and/or KPI’s. I’ll send an example.

1b. The relationship of the above deliverable to the logical model, is that the business deliverables here specifically the business matrix and the associated hierarchies will drive the core required data elements from the KPI and therefore the MDM perspective. In addition it will drive the necessary hierarchies( relationships) and thereby the associations that will  be required at the next level down which will involve the source to target mapping required for loading the reference tables.

1c. Lastly and once we’ve truly understand the clients requirements with enough detail in terms of metric definition and hierarchies we can then select and incorporate reference models or industry experts and validating and vetting our model, this should be relatively straightforward.

1d. From an MDM perspective, where multiple teams are simultaneously gathering information/requirements we would want to use and have access to the artifacts especially for new business process modeling or any BI requirements modeling.

2. Introduce the metadata mart concept and utilize local techniques and published capabilities(I have several that can easily be incorporated) to instantiate a metadata mart and incorporate metadata and  data profiling as part of the data modeling process and user awarement process.

2a. The deliverable here is a set of Excel pivot tables and/or reports that allow the users to analyze and understand the sources for domains and how they’re going to relate to the master data model even at logical level

3. Capture relations to defining master domains and documenting in Source to Target Matrixs the required mappings and transformations.

This process will result in a comprehensive set of deliverables that while in their final state will be the logical and physical data models for MDM, in addition the client will have the necessary data dictionary definitions as well as I high-level source to target mappings.

My concern with presenting a high-level logical MDM model to user is that it is intuitively too simplistic and to straightforward. Obviously at the high-level the MDM constructs look very logical for customer, location, products etc..

They don’t expose the  complexity and frankly the magic(hard work) from an MDM perspective of the underlining and supporting source reference models and associative models that are really the heart and soul of the final model. The sooner and more immediate that we can engage the client an organized! facilitated, methodological  process of simultaneously profiling and understanding their existing data and defining the final state a.k.a. the MDM model that they are  looking for the better we’re going to be in managing long-term expectations.

There’s an old saying which I think specifically applies here especially in relation to users and expectations it is “They get what they inspect, not what they expect

I know that this went on a bit long, I apologize I certainly don’t mean to lecture, but I think the crux of the problem in a waterfall or traditional approach to creating an MDM model is overcome by following a more iterative or agile approach.

The reality is that the MDM model is the heart of the integration engine.

Fast, Data Driven Netezza Load – The Prittie Good Approach

Leave a reply

A few years ago I have a client that required migrating and re hosting a data warehouse from the Oracle environment to the Netezza Appliance. The application had hundreds of tables and almost 2900 Source to Target Mappings. This would have taken 1000′s of hours of manual coding. For this client we implemented a reusable data driven architecture that relys on a metadata mart. The end result a greatly reduced TCO for generating the code required vs manual cosing. In addition the process leaves behind a metadata mart to report on for auditing and other Data Governance efforts. We generated over 100,000 lines of code and over 3000 load scripts.After implementation the application can be maintained via the tables and automatically regenerated, providing a pro active or self healing application architecture to respond to changes in incoming source files or target changes. Code Here is a version for Oracle Loader

Prittie Good Approach: The objective is to “tactically” enable the clients analytical and reporting tools to their data via the Netezza platform very fast, with minimal coding effort, but in a “strategic fashion”. The “Prittie” approach will load a client’s data into the Netezza platform as fast and inexpensive as possible while still providing a reusable “production level” set of code for implementation. The approach as defined is to minimize upfront source analyses and data profiling and leverage the “speed” of Netezza , while applying specific coding techniques developed by you to automatically generate a the SQL code necessary to import data into the Netezza environment from industry standard delimited files with headers and a mapping document. The application will build a Metadata Mart for support data driven code generation and as a tactical tool for Data Governance. The primary TCO driver or value add gained with using our approach is to significantly reduced time and cost for an initial load , data profiling and evaluation of data quality, and speed up the process of Business Analysis, as well as leveraging an iterative methodology. In addition business data profiling as well as source analysis can then be accomplished on the Netezza platform, very quickly, while capturing critical load “halting” problems as well as basic profiling metadata immediately and resolving them.

Target: Tables and Columns are derived from parsing machine generated DDL for existing tables, for table columns formats and column order.
Source: Filenames and Column are derived from parsing Header files extracted from industry standard “tab” delimited files with Headers, this provide columns contained in the files and there order.
Mapping: (Source to Target) are derived from parsing the “Bill of Lading” file containing cross reference of multiple filenames to target tables and a grouping column in this case. You original file also had external name this is no longer needed;

Prittie Approach Specific Logic: From these three inputs the application generates Netezza ANSI Standard SQL Code that create an external table with columns ordered by the incoming filename and associated with the DDL Column format, and the associated Netezza logic for external filename allocation and logging. A simple SQL statement is generated that checks for duplicates and data errors , that can be logged. In addition I have includde the ability to generate the Drop and Insert Logic to complete the “Prittie” Import and Load Architecture for Netezza.

Application Stability: The Source and Mapping files design are relatively stable. The Target is based on some of the idiosyncrasies found in the DDL created by the query tool used, with some additional effort this code can be enhanced to handle some “looseness”. The issue is in how the code (DDL) is generated and by what tool. The rest of the application is shielded from this by the creation of a “Source-Target-Mapping Cross Reference” table that is used in the actual code generation.

ETL Potential: As we have discussed the application could be extended easily to handle basic ETL. This could be accomplished by including one more that that contained transformations for specific table columns combinations or data types. An example would be to include a new mapping table that converted Oracle Datetime to ANSI as required. This is a simple example but the underlying design could handle relatively complex transformations. We could also move the Location and Logging Directory to a table to further make the application Metadata Driven.

Doug Prittie and Ira Warren Whiteside

MAD Skills: New Analysis Practices for Big Data

Leave a reply

I have had my first exposure to MAD Skills: New Analysis Practices for Big Data , very interesting approach , in many ways an extreme expansion of the concept for operational analytics via OLAP. The use of the “Sandbox” environment for statisticians and analyst, can also be incorporated in current BI applications using the Agile / SCRUM Approach modded with Extreme Scoping for BI(Larissa Moss).

I certainly see the advantage of enabling “deep” access to high end users , however the issues for broader use are still “Data Prep” and resolving Data Quality issues, as discussed in another thread in the Data Science LinkedIn group, also in the article the term “Data Driven” is used in the context of the “Functional” being developed, as opposed to automated reusable processes “Driven” by metadata.

Actually the entire approach seems to be geared towards being able to run one off processes and could be well served by a discussion of applying a Metdata Mart approach (Belcher Gartner) to retaining and reusing results. Also visit the Data Science Group at LinkedIN