What to do if BI tool doesn't support drill-across?

[gnilrets]

I'm building a data model for a healthcare company. We have an enrollment fact table that counts members in a month and a claims fact that keeps track of paid claims. We need to calculate a claims paid per member per month (PMPM) by summing up the dollars paid in a month and dividing by the number of members enrolled in a month.

Easy as pie to do in a drill across query. Unfortunately, our BI tool (SAS EBI) does not support drill-across queries! Any suggestions for how to deal with this?

Right now, one idea I have is to create a claims-enrollment fact table that have the dimensions shared by both subject areas. However, we also have confinement, premium, risk, groups, and financial subject areas and creating all possible drill-across fact tables would be impossible without massive resources. My other idea is to create drill-across views for each specific business question requiring drill-across, but that would be far less than ideal as well.

Any thoughts?

Thanks,
Sterling

[ngalemmo]

SAS lets you write SQL doesn't it? The query is a join of two aggregate subqueries or a union of the same. It shouldn't be that hard. If it is, you may want to look at other BI tools, or use a view.

[gnilrets]

I can write SQL to create a view that does a drill-across, but then I'd have to write one for every report that needs drill-across.

[Sponsored content]