I’m not sure if I’m going in the right direction with my data warehouse, data marts, cubes and business intelligence development. Our organization deals with healthcare planning so I have different databases dealing with census, mortality, inpatient/outpatient utilization and financial data. Each database has a main feed of data that is at the annual level for census, mortality and outpatient utilization, quarterly for financials and daily for inpatient utilization. I have created fact tables and dimensions for each database so the users can see all the components that are available for cross analyzing.
Chuck Kelley’s Answer: Wow, good questions, hope my answers are just half as thoughtful.
First bullet I think the answer depends on your architecture and physical attributes. If you want them to be all in a single database, then do so. There are no major problems doing it this way. Regardless, I would create four (or more) Fact tables, one for each subject area (census, mortality, inpatient/outpatient utilization and financial data). Then, you can build whatever combinations and/or aggregates that you need that are needed to aid your user community.
Second bullet Excel is fine, but I would most like create a combination fact table that could then be queried by the user community. You can use relational OLAP (ROLAP) or hybrid OLAP (HOLAP) see documentation under Analysis Services and Flexible Data Mode for more information for using multiple fact tables. If the data is on two or more different time aggregates, I would lean toward a new fact tables with the data rolled up.
Third bullet You can by using ROLAP. I am not sure what you mean by snowflake in your example since it says State/County/Town table. That would lend itself more to star schema. Snowflake would have three tables State, County and Town. So, assuming that you have a single table, create the fact table with two relationships between the fact and the State/County/Town table one relationship for Patient_residence and one relationship for facility_location. Analysis services should run fine.
Fourth bullet One day I will get my analysis services up and running again (I have not had time to work on it and it has been down for two to three months) and will respond to you directly via e-mail (unless one of the other experts answers it). At the moment, I cannot answer this bullet.
Clay Rehm’s Answer: Here are my responses to your questions:
Register or login for access to this item and much more
All Information Management content is archived after seven days.
Community members receive:
- All recent and archived articles
- Conference offers and updates
- A full menu of enewsletter options
- Web seminars, white papers, ebooks
Already have an account? Log In
Don't have an account? Register for Free Unlimited Access