Using N-Tier in Business Intelligence

Question

I have read that you also can apply N-Tier for Businesss Intelligence based on these criteria: *Presentation
*Functional logic
*Data

What is your experience when you are using N-Tier?

// Fullmetalboy

Answer 1

N-Tier may be a disingenuous term

I think the term 'N-tier' is disingenouos whe used in the context of a business intelligence system. In transactional systems 'N-Tier' describes a distributed system with an application server, ESB or some other networked middle tier. Data warehouse systems don't work in a way analogous to this, so the term is likely to breed confusion.

Separating data and functional logic

You could build a system in terms of data that's as raw as possible and then put a transformation layer on it, which is then consumed by a reporting layer. The transformation layer could take the form of:

• A series of database views.

• A metadata layer from the reporting tool (e.g. a Report Model or a Business Objects Universe). A category of tools known as 'Enterprise Information Integration' extend the notion of the metadata layer into something like an in-memory ETL tool, allowing the reporting or functional logic layer to implement complex transformations. However, this approach is fiddly to implement and not widely used unless something precludes implementing a more conventional ETL architecture.

• A suite of data marts with their own ETL.

This would give you 'Data', 'Functional Logic' and 'Presentation' layers as per your example. Some data warehouse systems are implemented a bit like this, although it (IMHO at least) would be something of an anti-pattern. Some issues with pushing business logic downstream of your ETL include:

• Depending on the capabilities of your functional logic medium, the possible transformative capabilities may be limited, resulting in leaky abstractions that limit the value of any ad-hoc reporting facility placed above them. Ad-hoc tools need clean data that behaves consistently, and the data really has to be in a format that plays nicely with the tool.
  
  If the data is not consistent, clean and in the right format you are effectively limited to bespoke SQL reports of the data or building supplementary data marts to support ad-hoc facilities. Relying on data marts has a tendency to proliferate a large volume of ad-hoc tertiary ETL processes with overlapping but subtly inconsitent functionality. This tends to generate reconciliation and data quality issues and fails to provide a 'single source of the truth.'
  
  Generally this situation will generate a large maintenance workload and erode confidence amongst the user community.

• A database schema that is not optimised for reporting may perform poorly.

• 'Raw data' implies that the data is kept in a form that is fairly isomorphic to the source, which is mutually exclusive with any notion of conformed data.

• 'Raw data' is also typically available from staging and archival areas if needed for auditing. You will get a class of power users who 'just want the data', and these people may have a lot of political influence. Allowing this to continue alongside a data warehouse project fails the 'single source of the truth' goal.
  
  If the warehouse has a proper conformed data mechanism (e.g. an operational data store) then this is supposed to provide the single source. Dealing with conflicting definitions and user communities who insist on their own stovepiped systems is a whole topic in itself.

Nonetheless, this is more common than might be expected. I think the main reason one encounters data warehouse projects implemented in this manner is that ETL tools are quite clumsy to work with if your requirements for tranformation logic are complex. ETL tools often have the effect of dumbing down the architecture and fobbing off logic into the reporting layer, which significantly degrades the effectiveness of the data warehouse initiative. The volume of work and presence of a central database gives the illusion of a data warehouse while providing little of the benefit.

Another N-tier view of a data warehouse

One could interpret 'Data', 'Functional Logic' and 'Presentation' as an ETL and reporting process in a more well organised data warehouse system. In this case, 'Data' could be interpreted as the staging layer, 'Functional Logic' implemented in the ETL, presenting a dimensional data store and/or suite of data marts, and 'Reporting' implemented through a reporting and ad-hoc query suite.

Considered harmful

For this reason, I think the concept of 'N-tier' is unhelpful and even a bit disingenuous. It sounds a lot like something a middleware company or consultancy might describe in a white paper - a flawed and even somewhat misleading theoretical notion that sounds good on paper.