WP_Data Management Best Practices

W H I T E P A P E R

www.persistent.com

—

Business Process Dimensional Model.

Each of the organization's business processes can be

represented by a dimensional model (see section

for a details). As they are conformed, dimensions

5.1

deliver the same content, interpretation and presentation regardless of the business process involved,

so they can be shared across the enterprise DWenvironment, joining tomultiple fact tables representing

various business processes.

—

Physical Data Model

. This is the database model implementation to support the logical model enabling

scalability, high performance and easy maintenance.

DataArchitecture is covered in chapte

of this document, for the dimensional modeling part, and chapter for

the data quality improvement (data cleansing) part.

Application (DW/BI system) Architecture

. This is the processes and tools we apply to the data, a

combination of custom code, home grown utilities, and off-the-shelf tools answering the question

“

how”—how

do we get at this data at its source, have it conform to the data architecture and meet the business

requirements, and move it to a place that's accessible. The balance of the architecture has shifted towards off-

the shelf tools, as they have become more capable of handling a broader range of complex warehouse

requirements. Thus, when planning for the application architecture it is crucial to select the right products to

support it.

In this section

we provide a quick description of the natural separation between the internal workings of the

4.2

data warehouse –the back room– and its public BI face –the front room.

Infrastructure and Security

. Infrastructure provides the underlying foundation for all the architecture

elements described in the previous two points. This includes the server hardware, the disk subsystems, the

networks, the database systems and the desktops. Security is a complex area, intertwined with networking

infrastructure, that the higher-level components take for granted; it must be well understood, as it goes far

beyond recognizing legitimate data warehouse users and giving them specific rights to look at some, but not

all, of the data.

In section

below we will comment on our experience with MPP databases and SMP databases; also,

4.4.1

chapte

focuses on all security aspects of a DW/BI system.

Creating the technical architecture is the first step after the requirements are finalized, it may be done in

conjunction with the process of finalization of the requirements.

The value of technical architecture lies way beyond just its documentation. It is the only systematic way to ensure that

requirements are being tracked and met, and ensuring that communication between all stakeholders is being carried

out at the sufficient level of detail. The building blocks and dependencies are the basis for the project plan. The

architecture also documents the decisions and tradeoffs made to address facets of flexibility, productivity and

maintenance.

4.2 DW/BI systemarchitecturemodel

The Kimball DW/BI system architecture introduced in chapter 4 of

separates the data and processes comprising

[1]

the DW/BI system into the backroom extract, transformation and load (ETL) environment and the front room

presentation area, as illustrated in the following diagram