Counters in Analytic views

[former_member215109]

Hello,

There is an option to create counter type measures with calc views that perform a count distinct of e.g materials, customer etc. However, there is no such option with an analytic view. I would hate to create a calc view just to use the counter feature of calc views. Is there an opportunity to create a calculated measure in an analytic view and write the SQL to do a count distinct in the expression editor?

Thanks,

[former_member199876]

Hello,

There are two options,

Option 1:

You may add the column from which you need to get the distinct count, as a Measure, with an aggregation type of 'COUNT'.  This can be set in the Semantic Node.

In this option, please switch the flag "Allow Relational Optimization" to False, if you are getting inconsistent counts.

Option 2:

Add a numeric calculated column with a constant value of '1' and aggregate it with SUM in the Semantic node.

Let me know if that works for you.

Regards

Ajay

[Former Member]

I wanted to count # of VBAP records and first did that in the calculation view (created a counter on attribute VBELN with POSNR). This worked fine but response time of my view having 15 mil VBAP records went up to about 18 seconds.

Then I tried on the analytical view on VBAP: created a calculated measure (SUM) with static value 1 doing a calculate before aggregation. Same result but response time was ~3 seconds.

This test was on SP4.

Haven't tried again on SP5.

[former_member184768]

BTW, one point. With the very basic nature of columnar database implemented in HANA, the distinct count of material / articles / documents is quite easy from database perspective. This is whole reason for using the columnar database as the processing will happen on the column itself which is stored in most efficient manner in the DB.

Alternatively you can check the distinct values in the columns using the distinct option on the table in HANA studio.

For reporting purpose it should not impact the performance if you select the distinct value from the analytic view.

Regards,

Ravi

[hai_murali_here]

Hi,

Counters has been designed only for Calc Views and not for Analytic Views.And creating Calc Measure with SQL script is not possible.

Rgds,Murali

[Former Member]

Hi Murali

  I have a query over here , calculation view is mainly used when we can't get the desired facts from a single table , if we use calculation view for the purpose of count will that not degrade the performance of query execution?

Thanks

Santosh.

[former_member182277]

Hello Santosh,

It will not degrade the performance. As you are filtering the data as you are selecting the distinct records from the view/table.

Regards,neha

[hai_murali_here]

Hi Santosh,

The use of Calc Views are to reduce the data retrieval time by using the CE functions and hence improving the performance.Whenever you want to implement some complex business logic which cant be addressed with Analytic Views,you will go for Calc Views.There is a very good comparison table about all these things in SDN.So when you use Calc Views,it will improve the performance.

And using Count will not affect the performance as we retrieve only limited set of values here.Ideally Count should have been included in Analytic View itself rather than putting it into Calc Views.But that'show they designed it

Rgds,Murali

[Former Member]

Hi Neha and Murali

  Thanks for the elaborated explanation on that point .Murali can you provide the link for the comparison table .

Thanks

Santosh

[hai_murali_here]

Here it is,

https://www.experiencesaphana.com/blogs/datamart/2011/10/17/choosing-the-right-persistency-model--un...

Rgds,Murali

[Former Member]

Hi Murali,

I do not see any option to create counter in calculation view.

Only these options are available. I am using HANA Studio 1.0.68

I an new to HANA. could you help me in this.

Regards,

Mathivanan

[hai_murali_here]

Hi Mathi,

You have the Counter option under Calculated Columns in Calc View with Aggregation node in SP7.

Rgds,

Murali

[former_member182277]

Hello,

I don't think so, that you can write SQL in the calculated measure.

Regards,Neha