multi fact query using IDT

[Former Member]

Hi All,

I have a requirement where there is a fact less fact table (my fact 1) with id's (i'll do count distinct on these IDs to get the count of calls etc). To keep things simple lets assume there are 2 dimensions. one is geography (joins to fact at zip level) and another time dimension (join at day level ... call date). Using these three tables I can easily find "# calls" in each ZIP or Area or Region of the time period the user is interested in.

Note: Though there are say 30 days in a month or 20 work days (excluding weekends and holidays) in a month , every day may not have a call registered.

Hence to get the exact "# of work days" in any given period, I have create another fact which all all day keys and a flag that says if that day is a work day or not. (this is my fact 2).

please check the image for all details.

Measures in my report:

# Working Days	Count(distinct case when WorkDays_fact.work_day_flag = 1 then Day_key else null end)
# Calls	count(distinct Call_Activity_fact.Call_ID)
# Calls Per Day	# Calls / # Working Days

"# Working Days" and "# Calls" are directly from fact but "# Calls per Day" is a derived metric based on the first two.

User input:

User Input:

Area: A1, A2, A3

Time Period:  Jan 23, 2016 to Jul 29, 2016 (start Day to End Day)

for the above selection my report output requirement is :

Area	# Calls	# working Days	# Calls per Day
A1	100	131	0.8
A2	150	131	1.1
A3	2000	131	15.3

This is a self-serve universe that I will be exposing to the users to create their own reports and the above example report is one such.

Hence I have to create these measures and data model joins all in the universe itself so that the user can just do drag and drop in the report.

Current Query being generated (wrong one) that I currently get from my universe:

Select g.Area,

Count(distinct case when f2.work_day_flag = 1 then Day_key else null end) as "# Working Days",

count(distinct f1.Call_ID) as "# Calls",

from

Call_Activity_fact f1

inner join time t on f1.day_key = t.day_key

inner join geography g on f1.zip = g.zip

inner join WorkDays_fact f2 on f2.day_key = t.day_key

group by g.area

My Expectation of query output: (2 queries to handle the measures properly):

Query 1:

Select g.Area,

count(distinct f1.Call_ID) as "# Calls",

from

Call_Activity_fact f1

inner join time t on f1.day_key = t.day_key

inner join geography g on f1.zip = g.zip

group by g.area

Query 2:

Select

Count(distinct case when f2.work_day_flag = 1 then Day_key else null end) as "# Working Days",

from

WorkDays_fact f2

inner join time t on f2.day_key = t.day_key

"# Calls" to come from query 1 and "# Work Days" form Query 2 and then behind the scene it should calculate the derived metric "# Calls per Day".

Can anyone help me achieve this through IDT? i tried searching SCN for multi-fact seltting but couldnt get anything similar to my problem.

I tried creating 2 contexts but didnt help:

context 1: Time, Fact2 (Workdays_fact)

context 2: time, geography, Call_activity_fact(fact1)

Thanks,

Srikanth

[mhmohammed]

Hi Srikanth,

Why don't you have that Work_Day_Flag in the Time Dim? Its fine.

Now, the solution, please create 2 new Detail objects (with Area as assigned dimension) for #Calls and #Working Days, and finally create another Detail object for #Calls per day with the formula you have. Check if that works and let us know.

Thanks,

Mahboob Mohammed