Data Integration Challenge – Building Dynamic DI Systems – I

One other challenge in a data integration project as it is with any other IT system is to build a data integration environment that is agile and flexible enough to accommodate the system changes related to business rules. The benefit of building a dynamic system is that the code changes are less frequently done or completely avoided in many cases, so it is very much a de-facto process to look for design opportunities to build a DI system that is dynamic.

A dynamic DI system accommodates variation in the incoming data and is able to respond without system failures or mal-data processing, in many such scenarios the DI environment also gets to be controlled by the Business Team with lesser support from the IT team.

Following are some of the design aspects towards getting a DI system dynamic

1. Avoiding hard references, usage of parameter variables
2. 
   
3. Usage of lookup tables for code conversion
4. 
   
5. Setting and managing threshold value through tables
6. 
   
7. Segregating data processing logics into common reusable components
8. 
   
9. Ensuring that the required processes are controllable by the Business team with the required checks built in
10. 
    

Avoiding hard references, usage of parameter variables:
In scenarios like defining simple database connections or to using a “Business Unit Number” for filtering data while extracting required data from a source system can be parameterized through variables. In the case of database parameterization it enables easier code movement across development-test-production environments and in the scenario of the Business Unit Number parameterization it enables running the same DI program for one other Business Unit with same data by just changing the parameter variable of Business unit Number

Usage of lookup tables for code conversion
In scenarios like where the incoming data value has to be converted to one other “standard value”, we usually write a IF..THEN… ELSE… syntax, here again we can bring in dynamism by having a lookup table which would carry the incoming value as one column and the standard value to be replaced as another column. The benefit is if there is any further change to the standard value the lookup table can be updated without opening the code and as well we can insert a new record into the lookup in case we need get the DI process handle a code conversion for a new incoming value.

We shall see more details and other design aspects in the coming days.

You might want to read these awesome related posts Data Integration Challenge

Business Intelligence Utopia – Implementation Enablers

In this series of posts on Business Intelligence Utopia, I have presented some thoughts around 5 key technology enablers that in my view will take BI to the next level. They are:

1. Proliferation of transactions systems that support the analytical feedback loop Real-time / Right-time

2.Data Integration

3.Data Governance

4.Service Oriented Architecture

5.Extensible Data Models

All of these put together answer the question of “Where do you want to see Business Intelligence in the future”? I have 5 more to go to complete the discussion on the “Power of Ten” key enablers.

At this point, I felt that the question of “How to make it happen?” is as important as the technology enablers and hence decided to take a bit of digression to focus on some implementation aspects for Business Intelligence Utopia. I call them the “Implementation Enablers”. These enablers focus on process methodologies, specific techniques etc. that helps in managing the evolution of Business Intelligence within your organization.

There are 3 implementation enablers of interest, at this point in time:

1. Agile Framework for BI
2. 
   
3. Business Intelligence Calibration
4. 
   
5. Function Point based Estimation technique for Data Warehousing
6. 
   

You can also visit the following link http://www.hexaware.com/webcastarchive1.html to listen to my recent webinar on “Agile Framework for Calibrating the Enterprise Data Warehouse” to get an idea of the implementation enablers.

To reiterate, my approach for future posts on this blog is to write about Business Intelligence Utopia along 2 dimensions:

Technology enablers – 10 of them have been identified. 5 of them were discussed already and 5 more to go.

Implementation enablers – 3 of them have been identified and will be elaborated in the next few weeks.

Bit of information – I recently took the TDWI (The Data Warehousing Institute) assessment of  Business Intelligence  Maturity done using their online tool and got some very good insights. If you are interested, please use the link- http://tdwi.org/display.aspx?id=8500 to take the assessment for your organization.

Data Integration Challenge – Understanding Lookup Process – III

In Part II we discussed ‘when to use’ and ‘when not to use’ the particular type of lookup process, the Direct Query lookup, Join based lookup and the Cache file based lookup. Now we shall see what are the points to be considered for better performance of these ‘lookup’ types.

In the case of Direct Query the following points are to be considered

