SQL Data Warehouse Design Patterns
There are clear rules of modelling an OLTP relational database developed by Boyce–Codd before I was even born. However, dimensional modelling patterns are still rather vague and modellers are faced with some
tough decisions in real world of complex data relationships, tricky/absent reporting requirements and various aspects of performance and ease of use. I would like to present a set of design patterns to use when tackling common DW modelling problems and challenges: when to use star or snowflake, junk and mini dimensions, bridge tables and other data warehouse modelling tricks.
There are many books on DW terminology and general theory, so I will assume you have at least a grasp of common lingo: dimension, fact, slowly changing dimension types, star and snowflake schemas, etc.
Patterns I present aim to follow one common structure:
Problem (What?) -> Solution(How?) -> Reasons (Why?)->Consequences (Why not?)
The idea is not just presenting a common framework for sharing what works and what doesn’t, but also explain why that is the case and what happens when the patterns are not followed.
These patterns are equally applicable for a Kimball or Inmon religion, so both camps are very welcome to attend.
Sorry, there are no downloads available for this session.
Dmytro Andriychenko's Summary
I am a flexible senior data and systems integration professional who enjoys a challenge of both technical and personal nature. I am particularly intersted in projects involving novel clashes of technologies prompting unusual approaches or organisational challenges. I love performance tuning and optimising IT system infrastructures.
These are main areas of my technical expertise:
- SQL Server Development, especially Enterprise Data Warehouse design and implementation
- Microsoft BI: complete stack of SSIS, SSAS, SSRS and SharePoint BI
- SharePoint administration, configuration and development including Kerberos configuration and troubleshooting
- MicroStrategy, including Microstrategy Architect, administration and development
- Business Objects: Data Integrator and Crystal Reports
I also have experience of designing BI systems end to end from hardware and application tiers to systems integration, requirements engineering, database and ETL design and development.
My decade of IT experience rests on a solid educational platform of two degrees in Economic mathematics and Decision support systems completed with distinction.
I quickly adapt to any team of developers and find a way to improve things and channel the work in the right direction. I find technical challenges exciting and stimulating, I usually get on well with most people and always enjoy sharing my knowledge and experience with others.
Data Integration, Data Warehousing, SQL Server Tuning, Systems Architecture, Data architecture, Database development, design, development and maintenance,
Dmytro Andriychenko's Experience
Public Company; 1001-5000 employees; Computer Software industry
November 2009 – Present (3 years 2 months) Newcastle upon Tyne, United Kingdom
- Lead designer on Finance BI project replacing manual financial consolidation Excel spread mart. The result is fully automatic process based on user-editable business rules engine. I have written high-level and detailed design document and built a working proof-of-concept prototype using T-SQL and SSIS for ETL, SSAS and Excel for presentation and SharePoint for master data management.
- Key role in designing, implementing and troubleshooting a brand new high availability BI Infrastructure for Sage UK based on SQL Server 2012 cluster and four node SharePoint 2010 farm with Kerberos delegated authentication.
- Completed full cycle of an ETL project from requirements engineering and implementation using Business Object Data Integrator (BODI) to release and support. The result of the project was a reduction in the duration of the business process from three days to five minutes.
- Administered over a dozen of SQL Servers instances (2005 and 2012) in all development environments (Dev, PreProd and Production) with over 60 databases including the data warehouse (2TB in size).
- Optimised data warehouse performance by tuning indexes using SQL Server DMVs (dynamic management views) and advising ETL developers on more efficient ways of working with SQL Server.
- Optimised many existing BODI data flows to work better with SQL Server by leveraging native database processing to achieve 3-4 times faster execution (profiling, testing, business analysis, execution plan analysis etc).
- Designed and implemented new high-performance MicroStrategy v9 hardware and software infrastructure achieving better availability and reducing costs by virtualizing DEV environments.
- Designed Data Warehouse for MicroStrategy implementation of human resources reporting.
- Over a series of meetings and networking events negotiated a saving of £20k against software maintenance contract and achieved development environment license complience worth £40k.
Systems Integration Analyst/Developer
September 2007 – October 2009 (2 years 2 months) Newcastle upon Tyne, United Kingdom
- Coordinated a team of five in-house IT professionals and seven India-based (Bangalore) on developing complex integration platform for creating internal data warehouse from disparate bio-informatics data from a multitude of sources, formats and domains.
- Designed, developed and maintained MySQL database for the internal data warehouse.
- Developed ETL processes based on Pentaho Business Intelligence Suite and KNIME.
- Maintained and administered Oracle database backend of chemical information system.
- Designed and implemented a multi-user authoring tool for off-shore team (India) for collecting data on bio-chemical interactions. Supported the off-shore team on using the tool and managed outsourced workload and data exchange between the off-shore team and in-house data warehouse.
- Re-designed IT hardware infrastructure for the company including virtualisation of storage (FC SAN instead of internal storage) and disaster-recovery solution. Changed IT support model saving £15k pa while improving reliability and service level. Moved hosting of MPP solution to in-house server room saving £3k pa.
- Delivered analytical reports to CEO involving complex statistical analysis techniques such as logistic regression and multidimensional scaling.
- Achieved a saving of over 40 thousand pounds through successful managemanent of relationships with third parties (suppliers, partners, etc.).
Systems Analyst/Database architect
September 2004 – August 2007 (3 years) Newcastle upon Tyne, United Kingdom
- Created a complex XSLT transformation for porting the old contents management system (CMS) data into SQL Server 2005 database of the new authoring environment
- Designed, developed and maintained database layer for a drug ontology CMS tool featuring heavy database side business logic. The tool used database-side set operations for making inferences based on the drug ontology relationships.
- Designed, developed and maintained database layer for the company main product http://cks.library.nhs.uk/home. I have also successfully implemented and managed fail-over cluster and backup system for this website based on SQL Server 2005
- Implemented a system of automatic XML data update of one of the main data sources dm+d using SSIS.
- Conducted regular workshops on statistical analysis for the benefit of the clinical authors within the company
The video is not available to view online.