cancel
Showing results for 
Search instead for 
Did you mean: 

Calculate Distinct Count for specific Attribute

Former Member
0 Kudos

Hi All,

I am trying to calculate distinct count for specific Attributes while showing non related column. For example

I wanted to show distinct count for Article of "Country, Region, Store" while showing "Country, Region, Store and Banner" column.

Raw Data

Expected Result for (County, Region and Store)

Expected Result for Country, Region, Store and Banner

Anyone have any idea how I can show Distinct Article count for only "Country, Region and Store" while showing "Country, Region, Store and Banner" Column?

Regards,

Kang

Accepted Solutions (1)

Accepted Solutions (1)

lbreddemann
Active Contributor
0 Kudos

This is what you would do with "constant selection" in SAP BW Bex-reports.

Therefore, option

1) use SAP and Bex-reports

or

2) build a separate data stream into you model that keeps the grouping level and join the data before the final projection.

Technically, option 2) is what constant selection also does.

- Lars

Former Member
0 Kudos

Makes sense Lars. Constant Selection (SAP Library - BW Business Explorer)

The question in my mind is whether BW will process this exception aggregation at the BW app server layer or at the database layer.

My guess is unfortunately the former, which means you need a HANA model to achieve this query.

lbreddemann
Active Contributor
0 Kudos

The last time I saw BW on HANA executing such a query it created two FEMS and had them executed on HANA level.

"Just" combining the data for the report was then done in the OLAP layer.

Remember Blog: #BW-on-HANA and the “FEMS” | SAP HANA?

Covers it quiet well.

So, yes, for such non-relational report requirements, just SQL likely doesn't allow for an elegant solution, but the HANA models fortunately do.

Former Member
0 Kudos

I'll have to try this for a real-world scenario some time. Two FEMS combined in the OLAP layer is fine if the result-set is small. If the interim-result sets become large then bad things happen in the OLAP layer!

Same with Tom's Blog: #BWonHANA: OLAP Compiler Demo - I'd be interested to check that the COUNT DISTINCT in BW uses the bw-pop for COUNT DISTINCT that was available in SPS06 for SQL. I noticed this month that Analytic View "counters" do not use it.

As you say, worst case for tricky queries, we can create HANA models. The downside is the HANA models are often query-specific.

Answers (3)

Answers (3)

former_member221711
Active Participant
0 Kudos

Hi HK,

Try using ROLLUP/CUBE operator in GROUP BY clause.

You can have a separate calculated column as COUNTER. along with the operators mentioned above.

Check the links below:

SQL server - Difference between CUBE operator & ROLLUP operator.

http://wikidba.wordpress.com/tag/difference-between-rollup-and-cube/

Regards

Madhusudan

Former Member
0 Kudos

What you say is true, but BW has much more sophisticated technology available in - which allows for potentially very complex requirements to be modeled. Question is whether the optimizer works correctly for this scenario.

If it doesn't, it's much better to use a graphical calculation view and then consume it into a VirtualProvider. This allows for flexible multi-dimensional modeling.

If you use ROLLUP then you have constrained your model to work with a specific result set. Hope this clarifies.

Former Member
0 Kudos

Hi All,

I manage to solve this problem by making the SKU a calculated key figure in BEx therefore a delegated measure in WEBI. This will force SKU to be at  "Country, Region, Store" level as per report dimension.


Regards,

Kang

Former Member
0 Kudos

Sorry Kang I don't quite understand. I thought your requirement was to have the COUNT DISTINCT at a different aggregation level to the measures in the query. Is that not the case?

Delegated measures I thought would only ensure that the measure is not re-aggregated in Webi (which would cause incorrect results).

What Constant Selection does is to allow the COUNT DISTINCT to be at "Country, Region, Store" level even when you have "Country, Region, Store, Banner" in the dimensions.

former_member182302
Active Contributor
0 Kudos

Hi HK,

If you were asking about Static reports, Then you can use window function: DENSE_RANK function to get the rank to display the number ( distinct count ).

Else you can use either of the options mentioned by Lars in the above comment.

Regards,

Krishna Tangudu