How to create one measure in logic layer

User472204-OC

Hi Experts,

I do not know how to create one measure in logic layer based on the business requirement(Count the customer numbers for one time during a period of time).
It is very easy to write SQL in DB, but I do not know how to create model in Admin Tools.
BusinessDate : Customer Order Date
customerID : Customer ID
Platform : Customer Order Platform
counts: Customer Order Numers for one day and one platform
For example（Table）:
BusinessDate|customerID|Platform|counts
20171225|10001|A|1
20171225|10001|A|2
20171226|10001|A|3

SQL :
WITH a AS (
SELECT customerID , Platform , sum(counts) AS COUNTS FROM temp
WHERE BusinessDate BETWEEN '20171225'  AND '20171230'
GROUP BY customerID , Platform
HAVING sum(counts)=1
)

SELECT  Platform, COUNT(DISTINCT customerID) FROM a
GROUP BY Platform

PS:  I find I only use one aggregation, not two aggregation (Firstly Sum, Secondly Count Distinct)

[Deleted User]

Click on "Based on dimensions"...

Robert Angel

+2 to Christian.

Note generally if you cannot do it with one measure you may be able to do it with multiple measures and you can; -

1. Create logical measures (measures based on measures)

2. Not expose all measures to end users

So usually you can do it in the business model layer, without resorting to SQL...

User472204-OC

Hi Christian,

Maybe I can not write clearly.

You can see my sample SQL, I should count users for ordering one time in a period of time.

One aggregarition is sum(orders) in a period of time and count() =1, and find these users,then count distinct it

PS: the aggregate is on different basical index

User472204-OC

Hi Robert,

Yeah, it can be created multiple columns for generating final measure, but I do not know how to create it based on this requirement.

IF I create one meause for sum(orders columns), and filter it for sum()=1，but I do not count customerId columns based on last condition

Robert Angel

What I am saying to you is the way to get your answer is to chop it down into smaller things that you can achieve.

i.e.

Can you count distinct employee id, by platform etc - yes

etc

You can calculate ALL of the elements, you just need to then put together all of the simple / physical measures into a new logical measure - yes?

Robert Angel

Sorry, now you have lost me, can you put it as a simple formula please?

Robert Angel

Is your requirement in plain english; -

By platform, give me the count of (distinct) customers where the customer only has 1 count (not sure what we are counting!) for that platform within a dynamically specified *at run time* time frame?

So if you pull;

Platform, Customer Id, Count

Put a filter on count for =1 (add the time filter, but do not add it to the analysis)

Would all of the entries that you see there be the number of rows that you want as the final answer?

[Deleted User]

OBIEE = models.

OBIEE != SQL queries

I'll leave you with Robert.

User472204-OC

Hi Christian,

Maybe you misunderstand my meaning. I know OBIEE is not equal to SQL query,

I only expressed my business requirement for writing SQL for understanding easily. However ,I cannot know how to create final measure for implementing this quirement. Because users do not do complex operation in ad-hoc. and they want to drag some important columns named dynamically specified  time 、platform and measure to look at the result.

User472204-OC

Hi Robert,

Yeah, your understanding is correct.

count is one column created in DB and is on behalf of order numbers by one day、one customer and one platform.

Your suggestion is firstly that I should aggtegate  this "count" coumns in logical layer for sum , and users should be filter it in ad-hoc and drag customerID in report,then hiden it, right? If it is right for understanding, users should do additional operation in analytic or ad-hoc.

Since this is ad-hoc, users only want to drag one calcuated measure ,not do complex operation. (Such as time,platform and calcuated measure, and so on .)