Analytics Software

Hi,

I have set up a model in OBIEE of my star schema data model. For most purposes i only need queries based on foreign key joins between the dimensions and the fact table, but for the customers dimension i would like the count the total number of existing customers for each ranking over time (independent of whether or not the customer made a transaction in that period). In other words, i want to compare the customer dimension against the time dimension independent of the fact table.

The following SQL gives me the exact results that i want, but im unsure of how to go about implementing this in OBIEE:


select t.yearid
,t.weekid
,accnt.rank_w52
,count(distinct accnt.account_id)

from dim_time t, dim_eq_account accnt
where t.dayid >= accnt.start_effective_date and ((t.dayid < accnt.end_effective_date) or accnt.end_effective_date is null)
group by t.yearid, t.weekid, accnt.rank_w52


Any advice on how to approach this would be greatly appreciated.


best regards

TM