| Analyzing
Business Requirements |
Analyze the scope of
a project.
- Identify the major subject areas that will
be incorporated into the data warehouse.
|
| Analyze the extent of
a business requirement. |
| Analyze security
requirements. |
| Analyze performance
and scalability requirements. |
| Analyze
maintainability requirements. |
| Analyze human factors
requirements, such as target audience,
localization, accessibility, roaming users, Help,
and special needs. |
| Defining
the Technical Architecture for a Solution |
| Identify which
technologies are appropriate for implementation of
a given business solution. Technologies include
design tools, data transformation tools, storage
tools, presentation access tools, management
tools, and scheduling tools. |
| Choose a data storage
architecture. |
| Developing the Logical Design |
| Identify the sources
of data from the operational databases. |
| Identify the encoding
structure and key structure for integrating all
data. |
| Identify the
filtering requirements for operational data. |
| Assess whether a data
mart schema should be integrated within the
enterprise data warehouse schema. |
| Assess the level of
detail required for data. |
| Deriving
the Physical Design |
| Assess how a given
logical design impacts performance,
maintainability, extensibility, scalability,
availability, and security. |
| Assess whether data
should be queried from a relational database or a
multidimensional database. |
| Choose a schema
design for a relational database. Design options
include normalized, star, or snowflake. |
| Group data into fact
tables and dimension tables by applying
denormalization rules. |
| Creating
Data Services |
| Use Microsoft
ActiveX® Data Objects (ADO), ActiveX Data
Objects Multidimensional (ADO MD),
multidimensional expressions (MDX), or Microsoft
English Query to access or manipulate a data
source. |
| Write SQL statements
that retrieve and summarize data. SQL statements
include SELECT, ROLLUP, CUBE, and HAVING. |
| Replicate data among
data marts. |
| Implementing a Physical Data Warehouse
and Implementing OLAP Services |
| Implement a data
storage architecture by creating and managing
files and filegroups. |
| Use visual database
tools to create databases and database tables that
enforce data integrity and referential
integrity. |
Populate the data
warehouse with data from an external data source
by using Data Transformation Services (DTS).
External data sources include other SQL Server
databases, comma-separated files, delimited files,
and OLE DB for ODBC.
- Track data lineage.
- Store DTS packages in the repository.
|
| Choose an indexing
strategy to optimize performance for relational
decision support. |
| Create, maintain, and
optimize indexes. |
Design the
multidimensional OLAP model.
- Create the dimension hierarchy.
- Create measures.
- Assign member properties.
|
Create and maintain
OLAP aggregations.
- Choose the data storage mechanism,
specifically MOLAP, ROLAP, or HOLAP.
- Build the aggregations.
- Partition data for scalability.
- Perform incremental updates of cubes.
- Merge incremental updates with the main
partition.
- Monitor and optimize aggregations based on
usage.
|
| Implement security
for databases and cubes. |
| Configure SQL Server
options for optimal performance. |
| Maintaining a Database and VLDB |
| Monitor and optimize
the amount of space in the database. |
Perform backup
procedures, restore procedures, and roll-off
procedures on the data warehouse.
- Develop archiving procedures.
- Develop methods for refreshing data.
|
| Perform disaster
recovery procedures on the database. |
| Maintain database
indexing. |
| Verify database
consistency. |
| Monitor and optimize
query performance. |
Automate maintenance
tasks by using alerts and agents.
- Schedule DTS events.
- Schedule backup events.
- Schedule replication events.
|