• Index on the lookup condition columns
• Selecting only the required columns

In the case of Join based lookup, the following points are to be considered

• Index on the columns that are used as part of Join conditions
• Selecting only the required columns

In the case of Cache file based lookup, let us first try to understand the process of how these files are built and queried.

The key aspects of a Lookup Process are the

• SQL that pulls the data from lookup table
• Cache memory/files that holds the data
• Lookup Conditions that query the cache memory/file
• Output Columns that are returned back from the cache files

Cache file build process:

Based on the product Informatica or Datastage when a lookup process is being designed we would define the ‘lookup conditions’ or the ‘key fields’ and also define a list of fields that would need to be returned on lookup query. Based on these definitions the required data is pulled from lookup table and the cache file is populated with the data. The cache file structure is optimized for data retrieval assuming that the cache file would be queried based certain set of columns called ‘lookup conditions’ or ‘key fields’.

In the case of Informatica, the cache file is of separate index and data file, the index file has the fields that are part of the ‘lookup condition’ and the data file has the fields that are to be returned. Datastage cache files are called Hash files which are optimized based on the ‘key fields’.

Cache file query process:

Irrespective of the product of choice following would be the steps involved internally when a lookup process is invoked.

Process:

1. Get the Inputs for Lookup Query, Lookup Condition and Columns to be returned
2. Load the cache file to memory
3. 
   
4. Search the record(s) matching the Lookup condition values , in case of Informatica this search happens on the ‘index file’
5. 
   
6. Pull the required columns matching the condition and return, in case of Informatica with the result from ‘index file’ search, the data from the ‘data file’ is located and retrieved
7. 
   

In the search process, based on the memory availability there could be many disk hits and page swapping.

So in terms performance tuning we could look at two levels

1. how to optimize the cache file building process
2. 
   
3. how to optimize cache file query process
4. 
   

The following table lists the points to be considered for the better performance of a cache file based lookup

Category	Points to consider
Optimize Cache file building process	• While retrieving the records to build the cache file, sort the records by the lookup condition, this sorting would speed up the index (file) building process. This is because the search tree of the Index file would be built faster with lesser node realignment • Select only the required fields there by reducing the cache file size • Reusing the same cache file for multiple requirements for same or slightly varied lookup conditions
Optimize Cache file query process	• Sort the records that come from source to query the cache file by the lookup condition columns, this ensures less page swapping and page hits. If the subsequent input source records come in a continuous sorted order then the hits of the required index data in the memory is high and the disk swapping is reduced • Having a dedicated separate disk ensures a reserved space for the lookup cache files and also improves response of writing to the disk and reading from the disk • Avoid querying recurring lookup condition, by sorting the incoming records by the lookup condition

You might want to read these awesome related posts Data Integration Challenge

Business Intelligence Utopia – Enabler 5: Extensible Data Models

Enabler 5 in my list for Business Intelligence Utopia are the ubiquitous, hard-working “Data Models”. Data Model is the heart of any software system and at a fundamental level provides placeholders for data elements to reside.

Business Intelligence systems with all its paraphernalia – Data Warehouses, Marts, Analytical & Mining systems etc. typically deals with the largest volume of data in any enterprise and hence data models are highly venerated in the Data Warehousing world.

At a high level, a good Data Warehouse data model has the following goals: (Corollary – If you are looking for a data modeler look for the following traits)

1) Understand the business domain of the organization

2) Understand at a granular level the data generated by the business processes

3) Realize that business data is an ever-changing commodity – So the placeholder provided by the data model should be relevant not only for the present but also for the future

4) Can be described at a conceptual and logical level to all relevant stakeholders

5) Should allow for non-complicated conversion to the physical world of databases or data repositories that is manipulated by software systems

Extensible Data models deal with all the 5 points mentioned above and more specifically has future-proofing as one of its stated goals. Such extensible models are also “consumption agnostic”, i.e. – it provides for comparable levels of performance irrespective of the way data is being consumed.

