Database Answers

 

 

 

4.3 Stage 3 – Data Marts

4.3.1 Overview

4.3.2 Best Practice

Question : How do I improve the performance of my Data Mart ?

Every DBMS produces what is called an Execution Plan for every SELECT statement.

The steps to improving the performance involve checking this Execution Plan against the Indexes that exist, and making sure that the Query Optimizer has used the appropriate Indexes to obtain the best performance.

This is a specialized area where DBA’s spend a lot of their time when they are looking after production databases where speed is a mission-critical factor.

 

Data Marts are always created to support Business Intelligence, which includes Performance Reports, Balanced Scorecards, Dashboards, Key Performance Indicators and so on.

Best practice always requires user involvement and a generic design to support a flexible approach to meeting changing requirements.

Users will always want changes to their first specifications of their requirements.

The insight that they obtain from the first Reports helps them identify more precisely what their long-term requirements will be.

Therefore flexibility is important.

A well-designed Data Mart will anticipate the areas where flexibility is required.

The design process should always follow two steps :-

 

4.3.3 Templates

A range of Data Mart diagrams is available in the Case Study Chapters.

 

This example is a Data Model for a Generic Data Mart for Customers.

The Customer Types include Parking Ticket Holders and Voters

 

 

 

 

 

 

If you have a requirement that is not included here, please contact us by email at barryw@databaseanswers.org and we will help you to get started.

 

 

4.3.4 Tools

Data Modeling Tools are used to create Data Marts and here is a sample of the most popular Tools available :-

 

4.3.5 Tutorials

This Tutorial defines a step-by-step Approach to the design of a Generic Data Mart.

Performance tuning is important after the Data Mart is up and running.

Microsoft’s Unified Data Model (UDM) is a Generic approach.

 

Here is a series of Steps in designing a Database :-

Step 1. Establish the User Requirements

Step 2. Identify the Data Sources

Step 3. Define the Data Mart and ensure that that the design supports the User Requirements.

Step 4. Obtain Sample data

Step 5. Produce first draft Reports and review with the Users.

Step 6. Confirm Data Mart design.

Step 7. Verify end-to-end data flows..

 

 

4.3.6 How do I ?

The first step is to think about the Data Mart as a place where you simply throw all available data and provide ‘hooks’ so that any combination of data can easily be retrieved.

Briefly, the Key fields in Tables involved become Dimensions in a Data Mart.

Facts include all the basic data plus any derived data, typically averages, percentages and totals under various headings.

 

 

 

4.3.7 Qualities for Success in Designing Data Marts

To be successful in designing Data Marts it is important to have a talent for visualizing the User’s Requirements and for translating this to a formal design of Dimensions and Facts, together with the most important aspect, which is the derivation of the data required from the underlying basic data.