Data Mining has now its open interface: JDMAPI

I've always been interested in data mining because it mixes some advanced statistical or mathematical methods with complex data computation algorithm (typically developped for computer learning). On the negative side, its application may have a bad press (sometimes well deserved) because of potential abuse it can lead to... I will not dwell time on identity and privacy sensitive issues, but when the goal is respectful of people privacy's right, one can leverage data mining to bring normal analysis to a much higher level. This is achieved through data induction (let the data speak for itself...) as opposed to data deduction (deducts conclusion based on specific report produced) typically encountered in more classical BI application.

Data mining functionality is now built-in inside Oracle 10g through a standardized JSR (http://www.jcp.org/en/jsr/detail?id=73). Although this first effort is limited in scope, it will ensure application could be developed independent on vendor proprietary API. It is currently being continued through a more complete initiative (JSR-247, http://www.jcp.org/en/jsr/detail?id=247) which will bring more mining functionality and advanced algorithm. Most big players contribute to this standardization effort: Oracle, SAS, Hyperion, SPSS, SAP, IBM, etc.., but yes of course except Microsoft.

The standard API also offers extensibility (each vendor can provide additional functionality not explicitly defined within the standard) and also covers the use of Web Services which will ensure complete independence of platform and language implementation.

More info can be found by googling JDMAPI.

I'll try to analyse this API in more detail and let you know my discoveries...

From ER to OO

In my previous posts I've been sharing knowledge valuable for people dealing with the technology underlying relational database management system (RDBMS for short). This technology is used to store literally any information held by organization. I've been dealing with this technology since about 1996, and still continue to do so mainly because of its ubiquity in IT world. Relation databases store information using set theory and implement transaction and concurrency control to handle a large number of simultaneous connection, and as such their scope are fairly limited (although most big players are trying to include more functionality and processing flexibility into their engine, e.g. Oracle experiencing with the inclusion of JRE within their database...).


After doing modeling and designing architecture for database for some time, I started designing and developing stuff in Object Oriented language environment (around 2002). At first this can be quite daunting with all the flexibility OO programming can offer... Compared to database modeling where you have a rigid framework and theory guiding your work, OO modeling seems more to stimulate your artistic and creativity ability than your analytical expertise.

To overcome this new paradigm, here's some pragmatic steps I did and applied in learning Java, free of charge (or almost):

1. Getting and reading good reference documentation, such as the free resource from Bruce Eckel, Thinking in Java. This first step will only help you gaining some knowledge, but to be able to do it yourself in an elegant and flexible way you'll definitely need more experience. After some practice you'll seem to face recurrent problem over and over... this is where step 2 kicks in.


2. Getting a good reference on Design Pattern, this will teach in developing code with better quality (from aspects such as flexibility, robustness, adaptive, less error prone, etc.) following pattern developed by experienced developer. A good introduction book would be Head First Design Pattern, but to get the real reference document you should go to Gang of Four.


3. If you're still shy and afraid of downloading a free copy of Eclipse to experiment and code yourself (at this point maybe you should simply reconsider coding ;-), then what is still available to you are millions of lines of quality code (mostly in Java) available in the best open source project, more on that later. However, most likely you'll actually be programming your own stuff relying one or many open source components, at least that's how I did it.

Martin

Design vs Coding, and DDD

I have the chance (some would probably not call it that way) to deal with architecture/design stuff in my work, in addition to doing real coding and hands-on development work during my consulting activities.

For me, it's sort of an experimental way aiming to find which of one of these two would fulfill my day the most. After doing the two for the past 2 years: I came up to the conclusion: the mix of both is what fits me since coding allow to stay more in touch with the technical aspect and the technology while the design and architecture helps me keep some perspective as to which technology can make some difference and which adds up to ... well mostly hype!

An interesting approach that I find in OO-type of development that actually stresses the importance of having both competence in order to build complex system having all usual quality indicators. This approach originated from Eric Evans and is now well accepted and referred to as Domain-Driven Design (or DDD). I will not describe it here, please refer to his book.

However let me just comment of one aspect that I find particularly useful during OO design of a typical business application. It really helps in producing well organized, well designed, well documented and logical Domain Model construction. Very briefly the way I see this particular concept, is similar to a form of taxonomy that helps you organize and classified each object that you design in your Domain Model based on their roles:

• Entity Objects are those with a very precise identity and transactional lifecycle, and for which we usually worry when multiple instances of the same identity could be found at runtime. They would typically be mapped to a unique row in the persistence database.

• Value Objects are those without any identity where its field attribute are only compared by value (i.e. two instances with same value are considered equal).

• Factory Objects are those concerned with the creation of object(s)... more often justified when creating group of objects along with their associated graph. This is required when this creation involve enough complexity to warrant encapsulation through factory.

• Service Objects are those used to handle and implement business service along with associated business rules. These service objects usually operate on multiple collaborators entity objects as well as their repository.
  

• Repository Objects are those used to handle entity lookup and persistence through an abstract layer hiding any details and idiosynchracies of the related persistence engine. They are typically used by Service objects, but sometimes by Entity Object, in which case Entity object would have a reference to the Repository passed inside its methods requiring Repository functionality.
  

I find this taxonomy very insighful and convenient, and this is certainly useful to adopt..still worth it when only used to help standardizing design terminology among developers and architect!


Martin

Intelligent Agent part 4: Implementation with OO programming

Intelligent agent implementation using Object Oriented programming paradigm

Wooldridge in Multiagents Systems provides a comparison between object oriented system and agent system paradigm, which seems awkward in the sense that the two paradigms serve different purposes. According to his reasoning, OO does share agent system characteristics by exhibiting autonomy over its state (and object control its state by making it private), however fails in controlling its behaviour (because once object methods are made public, control is switched to external object) contradicting the autonomy notion of agent. Other observations include the fact the agent have their own thread of control whereas in standard object model, a single thread of control is allocated to the entire system.

Actually, OO paradigm is sophisticated enough to provide mechanism required by the agent specification. For example, the access control granularity of object could allow to make only state information visible through methods and no operation done on the objects themselves (or condition could be applicable to the operation in order to respect the autonomy notion of the object), furthermore most OO implementation offers robust multi-threading control capacity, etc.). Surprisingly, the article refers to active object, which shares the property just mentioned, and also mentions that OO system can be used to build agent based system. We could add that it is actually being used, see Bigus for specific application example. Furthermore, from an ad-hoc search on the current software architecture currently available in building agent-based system, we see that the majority of development frameworks are actually originated from OO language implementation (reference: http://www.multiagent.com/) and most of them are actually from a well accepted general purposes language, i.e. Java:

Academic research based:

• Cougaar Agent Architecture (http://www.cougaar.org/)

• Jadex BDI Agent System (http://vsys1.informatik.uni-hamburg.de//projects/jadex/)

• JATLite (http://java.stanford.edu/)

• JATLiteBean (http://kmi.open.ac.uk/people/emanuela/JATLiteBean/)

• Java Communicating Agents (http://jacomma.sourceforge.net/)

• JESS, the Java Expert System Shell (http://herzberg.ca.sandia.gov/jess/)

• MAP, a Mobile Agent Platform (http://sun195.iit.unict.it/MAP/)

• Excalibur (http://www.ai-center.com/projects/excalibur/)

• Bee-gent Multi Agent Framework (http://www2.toshiba.co.jp/beegent/index.htm)

• Zeus Agent Building Toolkit (http://more.btexact.com/projects/agents/zeus/index.htm)

Commercial based product:

• Jade (http://jade.tilab.com/)

• CybelePro (http://www.cougaar.org/)

• JACK Intelligent Agent (http://www.agent-software.com.au/jack.html)

• Living Systems Technology Suite (http://www.whitestein.com/pages/solutions/ls_ts.html)

• TSpaces (http://www.almaden.ibm.com/cs/TSpaces/)

Taken from

“Constructing Intelligent Agents with Java” (1998) from J.P. Bigus and Internet ad-hoc researches

Intelligent Agent part 3: Multiagent and Societies

Multiagent systems and Societies of Agents

Analyzing and considering only individual agent architecture is neither convenient nor realistic as most environments contain multiple agents interacting together in various ways. An important aspect of these multiagent systems or societies of agents consists of the infrastructure facilitating the interaction between agents during their course of action. These interactions should not compromise autonomy of agent whether they are cooperative, competitive or simply self-interested.

During message exchanging, some form of protocol or mechanisms dictating the communication are necessary to ensure that agents “understand” each other. These protocols are defined at various communication levels. We have, from lowest to highest respectively, level specifying interconnection, level responsible of format and syntax and level for meaning or semantic. At a more abstract level, another protocol is also necessary to organize interaction between agents as to mimic “real conversation” taking place not just single message exchanged.

Communication Level Protocol

To simplify software agent design, “speech act theory” serves as a model for handling the various aspect of communication. Speech act views human language as actions (e.g. request, suggestion, commitment, replies, etc). These actions have three dimensions; locution (the words), illocution (the intended meaning) and the perlocution (the actions resulted of the locution).

One good example of a protocol for exchanging information and knowledge that rely on “speech act theory” performative model is called KQML (or Knowledge Query and Manipulation Language). This protocol is specifically useful for decoupling both the message and the communication semantic. This is achieved by separating the message into multiple fields; language (e.g. Knowledge Interchange Format -KIF or Lisp, Prolog, etc), content (i.e. representing the message itself and expressed using the language specified) and ontology (representing the objects and their inter-relationship in a domain of interest) are all part of separate fields.

Interaction Level Protocol

As for the higher level of communication, concepts such as coordination, cooperation, contract net, blackboard system, negotiation and market mechanisms have all been devised in order to specify agent interaction protocol.

Coordination protocol ensures that global goals are achieved while respecting global constraint and reducing redundant problem solving. Coordinated systems have often been designed in DAI field (distributed artificial intelligence) through techniques that distribute both control and data. However, this leads to some difficulty such as agents having only partial and imprecise information knowledge on the global goals and also having less coherent global behaviour. Coordination uses two keys structures to formalize itself in distributed agent system. The first one called commitment is used by the agent as a pledge to undertake a specified course of action, while the second one called convention is a means of managing commitments in changing circumstances. Commitment ties agent into future activities and convention dictates the rules and constrains under which these commitments are re-evaluated in case of changes and new reality.

Coordination is not the only abstract level protocol existing, the following gives a brief definition of alternative mechanisms existing to support agent interaction:

• Task decomposition and distribution. Often utilized in a more cooperative type environment in which the system designer takes care of the task decomposition of agents. This can be done by spatial decomposition where agents are responsible of task based on the information source and decision point location, or by functional decomposition where agents are defined and allocated by expertise.

• Contract Net. This mechanism is modelled following the goods/services exchange economic rule. The roles of agent are not specified before hand but rather through a bidding system in which managers (agent announcing task for bidding) award contractors (agent winning a contract after submitting a bid) specific problem resolution task.

• BlackBoard System. Central interaction point shared and used by all agents to reach common solution.

• Negotiation. Utilized in situation involving agents having conflicting goal. This needs some form of concession and alternatives search from the agents to reach and move toward agreements.

• Market mechanism. Often a direct interaction between agents is just impossible due to the significant number of agents involved. In this scheme, a voting system can be put in place allowing agents to vote between a set of alternatives solution proposed by a smaller group of agent.

Taken from

1. “Multiagent Systems, a Modern Approach to Distributed Modern Approach to Artifical Intelligence” Chapter 2 Mutliagents Systems and Societies of Agents, Huhns and Stephens (1999)

Intelligent Agent part 2: Architecture

Here the second part of my research study on software intelligent agent.

Intelligent Agent Architecture

Current agent architecture can be classified as either 1) logical based agent, 2) reactive agent, 3) BDI agent (belief-desire-intention) and 4) layered architectures. The following summarizes very briefly these approaches and provides some insight in relation to their pros/cons.

The logic based approach is when the decision making strategy reduces to a problem of proof (predicate logic), i.e. the agent uses its symbolic representation of the world (usually through logical formulae) to accomplish logical deduction in order to select an appropriate action. In this scheme, the deduction rules that determine the agent behaviour are the “program” (execution), whereas the symbolic information/ representation of the environment is the current database (data).

This traditional approach originating from early AI work, suffers from disadvantages such as the following:

1. Because of Calculative rationality (the optimal action is deduced at time j based on the environment condition as seen at time i, where j>i), we must assume that the environment did not change during the time agent chooses its action, reducing the practicality of such agents. Although alternative compilation of the logical specification into a more amenable form can provide better efficiency, this issue still persist in time-constraint and highly dynamic environment.

2. Perception of the agent is realised through a non-obvious mapping from real environment condition to a set of symbolic formulae. It has been acknowledged that as seductive and useful logic is to conceptualise and specify agent, it is quite difficult when one tries to represent and reason about complex, dynamic of physical or non-physical environment using a set of explicit symbolic reasoning.

The reactive agent architecture (alternatively referred to as behavioural or situated) proposed by R. Brooks intends to simplify and avoid the difficulty of symbolic representation by providing a more economical, elegant and robust agent whose behaviour is simply a function of its local environment. One of the most famous technique called Subsumption architecture is characterized by an action-perceptual input mapping (situation  action) and a layer controlling hierarchy in charge of resolving possible simultaneous action conflicts (lower layer action takes precedence over higher or more abstract layer). Agent performance and complexity which depends on the number of behaviour and number of percept (raw sensor input is used as opposed to complex symbolic representation) can result in a constant decision time when hard-wiring decision making process, making such agent computational simple and attractive. However, this simplicity leads to some disadvantages:

1. Only a local environment information is gathered making non-local information not possibly taken into account

2. How could purely reactive paradigm lead to some form of experience learning and performance improvement over time?

3. Implementation engineering relying heavily on experimentation, trail and error methodology

4. The hierarchical form of action control can lead to confusion when a large number of different behaviours are modelled.

The BDI or Belief-Desire-Intention architecture is influenced by the study of practical reasoning (human reasoning taking place during continuous decision-action process). This philosophical concept explains any action taken by a combination of intention (what goals are to be achieved) and a chosen option (how to proceed for achieving these goals). It is assumed in BDI system that course of action taken to satisfy goals should not stop simply because of failure but in the mean time it should not persist without any reasonable chance of success (or without the original intention remaining relevant). This represents a dilemma between the right balance of reactiveness and proactiveness behaviour contained in a BDI agent. Pure goal directed agents will never stop to reconsider their intention even after important environment changes that could invalidate the original goal. Meanwhile pure event driven agents never spend enough time trying to achieve their goals and waste all computational time in reacting to their environment. In general the more static an environment is the more pro-active agents should be, whereas in more dynamic environment agents need to have the ability to react to changes.

The layered architecture aims to accomplish a better balance between agent pro-activeness and re-activeness by introducing the concept of specialized dedicated layer. Layering architecture into subsystems allows one to design specialized subsystems adapted for specific task, for example one subsystem would deal with reactive behaviour while another one with pro-active behaviour. Most of these architectures use either horizontal layering or vertical layering for their information and control flow mechanisms. Horizontal layering requires some form of mediation between all layers since they all have access to input and can all produce some action output. Vertical layering involves a flow sequence between all layers, with lowest level layer receiving the input and forwarding it to highest or more abstract layer if necessary (i.e. when current layer expertise and competence cannot process or deal with input). Input information flowing from lowest to highest is referred to by bottom-up activation while command flowing from highest to lowest is referred to by top-down execution. This architecture is very popular due to its pragmatism and intuitive mapping from pro-active, reactive, social behaviours into specific and dedicated subsystems.

Taken from

1. “Multiagent Systems, a Modern Approach to Distributed Modern Approach to Artifical Intelligence” Chapter 2 Mutliagents Systems and Societies of Agents, Huhns and Stephens (1999)

Intelligent Agent part 1: Definition and Theory

I did some research study for a friend of mine (no more details are possible ;-) on intelligent agents (software). This is a quite a broad area and very interesting. While doing this academic-oriented research reminded me on the period of my master degree, I must admit that working around a theoretical framework greatly contrasted with my daily work at the job... which is all about practical and not much about theoretical.

I've decided to publish some of the theoretical material I've surveyed... here is the first part.

What are intelligent agents

Agents are loosely defined as being a computer based system characterized by their autonomy (no direct human intervention required), their social ability allowing them to communicate with other agents or human, their reactivity allowing them to react to the changing environment and finally their pro-activeness which also permit them to take initiative to meet their goal. In fact the key characteristics of reactivity, pro-activeness and social ability can be considered as main differentiator between “normal” agent and “intelligent” agent.

Other more formal definition would also include some human alike concepts such as knowledge, belief, intention and obligation, as well as additional properties like mobility, veracity, benevolence and rationality.

An important aspect of agent has to do with their surrounding environment which they operate in. To see the impact of environmental properties on agent, one can classify them as being:

1. Accessible/inaccessible: accessible environment would be one for which all states are known up-to-date and accurately. In all practical cases, the environment is inaccessible, e.g. surrounding physical world, Internet, etc.

2. Deterministic/non-deterministic: except for the most trivial case, agents has only partial and non-deterministic control over their environment are, i.e. same action performed twice on seemingly identical circumstances may appear to have different consequences. Although non-deterministic lead to more complex system, to all intents and purposes it is recognized that a highly complex deterministic environment is just as difficult as a non-deterministic.

3. Episodic/non-episodic: episodic system has performance dependant only on the current discrete episode. This simplifies design since all possible scenarios and interaction in future episodes can be ignored in relation to deciding which actions are to be taken by the agents.

4. Static/dynamic: static environment only observe changes in reaction to action performed by the agent, in most situation other unknown/known processes operate on the environment making it dynamic.

5. Discrete/continuous: Discrete environment implies only a finite number of actions and percept to be possible.

It is recognised that with respect to building agent, most complex class of environment are those that are inaccessible, non-deterministic, non-episodic, dynamic and continuous.

Agent theory

A substantial part of agent theories are derived around the concept of intentional system, i.e. system with behaviour that are predicted based on similar human type attitudes: belief and knowledge (referred to as information category), and desire, intention, obligation, commitment and choice (referred to as pro-attitudes). Following this concept, merely all objects from the most simples automata-like objects to the most complex ones, can be described by having some form of intentional stance. This fact helps to provide a level of abstraction useful when dealing with the complexity of the latter.

As “compelling” as the intentional notions are for an intuitive representation, description, understanding and explanation of agent based system, it can certainly represent a difficult challenge when one wishes to reason about this inside a logical theoretical framework. Theory for representing intentional notion cannot be based using classical relying on truth functional operator, because this notion is said to be referential opaque. Substitution rule applicable to first order logic are not possible with intentional sentence such as “Janine believes p”, since replacing p with an equivalent will not preserve meaning.

Because of this limitation, other formalisms have been proposed for handling the syntactic rules and the semantic model associated with intentional system. One of the best known semantic model is characterized by the so-called “possible worlds” which is supported by an attractive mathematical tools called the “correspondence theory”. However as compelling as this model is for its logic representation, it suffers from limitations related to the famous “logical omniscience” (see problem due to infinite knowledge caused by statement logical tautology). In this 1995 study, the authors recognized that alternatives theories developed for either belief/knowledge representation (information attitudes) or goals/desire (pro-attitudes) all suffer from various deficiencies and/or limitations representing challenges to the agent theorist.

A more useful and complete theory for agent system should not only try representing the intentional stance but should also try embracing a larger logical framework interested in other components such as the dynamic aspects of agency, the relationship between the agent’s information and its pro-attitudes, the cognitive states change over time and reaction to the environment stimuli, etc. Some tentative work aims to achieve this very challenging task.

Of particular interest is communication between agents, which are modelled using the “speech act theory” stating that all communications are considered as actions performed to bring change to the world (usually to the listener internal state). This theory attempts to classify between the various forms of speech acts, with the two prominent ones being representative speech act for informing objectives and the directives one for requesting objective. See Multiagents system discussion below.

Taken from

1. “Intelligent Agents: Theory and Practice”, Wooldridge and Jennings (1995)

2. “Multiagent Systems, a Modern Approach to Distributed Modern Approach to Artifical Intelligence” Chapter 1 Intelligent Agents, Wooldridge (1999)

Data Warehouse Modeling guideline Part 3: Aggregate Data Modeling

Here's the third and final part of my guideline, ....this time I'm covering very briefly a complex subject : the aggregation strategy.


General

1. A first level of aggregation should be built on top of the atomic database level, mainly for 1) boosting query performance done on atomic data, 2) for storing pre-calculate KPI which are usually aggregated data, and optionally to archiving historical data not kept at atomic level.

2. The first level aggregation is best when in the same infrastructure as the atomic level (i.e. relational model). It can be accompanied with a second-level which uses OLAP independent Cube technologies.


Dimension Entity

1. Two roles dimension

1.1. In the context of a migration count aggregation, only the dimension of interest must have the dual roles (Origin and Destination), all other dimension functionally dependent on the migration i.e. are modified after the migration, must be defined and fixed as either Origin or Destination.



Fact Entity

1. Definition of the type of fact

1.1. In aggregation the rule of not mixing two types of facts into the table (instantaneous event-based vs. interval or end of period snapshot-based) can be violated. This is because aggregation usually needs to provide a wealth of information and because there is a need to limit the number of aggregate to build/maintain

1.2. (1.1 continued) The mix can only be possible under the condition that it does not affect the granularity. Granularity must always be fixed, i.e. a state-based EOP aggregation cannot accept event-based counts if these affect the subscriber-EOP by axis grain.

1.3. (1.1 continued) If any event measure affect the grain (may potentially require to have BOP by axis to be represented because number of subs that have cancelled some conditions may not exist in the original grain) then it can't be integrated. In this case, it is much more suited to use OLAP Cube which deal easily with the BOP-EOP comparison.

1.4. (1.1 continued) Furthermore, great care must be applied in doing this, because the occurrence of all axis is inconsistent, event should track occurrence of axis at the time of the event while EOP state number track them at the EOP snapshot time. This implies that computation between measures is not possible by axis.

2. Definition of Measures

2.1. In the aggregation, it is common practice to store explicitly independent measures (e.g # of active subs, # of ghost subs, or Nb of sms, Nb of normal calls) that correspond to various dimension condition. This helps decreasing the granularity of the aggregation table and ignores the dimensions only needed to identify the independent measures.

Martin

Data Warehouse Modeling guideline Part 2: Fact

Here's the second part of my small ad-hoc modeling guideline applicable to data warehouse using relational database.

Fact Entity

1. Definition of the type of fact
1.1. Never mix two types of facts into the same fact table (instantaneous event-based vs. interval or end of period snapshot-based). Exceptions to this rule are frequent in the aggregation layer (see rule in Aggregation section).

1.2. Dimension definition must restrict the use of this dimension to fact of same nature (e.g. a dimension defining transaction type must not be tied into snapshot fact type).

1.3. (1.2 continued) The nature of the dimension must be clear enough, in other words its definition should not depend on the fact it is tied to.



2. Definition of Measures

2.1. Store an attribute as measure into the fact table only when this detail info is necessary (e.g. for legal issues) but no end-user query will use this as constraint.

2.2. Every limited value space attribute should be designed as dimension and not measure.

2.3. Try avoiding the use of intelligent measure taking a contextual meaning linked to some business rule. All measures should be modeled denormalised even if this implies space wastage.

2.4. (2.3 continued) Because these are usually encapsulated into "hidden flag", it is subject to misunderstanding and misuse in the report.


3. Dimension choice to tie with fact

3.1. All dimensions that qualify the facts should be present in order to avoid any uncertainty in one fact (the goal is to have a fully qualified fact). When a single amount is described only partly by one or many dimensions, it must be corrected by adding every new dimension that will fully qualify the amount.

3.2. Limit the number of dimension for the first implementation of a star (especially for large fact table), so it can evolve with time.

3.3. Try not to tie dimensions that are not directly a characteristic of the fact (e.g. derived dimension dependent on different relationship, subscriber having a has-a relationship with other dimension), these relationships should be modeled in the aggregation strategy.



4. Snapshot-based fact

4.1. Although it seems possible to mix two interval periods (e.g. weeks, months) into the same fact, this will always result into difficulty with future requirements implying usually the addition of new facts. Note that this apply only when the same Date hierarchy is used for all intervals period, meaning that end of period that coincides on the same day will use the same surrogate key.


5. Exceptions in fact

5.1. Exception handling for a fact is necessary whenever the fact is not applicable throughout the dimension space. That is handled with foreign keys (usually negative keys) pointing to an exception for all the irrelevant dimensions.

5.2. (5.1 continued) The exceptions applied to the entire fact (fact-scope) require specific exceptions values stored over the whole hierarchy dimension structure.

5.3. (5.1 continued) These fact-scope exceptions are commonly of the following nature: 1- Error, 2- Unknown, 3- Not Applicable, 4-To be defined, 5- No history, 6- No reference, etc.

Martin

Data Warehouse Modeling guideline Part 1: Dimension

Here's the first part of my ad-hoc modeling guideline listing general advice as well as more specific ones related to dimension. Please bare with me all this "techie jargon", it is mostly stolen from R. Kimball terminology in dimensional data model. This guideline assumes a good understanding and knowledge on datawarehouse concept and data modeling.


Atomic Data Modeling (in the sense of most granular transactional data)

General

1. Limit the first implementation to a minimum number of star schema and number of source data. Usually a first implementation would involve a single transaction table accompanied with its snapshot state.

2. Avoid the use of generic terms for naming dimension and columns, especially at the start of the implementation process of datamarts.

3. Try to impose as many constraints as possible ("Not Null") for all attributes upfront and not after the creation of the table. Fact measure or Foreign Key should never be equal to Null to facilitate indexing and ETL processing.

4. Avoid the mix of transaction based and snapshot based fact records inside the same fact table. This usually results in a variable time interval fact with two links with timestamp: an Effective date and an End date, the effective is usually triggered by an event while the period defines a state which finish at the End date. This generates a number of limitations such as: 1- difficulty to evolve the model with new dimension, as when we add any changes this affects the previous history of record, 2- time axis does not have a unique definition in reporting tool such as BusinessObjects, 3- query of type EOP which counts number of record having certain status are usually slower and more cumbersome in SQL.



Dimension Entity

1. Junk dimension

1.1. Use junk dimension to gather various types of unrelated and unstructured information that would generate too many mini-dimensions.

1.2. Do not use junk dimension when data is volatile and/or the domain of information covered by these tends to grow.

1.3. Junk dimension can sometimes be used when there is a need to tie multiple dimensions into a large volume star schema that cannot easily accommodate for new dimensions.

1.4. Junk dimension usually needs to be accompanied with database View that make more sense for the business user (can be done at reporting tool level as well)

1.5. Junk dimension can sometimes be used to support a many-to-many relation with the fact. This is only recommended for a dimension having the many-to-many relationship upper bounded with a relatively small number (i.e. the number of columns necessary to have in the dimension is small and static).


2. Big dimension

2.1. Try to split this dimension into smaller dimensions, otherwise the SCD type II is unrealistically implemented.

2.2. (2.1 continued) For example, customer can often be splitted into entities related with: Identity, Account, Demographic/Geographic and Value-based derivable attributes.

2.3. (2.1 continued) The relationship between these new dimensions could be evaluated as fact, i.e. plugged directly into a history fact table (e.g. customer relating to demographic/geographic) or directly linked with snowflakes (usually the link is SCD type I).

2.4. Big dimension often hides multiple smaller dimensions.


3. Interrelated dimensions

3.1. Do not store attributes from external dimension into another dimension (e.g. customer). These usually represent relationship (usually current situation) that should fundamentally be evaluated through a fact.

3.2. (3.1 continued) Storing these relationships into dimension only creates data redundancy and is prone to data inconsistency.

3.3. (3.1 continued) If no history is needed from those relationships and no detail is necessary (i.e. there is no need to evaluate them as fact), then all dimensions should be merged into the most significant one.

3.4. (3.1 continued) Compromises must be taken between the size of the dimension vs the number of smaller dimensions (see Big dimension tips). This recommendation can have exception, especially when relationship between the two dimensions does not change and that the link between the two is difficult to obtain through the fact (e.g. Customer relating to Dealer). For this exception, it may help and improve performance for aggregation building or query restitution to duplicate the link into the most significant dimension (e.g. customer).


4. Date dimension

4.1. Date aggregated dimension (grouping of days) usually encompasses inconsistent time period. These periods should be modeled as independent hierarchy structures. Period not having a fixed number of days (e.g. week period as defined in Oracle) should have a Period_size indicator for averaging.

4.2. (4.1 continued) This offers the advantage of having a single entry point (one dimension to create) for storing all various independent period structures such as week-year, month-quarter-year, holiday-year, etc.


5. Exceptions in dimension

5.1. Dimension-scope exceptions are applicable whenever there is a hierarchy level attribute not applicable for the specifics dimensions member. This attribute must take the exception value.

Martin

Data Warehouse

My professional work and interest have long been involving the creation and modeling of very large databases designed for reporting and analytical needs called, data warehouse. These systems also referred to as OLAP or On-line Analytical Processing (a term coined after its couterpart called OLTP, On-Line Transaction Processing in which the data is volatile and transaction-based) can be implemented in multiple ways:

• ROLAP which uses a relational engine (exactly like DBMS used in OLTP, e.g. Oracle)
• MOLAP using a multi-dimensional engine (e.g. Cognos PowerPlay)
• HOLAP which is a combination of the two approach often keeping the atomic fact data in the relational database and build the aggregation for each dimension inside a MOLAP cube (e.g. Microsoft Analysis Services)

These systems complexity arise mostly from its data source characteristics (high volume, high heterogeneous, medium quality, etc.), from its performance expectation (should report millions of facts in split second! ), from its multi-department initiative and often misaligned requirement (in the case of corporate warehouse), from its challenging selling point (supposedly can report on any data produced within the company), and from its vast technology offer on the market.

For those interested more on this subject, I would recommend books from Ralph Kimball who has developed some interesting theory taking very pragmatic approach.

In the next few notes, I'll be sharing some Guidelines I've written after experimenting with modeling real-world datawarehouse using the relational paradigm (i.e. ROLAP). These notes will give unstructured tips and hints for building dimensional model into relational database. Although my notes are based on real experience done on projects implemented in Oracle, they are far from being exhaustive.

Martin

Web site journey

I've hesitated probably too much time in trying to decide which form should this site be in... (should it be a blog, a simple and static personal page, or a fancy dynamic CMS (Content Management System). I started looking for blog system in the open source (ok I'm biased toward using Java since it is the language I have experienced with and also probably too lazy in learning an easier one such as PHP ;-). So I've installed and played a bit with tools such as Pebble and Roller Weblogger.

These tools worked perfectly fine and seems to be well designed so I even posted my first few notes using these. Later I decided to look for a more organized site structure with clearer hierarchical content form than what a weblog, with its calendar-dependent organisation, could offer. I realized that the concept of CMS (content management system) offer very convenient and powerful way to keep a dynamic personal site. So I've checked and found out that there are literally thousands of CMS only looking at the open source ones, but lucky for me most of them are PHP-based, so they were quickly eliminated ;--))! I even installed Magnolia for a couple of months and created a great deal of content.

However, I'd assumed from the beginning that hosting these applications would be a simple matter of installing a Tomcat server on my desktop and loaded it up with the web app! Although this turns out to be true, the biggest obstacle was not related to software installation and configuration but rather to hardware networking: my upload Internet link is simply way toooo sloooow to encourage even the 14.4Kb modem users to check my site! (without mentioning the trouble with having a 24/7 server in my basement...).

I could have looked up for an web hosting with Tomcat installed (there's plenty of them), but then I decided to simply go the easy way and registered with Blogger!

Martin

First Weblog

This blog contains some information concerning me (Martin Ouellet), my professional interest, a little of my personal interest, and any other subject that I'm willing to share to the entire world (not literally, but this becomes a potential reality with Internet...).

Why did I choose to publish this site, first and foremost because I'm very interested in new technology and more specifically the Internet, Java, Data Warehouse, Data mining, Telecom, Software development in the open source community and this site simply re-iterates this interest.

Secondly, because being away from my family and friends from time to time (I've moved back and forth from/to Canada and Europe in past years) this allows me to stay in contact with those that count most to me.

You'll see in the attached image myself walking with my first child and enjoying my time in Lausanne (directly back from me was my former apartment in "la cité" in the heart of the old town).