Analytical Consolidation of Reporting Requirements – 1

In this article, I will try the o give the general idea about the analytical consolidation of reporting requirements. I will dive into more technical details like mathematical notations, meta data and rules in following related articles.

This is a new concept -well at least for me- because I developed it. Accordingly, some terminology and table notations used in this and following articles are also new. Similar concepts might have already been developed by someone else independently (actually there must be similar concepts, because this is a quite intuitive and common sense concept), but so far I couldn’t find anything in that vein.

The analytical consolidation concept shouldn’t be confused with the consolidation of financial statements, which is a totally different subject matter within the realm of accounting. Analytical consolidation explained here is a mathematical concept about the information content of tables.

What is this analytical consolidation of reporting requirements about? In summary, it is a concept and methodology for the aggregation (compression) of reporting requirements whereas each report is represented with a table. I will try to explain this with some simple examples.

Assume, we have the following business scenario:

• Operational IT systems handle daily business operations and transactions. Analytical IT systems are used for only reporting purposes.
• Reporting applications like Business Objects extract data from the analytical database in order to generate the desired reports.
• In order not to jeopardize the operational system, reporting applications run only on the analytical database; that is, they don’t have access to the operational database.
• The operational system is tuned for the security and performance of transactions, the analytical system is tuned for the flexibility and reporting performance.
• Because the transactional database is the primary information source, all the source data that are needed to generate the reports must be transferred from the transactional to the analytical database (Data or Business Warehouse in Business Intelligence jargon).

The question is, what minimal information is needed from the source system in order to generate all the desired reports? This concept was inspired by this question alone as I was working at an insurance company (Allianz-Suisse, 2005-2007) as Business-IT coordinator responsible for the financial planning process. Somehow, I was also involved in a large scale reporting project. I still remember the name of the database in which all the reporting requirements were stored: meta-Sultan! With some VBA programming on this MS Access database I was able to demonstrate that analytical consolidation really works.

To continue with the example, assume following two reports are requested by the management:

R1: Sales by country and year (formal notation: sales {country, year})
R2: Sales by region and month (formal notation: sales {region, month})

… where region is a subdivision of a country; that is, a country is divided by multiple regions.

Question:
What is the minimal information required from the source system (i.e. transactional database) required to generate these two reports?

Answer:
A single table T1 with sales by region and month (i.e. the exact content of the second report R2) is sufficient to generate both reports because we can get the first report R1 by applying simple aggregation operations on the source table T1. By summing up sales by regions we get sales by country; by summing up months we get sales by year.

Let’s apply some inductive reasoning and generalize the observations related with the example above:

We applied a table operation (i.e. aggregation of sales over regions and months) on the source table T1 to obtain the first report R1 (which is actually another table). Possible table operations are not limited with the simple summing aggregation; table combination and separation are examples for other table operations, as we will see in the following related articles.

The information content of the source table T1 encompass (or contain) the information content of the report R1, because we can obtain R1 by applying some table operations on T1.

The information content (IC) of the requested reports may have some redundancies among each other, like in the example above. If there are redundancies, all the reporting requirements can be consolidated to result in a smaller set of source tables, with which all the desired reports can be generated. This is in fact what the analytical consolidation does: Consolidate the reporting requirements to get the minimal set of source tables used to generate all the required reports.

We could also write IC(T1) contains IC(R1, R2) because we know that with T1 alone we can generate both reports R1 and R2.

That we can generate a set of reports (R1, R2, … Rn) by applying some table operations on a set of source tables (T1, T2, … Tm) means, the information content of the source table set contains the information content of the report table set.

table operations on (T1, T2, … Tm) –> (R1, R2, … Rn)

In that sense, analytical consolidation works like a reverse function. We start from the reporting requirements to get the minimal set of source tables:

consolidate (R1, R2, … Rn) –> (TR1, TR2, … TRm)

Let’s put everything together to complete the simple business scenario, and show how it works step by step:

Let’s put everything together to complete the business scenario, and show how it works step by step:

1) Management defines the reporting requirements (R1, R2, …, RN). Allegedly, all these reports need to be generated for supporting their decisions. I say allegedly, because my personal experience showed me that most of the requested reports are in fact not needed for anything at all, but that is not the issue here.

2) Analytical consolidation is applied on the reporting requirements in order to get the minimal set of source tables that are required to generate all the desired reports.

consolidate (R1, R2 … Rn) –> (T1, T2, … Tm)

3) The data content of the consolidated tables (T1, T2 … Tm) are extracted from the operational database and transferred to the analytical database.

4) The analytical system is now ready to generate all the desired reports. The analytical consolidation methodology can also clarify the table operations that should be applied on the source tables to generate all the reports.

What else can analytical consolidation do?

• Clarify whether all the reports can be generated with the available source data; if some reports cannot be generated, find out, what information is missing to generate these reports.
• For any given sets of table like (TA1, .. TAn) and (TB1 … TBm) find out whether the information content of the first set contains the second one, and vice versa. Which tables of the first set can be generated by applying some table operations on the tables of the second set, and vice versa.

I hope, you can now see the meaning and generality of the concept analytical consolidation of reporting requirements. It is actually more general than a concept about reporting requirements; it is a kind of information theory about tables (or relational groups) in its infancy.

Please don’t hesitate to send your comments for any remarks, questions or corrections. All feedbacks are welcome.
Tunç Ali Kütükçüoglu
Zürich, 29. February 2012

Note: Registered members can download supplementary PowerPoint slides for this article from the download page.

Copyright secured by Digiprove © 2012 Tunc Ali Kütükcüoglu