It is important for Business Intelligence practitioners to understand the goals of their data models before embarking to use specific techniques for implementation. Entity-Relationship & Dimensional modeling (http://www.rkimball.com) has been the lingua-franca of BI data modelers operating at the conceptual and logical levels. Newer techniques like Data Vault (http://www.danlinstedt.com/) also provides some interesting thoughts in building better logical models for Data Warehouses.

At the physical implementation level, relational databases still form the backbone of the BI infrastructure, supplemented by multi-dimensional data stores. Even in the relational world, traditionally dominated by row-major relational vendors like Oracle, SQL Server etc. there are column-major relational databases of the likes of Sybase IQ with claims of being built ground-up for data warehousing.

In this article on column major databases – http://www.databasecolumn.com/2007/09/one-size-fits-all.html, there is reference to a new DW specific database architecture called Vertica. It makes for a fascinating read – http://www.vertica.com/datawarehousing. The physical layer is also seeing a lot of action with the entry of data warehousing appliance vendors like Netezza, Datallegro etc. (http://www.dmreview.com/article_sub.cfm?articleId=1009168).

The intent of this post can be summed up as:

a) Understand the goals of building data models for your enterprise – Make it extensible and future proof

b) Know the current techniques that help envisage and build data models

c) Be on the look-out for new developments in the data modeling and database world – There is lot of interesting action happening in this area right now.

Extensible data models combined with the right technique for implementing them, lists as Enabler 5 in the “Power of Ten” for implementing Business Intelligence Utopia .

Business Intelligence Utopia – Enabler 4: Service Oriented Architecture

Service Oriented Architecture (SOA) and its closest identifiable alter-ego “Web Services” is another example of hyped-up, much maligned technology buzzword that takes at least 2 or 3 slides in any “bleeding-edge” technology presentation. Having said that, whatever I have investigated on Service Oriented Architectural concepts till now, is enough to warrant its listing as enabler no. 4 for Business Intelligence Utopia.

There are many powerful ways through which SOA can add significant value to the BI environment. The kind of BI, performance management and data integration artifacts that can be developed and published as web services include: Queries, Reports,  OLAP slice services (MDX queries), Scoring and predictive models, Alerts, Scorecards, Budgets, Plans, BAM agents, Decisions (i.e., automated decision services), Data integration workflows, Federated queries and much more. You can get more information at the link: http://www.b-eye-network.co.uk/view-articles/4729

But the idea that fascinates me with respect to Business Intelligence on SOA, is the concept of “Analytical Smorgasbord”. Imagine a scenario where the business user can assemble their own analytical components from a mélange of available ones, resulting in complete customization of information for the user to take his/her decisions. Each of these available analytical components is self-contained and performs a particular piece of BI functionality. These components are ‘Web-Services’ and the SOA in such an enterprise is all about –

a) How are these components created?

b) How do the components interact?

c) How is the information published and consumed, in a secure manner?

The concept of “Analytical Smorgasbord” truly empowers the business users and is a powerful way to enable, what Gartner terms, as “Information Democracy” in the enterprise. It is important to note that the concept of analytical aggregation changes the Data Warehousing paradigm in a profound way – From “Pulling data” to “Seeking data”. In more simplistic terms, the end-user analytics should go and fetch data wherever it is rather than expecting all data to be consolidated into one data repository (typically a data warehouse or data mart). More on this in future posts, under the topic of “Guided Analytics”.

The true intent of this post is to encourage the BI community to start looking at SOA from the end-user analytical standpoint, so that web-services does not remain a mere technology toy but really helps in “Putting the business back in BI” – http://www.tdwi.org/Publications/display.aspx?id=7913

I have intentionally left out the technology details related to SOA. You can find wonderful resources on the web like this one: http://www.dmreview.com/portals/portal.cfm?topicId=1035908 It is becoming increasingly important for BI practitioners to acquire/develop knowledge on Web technologies, XML, SOAP, UDDI, etc. as different domains are converging at a rapid pace..

Enabler 4 in the “Power of Ten” is more precisely defined as – Service Oriented Architecture enabling the creation of BI “Analytical Smorgasbord”.

You might want to read these awesome related posts Business Intelligence Utopia