BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: Upcoming Events: PGHBOUG: Aug 23.

[IDT 4.x] Calculated Measures for Human Resource Model


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
icebergue
Forum Member
Forum Member



Joined: 12 Jun 2018

Posts: 3


flag
PostPosted: Tue Jun 12, 2018 3:35 pm 
Post subject: Calculated Measures for Human Resource Model

Hi, I've been modeling a Human Resource model using Dimensional Modeling aka Star Schemas. The fact table granularity at the employee level, as it is a periodic snapshot of all the employees at a certain moment in time (a specific day).
There are several dimensions associated with the employee: his department and company, his category and subcategory, and some others.

I want, at the Business Layer level to leave several metrics that are relevant to the users. I cannot simply create a general metric and create filters and force the users to always create what they need at report time in Webi, although that is also done easily.

My problem is on calculated measures. At the business layer, I've created 2 measures.

Measure A:
select is a distinct count of a field X
where has an IN clause: IN (1,2,3)

Measure B is a subset of A, basically:
select is a distinct count of a field X
where field = 1

Measure C is a ratio that gives the weight of B on A:
Should be in theory B / A

But if I drag the @Select Measure B / @Select Measure A (using the BL objects, not the underlying SQL query), it does not work. It provides 1 on a query like filters are being ignored.

Anyway, what is your advice to achieve this, since this makes only sense at non-granular levels (employee), like the Company level? It's a factless table, and mostly all measures are counts of employees with certain criteria on each dimension.

Please feel free to advise and feel free to ask any questions.

Thank you

EDIT: At the moment I am using distinct counts with a case clause replacing the where as a workaround.
Back to top
jemstar
Forum Associate
Forum Associate



Joined: 30 Mar 2006

Posts: 828
Location: Dublin , Ireland


flag
PostPosted: Wed Jun 13, 2018 3:32 am 
Post subject: Re: Calculated Measures for Human Resource Model

When it comes to measures, I would use CASE statements over placing anything in the WHERE clause of an object. This means that multiple measures can be added to a query without conflicting each other
e.g. Male staff count, Female Staff Count

I would also create predefined universe filters that would be useful to users
e.g. Permanent Staff, Contract Staff, Maybe some filters based on the Leave date or start date

I would also make extensive use of universe based prompts in both measures and pre-defined filters

_________________
regards,

James

http://www.jemstar.ie/

Available for Contract Work In Ireland (and remote work worldwide)

Expert in Universe Creation directly over Enterprise applications (JDE, BMC Remedy, EBMS, Jira, ArisG, Bespoke, Mercator, RAPID, M3, Movex, Investpro)
Back to top
icebergue
Forum Member
Forum Member



Joined: 12 Jun 2018

Posts: 3


flag
PostPosted: Thu Jun 14, 2018 7:05 am 
Post subject: Re: Calculated Measures for Human Resource Model

Hi James, thank you for your feedback. That's what I'm currently doing, so it seems I'm on the right track. I'm not an expert on Universe creation but I've read several best practices pointing the same issue in WHERE clauses so I thought it was best to register in this community and hear from more experienced professionals.

Best regards
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0301 seconds using 18 queries. (SQL 0.0033 Parse 0.0009 Other 0.0260)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo