Analytical view: Measure can be from only a single table SAP HANA

[rejiomusic]

Since not coming from an analytical back ground, I had this basic question that why can we only get measure from a single table out of the joined tables in data foundation layer of anaytic view.  Is there a specific reason or is it a limitation?

When we add measure from two table, it gives error as below:

  Central table not unique. Base measures defined for different tables

Please enlighten

[lbreddemann · Accepted solution]

Hmm... "why"-questions about implementation aspects typically are not satisfactory for anyone not involved in the actual implementation...

But I'll give it a shot anyway

Ok, so what's the problem you solve with an analytic view?

It's that of the data "cube" based on a star schema.

The star schema (and its variations) is a modelling approach where you have all the transaction information - the "facts" - in a central fact table (great naming going on there).

The relevant descriptive information for these facts, aka master data are linked to this fact table and called "dimensions".

Choosing this name only makes sense once you realize how the "cube" is supposed to be used.

The idea here is that you pick the dimensions of your interest in the data and select specific values for each of the dimension you find interesting. With this choice of specific values for dimension you now look into the facts table and aggregate what ever data you find there matching your selection.

In terms of database tables it means, that you want to specify filters on the dimension tables (on the outer tables of the star schema) and by joining to the fact table these filters are applied to the fact table.

Whatever data of the fact table is not filtered away gets grouped and aggregated.

Notable here is that the grouping is based on dimension fields and the aggregation only happens on the so called "measurement" columns in the fact table.

For this funny construct, you don't want to employ classic table-by-table-joining. Instead it would be great to actually be able to combine the different filters from the "outer tables" at once to the fact table and perform the aggregation then.

THIS is what the analytic view does for you.

It is highly optimized to perform this very specific query pattern and lots of the optimizations are build upon the column store table internal data structure.

That's why you cannot have row store tables in an analytic view and that's why you can have only one fact table with measurements in it.

There you go - now you know.

- Lars

[rejiomusic]

Hi Lars/Santosh,

Thanks a ton for your patience and for putting up such a big write up there !

I did understand now the reason why it does so and that is the same i expected it to be, but just to conclude my line of thoughts , a couple more question:

1) So is it something like  thumb rule to big data/Analytics or we in HANA have restricted it for some limitation? i.e. If I have more than one table, I can join them in the data foundation, but cant use the measure from the other table.

2) Also does that mean, I should ideally use a de-normalized table to maximum extent to have better performance at the fact (data foundation level) ?

[lbreddemann]

1) This "limitation" is due to the star schema model and SAP HANA's Analytic Views are optimized to support this model (including its limitation).

Compare that to other platforms and you'll find similar restrictions (e.g. the STAR TRANSFORMATION optimization also is based on a single fact table).

2) You're mixing stuff up here and generalize far beyond sense. How you store your data is not necessarily dictated by a specific way of querying the data later on. Query performance is one aspect but by far not the only one.

Analytical Views are not the only development tool in SAP HANA - you may want to consider the many other options if AVs are not the optimal choice for your use case.

- Lars

[rejiomusic]

Thanks Lars.

I have my answer.

[Former Member]

Hi Reji,

I agree with the Lars comments. In simple terms analytical view has restriction to have measures from only table(important point to remember during hana implementations). I feel this restriction is due to star schema formed by analytical view. Star schema is nothing but Center table(FACT TABLE) having attributes and measures joined with Dimension table(Master data table example material, account number and Customer).

In your example analytical view, when measures from 2 tables are selected for output,system will not able to create the star schema which is the foundation for analytical view. 

So always remember during the implementation analytical view can give measures from only one table.

If you need measures in output from more than one table then go for the calculation view.

Hope my message helped you to clarify the confusion.

Regards

Santosh.