I apologize in advance for the wordiness of this post. I never know what will be important so I usually write more than needed.
A little background
I have a week and a half to create a POC for using OLAP behind client-facing reports. We aren't committed to a reporting tool and we have a lot of data, so OLAP seems to make sense(?). Right now we use SSRS 2005 against a somewhat flattened reporting database but we have a new client who's asking us to become sophisticated quickly.
I worked with SSAS 10 years ago - built cubes and simple pivot tables off of them - no MDX. I'm savvy with SSRS, but against relational sources. We have no dimensional model so I have to mock one up. I was going to do the mocking in SSAS 2012 (against a SQL 2005 DB).
Requirements for the POC
Report information about physician performance across multiple attributes
Dimensions:
- Time (Date/Month/Year)
- Physician
- Physician Specialty (1:M with Physician)
- Affiliation 1 > Affiliation 2 > Affiliation 3 (hierarchy, 1:M with Physician)
- Registry > HEDIS Measure (hierarchy, M:M with Physician, HEDIS hereinafter because "Measure" gets confusing)
Measures (lowest granularity is Physician/Date/HEDIS - drillthrough to patient data will be necessary at some point):
- Patient Population
- Patients Seen (a portion of Patient Population)
- Score (this is our KPI - quotient % of Patients Seen / Patient Population)
- Quartile (based on Score; measured for Physician, Affiliations 1, 2 & 3, HEDIS and Specialty across Day/Month/Year)
I have created a functional cube with all of the dimensions and the two additive measures (Patient Population & Patients Seen). I went to add Score and Quartile and froze. Now I'm in analysis paralysis mode and panicking. I didn't know MDX was so unintuitive (or maybe I'm just dense), and that calculating quartiles would be such a problem!
So now I'm trying to throw something together in the database with views and static tables. I'm pretty junior when it comes to dimensional modeling. I need to design the tables for the quickest, easiest cube development and report turnaround possible. It doesn't need to be perfect, but I haven't seen a project like this all the way through and I would love some advice on how to avoid running into obvious "gotchas" during cube and report development due to poor DB design choices. Can someone can give me a generalized "what would you do in my shoes" run down?
Here are some of my questions/concerns in the form of mental vomit
So I have fully additive and non-additive measures, right? (I'm not even sure what Score is - I don't think it qualifies as semi-additive?). Anyway, I'm torn as to whether to store these non-fully-additive metrics in a Measure Dimension or multiple fact tables at different granularities.
It seems like going the fact table route might be less confusing, but then does each fact table get its own cube and drill down/across would be accomplished through some sort of linking in Excel or SSRS? E.g., you're looking at the YTD Physician Scores by Annual HEDIS Quartile...how do you drill down to the MTD Physician Scores by Monthly HEDIS Quartile if they are in different cubes? Or would they be in the same cube in different Measure Groups maybe...? Or if I go with the Measure Dimension and use a single cube, how do I protect the user from the scenario above...they're looking at the YTD Physician Scores by Annual HEDIS Quartile, then replace Annual HEDIS Quartile with Monthly HEDIS Quartile - how is such a thing that prevented? Or is this scenario even legit?
I'd love to throw this in SSRS where I can control things through parameters but how hard is it to such against an OLAP source? Not to mention interactive graphs?
I am so confused right now I don't even know if these questions are making sense. Any help (or even links to succinct documentation that you found helpful) would be great!
Some thoughts:
LastChild
, i. e. each month shows the value of its last day, each year the value of its last month, etc. For the "patients seen", I am not sure what you want to do: Should this be distinct patients seen? In this case, you cannot aggregate across time. You would have to have the data on patient level, and link the patient dimension (possibly just consisting of the patient id and invisible to end users) via a many-to-many relationship to the main fact table. Then the measure could be a DistinctCount.[Measures].[patients seen] / [Measures].[patients population]
.WITH clause
: Define it as a member of your time hierarchy using the Ytd
and ParallelPeriod
functions.WITH
clause in the MDX that you write in SSRS. I would rank the set on which you want to calculate the quartiles, and then use a CASE
expression with four WHEN
clauses for the four quartiles, something like `WHEN rank >= count / 4 and rank < count / 2 THEN "Q2", where you would have to decide how to exactly handle the borderline cases.