Webcast : ER/Studio Data Architect
A Medical Data Warehouse Model
Presenter: Michael Blaha
Building a large data warehouse that supports the reporting and analytical needs of your organization by using a data model involves several steps:
- Understand the business requirements: Analyze the business needs and requirements to determine the goals and scope of the data warehouse. Engage with stakeholders to understand their reporting and analytical needs.
- Identify data sources: Identify the data sources that will populate the data warehouse, such as transactional databases, external data sources, and data streams.
- Design the data model: Choose a suitable data modeling approach for your data warehouse, such as the star schema, snowflake schema, or data vault. Create a logical and physical data model that represents the structure of the data warehouse, including tables, columns, relationships, and indexes.
- Extract, transform, load (ETL) process: Design and implement an ETL process to extract data from the source systems, transform it into the required format, and load it into the data warehouse. This may involve data cleansing, aggregation, and enrichment.
- Implement the data warehouse: Create the physical database structures, such as tables, indexes, and partitions, based on the data model. Configure the data warehouse for performance, scalability, and security.
- Optimize for performance: Analyze the query patterns and performance requirements of the data warehouse, and optimize the data model, indexing strategy, and partitioning scheme. This may involve denormalization, materialized views, or other optimization techniques.
- Implement data governance: Establish data governance policies and procedures to ensure data quality, security, and compliance. This may include data validation, monitoring, and auditing.
- Create reports and analytics: Develop reports, dashboards, and analytical tools that leverage the data warehouse to provide insights and support decision-making.
- Monitor and maintain: Monitor the performance and health of the data warehouse, and perform maintenance tasks such as updating statistics, rebuilding indexes, and archiving historical data.
- Iterate and develop: Review and refine the data warehouse and data model as business requirements change or as new insights emerge. This may involve adding additional data sources, changing the data model, or optimizing the ETL process.
This session will describe a large data warehouse that we built with the medical data for a large hospital. The warehouse has a Kimball architecture with 200+ dimensions and 100+ facts. The primary input source is from Epic Clarity. We’ll describe the project and give a flavor of the data model. We’ll discuss some of what went right and wrong about the project and take questions from the audience.
About Michael: Michael Blaha is a consultant and trainer who specializes in conceiving, architecting, modeling, designing and tuning databases. He has worked with dozens of organizations around the world. Blaha has authored seven U.S. patents, seven books, many articles, and two video courses. His most recent publication is the Agile Data Warehouse Design video course from O’Reilly. He received his doctorate from Washington University in St. Louis, and is an alumnus of GE Global Research in Schenectady, New York.
Topics : Data Modeling,
Products : ER/Studio Data Architect,